Change Data Capture for Asynchronous Secondary Replica

Question

MSDN states the following in Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups) for SQL Server 2014:

Limitations and Restrictions:

Change tracking and change data capture are not supported on secondary databases that belong to a readable secondary replica:

• Change tracking is explicitly disabled on secondary databases.
• Change data capture can be enabled on a secondary database, but this is not supported.

This confuses me: You can not track the changes. However you can enable CDC?

The scenario I am trying to achieve is to use SSIS CDC components on an asynchronous secondary replica. Is this possible? If not what would be other viable approaches?

Answer 1

Change Tracking and Change Data Capture are, while having similar goals, actually separate features. For AlwaysOn Availability groups, the limitations indicate that you can't use the synchronous Change Tracking feature at all, though the asynchronous Change Data Capture is possible to enable: It's just not a feature Microsoft will support for Active Secondaries.

Generally, things like that aren't supported because there exists a supported way to do so. In this case, enabling CDC on the primary node of an AlwaysOn Availability Group is completely supported, allowing the captured changes to be replicated along to the Active Secondaries, so would likely be the path you should explore for your SSIS needs on the secondaries.

You can read more about the differences between Change Tracking and Change Data Capture here, and more about setting up CDC on the primary replica here.