Prerequisites

The following section lists the prerequisites for working with a Google Cloud SQL for SQL Server source.

Client Prerequisites

Qlik Replicate for Windows

Install Microsoft ODBC Driver 18.3 for Windows on the Qlik Replicate Server machine.

Qlik Replicate for Linux

Install Microsoft ODBC Driver 18.3 for Linux on the Replicate machine.

Then, open a Unix shell and do the following:

1. Change the working directory to <Replicate-Install-Dir>/bin.

2. Stop the Replicate service and optionally confirm that it has stopped as described in Replicate server procedures.

3. Copy the driver location to the site_arep_login.sh file as follows:

   echo "export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/microsoft/msodbcsql18/lib64/" >> site_arep_login.sh

   This will add the driver to "LD_LIBRARY_PATH" and update the driver location in the site_arep_login.sh file.

4. Optionally, confirm that the driver location was copied:

   cat site_arep_login.sh

5. Start the Replicate service and optionally confirm that it has started as described in Replicate server procedures.

6. Replicate requires the following ODBC library: libmsodbcsql-18.3.so.1.1

   To check which library version is currently installed Issue the following command:

   ls /opt/microsoft/msodbcsql/lib64/

   If the existing library has a different version number (e.g. libmsodbcsql-18.0.so.1.1), you need to create a symbolic link between the existing library and the required library.

   To do this, issue the following commands:

   cd /opt/microsoft/msodbcsql18/lib64/
   ln -s existing_library_name libmsodbcsql-18.3.so.1.1

   where existing_library_name is the name of the currently installed library (e.g. libmsodbcsql-18.0.so.1.1).

Preparing the database for CDC

The following section explains how to set up the database for CDC.

To set up the database for CDC:

1. Set up the database for MS-CDC by executing the following:

   EXEC msdb.dbo.gcloudsql_cdc_enable_db '[DATABASE_NAME]'

   where source_db_name is the name of the source database.

2. The replication task automatically enables MS-CDC for all the source tables. However, if you prefer to enable MS-CDC manually, execute the following script (for each of the intended source tables):

   exec sys.sp_cdc_enable_table

   @source_schema = N'schema_name',

   @source_name = N'table_name',

   @role_name = NULL

   where schema_name and table_name are the names of the source schema and the source table respectively.

   Information note

   • The CT table names must use the default naming format: <SchemaName>_<TableName>_CT

   • The CT table must include the following columns: [__$start_lsn], [__$end_lsn], [__$seqval], [__$operation], [__$update_mask], <all the data columns>, [__$command_id]

3. Make sure the MS-CDC capture and cleanup jobs are running.

   Information note

   • You can control the jobs using sys.sp_cdc_change_job. For additional information, see the Microsoft Help.
   • To reduce latency, you might want to set the @pollinginterval parameter to a small value.