Static MS SQL Server CDC: Native CDC with Gluesync Setup

Source data from MS SQL Server (via CDC)

Prerequisites

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

  • valid user credentials with permissions to read, and write to the source tables and respective database, Gluesync requires that role to activate CDC for needed tables and create its table structure under the GLUESYNC schema (automatically created by Gluesync);

  • valid user with administrative privileges to access the host where MS SQL Server is running, this will be then used to turn on SQL Server Agent;

To create a valid user for Gluesync on your MS SQL Server database you can run the following query: 
CREATE LOGIN gluesync
    WITH PASSWORD = 'YouDecide!';
GO

ALTER SERVER ROLE db_owner ADD MEMBER gluesync;
--- db_owner role is required for executing Microsoft's SQL Server CDC functions
GO
Given user gluesync present in the example above is not a mandatory user name for Gluesync, you can define whatever user name you’d like.

Setup via Web UI

  • Hostname / IP Address: DNS record or IP Address of your server;

  • Port: Optional, defaults to 1433;

  • Database name: Name of your database;

  • Username: Username with read/write access to the tables;

  • Password: Password belonging to the given username;

  • Max connections count: Maximum number of connections the pool can instantiate.

Specific configuration

The following example shows how to apply the agent-specific configurations via Rest API.

  • Enable recursion protection: (optional, defaults to false) Boolean value that avoids recursion when two-way sync capability is configured on the same table;

  • Temporary table name prefix: (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;

Setup via Rest APIs

Here following an example of calling the CoreHub’s Rest API via curl to setup the connection for this Agent.

Connect the agent

curl --location --request PUT 'http://core-hub-ip-address:1717/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
        "hostCredentials": {
        "connectionName": "myAgentNickName",
        "host": "host-address",
        "port": 1433,
        "databaseName": "db_name",
        "username": "",
        "password": "",
        "maxConnectionsCount": 100,
        "enableTls": true,
        "certificatePath": "/myPath/cert.pem"
      }
}'

Setup specific configuration

The following example shows how to apply the agent-specific configurations via Rest API.

curl --location --request PUT 'http://core-hub-ip-address:1717/pipelines/{pipelineId}/agents/{agentId}/config/specific' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
      "configuration": {
            "enableRecursionProtection": false
      }
}'

Setting up for CDC

Enabling SQL Agent

Gluesync requires SQL Agent to be enabled and running on the source MS SQL Server database. This task is intended to be carried on by a DBA. To execute this task DBA should access MS SQL Server with sysadmin role permissions.

Enabling CDC on the source database

Gluesync requires CDC to be enabled at the database level. To enable CDC at a specific database level Microsoft SQL Server comes with a handy function called sys.sp_cdc_enable_db that by passing to it the db name will enable CDC over it.

To learn more about that stored procedure we suggest you take a look at Microsoft’s documentation through the following link.

Enabling and executing CDC tasks on tables

Gluesync requires privileges to execute a set of Microsoft-provided functions that are part of the MS SQL Server database package. A complete list of functions involved in the CDC process is listed under the following section in Microsoft MS SQL Server documentation.

Every function requires at least a db_owner role to be given to the Gluesync user to perform its duties.

Enabling CDC agent (SQL Server Agent)

CDC on MS SQL Server is served through a service called SQL Server Agent, you can read more about it by following this link pointing directly to the Microsoft SQL Server website.

Enabling the agent is a mandatory step that needs to be carried on by a DBA. Here we have collected steps for enabling the agent both for MS SQL Server Windows-based and Linux deployments.

If you’re considering running MS SQL Server under a container environment (like docker for instance) consider following the Linux section.

Turn on CDC agent under Linux

With a user with administrative privileges on the machine where MS SQL Server is running, you need to issue the following commands:

/opt/mssql/bin/mssql-conf set sqlagent.enabled true

After having successfully performed this step you’ll be requested to restart your MS SQL Server database instance.

If you need to enter inside the container to run the given bash command you can issue that from your host: docker exec -it --user root CONTAINER_NAME bash.

Turn on CDC agent under Windows

Microsoft has a detailed guide on how to enable SQL Server Agent when running MS SQL Server under Windows-based environments.

The detailed guide can be found here at this link.

Checking if SQL Server Agent is running properly

To check if the SQL Server Agent has been properly set and is currently running you can issue the following commands:

# This will enable the sp_configure to display advanced options
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

# This will print out the full status of the SQL Server Agent
EXEC sp_configure 'Agent XPs';

The resulting output will look like the one in the following picture.

Query output showing the two columns

Lastly: enabling CDC on tables

For transaction logs to be available for the tables that you intend to source data changes from a SQL command has to be sent to a built-in stored procedure available in MS SQL Server, please follow the example below and repeat it for every table you want to replicate:

EXEC sys.sp_cdc_enable_table @source_schema = MYSCHEMA, @source_name = MYTABLE, @role_name = null, @supports_net_changes = 0
go

Happy CDC!

Troubleshooting

Here is the list of common errors.

  • 22836: Could not update the metadata for database XYZ: If the error returned was 14234: The specified @srv is invalid (valid values are returned by sp_helpserver), then this means that the Server name mismatches and here’s a workaround that could solve the issue you’re facing:

DECLARE @OldServerName AS NVARCHAR(128);
SELECT @OldServerName=srvname from master.dbo.sysservers;
sp_dropserver @OldServerName
GO

DECLARE @ServerName NVARCHAR(128) = CONVERT(sysname, SERVERPROPERTY('ServerName'));
EXEC sp_addserver @ServerName, 'local';
GO

Working with Before & After images

Before & after images are a feature that allows Gluesync to track the changes that have occurred in your database and comparing them with their previous values. This enables Gluesync to apply only the changes that have occurred, saving bandwidth and processing time.

This feature is automatically enabled and available for use in this agent.