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.

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?

share|improve this question
    
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 Jun 13 '13 at 6:48

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
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 Jun 3 '13 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 Jun 8 '13 at 9:07

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.

share|improve this answer
    
We are using AlwaysOn Failover Cluster Instances not AlwaysOn Availability Groups –  Robert May 31 '13 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 May 31 '13 at 18:52
    
Is there a way for it to happen automatically? –  Robert Jun 3 '13 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 Jun 3 '13 at 16:06

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.

share|improve this answer

Your Answer

 
discard

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

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