Microsoft SQL Server 2012 Bible

New in SQL Server 2012

Although things haven't necessarily changed for CDC in SQL Server 2012, if you want to use CDC with the new AlwaysOn features, you need to know the ground rules. In short: CDC has to read the data from the Primary Replica. On failover, CDC can pick up on the new Primary where it left on the old Primary. However, if you haven't set up the SQL Agent Jobs on the failover machine, you need to do that on failover for everything to work properly. It might be advisable to set up the connection string to use the Primary Replica's Availability Group Listener name instead of the node name so that things continue to work on failover.

When reading from the CDC Change Tables, you can do so at the Primary or the Secondary. Obviously reading the data from the Secondary can help take the performance load off your Primary. If you decide to read the Change Table data at the Secondary, you must specify the connection with readonly intent. Optionally, you can connect to individual nodes of your Secondary, or you can specify the Availability Group name of the Secondary and have your query automatically routed to whichever Secondary is currently available.

For more information on AlwaysOn see Chapter 27, “Database Mirroring.”