955
Chapter 41: Data Change Tracking and Capture
41
There are only a few drawbacks to CDC:■ Cost: It requires Enterprise Edition.■ (^) T-Log I/O: The transaction log experiences about twice as much I/O because CDC
reads from the log.
■ (^) Disk space: Because CDC essentially stores copies of every transaction’s data, with-
out proper maintenance there’s the potential that it can grow like a transaction log
gone wild.
■ SSIS: You will probably need to set up an SSIS package for the data.
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.”Enabling CDC
CDC is enabled at the database level fi rst and then for every table that needs to be tracked.
Because CDC reads from the transaction log, you might think that CDC requires the data-
base to be set to full recovery model so that the transaction log is kept. However, SQL
Server doesn’t fl ush the log until after the transactions have been read by CDC, so CDC can
work with any recovery model, even simple.Also, and this is important, CDC uses SQL Agent jobs to capture and clean up the data, so
SQL Agent must be running or data will not be captured.c41.indd 955c41.indd 955 7/31/2012 10:17:21 AM7/31/2012 10:17:21 AM
http://www.it-ebooks.info