The Rocket® Data Replicate and Sync Data Capture for MS SQL Server is based on the Change Data Capture mechanism.

Data capture with MS SQL Server CDC is supported for the Enterprise Editions of MS SQL Server 2008 R2, 2012, 2014, and 2016. Starting from MS SQL Server 2016 SP1, the standard edition is supported as well.

The CDC data extraction must be performed on a Windows machine with the installed "SQL Server Native Client" ODBC driver. The supported versions are:

• SQL Server Native Client 10.0
• SQL Server Native Client 11.0
• SQL Server Native Client 12.0
• SQL Server Native Client 13.0
• SQL Server ODBC driver 17.x

The following requirements must be met to process changes from MS SQL CDC data:

• The database in question must be activated for CDC. The corresponding command sequence can be:

  use [master]
  exec sys.sp_cdc_enable_db
  GO

• Each table in question must be activated for CDC. The corresponding command sequence can be:

  use [master]
  EXEC sys.sp_cdc_enable_table
  @source_schema        = N'schema',
  @source_name          = N'tablename',
  @role_name            = NULL,
  @capture_instance     = N'Rocket® Data Replicate and Sync_tablename',
  @supports_net_changes = 1
  GO

When setting up the database, pay attention to the setting of the polling interval for the log scan. The default value is five seconds. This setting can be changed via the Stored Procedure "sys.sp_cdc_change_job". In an Always On cluster, the values on all machines should be equal.

In a bidirectional replication, a table is created in the output target in a specified schema. This table is used as the identifier for Rocket® Data Replicate and Sync. For different output targets to the same MS SQL Server, a different schema has to be chosen to avoid database blockades on this table in parallel operation.