Enable CDC with Database Mirroring environment

Hi SQL Server Folks – Recently I got a situation where database mirroring was setup in my production environment, but due to other needs I need to enable CDC (Change Data Capture) feature also at my SQL Server 2008 R2 instance. If you wants to know about CDC and its usage, you can follow below link :

http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

To explain & simplify the situation in our environment I’m taking an example here :

1. We had a Server A and Server B. Database Mirroring on a database name as “Test” was setup from Server A to Server B, here Server A was principal and Server B was acting as Mirror.

2. We enabled the CDC feature at Server A @ database “Test” at “Emp” table. As soon as you enable the CDC it will create two jobs at Server A with name as below :
 cdc.<db_name>_capture
cdc.<db_name>_cleanup

Also it will create a system table name as cdc_jobs under msdb system database that will keep the information about above 2 jobs.

3. But here the problem is, it will not create those above jobs and system table at Server B. So if you fail over the database “Test” from Server A to Server B, changes that you made on emp table will not capture in CDC related tables at server B. So to make CDC effective on both the servers we need to create above 2 CDC jobs at Server B also. To create the above 2 jobs at Server B follow the below below process :

a)  Stop and disable both CDC jobs at Server A.

b) Fail over the database mirroring for “Test” from Server A to B.

c) Connect to Server B now since here db “Test” is Principal now and

fire the below commands :

Use  Test                                                                                                                                                              go

EXEC sys.sp_cdc_add_job @job_type = N’capture’;                                          

GO

EXEC sys.sp_cdc_add_job    

@job_type = N’cleanup’, 

@start_job = 0,    

@retention = 5760;

d) After executing above statements It will create 2 CDC jobs (CDC Capture & CDC cleanup) and a system table name as cdc_jobs in msdb database at Server B.  So now if you will make any  transaction at Server B at emp table  under “Test” db, it will also capture under CDC related tables and will mirror to mirrored Server A.

Hope this article will help, Thanks for reading and stay tune for upcoming posts.

Cheers,                                                                                                                                                         Gaurav

Advertisements

3 Responses

  1. Hi Team ,
    Thanks for the wonderful blog on CDC mirroring .

    I have one question :
    I have 2 databses that are CDC enabled . These databases are mirrored . When i am running SSIS package , On failure of Primary DB , My SSIS package is not switching on to Secondary DB . I have follwed your above approach and in both the DBs , the two jobs are there (Capture and clean up) . error : cannot aquire runtime connection

  2. This inh’istgs just the way to kick life into this debate.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: