DECLARE @ReturnCode int
EXEC @ReturnCode = sys.sp_cdc_enable_db
Even though we performed the above action, nothing will happen until we define the tables and columns that it should monitor. When defining a table to monitor, there are a few parameters that need to be passed:
The schema name of the table to monitor (ex: dbo)
The name of the table to monitor
A database or server role which is used to grant access to the data. If the specified role does not exist, it will be created. Note: DB Owner role can always access the data
When enabled you are able to retrieve all changed values within a single row for a given time period (using LSN’s – Log Sequence Numbers). Otherwise, multiple rows are returned.
List of columns to capture. Must either include primary key, or specify a unique index using the @index_name parameter
Name of the filegroup to store the Change Data on
@source_schema = 'Sales' --mandatory
, @source_name = 'Customer' --mandatory
, @role_name = 'cdc_manager' --mandatory
, @supports_net_changes = 0
, @captured_column_list = 'CustomerID,CustomerType,TerritoryID'
, @filegroup_name = N'PRIMARY';
The first job, cdc.AdventureWorks_capture, is not much different than a replication job. Basically this job runs the repl log reader by executing it in an infinite loop. The next job, is the cleanup job which prunes the data by only retaining two days of data. (This is configurable however).
-- Update some records
UPDATE TOP (1) Sales.Customer
SET CustomerType = 'S'
WHERE CustomerType != 'S'
-- Query the capture table
From the capture table output, we see two records. One record represents the original value (update mask = 3), while the second record represents the new changed value (update mask = 4).