In this article, we''ll cover an overview of how to use Striim to migrate data between heterogeneous databases, for example, migrating data from a source database (i.e. SQL Server) to the destination database (i.e. MySQL).
In particular, we'll discuss how to:
- Deploy Cloud SQL Server (Source) database and MySQL (Destination) database instance on Google Cloud
- Enable Change Data Capture (CDC) in source database
- Use Striim Cloud for migration
- Create and run a Striim Service/App for database migration
- View the results in MySQL (Destination Database)
Database terminology
The most important data migration terms for these documents are defined as follows:
- Source database: A database that contains data to be migrated to one or more target databases.
- Target database: A database that receives data migrated from one or more source databases.
- Database migration: A migration of data from source databases to target databases with the goal of turning down the source database systems after the migration completes. The entire dataset, or a subset, is migrated.
- Homogeneous migration: A migration from source databases to target databases where the source and target databases are of the same database management system from the same provider.
- Heterogeneous migration: A migration from source databases to target databases where the source and target databases are of different database management systems from different providers.
Striim
Striim is a streaming Extract, Transform, and Load (ETL) data platform that enables real-time, zero downtime replication and transformation of data from a source database to one or more target database systems. It captures database operations like inserts, updates, and deletions from the transaction logs of the source database and replicates these changes to the target database using log-based change data capture (CDC) technology. Thanks to its user-friendly interface, users can quickly create their own pipelines to move and transform data with low or no code.
Image Source: Striim.com
Prerequisites
- For free trials, go to https://go2.striim.com/free-trial. You can choose between Striim Platform and Striim Cloud. For more information about Striim's partnership with Google, AWS, and Azure, please contact: partners@striim.com
- An available Striim instance (Version 4.1.2.0A or higher) in a cloud/on-premise environment that has access to the source and target database
- Access to Google Cloud Console and privileges to deploy Cloud SQL, SQL Server, and Cloud SQL MySQL instances
Continuous Migration (Change Data Capture)
Step 1: Create Cloud SQL, SQL Server instance
- In the Google Cloud console, go to the SQL Server Instances page
Go to Cloud SQL Instances
- Click Choose SQL Server
- Enter name for Instance ID
- Enter a Password for the SQL Server
- Click Create.
You’re returned to the instances list. You can click the new instance right away to see the details, but it won’t be available for other operations until it initialises and starts.
Note: In this example, the instance is created using default settings with a public IP address
Step 2: Connect to SQL Server using any database client (Azure Database Studio is used)
- Create database
CREATE DATABASE <DATABASE_NAME>;
- Create table in database created in 1
USE <DATABASE_NAME>;
CREATE TABLE <TABLE_NAME>(
<PRIMARY_KEY_COLUMN> int NOT NULL,
<PRIMARY_KEY_COLUMN> varchar(255) NOT NULL,
<PRIMARY_KEY_COLUMN> varchar(255) NULL,
<PRIMARY_KEY_COLUMN> int NULL
);
Step 3: Enable CDC (Change Data Capture) in Cloud SQL, SQL Server database/table
Enable CDC for source database on database and table level
- For database, execute the following
USE <DATABASE_NAME>;
EXEC msdb.dbo.gcloudsql_cdc_enable_db ‘<DATABASE_NAME>’;
- For Table, execute the following
USE <DATABASE_NAME>;
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name = N’<TABLE_NAME>’,
@role_name = N’STRIIM_READER’
- To verify if CDC is enabled, execute the following
SELECT name, is_cdc_enabled FROM sys.databases;
The is_cdc_enabled flag should be set to 1
Step 4: Create Cloud SQL MySQL instance
- In the Google Cloud console, go to the Cloud SQL Instances page
Go to Cloud SQL Instances
- Click Create Instance
- Click Choose MySQL Server
- Enter name for Instance ID
- Enter a Username and Password for the MySQL
- Click Create.
You’re returned to the instances list. You can click the new instance right away to see the details, but it won’t be available for other operations until it initialises and starts.
Note: In this example, the instance is created using default settings with a public IP address
Step 5: Connect to MySQL instance and perform the following steps
Execute the create database and create tables script used in Step 2.
Note: In this example, the database and table with the same name is created
Step 6: Create a new striim cloud service
Create a striim service
Striim cloud service page
Note: Use default configuration in advanced configuration
Striim cloud service creation in progress
Striim cloud service created
Provide a valid app name
create an app
Step 7: Source Database Configuration
- Select SQL Server CDC
- Provide Database details
- Database name for e.g. Organization
- Connection url for e.g. jdbc:sqlserver://<Database Server IP>:<Port>;DatabaseName=<Database Name>
- Tables for e.g. dbo.<Table Name>
CDC source database configuration
Provide Advanced Settings
Enter source database username and password
Note: For simplicity use default configuration under advanced configuration section
- Source database is configured
source database successfully configured
- Test source database connectivity
source database connectivity test
Step 8: Destination Database Configuration
- Click on the striim icon followed by the ‘+’ sign
add destination database
- Provide destination database configuration
- Adapter for e.g. DatabaseWriter
- Connection url for e.g. jdbc:sqlserver://<Database Server IP>:<Port>/<DatabaseName>
- Username and Password
- Tables for e.g. dbo.<Source Table Name>,<Destination Database Name>.<Destination Table Name>;
destination database configuration page
Provide Advanced Settings
Note: For simplicity, use default configuration under advanced configuration section
destination database advanced configuration page
- Click save and app is ready
save configuration and app is ready
Step 9: Provide database (source and destination) access to striim service as per the below picture
- Striim IP can be found here
striim cloud service details page
- Provide striim access to the database for both source and destination databases. Below picture shows the source access for striim
add striim IP to Cloud SQL database
Step 10: Deploy and run striim app for one time migration
Before migration the database state
pre migration source and destination database state
Step 11: Start striim service
- Deploy the app
deploy the app
- Start the app
start the app
- App started and ready for data migration
app is running and ready for data migration
Step 12: Add data to SQL Server database
- Insert data in source database
CLOUD SQL, SQL server insert data
- Striim app data preview
app preview for data migration from source to destination
Step 13: Verify MySQL database for all data migration
- Destination database state
data from SQL Server migrated to MySQL
- After migration database state (both source and destination)
post migration source and destination database state
Advantages
- Easy and one time configuration for data transfer
- Supports data migration between both homogeneous and heterogeneous databases
- Supports all databases across any platform
- Available in Google Marketplace
- Striim support is good, fast, and helpful
- Google Cloud DMS does not support few heterogeneous database migration, in such cases, striim could be ideal solution for migration
Disadvantages
Cost could be one of the factors to consider in comparison to other alternatives.
Remarks
In this article, both the source and destination database are on Google Cloud. The suggested solution can be used irrespective of whether the source database is on any other cloud or on-prem. The appropriate source database configuration needs to be configured and everything will work as it is.
References