Overview

This lab demonstrates how to perform an online migration of a SQL Server database to Spanner using Google Cloud's Data Migration Partner, Striim.

Striim is a comprehensive streaming ETL platform that enables online data migrations and continuous streaming replication from on-premises and cloud data sources to Google Cloud data services through a graphical drag and drop interface.

The following figure shows Striim's high level architecture.

Note: This lab will focus on the implementation of a continuous migration from SQL Server to Spanner and is not an explanation of database migration or database replication or why you might want to migrate your underlying database.

The figure below shows the architecture used for this lab.

What you will learn

In this lab you will learn to:

• Access and use an existing SQL Server database running on a Compute Engine virtual machine.
• Access and use an existing Spanner database.
• Deploy Striim through the Google Cloud Marketplace.
• Use Striim to read from a source SQL Server database.
• Use Striim to continuously replicate from SQL Server to Spanner.

Setup and requirements

What you'll need

For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.

1. Sign in to Qwiklabs using an incognito window.

2. Note the lab's access time (for example, 1:15:00), and make sure you can finish within that time.
   There is no pause feature. You can restart if needed, but you have to start at the beginning.

3. When ready, click Start lab.

4. Note your lab credentials (Username and Password). You will use them to sign in to the Google Cloud Console.

5. Click Open Google Console.

6. Click Use another account and copy/paste credentials for this lab into the prompts.
   If you use other credentials, you'll receive errors or incur charges.

7. Accept the terms and skip the recovery resource page.

Activate Google Cloud Shell

Google Cloud Shell is a virtual machine that is loaded with development tools. It offers a persistent 5GB home directory and runs on the Google Cloud.

Google Cloud Shell provides command-line access to your Google Cloud resources.

1. In Cloud console, on the top right toolbar, click the Open Cloud Shell button.

   

2. Click Continue.

It takes a few moments to provision and connect to the environment. When you are connected, you are already authenticated, and the project is set to your PROJECT_ID. For example:

gcloud is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.

• You can list the active account name with this command:

gcloud auth list

Output:

Credentialed accounts: - @.com (active)

Example output:

Credentialed accounts: - google1623327_student@qwiklabs.net

• You can list the project ID with this command:

gcloud config list project

Output:

[core] project =

Example output:

[core] project = qwiklabs-gcp-44776a13dea667a6 Note: Full documentation of gcloud is available in the gcloud CLI overview guide .

Before you begin

In order to work on the database migration some initial configuration is required:

1. Open a new web browser window and navigate to the Google Cloud Console (console.cloud.google.com). Use the project selector to choose the project with a leading name of 'qwiklabs-gcp.'

2. In the Cloud Shell, install a SQL Server client to allow easy access to the SQL Server instance. Execute the following commands and accept prompts with either Y or yes:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list sudo apt-get update sudo apt-get install mssql-tools unixodbc-dev echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc

3. On the Navigation menu (), click Compute Engine > VM instances. Copy the IP address for the SQL Server instance. The value appears in the External IP column. You can simply copy this value to your clipboard by hovering over the IP address, moving your mouse to the document's image, and then clicking Copy to clipboard.

4. Assign the SQL Server instance's IP address to the SQLSRVIP variable.

Note: Be sure to leave the outside single quotes. export SQLSRVIP='SQLSRV_IP_ADDRESS'

5. Run the following command to view the existing data in the Orders table. The table currently contains 10 rows. Your results will appear as shown below.

sqlcmd -S $SQLSRVIP,1433 -U striim -P striim-password -d striimlab -Q "select * from striimlab.dbo.ORDERS"

6. Type "cat" into the terminal window (without double quotes) and press Return/Enter. This will effectively pause the shell and preserve all of the configuration you have made while you perform steps outside the shell.

7. Verify that the Spanner instance has been set up with a database and table defined. In the Cloud Console, select Navigation menu > Spanner.

8. Click on the instance name striim-spanner-lab, and then click on the database name striimlab (under the Databases section). Confirm that the table orders is present.

9. Click on the table name orders in the bottom pane under Tables. Then click Data on the left pane. The table is empty.

Task 1. Set up the Striim server

You will set up an instance of the Striim server provisioned through the Google Cloud Marketplace.

1. Click on the Navigation menu > Marketplace. In the search box type Striim and hit Return or Enter. Select STRIIM ( metered ) from the list of options.

2. Click GET STARTED and Check the box for I accept the GCP Marketplace Terms of Service... and click on AGREE and Click DEPLOY.

3. On the New STRIIM ( metered ) deployment screen that appears, select the Zone set to .

4. Change the Machine Type to:
   Series: E2
   Machine type: e2-standard-4 (4 vCPU, 16 GB memory).

5. Leave the other options at their default values.

6. Scroll down the page.

7. Click Deploy.

Note: Marketplace solutions typically come with various resources that launch together in order to support the software. Be sure to review the monthly billing estimate before launching Striim in a regular, non-training account.

8. You will be redirected to the Deployment Manager page and once the environment is provisioned the deployment details will appear. Take note of the name of the VM and deployment zone (Instance and Instance zone in the example screenshot below).

Click Check my progress to verify the objective. Set up the Striim server

12. Return to the Cloud Shell. Press Ctrl+C to clear the paused "cat" command. Execute the following commands after replacing the placeholder INSTANCENAME with the Instance name and the placeholder ZONENAME with the Instance zone from the deployment details pane.

Based on the screenshot above, STRIIMVM_NAME would be set to striim-metered-1-vm and STRIIMVM_ZONE would be set to . Your values may vary.

export STRIIMVM_NAME=INSTANCENAME export STRIIMVM_ZONE=ZONENAME

13. Type "cat" into the terminal window (without double quotes) and press Return/Enter to pause the shell.

14. Return to the Deployment Manager tab, click the link for Site address. This will open up the Striim configuration wizard in a new browser tab. If there is a redirect notice, you can safely click the link provided.

15. If you receive a pop-up referring to the use of cookies, click I Agree. Click Accept Striim EULA and Continue at the bottom of the page.

16. Configure your Striim installation - use the values listed in the table below:

Property	Value (type value or select option as specified)
Cluster name	my_cluster
Admin password	striim-password
Re-enter Admin password	striim-password
Sys user password	striim-password
Re-enter Sys user password	striim-password
Striim Key Store password	striim-password
Re-enter Striim Key Store password	striim-password

17. Click Save and Continue.

18. Click Launch. Processing will occur in the background. Please wait a short while. Once all actions are complete, click the green Log In button.

19. Log in using the user admin and the password striim-password. This will take you to the Striim administration console.

You are now ready to configure a connection to the SQL Server instance.

Important: The "admin" user is a built-in user that must be used in order for login to succeed.

Task 2. Set up Striim Access to SQL Server

You will now deploy the SQL Server JDBC driver to allow Striim to connect to your SQL Server instance.

1. On the Deployment Manager page for your Striim instance, click SSH located next to the blue Visit the site button. This will open up a new window and will automatically SSH into the virtual machine.

2. If prompted to allow SSH-in-browser to connect to VMs click Authorize.

Note: If presented with a warning of "Please consider adding the IAP-secured Tunnel User IAM role to start using Cloud IAP for TCP forwarding for better performance", you may safely ignore the warning.

3. Run the following command to download the SQL Server JDBC driver:

sudo wget https://storage.googleapis.com/cloud-training/dbmigration/mssql-jdbc-7.2.2.jre8.jar

4. Copy the java archive file to the Striim library path, allow it to be executable, and change the ownership and group:

sudo cp mssql-jdbc-7.2.2.jre8.jar /opt/striim/lib/mssql-jdbc-7.2.2.jre8.jar 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 sudo chgrp striim /opt/striim/lib/mssql-jdbc-7.2.2.jre8.jar

5. Restart the Striim server to recognize the new JDBC driver:

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

6. Close the Striim SSH window.

Click Check my progress to verify the objective. Set up Striim Access to SQL Server

7. Go back to the browser tab for the Striim administration console, refresh the tab, and log in using the user admin and the password striim-password. The administration console will take a few minutes to refresh after the restart. If the administration console does not reload on the first attempt, keep refreshing until the administration console comes up.

If the administration console does not come up, use the site address, but with port 9080 instead of 9070, i.e. http://STRIIM_IP_ADDRESS:9080/.

Task 3. Create a Spanner access key

Striim requires an IAM access key so that it can securely connect to Spanner. Follow these steps to create the key:

1. Return to the project Cloud Shell, press Ctrl+C to clear the paused "cat" command, and create a service account for Striim to connect to Spanner by running the commands below. These steps will create a key named striim-spanner-key.json in your Cloud Shell's home path:

gcloud iam service-accounts create striim-spanner --display-name striim-spanner export sa_striim_spanner=$(gcloud iam service-accounts list --filter="displayName:striim-spanner" --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-spanner-key.json --iam-account $sa_striim_spanner

2. On the Deployment Manager page for your Striim instance, click SSH located next to the blue Visit the site button. This will open up a new window and will automatically SSH into the virtual machine.

3. Run the following command to determine the Qwiklabs student user account. Copy the results to the clipboard:

whoami

Example output:

4. Run the following command to set a variable equal to the user account within the Striim SSH terminal. Replace WHOAMI with the results from the last command. In the screenshot above this value is student-03-e55b52d3ee8c:

export USERNAME=WHOAMI

5. Run the following commands to create a directory and change ownership of that directory to the Qwiklabs user:

sudo mkdir /home/$USERNAME sudo chown $USERNAME /home/$USERNAME

6. You must close the Striim SSH window before proceeding to the next step.

7. Return to the project Cloud Shell and move the Spanner key you created to the Striim VM. The command below will copy the JSON file to the Striim server. Connecting by scp (secure copy) will cause an SSH key to be generated and you will be prompted to create a passphrase. When prompted to continue, confirm with "Y" and leave the passphrase field empty. Press Return or Enter to force the passphrase to be empty on each step:

gcloud compute scp ~/striim-spanner-key.json $STRIIMVM_NAME:~ --zone=$STRIIMVM_ZONE Important: If you receive a workgroup error in the console, you can safely ignore that message.

8. Move the JSON file on the Striim VM to the /opt/striim directory using the command below. The terminal will report that the connection is closed to the remote server. This is not an error or warning. Proceed to the next step.

gcloud compute ssh --zone=$STRIIMVM_ZONE $STRIIMVM_NAME -- 'sudo cp ~/striim-spanner-key.json /opt/striim && sudo chown striim /opt/striim/striim-spanner-key.json'

9. Confirm that the JSON file is now on the Striim server by issuing the following command. The key and file details appear in the output.

gcloud compute ssh --zone=$STRIIMVM_ZONE $STRIIMVM_NAME -- 'sudo ls -l /opt/striim/striim-spanner-key.json'

10. Type "cat" into the terminal window (without double quotes) and press Return/Enter to pause the shell.

You are now ready to create a Striim application.

Click Check my progress to verify the objective. Create a Spanner access key

Task 4. Create an online database migration

An online database migration moves data from a source database (either on-premises or hosted on a cloud provider) to a target database in Google Cloud. The source database remains fully accessible to business applications with minimal performance impact on the source database with this type of configuration.

In an online migration, an initial bulk load is performed, and a continuous capture of changes is executed. You then synchronize the two databases to ensure that data isn't lost. Typically both databases will be retained for long periods of time to test and verify that the application and users are not impacted by switching to a new cloud database.

1. On the Striim administration console, click the Apps button in the middle of the page.

2. Click the Create App button on the upper right hand side of the page.

3. There are multiple ways to create applications in Striim. For this lab click Start From Scratch (the option on the left).

4. Name the application something that is easily remembered. For this lab use the name Batch.

5. Below the application name, you can choose a namespace. These are logical groupings that help you organize applications. Use the default admin namespace. Click Save on the bottom right corner of the page.

6. The Flow Designer page loads. All of the out of the box connectors needed to create streaming data applications are in the left side menu bar.

7. To do a one-time, bulk initial load of data, use the Database Reader. Drag and drop the Database tile from the Sources pane to the application canvas in the middle.

8. Enter the following connection properties in the New Source pane on the right of the page.

Note: PRIMARY_ADDRESS is the IP address of SQL Server instance. You must replace this placeholder with the instance's IP address. You accessed the IP address in Setup and Requirements - step 4 of this lab.

Property	Value (type value or select option as specified)
Name	batch_source
Connection URL	jdbc:sqlserver://PRIMARY_ADDRESS:1433;DatabaseName=striimlab
Username	striim
Password	striim-password

9. Click the Show Advanced Settings link to add one additional configuration value.

Property	Value (type value or select option as specified)
Tables	striimlab.dbo.ORDERS

10. For Output To provide the following value.

Property	Value (type value or select option as specified)
New Output	batch_inprocess

11. Click Save.

12. Next perform a quick test to see if the configuration settings are correct and that Striim can successfully connect to the SQL Server instance. On the top middle of the page, there will be a button marked Created with a drop down arrow next to it. Click the button, and select Deploy App.

13. Select the default option and click Deploy.

14. Click the wave button beneath your Database Reader component, and then click the eye icon next to it to preview your data as it flows through the Striim pipeline.

15. Go back up to the top menu space, click Deployed, and then click Start App.

You should now see the Striim application running and data flowing through the pipeline. If there are any errors, it means that there is an issue connecting to the source database since there is only a source component in the pipeline. If you see your application successfully run but no data flows through, typically that means that you don't have any existing data in your database.

You've successfully connected to your source database and tested that it can read data.

16. Click Running on the top menu bar, and then select Stop App.

17. Click Stopped, and then select Undeploy App. We are now ready to connect to Spanner.

18. Click the wave button under the DatabaseReader. This time a "+" (plus) button will appear. Click the plus button and then select Connect next Target component.

19. Name this target object batch_target. In the Adapter field search for Spanner. Select SpannerWriter.

20. Enter the connection properties listed below.

Property	Value (type value or select option as specified)
Service Account Key	/opt/striim/striim-spanner-key.json
InstanceID	striim-spanner-lab
Tables	striimlab.dbo.ORDERS,striimlab.orders

Note: The Service Account Key property points to the fully qualified path and name of the key file that was generated earlier and moved to the Striim server.

21. Click Save.

22. Click the Created link in the middle of the designer page and then click Deploy App from the drop down.

23. Select the default option and click Deploy.

24. Click Deployed, and then click Start App.

25. Once the status of Running appears, navigate to another tab on the Navigation menu click Spanner. Click on the instance name striim-spanner-lab, and then click on the database name striimlab.

26. On the left pane, click Spanner Studio. then In the right side click NEW SQL EDITOR TAB, run the following query:

SELECT * from orders Note: It might take a minute or two for the data to appear due to the default configuration settings. Execute the query again until the data is loaded.

You have successfully set up your Striim environment and pipeline to perform a batch load.

27. Return to your Striim application. Click Running on the top menu bar, and then select Stop App.

28. Click Stopped, and then select Undeploy App.

Task 5. Create a continuous SQL Server to Spanner Data Pipeline

With an initial one time bulk load in place, you can set up a continuous replication pipeline. It will look very similar to the bulk pipeline that you just created, but with a different source object.

1. Click the Apps menu and then click Create New.

2. Click Start from Scratch, and then name it CDC. Leave the namespace as the default option admin.

3. Click Save.

4. This time, instead of choosing a Database reader source, choose the SQL Server CDC source and drag it on the canvas.

5. Enter the following connection properties in the right pane of the New Source window.

Note: PRIMARY_ADDRESS is the IP address of SQL Server instance. You must replace this placeholder with the instance's IP address. You accessed the IP address in Setup and Requirements - step 4 of this lab.

Property	Value (type value or select option as specified)
Name	cdc_source
Database Name	striimlab
Connection URL	jdbc:sqlserver://PRIMARY_ADDRESS:1433;DatabaseName=striimlab
Tables	dbo.ORDERS

6. Click the Show Advanced Settings link to input the Username and Password.

Property	Value (type value or select option as specified)
Username	striim
Password	striim-password

7. For Output To provide the following value.

Property	Value (type value or select option as specified)
New Output	cdc_inprocess

8. Click Save.

9. Click the wave button under the cdc_Source. This time a "+" (plus) button will appear. Click the plus button and then select Connect next Target component.

10. Name the target object cdc_target and then search for Spanner under the Adapter field. Select SpannerWriter.

11. Enter the following connection properties.

Note: The Tables mapping used in this configuration is slightly different from the one used in the batch load.

Property	Value (type value or select option as specified)
Service Account Key	/opt/striim/striim-spanner-key.json
InstanceID	striim-spanner-lab
Tables	dbo.ORDERS,striimlab.orders

12. Click Save.

13. Click the Created link in the middle of the designer page and then click Deploy App from the drop down.

14. Select the default option and click Deploy.

15. Click Deployed, and then click Start App.

16. Wait for the CDC app to be fully running before moving to the next step. Your screen should appear like the image below before you proceed.

17. Go back to the Cloud Shell. Press Ctrl+C to clear the paused "cat" command. Run the following command to add one row of data to the Orders table:

sqlcmd -S $SQLSRVIP,1433 -U striim -P striim-password -d striimlab -Q "INSERT INTO ORDERS (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID) VALUES (1011, '1568928576017', 'In-Store', 1011, 9, 13879.56, 320, 88252)" Note: The variable SQLSRVIP should still be set in the Cloud Shell. If it is not please issue the command from Setup and Requirements - step 5.

18. In the Cloud Console, navigate to Spanner, then to the striimlab database . click Spanner Studio. then In the right side click New Editor tab. Run the following query:

SELECT * from orders order by order_id desc

The newly inserted row, Order ID 1011, will appear at the top of the list.

Note: It might take a minute or two for the data to appear due to the default configuration settings. Please execute the query again until the data is loaded.

19. Return to the Cloud Shell and run the following commands consecutively to add 4 more rows of data to the Orders table:

sqlcmd -S $SQLSRVIP,1433 -U striim -P striim-password -d striimlab -Q "INSERT INTO ORDERS (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID) VALUES (1012, '1568928636017', 'CompanyA', 1012, 1, 19729.99, 76, 95203)" sqlcmd -S $SQLSRVIP,1433 -U striim -P striim-password -d striimlab -Q "INSERT INTO ORDERS (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID) VALUES (1013, '1568928696017', 'In-Store', 1013, 5, 7286.68, 164, 45162)" sqlcmd -S $SQLSRVIP,1433 -U striim -P striim-password -d striimlab -Q "INSERT INTO ORDERS (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID) VALUES (1014, '1568928756017', 'Online', 1014, 1, 87268.61, 909, 70407)" sqlcmd -S $SQLSRVIP,1433 -U striim -P striim-password -d striimlab -Q "INSERT INTO ORDERS (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID) VALUES (1015, '1568928816017', 'CompanyB', 1015, 1, 69744.13, 424, 79401)"

20. Return to the Spanner Studio tab to verify that the additional rows have made it to the database by repeating the earlier query:

SELECT * from orders order by order_id desc

Congratulations!

You have successfully set up a streaming pipeline from SQL Server to Spanner and used Striim to achieve an online database migration.

Next steps / Learn more

Try out other Google Cloud Platform features for yourself. Have a look at our tutorials, and also be sure to take a look at the Google Cloud Data Migration content.

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.