Configure CDC for alerts on MS SQL Server - Precisely Data Integrity Suite

Enable Change Data Capture at the database schema level to prepare for table-level CDC configuration.

1. Check if CDC is enabled at the schema level.

   Run this query:

   SELECT name, is_cdc_enabled FROM sys.databases WHERE name ='<DatabaseName>';

   An output value of 1 means CDC is enabled. A value of 0 means it is not.

   Example output (CDC enabled)

   name | is_cdc_enabled |
   -----------------+--------------+
   SelectiveSchema | 1 |

2. Enable CDC at the schema level.

   Run this command:

   Use <DatabaseName>; EXEC sys.sp_cdc_enable_db;

   After successful execution, verify that CDC is enabled by running the check query again. It should return a value of 1.

   If CDC is not yet enabled, you'll see:

   SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'test';

   Output (CDC not enabled)

   name|is_cdc_enabled|
   ----+--------------+
   test| 0|

1. Enable CDC on the target table.

   EXEC sys.sp_cdc_enable_table @source_schema = '<schemaName>', @source_name = '<tableName>', @role_name = NULL;

   Replace <schemaName> with your schema name and <tableName> with the table name you want to monitor.

2. Update a record in the table, then run this command to verify CDC that CDC is capturing changes.

   DECLARE @tableName NVARCHAR(128) = '<tableName>', @schemaName NVARCHAR(128) = '<schemaName>', @captureInstance NVARCHAR(128), @sql NVARCHAR(MAX); IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'change_tables' AND schema_id = SCHEMA_ID('cdc')) BEGIN IF OBJECT_ID(QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName)) IS NOT NULL BEGIN SELECT @captureInstance = capture_instance FROM cdc.change_tables WHERE source_object_id = OBJECT_ID(QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName)); IF @captureInstance IS NOT NULL BEGIN SET @sql = N'SELECT TOP 1 sys.fn_cdc_map_lsn_to_time([__$start_lsn]) AS PRECISELY_VALUE FROM cdc.' + @schemaName + '_' + @tableName + '_CT ORDER BY __$start_lsn DESC;'; EXEC sp_executesql @sql; END ELSE RAISERROR('CDC is not enabled on the specified table %s.%s', 16, 1, @schemaName, @tableName); END ELSE RAISERROR('%s.%s : The specified table or view does not exist.', 16, 1, @schemaName, @tableName); END ELSE RAISERROR('CDC is not supported on this SQL Server instance.', 16, 1)

   Successful execution returns the timestamp of the change, for example:

   PRECISELY_VALUE
   2025-06-03 18:13:18.510

3. Verify that CDC data is captured in the change table.

   select * from cdc.<schemaName>_<tableName>_CT;

   Example:

   select * from cdc.dbo_product_CT;

   Example output (no data captured yet)

   __$start_lsn | __$end_lsn | __$seqval | __$operation | __$update_mask | productID | productName | productPlace | __$command_id

   To detect changes in the table, run:

   SELECT [__$start_lsn], [__$end_lsn], [__$seqval], [__$operation], [__$update_mask], EmployeeID, FirstName, [__$command_id] FROM SelectiveSchema.cdc.testSchema_Employees_CT;

   Example output (with captured changes)

   __$start_lsn | __$end_lsn | __$seqval | __$operation | __$update_mask | EmployeeID | FirstName | __$command_id
   5 | | 5 | 2 | | 211 | test | 1
    | | | | | 2 | | 221 | test | 1

4. Configure the retention period for change data.

   EXEC sys.sp_cdc_change_job @job_type = 'cleanup', @retention = 7;

   Note: The retention period determines how long change data is stored before the cleanup job removes it. The value is specified in minutes. The default is 3 days. Adjust this value based on your business requirements.

1. Use the verification queries from the schema-level and table-level tasks to confirm CDC is enabled at both the schema and table level.
2. Confirm that the CDC capture table has been created. Check that the change table exists with the naming convention <cdc>.<schemaName>_<tableName>_CT.
3. Check if the SQL Server Agent (CDC scheduler) is running.

   EXEC master.dbo.xp_servicecontrol 'QueryState', 'SQLServerAgent';

   Example output

   Name | Value
   Current Service State | Running
   or
   Current Service State | Sleeping

4. Start the SQL Server Agent using SQL Server Management Studio.
   1. In Object Explorer, expand the server where you want to manage the SQL Server Agent Service.
   2. Right-click SQL Server Agent and select Start, Stop, or Restart.
   3. In the User Account Control dialog, select Yes.
   4. When prompted to confirm the action, select Yes.
5. Check the status of CDC capture jobs.

   SELECT schedule.schedule_id As scheduleID, job.name AS JobName, schedule.name AS ScheduleName, schedule.enabled AS ScheduleEnabled, schedule.freq_type AS FrequencyType, schedule.freq_interval AS FrequencyInterval, schedule.active_start_date AS StartDate, schedule.active_start_time AS StartTime FROM msdb.dbo.sysjobs job JOIN msdb.dbo.sysjobschedules jobschedule ON job.job_id = jobschedule.job_id JOIN msdb.dbo.sysschedules schedule ON jobschedule.schedule_id = schedule.schedule_id WHERE job.name LIKE 'cdc%';

   Example output

   scheduleID | JobName | ScheduleName | ScheduleEnabled | FrequencyType | FrequencyInterval | StartDate | StartTime
   11 | cdc.DOAutomation_capture | CDC capture agent schedule | 1 | 64 | 0 | 20250612 | 0
   12 | cdc.DOAutomation_cleanup | CDC cleanup agent schedule | 1 | 4 | 1 | 20250612 | 20000
   9 | cdc.SelectiveSchema_capture | CDC capture agent schedule | 1 | 4 | 1 | 20250131 | 0
   10 | cdc.SelectiveSchema_cleanup | CDC cleanup agent schedule | 1 | 16 | 1 | 20250131 | 120000
   ScheduleEnabled values: 0 = not enabled, 1 = enabled

6. Update the CDC job frequency to run every 5 minutes.

   EXEC msdb.dbo.sp_update_schedule @schedule_id = 8, @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 5;

   Replace @schedule_id with the schedule ID from the previous query output. The @freq_interval value depends on the frequency type.

   Frequency type values:

   Value	Description
   1	One time only
   4	Daily
   8	Weekly
   16	Monthly
   32	Monthly, relative to freq_interval (for example, second Monday)
   64	When SQL Server Agent starts
   128	When the computer is idle