2

We currently have in place CDC, DW team is reading data, everyone happy. Then we needed to add couple new columns. Easy peasy following this answer... well, actually we had some hiccups but that's solved. But that was on a small environment just to check that the steps to follow are ok. Now is time to deploy that on a way bigger environment. So, question:

Is there a way to monitor CDC while the 2nd capture instance is running populating data in parallel with the old capture instance? Extended events maybe? Any recommendation on what to look? IO, log growth, storage, memory grants maybe?

1

We had a similar requirements like yours to have CDC enabled currently for one of ur database around 5 TB in size.

There are various parameters that you might want to consider when enabling CDC for capture job. Ones like maxtrans and maxscans really matter for what you want to extract regarding the changes happening in log file for tables configured for CDC

Please read here for more on parameters I am discussing above. Also it list dmvs to monitor the scan session for logs and latencies.

Also keep a monitor on transaction log file for both the user database involved in cdc along with growth of tempdb growing unusual. Might not be a scenario in your case but just telling you from our experiences.

I am not able to recall but I believe we browsed somewhere for the event that can monitor performance for CDC using xtended events. Apart from this you need to have a normal DBA monitoring of that sql server which we dbas have to do to avoid any last minute surprises.

  • Perfect scenario, someone that has gone through exact same situation on similar environment. Will check that link (how come I didn't found it?!). If you can remember that event to monitor perf for CDC with xEvents would be awesome, I'll keep searching, so far I've not found it – Yaroslav Jan 29 at 23:31
  • Yes set the job as mention in msdn article which also uses some xtended event to capture or trace I guess . But do check. For us monitoring was easy because we had third party tools like sentry to help us figure out. Also let me warn you in case you have mirroring configured for the database to be enabled with CDC. Failover is tricky. – KASQLDBA Jan 29 at 23:47
  • nope, thankfully no mirroring, and nor 3rd part tools, used to have sentryone on previous place, really handy and neat tool – Yaroslav Jan 30 at 0:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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