Microsoft SQL Server Source: Change Tracking

​

What you’ll need

• SQL Server 2012 and later, or Azure SQL Managed Instance, or Amazon RDS for SQL Server
• A service account with SELECT and VIEW CHANGE TRACKING on each replicated table
• Change tracking enabled on the database and on each table you replicate
• A primary key on each replicated table. Primary keys override isn’t supported with change tracking.

​

What change tracking captures

• Inserts and updates carry the row’s values at the time Artie reads it, not at the time of the change. If a row was updated three times between polls, the destination gets only the last version.
• Deletes carry only the primary key.
• The event timestamp is when Artie read the change, not when it was committed.
• Rows deleted right after a change can arrive first as an update with only the primary key set and every other column NULL. The delete follows on the next poll.

​

Setup

USE [ANALYTICS];
CREATE LOGIN artie WITH PASSWORD = 'use-a-strong-password';
CREATE USER artie FOR LOGIN artie;
ALTER LOGIN artie ENABLE;
GRANT SELECT ON DATABASE::ANALYTICS TO artie;
GRANT VIEW CHANGE TRACKING ON dbo.orders TO artie;
GO

ALTER DATABASE [ANALYTICS]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);
GO

USE [ANALYTICS];
ALTER TABLE dbo.orders ENABLE CHANGE_TRACKING;
GO

​

Advanced

USE [ANALYTICS];
DECLARE @object_id INT = OBJECT_ID(N'dbo.orders');
DECLARE @change_table_name NVARCHAR(MAX) =
  (SELECT TOP 1 name FROM sys.internal_tables WHERE parent_object_id = @object_id);
DECLARE @sqlCommand NVARCHAR(MAX) = N'EXEC sp_spaceused N''sys.' + @change_table_name + N'''';
EXEC sp_executesql @sqlCommand;

​

Limitations

• Intermediate updates and before values aren’t captured. See What change tracking captures.
• Primary keys override isn’t supported. Artie joins on the table’s real primary key.
• If the pipeline is down longer than the change tracking retention period, the cleaned-up changes are lost. Backfill the affected tables.