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 )

您可能感兴趣的

Wso2 carbon ESB连接H2数据库,H2客户端显示表结构和数据... H2 是一个短小精干的嵌入式数据库引擎,主要的特性包括:免费、开源、快速;嵌入式的数据库服务器,支持集群;提供 JDBC、ODBC 访问接口,提供基于浏览器的控制台管理程序;Java编写,可使用GCJ和IKVM.NET编译;短小精干的软件,目前最新版大概在8M左右。 常用的开源数据库有 H...
Better programming methods for chatbots In this story we will be talking about methods of developing chatbots and deploying them. Bear in mind that most of this article will directly concern...
我明明 immediate 关库的,怎么就打不开了?!... 五一放假期间,某客户的数据库出现故障,据说对方找了一些工程师折腾了一天,都无法将数据库open,其中参考了网络上的很多文章,也使用了一系列隐含参数,均无法将数据库打开。这里我简单的与大家分享一下这个case。 首先我介绍一下整个case的背景,客户在4月30号凌晨 通过shutdown ...
ModelForm 这是一个神奇的组件,通过名字我们可以看出来,这个组件的功能就是把model和form组合起来,对,你没猜错,相信自己的英语水平。 先来一个简单的例子来看一下这个东西怎么用: 比如我们的数据库中有这样一张学生表,字段有姓名,年龄,爱好,邮箱,电话,住址,注册时间等等一大堆信息,现在让你写一个创...
Announcing Entity Framework Core 2.1 Preview 2 Today we’re releasing the second preview of EF Core 2.1, alongside .NET Core 2.1 Preview 2 and ASP.NET Core 2.1 Preview 2 . Thank you so mu...
0
codingsight

责编内容来自:codingsight (本文源链)
阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。