CDC Interoperability with Mirroring and Recovery

CDC Interoperability with Mirroring and Recovery

The following email came in to a discussion group last week asking several questions about CDC including the interoperability of CDC and database mirroring.

The email:

Dear experts

Here are some questions regarding CDC in SQL Server 2008:

1. What will happen to CDC if we truncate or backup database log files? Will the operation wait for CDC log parser to complete all of the transactions?
2. If disaster occurs, what will happen to CDC and what we should do to ensure the transactional consistency?
3. Is it possible to combine database mirroring technology with CDC? Are there any possible risks?

Any information would be appreciated, thanks in advance!

My response:

1. CDC uses the same log reader as replication. It will preserve transactions in the log until CDC has consumed them. It won’t allow you to truncate that part of the log file.
2. That completely depends on what you mean by disaster. If you are simply asking about restoring from backup, then you can use the KEEP_CDC option to retain the CDC metadata and tracked changes. It will be transactionally consistent as to the time of the backup. Anything outside of the database, such as the CDC jobs, will need to be created separately.
3. Yes, CDC is fully compatible with database mirroring. CDC is preserved and fully functional on failover. You can even read the CDC data from a database snapshot of the mirror database.

Let’s Prove It

I did some tests to validate (never a bad idea) exactly how CDC works with database restores on a different machine. Rather than stepping in and out of the code in this post, I included comments inline to indicate what I’m doing with each piece of code. This first set of code is run on my default instance to set up the database and CDC.

-- Create New Database
Create Database CDCTest;
Go

-- Switch to Database
Use CDCTest;
Go

-- Add Filegroup for CDC data
Alter Database CDCTest Add Filegroup CDCData;
Go

-- Add File for CDC Data
Alter Database CDCTest Add File(
    Name = N'CDCData',
    Filename = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\CDCData.ndf')
    To Filegroup CDCData;
Go

-- Enable CDC for the database
Exec sp_cdc_enable_db;
Go

-- Create a table with some data in it
Select *
Into dbo.MasterFiles
From sys.master_files
Go

-- Add Primary Key to table
Alter Table dbo.MasterFiles
    Add Constraint PK_MasterFiles Primary Key 
    (database_id, file_id);
Go

-- Enable CDC on the table
Exec sp_cdc_enable_table @source_schema = 'dbo',
    @role_name = Null,
    @source_name = 'MasterFiles',
    @filegroup_name = 'CDCData'
Go

-- Create full backup before making any data changes
Backup Database CDCTest To Disk = 'c:\bak\CDCTest.bak';
Go

-- Delete top 5 entries in the tables
Delete Top(5) dbo.MasterFiles;
Go

-- Back up the log. Want to demonstrate that CDC changes are in the log backups
Backup Log CDCTest To Disk = 'c:\bak\CDCTest.trn';
Go

Now that I have everything set up on my default instance, I want to verify what changes have been captured by CDC:

-- Query CDC for all changes to the table
Declare @from_lsn binary(10),
    @to_lsn binary(10)

Select @from_lsn = MIN(__$start_lsn)
From cdc.dbo_MasterFiles_CT;

Set @to_lsn = sys.fn_cdc_get_max_lsn();

Select __$start_lsn, __$seqval, __$operation, __$update_mask,
	database_id, file_idFrom cdc.fn_cdc_get_all_changes_dbo_MasterFiles(
    @from_lsn,
    @to_lsn,
    N'all');

Results:

Now to switch to my 2nd instance on the same machine named SQL2. Because this is on the same machine, I have to be sure to move the database files to different file paths in the restores.

Test #1: Restore Full backup without using KEEP_CDC option:

Restore Database CDCTest From Disk = 'c:\bak\CDCTest.bak'
    With Move 'CDCTest' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCTest.mdf',
        Move 'CDCData' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCData.ndf',
        Move 'CDCTest_log' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCTest_log.LDF';
Go

Select is_cdc_enabled
From sys.databases
Where name = 'CDCTest'

Select COUNT(*)
From CDCTest.sys.tables
Where name = 'dbo_MasterFiles_CT'
Go

So you can see that simply restoring the database does not keep CDC enabled or retain the CDC tables. If you are restoring the database onto the same server, it will automatically retain the CDC settings and data, but not if on another server. You have to use the KEEP_CDC option. Let’s try it again. This time I’ll restore the full backup using the KEEP_CDC option.

Restore Database CDCTest From Disk = 'c:\bak\CDCTest.bak'
    With Move 'CDCTest' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCTest.mdf',
        Move 'CDCData' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCData.ndf',
        Move 'CDCTest_log' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCTest_log.LDF',
        Replace,
        KEEP_CDC;
Go

Select is_cdc_enabled
From sys.databases
Where name = 'CDCTest'

Select COUNT(*)
From CDCTest.sys.tables
Where name = 'dbo_MasterFiles_CT'
Go

Use CDCTest;

Select COUNT(*)
from cdc.dbo_MasterFiles_CT
Go

Using the KEEP_CDC option with the restore command restore the database with CDC enabled, and the table is there. Why is the table empty though? Do we lose the CDC data that already existed?

The answer is quite simple. The table is empty because it was empty when I made the full backup. The deletes were performed after the full backup. If I restore the log file, the data should be in the table, right? Let’s see. In the restore below, you will note that I don’t use KEEP_CDC until the final log file restore. The KEEP_CDC and NoRecovery options are incompatible. If you include both options, you will get an error. Use KEEP_CDC only when you are completing the recovery.

Use master;
Go

Restore Database CDCTest From Disk = 'c:\bak\CDCTest.bak'
    With Move 'CDCTest' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCTest.mdf',
        Move 'CDCData' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCData.ndf',
        Move 'CDCTest_log' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCTest_log.LDF',
        Replace,
        NoRecovery;
Go

Restore Log CDCTest From Disk = 'c:\bak\CDCTest.trn'
    With Keep_CDC;
Go

Select is_cdc_enabled
From sys.databases
Where name = 'CDCTest'

Select COUNT(*)
From CDCTest.sys.tables
Where name = 'dbo_MasterFiles_CT'
Go

Use CDCTest;

Select COUNT(*)
from cdc.dbo_MasterFiles_CT
Go

So this time, there are 5 records in the table. This should be the result of the 5 columns we deleted in the original database. Let’s query for all changes again to see if we get the same information as before.

Declare @from_lsn binary(10),
    @to_lsn binary(10)

Select @from_lsn = MIN(__$start_lsn)
From cdc.dbo_MasterFiles_CT;

Set @to_lsn = sys.fn_cdc_get_max_lsn();

Select __$start_lsn, __$seqval, __$operation, __$update_mask,
    database_id, file_id
From cdc.fn_cdc_get_all_changes_dbo_MasterFiles(
    @from_lsn,
    @to_lsn,
    N'all');
Go

Now we have the database restored with CDC enabled, the CDC table intact, and all of the CDC data is present.

Let’s take a look at database mirroring and show that database mirroring fully supports reading the CDC data from a snapshot. Note that a snapshot is point-in-time data and will not be updated as the CDC data changes. For this, we will restore the database again leaving it unrecovered so we can configure database mirroring. This means that we won’t be specifying the KEEP_CDC option, but it will be retained automatically.

There is a point in the middle of the code, where we pause processing to set up database mirroring.

Use master;

Restore Database CDCTest From Disk = 'c:\bak\CDCTest.bak'
    With Move 'CDCTest' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCTest.mdf',
        Move 'CDCData' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCData.ndf',
        Move 'CDCTest_log' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCTest_log.LDF',
        Replace,
        NoRecovery;
Go

Restore Log CDCTest From Disk = 'c:\bak\CDCTest.trn'
    With NoRecovery;
Go

-- Pause here to set up database mirroring

Create Database CDCTestSnap
    On (Name = CDCTest,
        Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCTestsnap.ndf'),
        (Name = CDCData,
        Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\DATA\CDCDatasnap.ndf')
    AS SNAPSHOT OF CDCTest;
Go

Use CDCTestSnap;

Select COUNT(*)
From CDCTestSnap.sys.tables
Where name = 'dbo_MasterFiles_CT'

Select COUNT(*)
from cdc.dbo_MasterFiles_CT
Go

Declare @from_lsn binary(10),
    @to_lsn binary(10)

Select @from_lsn = MIN(__$start_lsn)
From cdc.dbo_MasterFiles_CT;

Set @to_lsn = sys.fn_cdc_get_max_lsn();

Select __$start_lsn, __$seqval, __$operation, __$update_mask,
    database_id, file_id
From cdc.fn_cdc_get_all_changes_dbo_MasterFiles(
    @from_lsn,
    @to_lsn,
    N'all');
Go

Summary

To summarize everything above, yes you can preserve the CDC data when restoring a database, and it is fully compatible with database mirroring. As with everything, if you have CDC enabled databases, you should account for these in your recovery plan. Once you have recovered the database, if you didn’t use the KEEP_CDC option, there is no way to get that data back without restoring the database again. So be prepared to handle this as part of your disaster recovery plan!!