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:
- 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?
- If disaster occurs, what will happen to CDC and what we should do to ensure the transactional consistency?
- 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:
- 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.
- 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.
- 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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATACDCData.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:bakCDCTest.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:bakCDCTest.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:
__$start_lsn | __$seqval | __$operation | __$update_mask | database_id | file_id |
0x00000027000000890009 | 0x00000027000000890003 | 1 | 0x3FFFFFFF | 1 | 1 |
0x00000027000000890009 | 0x00000027000000890005 | 1 | 0x3FFFFFFF | 1 | 2 |
0x00000027000000890009 | 0x00000027000000890006 | 1 | 0x3FFFFFFF | 2 | 1 |
0x00000027000000890009 | 0x00000027000000890007 | 1 | 0x3FFFFFFF | 2 | 2 |
0x00000027000000890009 | 0x00000027000000890008 | 1 | 0x3FFFFFFF | 3 | 1 |
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:bakCDCTest.bak' With Move 'CDCTest' To 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCTest.mdf', Move 'CDCData' To 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCData.ndf', Move 'CDCTest_log' To 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCTest_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
is_cdc_enabled |
0 |
(no column name) |
0 |
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:bakCDCTest.bak' With Move 'CDCTest' To 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCTest.mdf', Move 'CDCData' To 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCData.ndf', Move 'CDCTest_log' To 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCTest_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
is_cdc_enabled |
1 |
(no column name) |
1 |
(no column name) |
0 |
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:bakCDCTest.bak' With Move 'CDCTest' To 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCTest.mdf', Move 'CDCData' To 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCData.ndf', Move 'CDCTest_log' To 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCTest_log.LDF', Replace, NoRecovery; Go Restore Log CDCTest From Disk = 'c:bakCDCTest.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
(no column name) |
1 |
(no column name) |
5 |
is_cdc_enabled |
1 |
(no column name) |
1 |
(no column name) |
5 |
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
__$start_lsn | __$seqval | __$operation | __$update_mask | database_id | file_id |
0x00000027000000890009 | 0x00000027000000890003 | 1 | 0x3FFFFFFF | 1 | 1 |
0x00000027000000890009 | 0x00000027000000890005 | 1 | 0x3FFFFFFF | 1 | 2 |
0x00000027000000890009 | 0x00000027000000890006 | 1 | 0x3FFFFFFF | 2 | 1 |
0x00000027000000890009 | 0x00000027000000890007 | 1 | 0x3FFFFFFF | 2 | 2 |
0x00000027000000890009 | 0x00000027000000890008 | 1 | 0x3FFFFFFF | 3 | 1 |
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:bakCDCTest.bak' With Move 'CDCTest' To 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCTest.mdf', Move 'CDCData' To 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCData.ndf', Move 'CDCTest_log' To 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCTest_log.LDF', Replace, NoRecovery; Go Restore Log CDCTest From Disk = 'c:bakCDCTest.trn' With NoRecovery; Go -- Pause here to set up database mirroring Create Database CDCTestSnap On (Name = CDCTest, Filename = 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCTestsnap.ndf'), (Name = CDCData, Filename = 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2MSSQLDATACDCDatasnap.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
__$start_lsn | __$seqval | __$operation | __$update_mask | database_id | file_id |
0x00000027000000890009 | 0x00000027000000890003 | 1 | 0x3FFFFFFF | 1 | 1 |
0x00000027000000890009 | 0x00000027000000890005 | 1 | 0x3FFFFFFF | 1 | 2 |
0x00000027000000890009 | 0x00000027000000890006 | 1 | 0x3FFFFFFF | 2 | 1 |
0x00000027000000890009 | 0x00000027000000890007 | 1 | 0x3FFFFFFF | 2 | 2 |
0x00000027000000890009 | 0x00000027000000890008 | 1 | 0x3FFFFFFF | 3 | 1 |
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!!
@SQLSoldier posts CDC Interoperability with Mirroring and Recovery | SQLSoldier | sqlmashup
[…] @SQLSoldier posts CDC Interoperability with Mirroring and Recovery | SQLSoldier Posted on November 29, 2010 by sqlmashup CDC Interoperability with Mirroring and Recovery […]
Henrik Nordtorp
This is a fantastic article !
Great code example. Everything just worked !!!
Nice job 🙂
SQLSoldier
Thanks Henrik!
SQL Server CDC; Lessons Learned | Russ Thomas - SQL Judo
[…] in time recovery? What about setting up a mirror? Robert Davis has expertly demonstrated that CDC and mirroring are compatible. But the issue we ran into was not setting up a mirrored server with CDC, it was moving the […]