Online Data Migration from SQL Server to Cloud Spanner using Striim

This post will focus on the implementation of a continuous migration from SQL Server to Cloud Spanner using Striim. The explanation will include steps for configuring and initial load and a continuous data replication using Change Data Capture. this is enablement post and doesn’t talk about a full scale database migration and its challenges. This is a “how to” post and doesn’t talk about a full scale database migration and challenges you face when considering a heterogeneous database migration.

Set up a Demo SQL Server Source Database

1. Setup a Virtual Machine with SQL Server Standard Edition Preinstalled using the the below GCLOUD commands in CloudShell.

Note – Make sure to update the image to latest version before executing. You can check the latest image name from Compute Engine > Images > Filter based on sql-2019 and you will find the latest available image name. See below for reference

Once you have selected the correct image replace export IMAGE='sql-2019-standard-windows-2022-dc-v20220513' in below script with the correct image name. You will also need to update the Project ID and Public IP of your laptop to allow RDP access to the VM through firewall.

export GCP_PROJECT_ID='xxxxxxxxxxxxxx'
export REGION='us-central1'
export ZONE='us-central1-a'
export NETWORK_NAME='network-1'
export PUBLIC_SUBNET='publicnet'
export PUBLIC_SUBNET_RANGE='10.0.0.0/24'
export MY_IP='<<YOUR PUBLIC IP HERE>>'
export HOST_TAG='mssql'
export HOST_NAME='sqlserver-1'
export IMAGE='sql-2019-standard-windows-2022-dc-v20220513'
gcloud services enable compute.googleapis.com
gcloud compute networks create $NETWORK_NAME \
    --project=$GCP_PROJECT_ID \
    --subnet-mode=custom
gcloud compute networks subnets create $PUBLIC_SUBNET \
    --project=$GCP_PROJECT_ID \
    --range=$PUBLIC_SUBNET_RANGE \
    --network=$NETWORK_NAME \
    --region=$REGION \
    --enable-private-ip-google-access
gcloud compute --project=$GCP_PROJECT_ID firewall-rules create allow-rdp-access \
    --direction=INGRESS \
    --priority=1000 \
    --network=$NETWORK_NAME \
    --action=ALLOW \
    --rules=tcp:3389 \
    --source-ranges=$MY_IP \
    --target-tags=$HOST_TAG
gcloud compute --project=$GCP_PROJECT_ID firewall-rules create allow-mssql-access \
    --direction=INGRESS \
    --priority=1000 \
    --network=$NETWORK_NAME \
    --action=ALLOW \
    --rules=tcp:1433 \
    --source-ranges=$PUBLIC_SUBNET_RANGE \
    --target-tags=$HOST_TAG
gcloud beta compute --project=$GCP_PROJECT_ID instances create $HOST_NAME \
    --zone=$ZONE \
    --machine-type=n2d-standard-2 \
    --subnet=$PUBLIC_SUBNET \
    --network-tier=PREMIUM \
    --maintenance-policy=MIGRATE \
    --tags=$HOST_TAG \
    --image=$IMAGE \
    --image-project=windows-sql-cloud \
    --boot-disk-size=100GB \
    --boot-disk-type=pd-balanced \
    --boot-disk-device-name=$HOST_NAME

2. RDP to the Windows server and connect to the SQL Server instance using SSMS. Once connected to the instance you will need to setup a sample database for migration.

3. Create a database named sample. Under the database create following table and add dummy data to the table. This is going to be our source data.

Create Database [sample]
GO
USE [sample]
GO
EXEC sp_changedbowner 'sa'
GO
CREATE TABLE dbo.Employee (
    EmpID INT IDENTITY(1,1), 
    EmpName VARCHAR(50) NOT NULL, 
    Designation VARCHAR(50) NULL, 
    Department VARCHAR(50) NULL, 
    JoiningDate DATETIME NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED (EmpID)
)
GO
INSERT INTO dbo.Employee 
	(EmpName, Designation, Department, JoiningDate)
VALUES 
	('Sandeep Arora', 'CLOUD ARCHITECT', 'IT', GETDATE()),
	('Shivam Bhatia', 'SR ACCOUNTANT', 'ACCOUNTS', GETDATE()),
	('Mujahid Ahmed', 'SR DBA', 'IT', GETDATE()),
	('Bhupinder Singh', 'SR DBA', 'IT', GETDATE()),
	('Varun Saluja', 'SR PROGRAMMER', 'IT', GETDATE()),
	('Rajeev Bhati', 'DIRECTOR', 'IT', GETDATE())
GO
select * from dbo.Employee 
GO

4. Create a user that will be used by Striim to authenticate to SQL Server sample database.

USE [master]
GO
CREATE LOGIN [striim] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [sample]
GO
CREATE USER [striim] FOR LOGIN [striim]
GO
USE [sample]
GO
ALTER ROLE [db_owner] ADD MEMBER [striim]
GO

Note: The image that I used comes with Windows authentication enabled only for SQL Server. You will have to make sure that SQL authentication is enabled and you have to restart the SQL Server instance for changes to take affect.

5. Enable CDC on the [sample] database and [dbo.Employee] table.

USE [sample]
GO  
EXEC sys.sp_cdc_enable_db
GO
EXEC sys.sp_cdc_enable_table
       @source_schema = N'dbo',
       @source_name = N'Employee',
       @role_name = NULL,
       @supports_net_changes = 0
GO

Note: I had to update the SQL Server Instance Name to match the hostname before I could successfully run the above command. Use the below SQL code for the same and then restart SQL Server instance. Check Microsoft Documentation on renaming SQL Server instance for more details.

EXEC sp_dropserver 'INST-INSTALL-SQ';  
GO  
EXEC sp_addserver 'SQLSERVER-1', local;  
GO

Set up the Striim Migration Software

You will need to set up an instance of the Striim Server through the Google Cloud Marketplace.

1. Ensure that you have selected your GCP Project that has billing enabled. Click Navigation menu > Marketplace. In the search box type Striim and hit Enter. Select from the options listed on the screen. Alternatively you can use the direct link to navigate to the marketplace. The licensing options are highlighted in red on the screen below. If you need pay as you go license for Striim use metered option. I am using the one with 7 days free trial as Striim can be very expensive to setup.

• Click Launch. You will be presented with configuration options for the Striim server. I am using the below configuration settings. Make sure you place the Striim server in network-1 where SQL Server is running.

2. Check the box for “I accept the GCP Marketplace Terms of Service”, choose whether to authorize information sharing with Striim.
3. Leave the other options as it is, click Deploy.
4. You will be redirected to Deployment Manager page, once the solution is provisioned, the deployment details will show. Take note of the name of the VM and Site Address.
5. Click Visit the site. This will open up the Striim configuration wizard in a new browser tab. If there is a redirect notice, you can safely ignore it and click through the link provided.
6. Accept the EULA by clicking Accept Striim EULA and Continue.
7. Configure your Striim installation:

Be sure to note the cluster name and the cluster and administrator passwords, as we will be using them later in this lab.

13. Click Save and Continue.
14. In the next screen will ask for license details. Leave the field empty and click Save and Continue.
15. Click Launch and then click the green Log In button.
16. Log in using the admin user and the administrator password. This will take you the Striim administrator console (screen shown below), and we are now ready to setup a migration app.

Note – Username is admin and password is Admin Password set at time of installation using console.

Note: The “admin” user is a built-in user that must be used in order for login to succeed. The firewall rule to allow Striim to connect to SQL Server on default port 1433 was opened while creating the SQL Server VM instance. SQL Server VM allows port 1433 for any VM in the publicnet subnet.

Set up the connector for SQL Server

You will now follow the Striim online documentation and use the Microsoft JDBC Driver for SQL Server to connect Striim to your SQL Server instance.

Note – For this step you will need to setup a firewall rule to allow SSH access to Striim server.

export TAG='striim-ssh'
export STRIIM_VM_NAME='striim-1-vm'
export STRIIM_VM_ZONE='us-central1-a'
export GCP_PROJECT_ID='xxxxxxxxxxxxxxxxxxxxxxxxxx'
export NETWORK_NAME='network-1'
gcloud compute --project=$GCP_PROJECT_ID firewall-rules create allow-ssh-access \
    --direction=INGRESS \
    --priority=1000 \
    --network=$NETWORK_NAME \
    --action=ALLOW \
    --rules=tcp:22 \
    --source-ranges='0.0.0.0/0' \
    --target-tags=$TAG
gcloud compute instances add-tags $STRIIM_VM_NAME \
    --zone $STRIIM_VM_ZONE \
    --tags $TAG

1. Navigate to the Virtual Machines console and ssh to the Striim virtual machine.
2. To read or write or write to Microsoft SQL Server, Azure SQL Database, or Azure SQL Data Warehouse, the Microsoft JDBC Driver for SQL Server must be present in Striim’s Java class path. The JAR file can be downloaded from here.
3. Download the driver and copy over the connector to the VM and unpack it. As of creation of this post the latest version for JDFC driver for SQL Server is – Microsoft JDBC Driver 7.2 for SQL Server. Download the file (sqljdbc_7.2.2.0_enu.tar.gz) locally and then scp it to the Striim server.

tar -xvzf sqljdbc_7.2.2.0_enu.tar.gz

3. Copy the file to the Striim library path, allow it to be executable, and change ownership of it. I basically downloaded the file locally to my laptop and then uploaded to Cloud Shell and then used gcloud scp to put it in the /tmp directory of the Striim server.

cd sqljdbc_7.2/enu/
sudo cp mssql-jdbc-7.2.2.jre8.jar /opt/striim/lib/
sudo chmod +x /opt/striim/lib/mssql-jdbc-7.2.2.jre8.jar
sudo chown striim /opt/striim/lib/mssql-jdbc-7.2.2.jre8.jar

4. We now need to restart the Striim server to recognize the new library. We can do this in the same SSH session.

sudo systemctl stop striim-node
sudo systemctl stop striim-dbms
sudo systemctl start striim-dbms
sudo systemctl start striim-node

5. Open the /opt/striim/conf/startUp.properties in your favorite file editor. Use elevated permissions to edit and save.

6. Add\Update MEM_MAX=12288m (12 GB Memory was allocated for Striim out of 15 GB RAM available on the instance). Adjust the memory according to RAM available on the server. Check official documentation on adjusting memory for more details. Save and Exit the editor

7. Restart Striim Server

sudo systemctl stop striim-node
sudo systemctl stop striim-dbms
sudo systemctl start striim-dbms
sudo systemctl start striim-node

Create a Cloud Spanner access key

Striim requires an access key so that it can connect to Cloud Spanner. Here are the required steps you need to do:

1. Create a service account for Striim to use to connect to Cloud Spanner. Run the below command in the Cloud Shell.

gcloud iam service-accounts create striim-sa --display-name striim-sa
export sa_striim_spanner=$(gcloud iam service-accounts list --filter="displayName:striim-sa" --format='value(email)')
export PROJECT=$(gcloud info --format='value(config.project)')
gcloud projects add-iam-policy-binding $PROJECT --role roles/spanner.databaseUser --member serviceAccount:$sa_striim_spanner
gcloud iam service-accounts keys create ~/striim-sa.json --iam-account $sa_striim_spanner

This will create a key called striim-sa.json in your home path.

2. Now we need to move the key you just created to the Striim VM. Identify the name of the virtual machine in which Striim is deployed.
3. Copy the JSON file to the Striim server with the following command. It might be that this causes an SSH key to be generated (in this case you have to provide a passphrase). Confirm when promoted to continue with Y and leave the passphrase field empty.

export STRIIM_VM_NAME='striim-1-vm'
export STRIIM_VM_ZONE='us-central1-a'
gcloud compute scp striim-sa.json $STRIIM_VM_NAME:/tmp --zone=$STRIIM_VM_ZONE

Important: There might be a workgroup error displayed in the console – you can safely ignore that. check /tmp directory on Striim server and the key should be copied over to the directory.

4. Now move back to the SSH console for the Striim VM and move the JSON file on the Striim VM that you copied in the last step to user home directory to the /opt/striim directory.

sudo cp /tmp/striim-sa.json /opt/striim 
sudo chown striim /opt/striim/striim-sa.json

You are now ready to create a Striim application for migration of data from SQL Server to Spanner.

Create a Destination Spanner Database

Now we will need a destination schema where we will migrate the data to in Spanner. The SQL Server and Spanner DDL statements are almost alike but you will have to pay attention to the data types. For Example:

1. INT translates to INT64 ()
2. VARCHAR translates to STRING
3. DATETIME translates to TIMESTAMP

You will have to do schema conversion before you can implement an equivalent schema in spanner database. Run the below statements in CloudShell.

export INSTANCE_NAME='spanner-demo'
export DATABASE_NAME='sample'
export CONFIG='regional-us-central1'
gcloud services enable spanner.googleapis.com
gcloud beta spanner instances create $INSTANCE_NAME \
--config=$CONFIG \
--description=$INSTANCE_NAME --processing-units=100
gcloud beta spanner databases create $DATABASE_NAME \
--instance=$INSTANCE_NAME \
--ddl='CREATE TABLE employee (EmpID STRING(50) NOT NULL, EmpName STRING(50) NOT NULL, Designation STRING(50), Department STRING(50), JoiningDate TIMESTAMP) PRIMARY KEY (EmpID)'

Note: Using integer values for Primary key will be anti-pattern if the values increases monotonically. The EmpID column in SQL Server is identity column so we will loading that as String in Spanner to avoid hotspots. Check Schema design best practices for Spanner to learn more.

You will have the database and table successfully created and you can verify the schema upon creation under the Spanner Console. INSTANCE > DATABASE > TABLE

Also, checkout the Striim data conversion utility for converting schema of large set of tables. Also, view datatype support and mapping from SQL Server to Spanner before proceeding with an actual migration project. Remember this is enablement post and doesn’t talk about a full scale database migration and its challenges.

Migration Strategy

The migration will involve 3 major steps

1. Load Full Data (Initial Load of Data) from SQL Server to Spanner
2. Switch to ongoing data replication between SQL Server and Spanner using CDC
3. Cutover and switch application to Spanner

Each step is discussed in detail in following sections of the post

Step 1 : Create an Online Migration App for Initial Database Load from SQL Server to Cloud Spanner

Copy the below TQL code and paste it in your favorite code editor on local PC. Modify the code by replacing,

1. [SQL SERVER DNS NAME OR IP ADDRESS] – Your SQL Server Source Instance IP Address or DNS Name.

2. [YOUR PASSWORD GOES HERE] – The password for [striim] user that was created in SQL Server. Striim encrypts the password so what I would recommend it to import this app into Striim GUI and then supply the password in the GUI. In our case the [striim] user password for SQL Server authentication is set to – P@ssw0rd. The encrypted format for P@ssw0rd on my Striim server was hdkwLUFVEcsguRY5ADBxjw==

You can use the same password as in the code to import the app (otherwise the import will fail) and then update the password in GUI and test the connection. Striim should automatically encrypt the password when supplied in GUI and you save the app.

CREATE APPLICATION initial_database_load USE EXCEPTIONSTORE TTL : '7d' ;

CREATE FLOW employee_tbl;

CREATE SOURCE sqlserver_employee_tbl USING DatabaseReader  ( 
Query: 'SELECT cast(EmpID as varchar(50)) as EmpID, EmpName, Designation, Department, JoiningDate FROM dbo.Employee',
  FetchSize: 100, 
  Username: 'striim', 
  ConnectionURL: 'jdbc:sqlserver://[SQL SERVER DNS NAME OR IP ADDRESS]:1433;DatabaseName=sample', 
  QuiesceOnILCompletion: false, 
  Password_encrypted: 'true', 
  DatabaseProviderType: 'SQLServer', 
  Tables: 'dbo.Employee', 
  Password: 'hdkwLUFVEcsguRY5ADBxjw==' ) 
OUTPUT TO sqlserver_employee_tbl_initial_load_stream  ;

CREATE TARGET spanner_employee_tbl USING Global.SpannerWriter  ( 
Tables: 'dbo.Employee,sample.employee', 
  CheckpointTable: 'CHKPOINT', 
  BatchPolicy: 'EventCount: 1000, Interval: 60s', 
  ParallelThreads: '2', 
  ServiceAccountKey: '/opt/striim/striim-sa.json', 
  InstanceID: 'spanner-demo' ) 
INPUT FROM sqlserver_employee_tbl_initial_load_stream  ;

END FLOW employee_tbl;

END APPLICATION initial_database_load;

3. Once the file is updated locally after replacing placeholders, save it as initial_database_load.tql. Next step will be to import the app into Striim. Follow below steps to import the app.

Steps to import the App into Striim

1. Navigate to Apps Section in the UI

2. Click on Add App button on the top right hand corner

3. Then use the import TQL file button to import an existing Striim app written in TQL format above.

4. Add the App to default namespace or you can add your own namespace by typing to add new namespace. Add a namespace migration for this setup.

5. Leave the passphrase section empty.
6. At bottom right click on the IMPORT button and this should successfully import the App into Striim.

Capture SQL Server Current LSN Number

Log into SQL Server and get the current LSN details. We will need that while switching from initial load to change capture mode.

SELECT sys.fn_cdc_get_max_lsn() AS max_lsn;

Run the Striim App

1. To run a Striim app you will need to first Deploy the app. Right click on the 3 dots on the side of the App and then click on the deploy button. Use the default deployment group to deploy all Flows in the App. Each flow represents a migration job for each table.

2. Once the App is deployed by click on the 3 dots you should be able to Start the app

3. To monitor the progress of the app click on the Monitor App button.
4. You can deploy and execute multiple apps at the same time using the same process.
5. No Changes can be made to the App while it is deployed. You will need to Undeploy the App first. To Undeploy the App click on the 3 dots presented on the right hand side of the app and click on Undeploy button.
6. Click on Manage Flow to open the App in the Editor mode.
7. To Arrange the App in a separate group for manageability and grouping all apps click on the Move to group button.

Verify Initial Load on Spanner

You can stop the app once all data transfer is completed and undeploy the initial_database_load app. The below image shows a successful initial load. The app doesn’t give a completion status and continues to run even after data transfer is complete. However, it the app will show now messages being transferred after the initial load is complete. You can run a select count(*) statement on SQL Server source and Spanner destination to know when the load is complete. Keep this is mind if the source still had data being written to it then the count won’t match and in that case you can use the max function on a column something like timestamp on source and map it to Spanner table max value of timestamp and if that matches that would be an indication that initial load is complete.

The next steps will be to setup ongoing data replication using CDC.

Step 2 : Create an Online Migration App for Continuous Data Replication from SQL Server to Cloud Spanner using CDC

1. Create an sql_cdc_to_spanner.tql file locally using below script. Make sure to update the ConnectionURL: [SQL Server Source] and InstanceID: [Spanner Destination] in the TQL script file to point to correct SQL Server Instance

CREATE APPLICATION sql_cdc_to_spanner;

CREATE FLOW Employee_CDC;

CREATE OR REPLACE SOURCE SQLCDC_Employee USING MSSqlReader  ( 
TransactionSupport: false, 
  FetchTransactionMetadata: false, 
  Compression: false, 
  connectionRetryPolicy: 'timeOut=30, retryInterval=30, maxRetries=3', 
  Password_encrypted: 'true', 
  ConnectionURL: 'jdbc:sqlserver://[SQL SERVER DNS NAME OR IP ADDRESS]:1433;DatabaseName=Sample', 
  Password: 'hdkwLUFVEcsguRY5ADBxjw==', 
  Username: 'striim', 
  Fetchsize: 0, 
  adapterName: 'MSSqlReader', 
  DatabaseName: 'Sample', 
  ConnectionPoolSize: 10, 
  StartPosition: 'EOF', 
  cdcRoleName: 'STRIIM_READER', 
  Tables: 'dbo.Employee', 
  FilterTransactionBoundaries: true, 
  SendBeforeImage: true, 
  AutoDisableTableCDC: false ) 
OUTPUT TO SQLCDC_Employee_Stream  ;

CREATE OR REPLACE TARGET Spanner_Employee USING Global.SpannerWriter  ( 
CheckpointTable: 'CHKPOINT', 
  BatchPolicy: 'EventCount: 1000, Interval: 60s', 
  IgnorableExceptionCode: '6', 
  adapterName: 'SpannerWriter', 
  Tables: 'dbo.Employee,sample.employee ColumnMap(EmpID=EmpID,EmpName=EmpName,Designation=Designation,Department=Department,JoiningDate=JoiningDate)', 
  ServiceAccountKey: '/opt/striim/striim-sa.json', 
  InstanceID: 'spanner-demo' ) 
INPUT FROM SQLCDC_Employee_Stream;

END FLOW Employee_CDC;

END APPLICATION sql_cdc_to_spanner;

2. Import the sql_cdc_to_spanner app to Striim under migration namespace using same steps used for initial load. See Steps to import the App into Striim above for more details.

3. Use the following settings for the CDC reader in the sql_cdc_to_spanner App

SQL Server (MSSQL Reader): set Start Position to the LSN you recorded before performing the initial load.

Use Manage Flow on the imported App to include the Start Position.

—– OR —–

3. Alternatively before importing the TQL you can use the Find and Replace feature of the code editor to set the StartPosition and then import the TQL. This is LSN you recorded before performing the initial load. Replace EOF with LSN.

4. For the Spanner Writer’s you will need to set the Ignorable Exception Code property and any duplicates to spanner from SQL Server will be ignored because of Primary key constraint. The code is already set in the TQL script. No new action required.
   • Spanner: 6, SQL Error [6]: ALREADY_EXISTS (6 is the error code for primary key constraint violation)

Verify Data Replication to Spanner using CDC

1. Insert new rows in SQL Server Employee table

USE [sample]
GO
INSERT INTO dbo.Employee 
	(EmpName, Designation, Department, JoiningDate)
VALUES 
	('John Schulz', 'CLOUD ARCHITECT', 'IT', GETDATE()),
	('Sofia Carpentar', 'SR ACCOUNTANT', 'ACCOUNTS', GETDATE())
GO
select * from dbo.Employee 
GO

2. Verify that new transactions have been replicated over to spanner successfully. The below image shows that now data replication is working as expected.

Step 3 : Database Cutover

1. Stop the client application. We don’t have an application running in the demo but in case if this was a real life application then you would need to stop that application
2. Verify no more records are pending replication to Spanner by performing a count on all tables.
3. Stop the sql_cdc_to_spanner App and undeploy.
4. Switch application to now read and write from spanner database

Note – Application integration with Spanner database is outside the scope of this post.

Summary

This is a high level demonstration of how to perform an online data migration from SQL Server Spanner using CDC. As highlighted before there are many challenges with a complex migration like this one. Some of the challenges include,

1. How to handle data Type conversions and transformations for unsupported data types?
2. We have only discussed about migrating one table. How can you create one app with separate workflow for multiple tables?
3. How to do load test and measure post migration performance?
4. How to rollback from Spanner to SQL Server?
5. What about application integration and cutover?

In case you are embarking on a similar migration journey yourself talk to us and we are happy to provide guidance and expertise to carry out complex migration not just from SQL Server to Spanner but from any data source to any other data source. Meanwhile, I hope this post was informative and I would like to thank you for reading.