Change Data Capture not running when Mirror is Suspended

by Greg   Last Updated July 24, 2017 15:06 PM

We have 2 SQL 2012 SP2 Enterprise servers that have some mirrored databases. One of these databases also has CDC enabled on a number of tables so that we can run some business logic when there are changes to the table.

Yesterday we did some maintenance on the mirror server (the primary was running exposed). A few hours later we re-enabled the mirror and it synchronised as expected.

What we found out was that the CDC data that we normally process close to real time didn't get collected until after the mirror was resumed.

2 questions out of this:

  • When you have a mirror configured, is CDC data only collected when the mirror is synchronized?
  • Is there anyway to make CDC capture run when a mirror is paused?


Answers 1


CDC uses the log reader, same as replication. When mirroring or availability groups are used, by default the log reader will not process log records that have not already been hardened at all mirroring / availability group secondary replicas.

This can be changed with Trace Flag 1448, which enables the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change. Even with this trace flag enabled, the log reader always waits for the synchronous secondary replicas.

References:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability#CDC

https://support.microsoft.com/en-us/help/937041/fix-changes-in-the-publisher-database-are-not-replicated-to-the-subscr

Alex Friedman
Alex Friedman
July 24, 2017 15:02 PM

Related Questions


What tables are affected by Change Data Capture

Updated May 21, 2015 06:02 AM


Change Data Capture in a services environment

Updated March 28, 2015 06:02 AM