7

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.

Improve this question

2 Answers 2

Reset to default
6

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)

Improve this answer
1
  • CDC enabled databases can be added to AlwaysOn Availability Groups, and databases that are members of an AlwaysOn availability group can be enabled for CDC. In both cases, however, CDC configuration is always performed on the current or intended primary replica.
    – Ruud van de Beeten
    Oct 24, 2013 at 8:39
1

If the CDC/CT data is written to the local database it'll be available on the read only secondary.

Improve this answer
4
  • Do you know of any Microsoft documentation that describes this? I'd really like to know if there are any best practices or gotchas in this type of deployment.
    – Matt O'Brien
    Aug 16, 2012 at 19:57
  • Everything written to the local database is fed to the secondary servers. This is done via the transaction log being streamed from one server to another so everything being written to the primary is sent to the secondary servers.
    – mrdenny
    Aug 18, 2012 at 2:21
  • @mrdenny -- i know this is sort of old post but is it possible to not have CDC enabled on readable secondary and yet keep it enabled in primary
    – BeginnerDBA
    Nov 7, 2018 at 22:17
  • No. If it's enabled on the primary, those transactions would go to the secondary as well.
    – mrdenny
    Nov 21, 2018 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.