Restoring Database with CDC Enabled

存储架构 2018-02-14

In this post, I am going to talk about an issue I found while restoring a backup of CDC enabled database to a different SQL instance. You may receive a warning message that includes some information about the cause of the warning, which can help you to resolve the issue.

The warning message is as follows:

Could not update the metadata that indicates database is enabled for Change Data Capture. The failure occurred when executing the command “.sys.sp_replhelp N’DisablePerDbHistoryCache”. The error returned was 3930

On the screenshot below, the error says that CDTEST database restore failed and it is not enabled for CDC.

Resolution

CDC is a feature enterprise edition available in 2008 or higher versions. It helps to audit/change tracking database changes like insert, update, and delete operations in a table. Changes are available in the relational format. It captures the changes in tables, mirrors the column structure in source tables, and applies those changes to target tables. Table-valued functions are provided to allow a systematic access to the data modified by consumers. Captured changes from source tables including insert, update and delete operations are applied to tracked source tables, while the entries describing those changes are added to the SQL Server transaction log. The log serves as input to the change data capture process. Functions are provided to enumerate the changes returning the information in the form of a filtered result set. The filtered result set is typically used by an application process to update a representation of the source in some external environment.

CDC configuration:

  • Enables CDC at the database level using the sp_cdc_enable_db system procedure
  • Creates a CDC schema and CDC database user.
  • Verifies if the database is enabled for CDC by looking at the is_cdc_enabled column for the database entry in the sys.databases catalog view
  • You must be granted with a sysadmin fixed server role in order to verify the status of CDC

You should have a valid object name that must be unique across instances. Changes are captured in a capture schema of the enabled database.

The change data capture table contains the first five columns as metadata columns. It captures the information about recorded changes. The rest of the columns hold the captured column data that is generated from the source table. Any DML operation appears as a row in a change data table. For example, data columns of the row that result from an insert operation contain the column values after the insert operation is completed. A delete operation contains the column values before the delete operation is completed. An update operation contains column values before the update operation is performed in the first-row entry and similarly second-row entry to identify the column values after the update.

The changes made to a capture instance for a source database depend on a capture interval. The change data available for a capture instance is called Capture validity interval for a database. It is the interval between the first capture instance and the present time. The change data capture cleanup process is responsible for the retention-based cleanup policy and it removes the changes in the table after the retention expiry date.

The cdc.lsn_time_mapping table is responsible for keeping committed changes and new entries during the capture process. This table contains the Log Sequence Number (LSN) commit and a transaction commit time. The maximum LSN value that is found in the mapping table represents a high water mark while the commit time represents the base from which a retention-based cleanup computes a new low water mark. All the change data is retrieved from the transaction log, thus, there is some latency between the change committed to a source table and the change within its associated change table.

Below, you can find a script to create the CDC enabled in a database.

CREATE DATABASE [CDCTEST]
 
 ON PRIMARY
 
(NAME = N'CDCTEST', FILENAME = N'D:DatabasesCDCTEST.mdf')
 
 LOG ON
 
(NAME = N'CDCTEST_log', FILENAME = N'E:LogfilesCDCTEST_log.ldf')
 
GO
 
 
USE CDCTEST
 
GO
 
CREATE TABLE dbo.Employee
 
  (EmployeeID INT CONSTRAINT PK_Employee PRIMARY KEY IDENTITY(1,1),
 
   FirstName VARCHAR(50),
 
   LastName VARCHAR(50))
 
GO
 
-- Enable Database for CDC template
 
EXEC sys.sp_cdc_enable_db
 
GO
 
-- Enable CDC for specified table “Employee” in current database
 
 
EXEC sys.sp_cdc_enable_table
 
  @source_schema = 'dbo',
 
  @source_name = 'Employee',
 
  @supports_net_changes = 1,
 
  @role_name = NULL,
 
  @index_name = 'PK_Employee'
 
GO                              
 
INSERT INTO dbo.Employee
 
  (FirstName, LastName)
 
VALUES
 
  ('Ganapathi', 'varma')
 
GO

Backup of the database with the enabled CDC in an existing instance and restore of the database to a different instance.

For backup and restore a database, I am using a great IDE, dbForge Studio for SQL Server, a tool for SQL Server management, administration, and development. The Backup Wizard feature helps you to automate the backup process and simplifies the backup procedure.

Let us connect to a source SQL Server.

To start an SQL Server backup, right-click a required database in Database Explorer, point to Tasks and select Back Up… on the shortcut menu. The Backup window opens.

On the screenshot below, in the General Tab, you can view or modify settings for a database backup operation. Specify a path to back up the database to a disk.

Then, select the Overwrite all existing back up set and Verify backup when finished options. Click Next.

On the screenshot below, select SQL Server backup options and click Back Up. You can compress the backup option.

As you can see, the backup is successful.

You can also use a T-SQL script below to back up a database.

Backup database CDCTEST to disk=’C:CDCTEST.bak’
 
 
Restore database CDCTEST from disk=’C:CDCTEST.bak’ with keep_cdc

The screenshot shows that the “Keep_CDC” option with the database restore statement must be used to restore the CDC enabled database backup successfully.

In addition, you need to add the Capture and Cleanup jobs using the following commands in the appropriate database.

exec sys.sp_cdc_add_job 'capture'
 GO
 exec sys.sp_cdc_add_job 'cleanup'
 GO

We have two SQL Server Agent jobs associated with a change data capture enabled database. A capture job is used to populate the database change table and a cleanup job is responsible for a change table cleanup. The capture and cleanup jobs are created when a replication is enabled for the first time and initiated change data capture for a table.

The sp_MScdc_capture_job stored procedure runs to initiate the capture job. This stored procedure starts by extracting the configured values for maxtrans, maxscans, continuous , and pollinginterval for the capture job from msdb.dbo.cdc_jobs. These configured values are then passed as parameters to the sp_cdc_scan stored procedure. This is used to invoke sp_replcmds to perform the log scan.

The retention policy is used to maintain cleanup and change table size. The cleanup job is responsible for handling and maintaining a strategy to clean up for all database change tables.

The sp_MScdc_cleanup_job stored procedure runs to initiate the cleanup job. This stored procedure starts by extracting the configuration values like threshold values and retention from msdb.dbo.cdc_jobs. A low watermark for all capture instances is initially updated in a single transaction when cleanup is performed. It then tries to remove entries from the change tables and the cdc.lsn_time_mapping table.

Ganapathi Chekuri

Ganapathi Varma is a Lead Database Administrator and Blogger. Working as Lead SQL Server DBA, he is responsible for managing 1000's of SQL instances providing database managed services, High Availability and Disaster Recovery. Other than SQL Server, he is also expertised in different datacenter technologies. He is the owner of MSSQLLover blog and fully dedicated to the SQL Server community.

Latest posts by Ganapathi Chekuri ( see all )

codingsight

责编内容by:codingsight (源链)。感谢您的支持!

您可能感兴趣的

在 DevOps 中以 API 看待共享数据库 作者 Manuel Pais ,译者 盖磊 在 WinOps 2017 大会上,Sabin.io 首席顾问 Simon Sabin 做了一个演讲,介绍 如何将数据库更改加入到持续部署模型中 。从数据库所有者的角度看,要实现在多个服务或应用间共享数据库,关键之一就是将这些...
传统病理学发展太慢,HistoWiz 想在云上搭建全球最大的癌症数据库... 原标题:传统病理学发展太慢,HistoWiz 想在云上搭建全球最大的癌症数据库 题图来源:视觉中国 2018年,钛媒体将陆续推出对于前沿云端科技创业公司的全景调查。本项目主要由阿里云“诸神之战”全球创客大赛推荐,钛媒体筛选,独家采访与追踪。记录最值得记录的中国前沿科技创新史,...
Citrix Fixes – Workspace Environment Management A list containing the majority of Citrix Workspace Environment Management support articles collated to make this page a one stop place for you to sear...
Laravel 的十八个最佳实践 本文第一次出现在 Laravel China 社区上,知乎不支持 Markdown 表格,更好的排版请见 —— Laravel 的十八个最佳实践 。作者 JokerLinly , 翻译改编自 Laravel 的十八个最佳实践 。 这篇文章并不是什么由 Lar...
Handling Index Creation with MongoEngine in Python MongoEngine is an Object Document Mapper (ODM) for working with MongoDB from Python. The ODM layer maps an object model to a document database i...