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.

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>;

1. 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>’;

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

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

Note: Use default configuration in advanced configuration

Provide a valid app name

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://:;DatabaseName=
• Tables for e.g. dbo.
  

  Provide Advanced Settings

  Enter source database username and password

  Note: For simplicity use default configuration under advanced configuration section

  1. Source database is configured
  
  1. Test source database connectivity
  

  Step 8: Destination Database Configuration

  1. Click on the striim icon followed by the ‘+’ sign
  
  1. Provide destination database configuration
  • Adapter for e.g. DatabaseWriter
  • Connection url for e.g. jdbc:sqlserver://:/
  • Username and Password
  • Tables for e.g. dbo.,.;
  

  Provide Advanced Settings

  Note: For simplicity, use default configuration under advanced configuration section

  
  1. Click save 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
  
  1. Provide striim access to the database for both source and destination databases. Below picture shows the source access for striim
  

  Step 10: Deploy and run striim app for one time migration

  Before migration the database state

  

  Step 11: Start striim service

  1. Deploy the app
  
  1. Start the app
  
  1. App started and ready for data migration
  

  Step 12: Add data to SQL Server database

  1. Insert data in source database

     

  2. Striim app data preview

  

  Step 13: Verify MySQL database for all data migration

  1. Destination database state
  
  1. After migration database state (both source and destination)
  

  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