Installation steps

Setup of Gluesync Change Tracking connector for MS SQL Server

Prerequisites

To have Gluesync MS SQL Server Change Tracking connector working on your database instance you will need to have:

valid user credentials with permissions to read, and write to the target tables and respective database;
each of the tables that you intend to source changes from needs to respect the mandatory requirement to have Primary Keys defined.

If your table doesn’t have a Primary Key defined and/or you’re using logical / clustered columns to uniquely define data in your MS SQL tables please checkout Gluesync CDC connector for MS SQL Server instead.

In the case of lack of Primary keys neither usage of cluster (compound) keys would work, so the Custom definition of clustered indexes feature is going to work in that specific case due to an MS SQL Server Change Tracking limitation.

Basic configuration example

This video tutorial will guide you through the basic configuration steps for setting up Gluesync for Microsoft SQL Server and Couchbase

The SQL to NoSQL module can be customized by using a configuration file, in JSON format. The file name to use must be specified as a parameter when launching the app, with the -f or --file tokens. The file should be composed by a union of the common configuration file (see here Installation steps) and source/destination specific configuration:

{
  ...
  "mssql": {
    "temporaryTableNamePrefix": "table_prefix",
    "statePreservationTableNamePrefix": "state_prefix"
  },

  "sourceChangeRetention": 5
}

Microsoft SQL Server-specific configurations are listed under the mssql property:

temporaryTableNamePrefix (optional): allow to specify a custom prefix for the temporary table created by Gluesync to improve performance. The typical use case is to allow multiple instances of Gluesync to run from the same source, thus avoiding conflicts at runtime;
statePreservationTableNamePrefix (optional): allow to specify a custom prefix for the state preservation table created by Gluesync to keep a checkpoint of processed changes. The typical use case is to allow multiple instances of Gluesync to run from the same source, thus avoiding conflicts at runtime;
sourceChangeRetention (optional): defaults to 5. Number of retention days preserved in the MS SQL Server change tracking system;

Looking for data modeling features or other options?

For more detailed configuration options, including the ability to perform data modeling, please have a look at the dedicated Data modeling section when sourcing from an RDBMS.

Troubleshooting

Here is the list of common errors.

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'SYS.SCHEMAS'.: Gluesync checks SYS.SCHEMAS table to verify that the GLUESYNC schema is in place, otherwise, he creates it. This error is thrown when insufficient permissions have been given to the user given to Gluesync. Please check permissions and try again.
IllegalArgumentException: Missing primary keys for entities…: At startup Gluesync checks for the required primary keys definition in defined table(s). This is a mandatory requirement of MS SQL Server Change Tracking. If you haven’t got any PK definition on your tables(s) you can’t proceed further using that connector. Please checkout Gluesync CDC connector for MS SQL Server instead.