Change tracking

Real-time replication from SQL Server with change tracking#

For real-time replicaiton from SQL Server, you can choose SQL Server Change Tracking as a CDC Extractor. Follow these steps to set up real-time replication using change tracking.

I. Prerequisites#

Required Permissions#

To allow replication, you need to first verify that the necessary permissions are in place on source SQL Server. For more information, seeSQL Server User Permissions.

Primary keys on tables#

Forfull mode replication with change tracking, make sure that all the tables that you need to replicate have primary keys.

II. Set up connection configuration#

Specify the connection details of your SQL Server instance to Replicant in one of the following ways:

• A connection configuration file
• Secrets management service
• KeyStore

Use a connection configuration file#

The connection configuration fild holds the connection details and login credentials.You can find a sample connection configuration filesqlserver.yaml in the$REPLICANT_HOME/conf/conn directory. The following configuration parameters are available:

type#

The connection type representing the database. In this case, it’sSQLSERVER.

host#

The hostname of your SQL Server system.

port#

The port number to connect to thehost.

username#

The username credential to access the SQL Server system.

password#

The password associated withusername.

auth-type#

The authentication protocol for the connection. The following protocols are supported:

• NATIVE (Default)
• NLTM

Default authentication protocol is alwaysNATIVE if you don’t explicitly set theauth-type parameter.

In case ofNLTM protocol, provide theusername inDOMAIN\USER format—for example,domain\alex.

extractor#

The CDC Extractor to use for real-time replication.

To use SQL Server Change Tracking, follow these steps:

• Setextractor toCHANGE.
• Follow the instructions inEnable change tracking.

is_azure#

Optional parameter. If you’re hosting SQL Server on Azure, you must set this parameter totrue.

max-connections#

The maximum number of connections Replicant uses to load data into the SQL Server system.

The following is a sample connection configuration:

type:SQLSERVERhost:localhostport:1433username:'alex'password:'alex1995'extractor:CHANGEmax-connections:30

Use a secrets management service#

You can store your connection credentials in a secrets management service and tell Replicant to retrieve the credentials. For more information, seeSecrets management.

Use KeyStore for credentials#

Replicant supports consuming login credentials from acredentials store. Instead of specifying username and passwordin plain text, you can keep them in a KeyStore and provide the KeyStore details in the connection configuration file like below:

credential-store:type: {PKCS12|JKS|JCEKS}path:PATH_TO_KEYSTORE_FILEkey-prefix:PREFIX_OF_THE_KEYSTORE_ENTRYpassword:KEYSTORE_PASSWORD

Replace the following:

• PATH_TO_KEYSTORE_FILE: The path to your KeyStore file.
• PREFIX_OF_THE_KEYSTORE_ENTRY: The prefix of your KeyStore entries. You can create entries in the credential store using a prefix that preceeds each credential alias. For example, you can create KeyStore entries with aliasessqlserver_username andsqlserver_password. You can then setkey-prefix tosqlserver_.
• KEYSTORE_PASSWORD: The KeyStore password. This parameter is optional. If you don’t want to specify the KeyStore password here, then you must use the UUID from your license file as the KeyStore password. Remember to keep your license file somewhere safe in order to keep the KeyStore password secure.

III. Create the heartbeat table#

Forfull mode replication, you need to create a heartbeat table. For example:

CREATETABLE"tpcc"."dbo"."replicate_io_cdc_heartbeat"("timestamp" BIGINTNOTNULL,PRIMARYKEY("timestamp"))

IV. Set up Extractor configuration#

To configure real-time replication according to your requirements, specify your configuration in the Extractor configuration file. You can find a samplesqlserver.yaml in the$REPLICANT_HOME/conf/src directory.

All configuration parameters forrealtime mode live under therealtime section. The following is a sample configuration:

realtime:threads:4fetch-size-rows:10000fetch-duration-per-extractor-slot-s:3heartbeat:enable:truecatalog:"tpcc"schema:"dbo"interval-ms:10000

For more information about the configuration parameters inrealtime mode, seeRealtime Mode.

Enable change tracking#

To use change tracking, you must enable change tracking in all databases and tables:

1. Enable change tracking in database:

   ALTERDATABASE database_nameSET CHANGE_TRACKING=ON(CHANGE_RETENTION=2 DAYS, AUTO_CLEANUP=ON)

   Replacedatabase_name with the name of the database you want to enable change tracking on.

2. Enable change tracking on table:

   ALTERTABLEtable_name ENABLE CHANGE_TRACKING

   Replacetable_name with the name of the table you want to enable change tracking on.