Online Data Migration from SQL Server to Cloud Spanner Using Striim

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:

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. This might cause 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 the 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 the 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’ll need a destination schema where we’ll migrate the data to Spanner. The SQL Server and Spanner DDL statements are almost alike, but you’ll 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’ll have to do a schema conversion before you can implement an equivalent schema in the Spanner database. Execute the below code 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 the primary key will be anti-pattern if the values increases monotonically. The EmpID column in SQL Server is the identity column, so we’ll be loading that as String in Spanner to avoid hotspots. Check Schema design best practices for Spanner to learn more.

You’ll 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 schemas of large sets of tables. Also, view datatype support and mapping from SQL Server to Spanner before proceeding with an actual migration project. (Reminder: this enablement post doesn’t discuss full-scale database migrations and their challenges.)

Migration Strategy

The migration will involve three 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 the 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 a 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 recommend importing this app into Striim GUI and then supplying 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 for importing 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’ll 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’ll need to first Deploy the app. Right click on the three 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

 

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