Migrate Data (CDC) between Heterogeneous Databases using Striim

[damlevinay11]

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

1. In the Google Cloud console, go to the SQL Server Instances page
   Go to Cloud SQL Instances
2. Click Choose SQL Server
3. Enter name for Instance ID
4. Enter a Password for the SQL Server
5. 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)

1. Create database

CREATE DATABASE <DATABASE_NAME>;

2. 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

1. For database, execute the following

USE <DATABASE_NAME>;

EXEC msdb.dbo.gcloudsql_cdc_enable_db ‘<DATABASE_NAME>’;

2. 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’

3. 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

1. In the Google Cloud console, go to the Cloud SQL Instances page
   Go to Cloud SQL Instances
2. Click Create Instance
3. Click Choose MySQL Server
4. Enter name for Instance ID
5. Enter a Username and Password for the MySQL
6. 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

1. Select SQL Server CDC

2. 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

3. Source database is configured

source database successfully configured

4. Test source database connectivity

source database connectivity test

Step 8: Destination Database Configuration

1. Click on the striim icon followed by the ‘+’ sign

add destination database

2. 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

3. 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

1. Striim IP can be found here

striim cloud service details page

2. 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

1. Deploy the app

deploy the app

2. Start the app

start the app

3. App started and ready for data migration

app is running and ready for data migration

Step 12: Add data to SQL Server database

1. Insert data in source database
   CLOUD SQL, SQL server insert data
   
   
2. Striim app data preview

app preview for data migration from source to destination

Step 13: Verify MySQL database for all data migration

1. Destination database state

data from SQL Server migrated to MySQL

2. After migration database state (both source and destination)

post migration source and destination database state

Advantages

1. Easy and one time configuration for data transfer
2. Supports data migration between both homogeneous and heterogeneous databases
3. Supports all databases across any platform
4. Available in Google Marketplace
5. Striim support is good, fast, and helpful
6. 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

• https://cloud.google.com/blog/products/databases/new-cloud-based-cdc-replication-across-databases
• https://cloud.google.com/sql/docs/sqlserver
• https://cloud.google.com/sql/docs/mysql
• https://cloud.google.com/database-migration/docs
• https://www.striim.com/docs/en/what-s-new-in-striim-cloud-4-1-2.html