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;
To access change tracking information by using the change tracking functions, the principal must have the following permissions:SELECT permission on at least the primary key columns on the change-tracked table to the table that is being queried.VIEW CHANGE TRACKING permission on the table for which changes are being obtained. The VIEW CHANGE TRACKING permission is required for the following reasons:Change tracking records include information about rows that have been deleted. The records use the primary key values of the rows that have been deleted. A principal could have been granted SELECT permission for a change tracked table after some sensitive data had been deleted. In this case, you wouldn’t want that principal to be able to access that deleted information by using change tracking.Change tracking information can store information about which columns have been changed by update operations. A principal could be denied permission to a column that contains sensitive information. However, because change tracking information is available, a principal can determine that a column value has been updated, but the principal cannot determine the value of the column.Reference
In order to enable Change Tracking for SQL Server, you will enable it at the database and table level.
  1. Enable Change Tracking for your database
USE [DATABASE_NAME];
ALTER DATABASE [DATABASE_NAME]
SET CHANGE_TRACKING = ON
-- Specify the retention period of your Change Tracking tables.
-- We recommend you setting this between 24h to 7 days
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
  1. Enable CDC for your tables
ALTER TABLE [TABLE NAME] ENABLE CHANGE_TRACKING;

​
Troubleshooting

Microsoft Troubleshooting DocumentationCheck Change Tracking space usage
DECLARE @object_id INT;
DECLARE @change_table_name NVARCHAR(MAX);
DECLARE @sqlCommand NVARCHAR(MAX);

SET @object_id = OBJECT_ID([TABLE NAME]);
SET @change_table_name = (SELECT TOP 1 name FROM sys.internal_tables where parent_object_id = @object_id);
SET @sqlCommand = N'EXEC sp_spaceused N''' + 'sys.' + @change_table_name + N'''';
EXEC sp_executesql @sqlCommand;
⌘I