Microsoft SQL Server

Source Microsoft SQL Server #

The extracted replicant-cli will be referred to as the $REPLICANT_HOME directory in the proceeding steps.

I. Set up Replicant Windows Agent #

To intsall and set up SQL Server Agent to use as CDC Extractor, please follow the instructions in Replicant SQL Server Agent Installation.

II. Check Permissions #

You need to verify that the necessary permissions are in place on source SQL Server in order to perform replication. To know about the permissions, see SQL Server User Permissions.

III. Set up Connection Configuration #

  1. From $REPLICANT_HOME, navigate to the sample connection configuration file:

    vi conf/conn/sqlserver.yaml
    
  2. Make the necessary changes as follows:

    type: SQLSERVER
    
    extractor: {CHANGE|LOG}
    
    host: localhost
    port: 1433
    
    username: 'USERNAME'
    password: 'PASSWORD'
    database: 'tpcc'
    
    max-connections: MAX_NUMBER_OF_CONNECTIONS
    
    #ssl:
    #  enable: true
    #  hostname-verification: false
    

    Replace the following:

    • USERNAME: the username to connect to the SQL Server
    • PASSWORD: the password associated with USERNAME
    • MAX_NUMBER_OF_CONNECTIONS: the maximum number of connections Replicant would use to fetch data from source—for example, 30

    Specify CDC Extractor #

    For your Source SQL Server, you can choose from two CDC Extractors. You can specify the Extractor to use by setting the extractor parameter in the connection configuration file to any of the following values:

    • CHANGE: The default value. With this value set, SQL Server Change Tracking is used for real-time replication. In this case, you don’t need to follow the documentation for Replicant SQL Server Agent.

      Enable Change Tracking

      To use SQL Server Change Tracking for realtime, all databases and tables must have change tracking enabled:

      • To enable Change Tracking on a database, execute the following SQL command:

        ALTER DATABASE database_name SET CHANGE_TRACKING = ON  
        (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
        

        Replace database_name with the name of the database you want Change Tracking enabled on.

      • To enable Change Tracking on table, execute the following SQL command for each table being replicated:

        ALTER TABLE table_name ENABLE CHANGE_TRACKING
        

        Replace table_name with the name of the table you want Change Tracking enabled on.

    • LOG: Uses the Replicant SQL Server Agent as CDC Extractor. In this case, please follow the Replicant SQL Server Agent docs.

    Specify authentication protocol for connection #

    • To specify an authentication protocol for the connection, set the auth-type parameter in the connection cofiguration file to any of the following values:

      • NATIVE
      • NLTM

      Default authentication protocol will always be NATIVE if you don’t explicitly set the auth-type parameter.

    • In case of NLTM as auth-type, provide the username in DOMAIN\USER format—for example, domain\replicant.

    Use KeyStore for credentials #

    Replicant supports consuming login credentials from a credentials store rather than having users specify them in plain text configuration file. Instead of specifying username and password as above, you can keep them in a KeyStore and provide its details in the connection configuration file like below:

    credentials-store:
      type: {PKCS12|JKS|JCEKS}
        path: PATH_TO_KEYSTORE_FILE
        key-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: you should create entries in the credential store for username and password configs using a prefix and specify the prefix here. For example, you can create keystore entries with aliases sqlserver_username and sqlserver_password. You can then specify the prefix here as sqlserver_.
    • KEYSTORE_PASSWORD: the KeyStore password. This 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 this password secure.

IV. Set up Extractor Configuration #

  1. From $REPLICANT_HOME, navigate to the applier configuration file:

    vi conf/src/sqlserver.yaml
    
  2. Make the necessary changes as follows:

    snapshot:
      threads: 16
      fetch-size-rows: 5_000
    
      _traceDBTasks: true
    #  min-job-size-rows: 1_000_000
    #  max-jobs-per-chunk: 32
    
    #  per-table-config:
    #  - catalog: tpch      
    #    schema: dbo
    #    tables:
    #      lineitem:
    #        row-identifier-key: [l_orderkey, l_linenumber]
    #        split-key: l_orderkey
    #        split-hints:
    #          row-count-estimate: 15000
    #          split-key-min-value: 1
    #          split-key-max-value: 60000
    
    realtime:
     # agent-connection:
     #   enable: true #Enable reading files from the remote server over a socket.
     #   host: # Host running remote SQL Server CDC agent
     #   username: # Specified in `domain\user` format.
     #   password:
     #   port:
      threads: 4
      fetch-size-rows: 10000
      fetch-duration-per-extractor-slot-s: 3
    
    • The agent-connection field is optional. It defines the parameters used to connect to the socket-based file server.
      • The user can be either local to the remote system or a domain account, but must have read/write access to the directory on the remote system where transaction files are written. This is configured as the staging directory on the remote system.

For a detailed explanation of configuration parameters in the extractor file, read Extractor Reference.