Skip to main content

​
Required settings

  • Host name
  • Port (default is 1433)
  • Service account
  • Database name
  • CDC enabled for database and tables to replicate
USE DATABASE_NAME;
CREATE LOGIN artie WITH PASSWORD = 'PASSWORD';
CREATE USER artie FOR LOGIN artie;
ALTER LOGIN artie ENABLE;
GRANT SELECT on DATABASE::DATABASE_NAME to artie;
GO;
In order to enable CDC for SQL Server, you will enable it at the database and table level.
  1. Enable CDC for your database
USE [DATABASE_NAME];
EXEC sys.sp_cdc_enable_db;
-- If you're using RDS:
EXEC msdb.dbo.rds_cdc_enable_db [DATABASE_NAME];

-- Now specify the retention period of your CDC logs, retention is specified as mins.
-- We recommend you setting this between 24h to 7 days
EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 10080; -- 7 days
GO;
  1. Enable CDC for your tables
-- You can specify the service_account as the @role_name to restrict access
EXEC sys.sp_cdc_enable_table @source_schema = 'SCHEMA_NAME', @source_name = 'TABLE_NAME', @role_name = null;

​
Troubleshooting

When you alter the table’s schema, you will need to rotate the capture instances so that changes continue to be captured.
-- If you modify an existing table:
ALTER TABLE customers ADD phone_number VARCHAR(32);

-- You'll want to rotate the capture instances so that changes continue to be captured.
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'customers', @role_name = NULL, @supports_net_changes = 0, @capture_instance = 'dbo_customers_v2';
GO

-- After some time, you can then drop the old capture instance by running `sys.sp_cdc_disable_table`.
EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'customers', @capture_instance = 'dbo_customers';
GO
By default, sys.sp_cdc_enable_table will use the primary keys of the table as the unique identifiers. If you are running into issues with this, you can optionally set @index_name to an unique index of your choice.For example, if you had a table that looked like this
CREATE TABLE orders (
    id INTEGER IDENTITY(1001,1) NOT NULL PRIMARY KEY,
    order_date DATE NOT NULL,
    purchaser INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    FOREIGN KEY (purchaser) REFERENCES customers(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);
If you don’t want to use id, you can do something like this:
ALTER TABLE orders ADD COLUMN prefix VARCHAR(255) DEFAULT 'orders' NOT NULL;
-- Create unique index
CREATE UNIQUE INDEX cdc_index ON dbo.orders (id, prefix);
-- When enabling CDC, use cdc_index instead of the primary key
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'orders', @role_name = NULL, @index_name = 'cdc_index';