Configuration for Microsoft SQL connection

Data Integrity Suite

Product

Spatial_Analytics

Data_Integration

Data_Enrichment

Data_Governance

Precisely_Data_Integrity_Suite

geo_addressing_1

Data_Observability

Data_Quality

dis_core_foundation

Services

Spatial Analytics

Data Integration

Data Enrichment

Data Governance

Geo Addressing

Data Observability

Data Quality

Core Foundation

ft:title

Data Integrity Suite

ft:locale

en-US

PublicationType

pt_product_guide

copyrightfirst

2000

copyrightlast

2025

To enable Freshness Alerts for assets retrieved by the Agent, it is essential to configure and validate the Change Data Capture (CDC) feature, ensuring that database tables remain up to date. When CDC is enabled, the system will return the Last Modified timestamp. If CDC is not yet active, it must be enabled prior to generating freshness alerts.

You require the following permissions to enable CDC feature for MSSQL connections

Permissions on CDC system views: SELECT on sys.cdc.change_tables and other views

Permissions on CDC functions: EXECUTE on sys.fn_cdc_get_all_changes_ and sys.fn_cdc_get_net_changes

Permissions on source tables: SELECT on the actual user tables

Permissions on change tables: SELECT on the change tables. This is often not required as most users interact with the CDC data via functions.

SQL TO Grant Permission

Grant SELECT on the CDC change tables view
GRANT SELECT ON sys.cdc.change_tables TO [UserName];
Grant EXECUTE on the CDC function for change data retrieval

GRANT EXECUTE ON sys.fn_cdc_get_all_changes_YourCaptureInstance TO [UserName];

GRANT EXECUTE ON sys.fn_cdc_get_net_changes_YourCaptureInstance TO [UserName];

Once the required permissions are assigned, you can follow the steps below to enable the CDC feature:

Follow the steps below to enable the CDC feature at the workspace level.
1. Check if the CDC feature is enabled at the schema level and table level.
  Command to check if CDC is enabled at the schema level:
  
  SELECT name, is_cdc_enabled FROM sys.databases WHERE name ='<DatabaseName>'; SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'SelectiveSchema';
  
  If the output value is 1, it indicates that the CDC feature is enabled.
  
  Output
  
  name |is_cdc_enabled|
  
  -----------------+--------------+
  
  SelectiveSchema| 1|
2. To enable the CDC feature
  - At schema level
  Query to be executed
  
  Use <DatabaseName>; EXEC sys.sp_cdc_enable_db;
  
  Upon successful execution, the CDC feature will be enabled.
  
  To verify, if the CDC feature is enabled, execute the following query again. This would return a value of 1 indicating that the CDC feature is enabled.
  Scenario wherein CDC is not enabled at the schema level:
  SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'test';
  Output
  
  name|is_cdc_enabled|
  
  ----+--------------+
  
  test| 0|
  
  If the output value is 1, it indicates that the CDC feature is enabled. An output value of 0 indicates that the CDC feature is not enabled.
  
  Check if CDC is enabled at the table level:
  
  To check if CDC feature is enabled at the table level, execute the following query, which upon successful execution will create a table as mentioned below:
  
  <cdc.<schemaName>_<tableName> _CT>
  
  Query to be executed
  
  SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'SelectiveSchema';
  
  Output
  name |is_cdc_enabled| -----------------+--------------+ SelectiveSchema| 1|
  - At table level
  To check if CDC feature is enabled at the table level, execute the following query, which upon successful execution will create a table as mentioned below:
  
  <cdc>.<schemaName>_<tableName> _CT
  
  If the CDC feature is not enabled, enable it using the following command at the table level.
  
  Query to be executed:
  
  EXEC sys.sp_cdc_enable_table @source_schema = '<schemaName>', @source_name = '<tableName>', @role_name = NULL;
  
  Once the feature is enabled, to verify the same, update the table in the database and execute the following command.
  
  Query to be executed:
  
  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)
  
  Upon successful execution, an on output will be returned with the timestamp of execution
  
  Output
  
  PRECISELY_VALUE |
  
  -----------------------+
  
  2025-06-03 18:13:18.510|

Verify if CDC data is captured in the table

Query to be executed

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

Output when no data is present

------------+----------+---------+------------+--------------+---------+-----------+------------+-------------+

Execute the following query to detect changes in the table or schema

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

Output

------------+----------+----------+------------+--------------+----------+---------+-------------+

5 | | 5 | 2| | 211|test | 1|

| | | | | 2| | 221|test | 1|

After enabling CDC, update retention period:

Query to update retention period:

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

Retention Period: The retention period is configurable and determines how long change data is stored before it is removed by the cleanup job. The retention value provided is in minutes. When enabling CDC, the retention period can be set, with a default value of 3 days. This duration can be modified according to business requirements.

Steps To Debug If Data is not being captured in CDC table post changes into table.

Verify CDC is enabled at Schema level and Table Level
Cross check that CDC capture table has been created.

Check if CDC scheduler is running or not. If it is not running then run it.

Query to run CDC scheduler:

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

Output

Name |Value |

---------------------+--------+

Current Service State|Running.|

Name |Value |

---------------------+--------+

Current Service State|Sleeping.|

Start the SQL Server Agent: Use SQL Server Management Studio
1. In Object Explorer, select the plus sign to expand the server where you want to manage SQL Server Agent Service.
2. Right-click SQL Server Agent, and then select either Start, Stop, or Restart.
3. In the User Account Control dialog box, select Yes.
4. When prompted if you want to perform the action, select Yes.

Capture Jobs running status

Query to be executed:

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%';

Output

----------+---------------------------+---------------------------+---------------+-------------+-----------------+---------+---------+

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|

Schedule Enabled 0/1

0 - not enabled

1 – enabled

Update cdc job frequency to run on every 5 minutes.

Query to be executed:

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

schedule_id : Can be obtained from the point note

freq_interval: Depends on Freq type.

freq_type

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