Arcion CDC Agent

Real-time replication from SQL Server with Arcion CDC Agent#

For real-time replicaiton from SQL Server, you can chooseArcion CDC Agent as a CDC Extractor. Follow these steps to set up real-time replication using the Arcion CDC Agent.

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 Arcion CDC Agent, we recommend that all the tables that you need to replicate have primary keys. If it’s not possible to define primary key on a table, follow the instructions inReplicate tables without primary keys.

Set up Arcion CDC Agent#

To set up Arcion CDC Agent, follow the instructions inArcion CDC Agent.

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 Arcion CDC Agent, follow these steps:

1. Setextractor toLOG.
2. Follow the instructions inArcion CDC Agent Installation.

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:'USERNAME'password:'PASSWORD'extractor:LOGmax-connections:MAX_NUMBER_OF_CONNECTIONS

Configuration user for Arcion CDC Agent#

Arcion CDC Agent requires thesysadmin role to configure datbase publications and subscriptions that the Agent uses for replication. Therefore, Arcion supports new parameters in the SQL Server connection configuration file from version 23.03.01.10.

config-username:'sa'config-password:'Rocket0128'config-auth-type:NATIVE

The three parameters in the preceding sample describe aconfiguration user. Arcion CDC Agent uses this configuration user to set up replication. You can specify these parameters eitherin plain text orin a KeyStore.

If you specifyconfig-username, Arcion CDC Agent uses this user to set up replication. If you don’t specifyconfig-username, Arcion CDC Agent uses themainusername.

We recommend that you explicitly specify these three parameters if you’re using version 23.03.01.10 and later.

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 credentials in a KeyStore and provide the KeyStore details in the connection configuration file:

credential-store:type: {PKCS12|JKS|JCEKS}path:PATH_TO_KEYSTORE_FILEkey-prefix:"PREFIX_OF_THE_KEYSTORE_ENTRY"password: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. Therefore, you need to setkey-prefix tosqlserver_.
• KEYSTORE_PASSWORD: Optional parameter for the KeyStore password. 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. Keep your license file somewhere safe to keep the KeyStore password secure.

Note thatthe following parameters must have entries in the KeyStore:

Parameters for connection between Arcion CDC Agent and Replicant

• sql-proxy-connection
• agent-connection
• sql-jobs-username
• sql-jobs-password

Parameters for configuration user

• config-username
• config-password

<key-prefix> + “sql-proxy-connection-”<key-prefix> + “agent-connection-”<key-prefix> + “sql-jobs-”

For example, consider the following KeyStore configuration:

credential-store:type:PKCS12path:/data/store/sqlserver.jkskey-prefix:"ss_"password:test01

For the preceding configuration, you need to add KeyStore entries in the following manner:

Username andpassword of SQL Server

echo"sa" | keytool -importpass -keystore /data/store/sqlserver.jks -storetype pkcs12 -storepass test01 -alias ss_username -keypass test01 -nopromptecho"Rocket0128" | keytool -importpass -keystore /data/store/sqlserver.jks -storetype pkcs12 -storepass test01 -alias ss_password -keypass test01 -noprompt

sql-jobs-username andsql-jobs-password

echo"TEST\administrator" | keytool -importpass -keystore /data/store/sqlserver.jks -storetype pkcs12 -storepass test01 -alias ss_sql-jobs-username -keypass test01 -nopromptecho"Rocket0128" | keytool -importpass -keystore /data/store/sqlserver.jks -storetype pkcs12 -storepass test01 -alias ss_sql-jobs-password -keypass test01 -noprompt

Username and password ofsql-proxy-connection

echo"sa" | keytool -importpass -keystore /data/store/sqlserver.jks -storetype pkcs12 -storepass test01 -alias ss_sql-proxy-connection-username -keypass test01 -nopromptecho"Rocket0128" | keytool -importpass -keystore /data/store/sqlserver.jks -storetype pkcs12 -storepass test01 -alias ss_sql-proxy-connection-password -keypass test01 -noprompt

Username and password ofagent-connection username and password

echo"alexwin10\alex" | keytool -importpass -keystore /data/store/sqlserver.jks -storetype pkcs12 -storepass test01 -alias ss_agent-connection-username -keypass test01 -nopromptecho"Rocket0128" | keytool -importpass -keystore /data/store/sqlserver.jks -storetype pkcs12 -storepass test01 -alias ss_agent-connection-password -keypass test01 -noprompt

Username and password of configuration user

echo"sa" | keytool -importpass -keystore /data/store/sqlserver.jks -storetype pkcs12 -storepass test01 -alias ss_config-username -keypass test01 -nopromptecho"Rocket0128" | keytool -importpass -keystore /data/store/sqlserver.jks -storetype pkcs12 -storepass test01 -alias ss_config-password -keypass test01 -noprompt

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.

Replicate tables without primary keys#

To use Arcion CDC Agent as CDC Extractor, we recommend that all replicated tables have primary keys. However, it’s possible to replicate tables without primary keys if the tables meet the following criteria:

The tables must have a set of non-nullable columns that uniquely identifies each row.

Replicant replicates a table without a primary key by creating a view of that table and then replicating that view instead. By default, each view Replicant creates has the following two properties:

• Each view belongs to thedbo schema.
• The name of each view starts with the prefixreplicant_.

However, you can change these default properties by specifying the following respective parameters inthe SQL Server source connection configuration file:

auxiliary-object-schema#

The name of the schema to which the view belongs to.

auxiliary-object-prefix#

The prefix to use for the name of each view.

For example, the following connection configuration for SQL Server uses the preceding parameters to set the view properties:

type:SQLSERVERhost:localhostport:1433username:'alex'password:'alex1995'extractor:LOGmax-connections:30auxiliary-object-schema:'admin'auxiliary-object-prefix:'price_'