Can we access Change Data Capture and Change Tracking on readable replica?

[Guest tarashee]

In many cases, it is very useful to have the ability to use a readable secondary replica to offload read only workloads and gain more resources for your read queries. Like for example the queries that are used for Reporting and BI.

 

 

 

Sometimes, accessing the records of tables and rows modification is a business requirement, and accessing these data on a readable replica will be better to avoid extra load on the primary instance.

 

 

 

SQL Server, Azure SQL and Managed Instance are providing two features that are used to record these Activities:

 

- Change data capture CDC

 

- Change Tracking CT

 

 

 

Readable Secondaries are available on features: Azure SQL Read Scale-out , Azure SQL Active geo-replication, Azure SQL Failover Group and Managed Instance failover Group .

 

 

 

Now, can we access CDC objects, and gather CDC data from the secondary node?

 

Or, can we access CT objects and data there also?

 

 

 

We can use and access CDC data on secondaries, but it needs to be enabled on the primary database first. But CT data is not accessible on Secondaries.

 

 

 

Please find below example of enabling CDC on secondary:

 

1. check CDC if enabled.
   
   
2. Enable CDC (for Database and tables) on Primary:
    
   EXEC sys.sp_cdc_enable_db --primary
   GO
   EXEC sys.sp_cdc_enable_table
   @source_schema = N'dbo', --schema name
   @source_name = N'T', --table name
   @role_name = N'cdc_Admin',
   @supports_net_changes = 0
    
    
    
    
    
    
    
    
   You can't run the CDC scripts on secondary, it will fail with below error. No need to try to enable CDC on secondary because it will be already enabled:
    
   
   
3. Retrieving CDC data:
   EXECUTE sys.sp_cdc_help_change_data_capture
   @source_schema = N'dbo',
   @source_name = N't';
    
    
   It can be done on primary and on Secondary:
    
   
   

 

 

 

Trying to access CT on Secondary:

 

1. Enable CT:
   -- the table must have a primary Key: create table CT (id int primary key, name varchar(10))
   ALTER TABLE CT ENABLE CHANGE_TRACKING
    
    
    
    
    
   
2. Query tracked data on Primary:
   SELECT
   c.SYS_CHANGE_VERSION, c.SYS_CHANGE_CONTEXT
   FROM ct AS e
   CROSS APPLY CHANGETABLE(VERSION ct, ([iD]), (e.id)) AS c;
    
   
   
3. On secondary, CT is enabled but CT data is not accessible:
   

 

 

 

Accessing CT data will fail with Error:

 

 

 

 

 

Msg 22117, Level 16, State 1, Line 1

For databases that are members of a secondary availability replica, change tracking is not supported. Run change tracking queries on the databases in the primary availability replica.

The same error you are getting from app

Wed Sep 28 16:15:22 GST 2022: Error(SSP73-53): com.microsoft.sqlserver.jdbc.SQLServerException: For databases that are members of a secondary availability replica, change tracking is not supported. Run change tracking queries on the databases in the primary availability replica

 

 

 

 

 

 

 

 

 

Conclusion:

 

Accessing Change Tracking data is not supported on replica.

 

But for Change Data Capture, it is possible to query it data on replica.

 

 

 

Additional Information:

 

I found also that as per the documentation CDC and CT on Always On availability group : Always On Availability Group replicas as well, CT has the same limitation.

 

 

 

CDC for Azure SQL is in Public preview:

 

Introducing Change Data Capture for Azure SQL Databases (Public Preview) - Microsoft Community Hub

 

Continue reading...