Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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)

share|improve this answer
    
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 '13 at 8:39

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

share|improve this answer
    
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 '12 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 '12 at 2:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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