Question

gutches on Wed, 06 Jul 2016 14:13:26


We are implementing Data Capture with our AlwaysOn environment SQL 2014 CU4. When an automatic Failover occurred Data Capture did not automatically failover. Even though the SQL Agent Data Capture jobs were on the Secondary Node they did not automatically start. To fix the issue I set the SQL Agent Data Capture job to run every 5 minutes and check to see if the Server Is Primary. If it is primary it executes the sys.sp_MScdc_capture_job. Is there a better way to make Data Capture failover automatically with AlwaysOn?

Sponsored



Replies

Lin Leng on Mon, 11 Jul 2016 05:01:42


Hi gutches,

By “Data Capture” did you mean Change Data Capture? If so, based on BOL the jobs should be created on all possible primary replicas. After that you can modify the job to check if the current is primary replica and that’s exactly what you have done. As far as I can tell it’s the only way to automatic failover the CDC jobs.

If you have any other questions, please let me know.

Regards,
Lin

Lin Leng on Sun, 17 Jul 2016 12:58:26


Hi gutches,

I’m writing to follow up with you on this post. Was the problem resolved? If you issue has resolved, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.

If you have any other questions, please let me know.

Regards,
Lin