3

Does Change Data Capture (CDC) work in a SQL Server 2012 AlwaysOn Failover Cluster Instances setup?

There would be two nodes in the AlwaysOn Failover Cluster Instances setup and two Domain Controllers.

We are not using AlwaysOn Availability Groups.

Will CDC work? and will it failover?

Improve this question
1
  • If CDC works with Failover Clustered Instances Please show me how to set up. because my boss order me to set up this solution Thanks very much
    – user24914
    Commented Jun 13, 2013 at 6:48

3 Answers 3

Reset to default
3

Yes CDC works with Failover Clustered Instances. It has since it was first introduced. There's nothing special that needs to be done to get it working. Just set it up like you would on a standalone non-clustered instance.

Improve this answer
2
  • Thanks. And after failover it will keep running or is there an manual task to start the SQL Agent to get CDC working on the new node?
    – Robert
    Commented Jun 3, 2013 at 10:52
  • If you are using traditional failover clustering (now called AlwaysOn Failover Clustering) and not AlwaysOn Availability Groups then everything will just work. Nothing special needs to be done.
    – mrdenny
    Commented Jun 8, 2013 at 9:07
4

Refer to Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups (SQL Server)

From the reference :

Change Data Capture:

Databases enabled for change data capture (CDC) are able to leverage AlwaysOn Availability Groups in order to insure not only that the database remains available in the event of failure, but that changes to the database tables continue to be monitored and deposited in the CDC change tables.

The order in which CDC and AlwaysOn Availability Groups are configured is not important. CDC enabled databases can be added to AlwaysOn Availability Groups, and databases that are members of an AlwaysOn availability group can be enabled for CDC. In both cases, however, CDC configuration is always performed on the current or intended primary replica.

Harvesting Changes for Change Data Capture Without Replication

If CDC is enabled for a database, but replication is not, the capture process used to harvest changes from the log and deposit them in CDC change tables runs at the CDC host as its own SQL Agent job. In order to resume the harvesting of changes after failover, the stored procedure sp_cdc_add_job must be run at the new primary to create the local capture job.

Improve this answer
4
  • We are using AlwaysOn Failover Cluster Instances not AlwaysOn Availability Groups
    – Robert
    Commented May 31, 2013 at 18:38
  • It will follow the same process .. In order to resume the harvesting of changes after failover, the stored procedure sp_cdc_add_job must be run at the new primary to create the local capture job.
    – Kin Shah
    Commented May 31, 2013 at 18:52
  • Is there a way for it to happen automatically?
    – Robert
    Commented Jun 3, 2013 at 15:55
  • Yes, on the passive node, you can create an SP that will execute during startup or just schedule it using SQLAgent Job that run at startup . So when sql server will failover to Passive node, it will execute the SP first .. Automatically.
    – Kin Shah
    Commented Jun 3, 2013 at 16:06
0

I'm pretty certain adding a startup job is not the way to go. In always on fail over, there is no SQL restart. What you need to do is script the job that is created and add code to check if passive then do nothing. You will want to modify the automatic job that is created as well.

Improve this answer

Your Answer

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

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