Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
    
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 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 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 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 at 4:59
    
Then it sounds even more like that might be it. An interesting test would be to see what happens for sync mirroring on a test system. –  mrdenny Aug 20 at 6:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.