Striim Migration Service for Google Cloud Documentation

Create the target tables

Before running the initial load application, you must create tables in the target that are compatible with those in the source. You may use any tool you wish.

The simplest approach is to use the native utilities of SQL Server and Cloud SQL for SQL Server as described in Importing data into Cloud SQL:

  1. Using sqlcmd or any client you prefer, connect to the source database and enter the following command to get the log sequence number (LSN) you will provide when starting streaming integration:

    SELECT sys.fn_cdc_get_max_lsn()AS max_lsn; 
  2. Back up the source database to a .bak file.

  3. Upload that file to a Google Cloud Storage bucket.

  4. Import the file to the target instance using Cloud SQL for SQL Server's import feature.

  5. After importing the backup file, in the same database, create the checkpoint table required for SMSGC to support recovery of the streaming integration application:

    CREATE TABLE CHKPOINT (
      id VARCHAR(100) PRIMARY KEY,
      sourceposition VARBINARY(MAX), 
      pendingddl BIT, 
      ddl VARCHAR(MAX));

At this point, you have performed the initial load including all foreign keys and other constraints, so go on to Create and test the streaming integration application.

If you prefer to use SMSGC's schema conversion utility, the command is:

striim/bin/schemaConversionUtility.sh \
-s="sqlserver" \
-d="jdbc:sqlserver:@//<ip address>:<port>;DatabaseName=<database name>" \
-u="<user name>" \
-p="<password>" \
-b="<database_name.% or a list of tables separated by semicolons>" \
-t="sqlserver"

For detailed instructions on using the schema conversion utility, see Create target tables using SMSGC's schema conversion utility.