Change Data Capture gets disabled when you break mirroring and bring the database online

Issue

================

Recently we encountered an issue where customer performed a planned failover for a mirrored database and after the failover the new principal went into suspect mode because of some reason (which is outside the scope of this blog) and was not accessible. The database which was mirrored had CDC(Change Data Capture) enabled.

To make the database accessible to end users as soon as possible we broke the database mirroring by executing the below command on new mirror server.

"ALTER DATABASE DB1 SET PARTNER OFF" 

After which we recovered the database by executing the below command and brought the database online.

"RESTORE DATABASE DB WITH RECOVERY".

Now we found after executing the above command CDC was disabled for that database and we were getting CDC related job failure alerts.

The question is why was CDC disabled after breaking the mirroring and bringing the database online?  

Steps to simulate the issue

=======================

We were able to do the repro by following the below steps:

a) Enabled CDC on a DB

USE TESTDB

go

EXEC sys.sp_cdc_enable_db

GO

select name, is_cdc_enabled from sys.databases where name ='TESTDB'

name is_cdc_enabled

--------------------------- --------------

TestDB 1

b) Restored the DB with no recovery on other server.

c) Enable mirroring for the database.

d) Perform manual failover.

e) Broke the mirroring using the below command:

"ALTER DATABASE DB SET PARTNER OFF"----Breaks DB mirroring

"RESTORE DATABASE DB WITH RECOVERY"----Command to bring the mirror database online.

f) Now check the sys.databases you will find the is_CDC_enabled is 0 which means CDC is disabled.

select name, is_cdc_enabled from sys.databases where name ='TESTDB'

name is_cdc_enabled

--------------------------- --------------

TestDB 0

Analysis

===============

I ran the profiler trace to see what going on when we execute the command "Restore database DB WITH RECOVERY" and found something interesting.

Restore database command is calling the SP ''sp_cdc_restoredb'' and which internally is calling 'sys.sp_cdc_restoredb_internal' whose job is to cleanup up the CDC related metadata.

'sys.sp_cdc_restoredb_internal' -- Description: Cleans up CDC

And on further research found the article https://msdn.microsoft.com/en-us/library/cc645938(v=sql.105).aspx#RestoreOrAttach

Which says : “If a database is restored to another server, by default change data capture is disabled and all related metadata is deleted.”

How do we fix it

===============

After breaking DB mirroring when we are recovering a database using command "RESTORE DATABASE DB" to bring it online, we should use with Keep_CDC option to retain CDC.

RESTORE DATABASE DB WITH KEEP_CDC,RECOVERY;

Hope this helps.

Author: Phani Raj, Support Engineer, Microsoft India GTSC

Reviewed by:

Devashish Salgaonkar, Technical Lead, Microsoft India GTSC

Vikas Rana, Technical Lead, Microsoft India GTSC