3

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?
Improve this question
7
  • This sounds really bad, especially when you consider the scenario where a mirror may be down for a significant amount of time. Can you reproduce this on demand?
    – Aaron Bertrand
    Aug 20, 2014 at 3:00
  • I think so. We only have the mirror setup in production so it's not easy to test on demand, but the last 2 days we've paused the mirror and the last two days the cdc data didn't process while the mirror was paused.
    – Greg
    Aug 20, 2014 at 3:45
  • I'm betting that pausing the mirror stops the log reader process, which is what async mirroring and CDC are using. This is probably happening on your sync mirror, because (I'm guessing here) that it's just change from sync to async under the covers and then the log reader is disabled. It makes sense that it would work this way. Doesn't give you a fix however.
    – mrdenny
    Aug 20, 2014 at 4:31
  • @mrdenny - sounds plausible. We are running the mirror as async normally as we don't want the latency we get with sync and the CDC data works as expected. Like I said though, the Log Reader being disabled sounds plausible
    – Greg
    Aug 20, 2014 at 4:59
  • 1
    @AaronBertrand We're currently testing out SQL 2014 using AlwaysOn availability groups. We setup CDC and 3 servers - 2 for our primary site and a DR server. The 2 primary are synchronous replication and the DR is async. If any of the servers goes offline (primary or not) CDC stops collecting.
    – Greg
    Mar 27, 2015 at 5:04

1 Answer 1

Reset to default
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://learn.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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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