Sql-server – Can CDC or Change Tracking be against a read only secondary

availability-groupschange-data-capturechange-trackingsql serversql-server-2012

We will be deploying SQL Server 2012 Availability Groups with read only secondaries and would like to execute our SSIS extracts against the secondary while utilizing CDC or change tracking (the decision hasn't been made as to which will be used at this time).

I am hoping that the underlying implementations of CDC and CT would allow for their functionality to exist and be consumed on the secondary but have not yet been able to find anything stating this one way or the other.

Does anyone have any experience (and better yet official documentation) that points to the viability of this solution (or not)?

We are leaning toward Change Tracking at this point because we do not have a hard requirement for historical data but I would like to be able to identify any issues/risks for either implementation.

Best Answer

I have been able to find the documentation that I was looking for. CDC is supported off of the readable secondary but Change Tracking is not unfortunately.

The link is specified below but here are the relevant parts for my needs.

Redirecting the Query Load to a Readable Secondary

While in many cases a client application will always want to connect to the current primary replica that is not the only way to leverage AlwaysOn availability groups. If an availability group is configured to support readable secondary replicas, change data can also be gathered from secondary nodes.

When an availability group is configured, the ALLOW_CONNECTIONS attribute associated with the SECONDARY_ROLE is used to specify the type of secondary access supported. If configured as ALL, all connections to the secondary will be allowed, but only those requiring read only access will succeed. If configured as READ_ONLY, it is necessary to specify read only intent when making the connection to the secondary database in order for the connection to succeed.

Change Tracking Note

Change tracking data must always be obtained from the primary replica. An attempt to access change data from a secondary replica will result in the following error:

Msg 22117, Level 16, State 1, Line1

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

Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups (SQL Server)