Migrate Data (CDC) between Heterogeneous Databases using Striim

Published in

Google Cloud - Community

7 min readMar 10, 2023

In this article an overview of how to use Striim to migrate data between heterogeneous databases will be discussed for e.g. migration of data from source database i.e. SQL Server to destination database i.e. MySQL

Following will be discussed

Deploy Cloud SQL Server (Source) database and MySQL (Destination) database instance on GCP
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 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 GCP 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>;

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

For database execute following

USE <DATABASE_NAME>;

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

2. For Table execute 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 is CDC is enabled execute 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 following steps

Execute the create database and create tables script used in Step 2

Note: In this example, the database and table with same name is created

Step 6: Create a new striim cloud service

Create a striim service

Note: Use default configuration in advanced configuration

striim cloude service creation in progress

Provide a valid app name

Source Database Configuration

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>

Provide Advanced Settings

Enter source database username and password

Note: For simplicity use default configuration under advanced configuration section

3. Source database is configured

4. Test source database connectivity

Destination Database Configuration

Click on striim icon followed by ‘+’ sign

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

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

Step 9: Provide database (source and destination) access to striim service as per the below picture

Striim IP can be found here

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

pre migration source and destination database state

Start striim service

Deploy the app

2. Start the app

3. App started and ready for data migration

app is running and ready for data migration

Step 11: Add data to SQL Server database

Insert data in source database

2. Striim app data preview

app preview for data migration from source to destination

Step 12: Verify MySQL database for all data migration

Destination database state

2. 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
GCP 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 factor in comparison to other alternatives

Remarks

In this article both, source and destination are on GCP. 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.