Before SQL Server 2008, the table data For database change monitoring, we usually use DML trigger to monitor and record the INSERT/UPDATE/DELETE data in DML operation, but it is difficult to maintain the trigger;
After the new function of SQL Server 2008: Change Data Capture (CDC) came out, I found that this is exactly what I want, because when I used DML trigger to implement it, I also recorded the UPDATE operation according to two records. The common disadvantage is that after the user modified the table structure, CDC will not automatically synchronize to the records, However, CDC also has DDL monitoring to supplement this defect; The advantage of CDC is that it uses asynchronous processes to read transaction logs to capture data changes.
3, Realization(1) Create a test database;
/******* Step1:Create sample database*******/ USE master GO IF EXISTS(SELECT name FROM sys.databases WHERE name = 'CDC_DB') DROP DATABASE CDC_DB GO CREATE DATABASE CDC_DB GO
(2) Before opening the CDC of the database, first query the status, is_ cdc_ An enabled value of 0 indicates that it is not enabled, and 1 indicates that it is enabled. When CDC is enabled for the database [CDC_DB], it is displayed in the CDC_ The 6 tables shown in Figure 2 below will appear in the DB system table;
/******* Step2:Open database CDC*******/ --Check whether the database is enabled CDC SELECT name,is_cdc_enabled FROM sys.databases WHERE name = 'CDC_DB' --Enable database CDC USE CDC_DB GO EXECUTE sys.sp_cdc_enable_db; GO --Check whether the activation is successful SELECT is_cdc_enabled,CASE WHEN is_cdc_enabled=0 THEN 'CDC Function disabled' ELSE 'CDC Function enable' END describe FROM sys.databases WHERE NAME = 'CDC_DB'
(Figure 1: database CDC status)
(Figure 2: enable system tables created by database CDC)
(Figure 3: database CDC status)
(Figure 4: adding new users and schemas)
After opening the CDC of the database, create a new user CDC and a new architecture CDC on the user and architecture respectively;
(3) Create a test table, enable capture of table row changes, and enable CDC for table [Department]. First, create [cdc].[dbo_Department_CT] in the system table, and create two jobs in the Agent, cdc.CDC_DB_capture and cdc.CDC_DB_cleanup: to enable table change capture, you need to start the SQL Server Agent service, otherwise an error will be reported. Each time capture is enabled for a table, a record table is generated to the corresponding record table.
/******* Step3:Enable change capture for tables*******/ --Create test table USE CDC_DB GO CREATE TABLE [dbo].[Department]( [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](200) NULL, [GroupName] [nvarchar](50) NOT NULL, [ModifiedDate] [datetime] NOT NULL, [AddName] [nvarchar](120) NULL, CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED ( [DepartmentID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO --Enable capture on table EXEC sys.sp_cdc_enable_table @source_schema= 'dbo', @source_name = 'Department', @role_name = N'cdc_Admin', @capture_instance = DEFAULT, @supports_net_changes = 1, @index_name = NULL, @captured_column_list = NULL, @filegroup_name = DEFAULT --Check for success SELECT name, is_tracked_by_cdc , CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC Function disabled' ELSE 'CDC Function enable' END describe FROM sys.tables WHERE OBJECT_ID= OBJECT_ID('dbo.Department') --Returns the change capture configuration information of a table EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Department'
(Figure 5: prompt information)
(Figure 6: newly added system table)
(Figure 7: generated capture and cleanup jobs)
(Figure 8: CDC status of the table)
(Figure 9: multiple database roles)
(Figure 10: sys.sp_cdc_enable_table configuration options)
The field value in the dark part of the figure above is executing sys.sp_cdc_enable_table.
(4) Test inserting data, updating data and deleting data. After executing these DML S, let's observe cdc.dbo_ Department_ What does CT record for us?
/******* Step4:Test DML change capture*******/ --Test insert data INSERT INTO dbo.Department( Name , GroupName , ModifiedDate )VALUES('Marketing','Sales and Marketing',GETDATE()) --Test update data UPDATE dbo.Department SET Name = 'Marketing Group',ModifiedDate = GETDATE() WHERE Name = 'Marketing' --Test delete data DELETE FROM dbo.Department WHERE Name='Marketing Group' --Query capture data SELECT * FROM cdc.dbo_Department_CT
(Figure 11: change record form)
For the insert/delete operation, there will be a corresponding row of records, while for the update operation, there will be two rows of records__$ Operation column: 1 = delete, 2 = insert, 3 = update (old value), 4 = update (new value);
(5) After enabling CDC, how do you get data from it? Through the data, we can recover the data;
/******* Step6:Viewing CDC records using LSN*******/ --http://msdn.microsoft.com/zh-cn/library/bb500137%28v=sql.100%29.aspx SELECT sys.fn_ cdc_ map_ time_ to_ lsn ('smallest greater than or equal', '2013-07-24 09:00:30') AS BeginLSN SELECT sys.fn_ cdc_ map_ time_ to_ LSN ('large less than or equal ',' 2013-07-24 23:59:59 ') as endlsn / ******** view all CDC records in a certain time period ******* / DECLARE @FromLSN binary(10) = sys.fn_cdc_map_time_to_lsn ('smallest greater than or equal' , '2013-06-23 09:00:30') DECLARE @ToLSN binary(10) = sys.fn_cdc_map_time_to_lsn ('largest less than or equal' , '2013-07-26 23:59:59') SELECT CASE [__$operation] WHEN 1 THEN 'DELETE' WHEN 2 THEN 'INSERT' WHEN 3 THEN 'Before UPDATE' WHEN 4 THEN 'After UPDATE' END Operation,[__$operation],[__$update_mask],DepartmentId,Name,GroupName,ModifiedDate,AddName FROM [cdc].[fn_cdc_get_all_changes_dbo_Department] (@FromLSN, @ToLSN, N'all update old') /* all of the updates contain only new values. All update old contains new and old values*/
(Figure 15: get LSN update through time)
(6) CDC maintenance
/******* Step5:Maintenance CDC*******/ --Returns change capture configuration information for all tables EXECUTE sys.sp_cdc_help_change_data_capture; --Returns the change capture configuration information of a table EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Department' --View which columns of a table have been captured and monitored,Use the returned above capture_instance Column value EXEC sys.sp_cdc_get_captured_columns @capture_instance = 'dbo_Department'
(Figure 12: field information of monitoring table)
Since the parameter of sys.sp_cdc_enable_table: @ captured_column_list = NULL, all fields of dbo.Department table are monitored. If you only care about some fields, it is strongly recommended to set this property when creating capture;
--All databases CDC Job information SELECT B.name,A.* FROM msdb.dbo.cdc_jobs AS A LEFT JOIN sys.databases AS B ON A.database_id = B.database_id --Current database CDC Job information EXEC sp_cdc_help_jobs
(Figure 13: database job information)
4, Supplementary description (Addon)There are four methods for SQL server to record data changes: trigger, Output clause, Change Data Capture (CDC) function and synchronous change tracking. The last two are new to SQL Server 2008.
The CDC function mainly captures the addition, deletion and modification of the specified table of SQL Server;
In addition to capturing data changes, CDC can also capture changes in DDL operations;
The function cannot be enabled on the system database and distribution database. And the performer needs to use sysadmin role permission;
CDC. < capture_instance > _ctcan see that the table named in this way is used to record the changes of the source table. For insert/delete operation, there will be one corresponding row of records, and for update, there will be two rows of records;
For the _ $start_lsn column: since the change is the transaction log of the source and database, the start sequence number (LSN) of the transaction log will be saved here;
For the _ $end_lsn column:
For the _ $seqval column:
For _ $operation column: 1 = delete, 2 = insert, 3 = update (old value), 4 = update (new value);
For the _ $update_mask column:
The recovery mode is the same as the simple mode, and CDC can be performed;
Although data changes can be captured, there is no way to find out who updated them?
Can this be used for rollback? Another path for backup? When the table is updated infrequently?
If some fields DDL are added or deleted, the created CDC table has not been changed. How to capture the data of the new fields? Modifying the field length and other operations will also modify the table fields corresponding to CDC;
sys.sp_ cdc_ enable_ @ role of table_ The name parameter refers to the role database role. What's the use of this parameter? What's the use of application roles?
cdc.Person_ Contact_ What does CT mean in the name CT? Capture Table？ (user. Schema table _ct)
SQL Server has started two jobs, one to capture and one to clear. Note that clearing is at 2 a.m. by default, clearing data for more than 72 hours. If CDC has been enabled in the table of the same database, the job will not be rebuilt.
all
Returns all changes within the specified LSN range. For changes caused by an update operation, this option returns only rows that contain new values after the update is applied.
all update old
Returns all changes within the specified LSN range. For changes caused by an update operation, this option returns rows that contain column values before the update and rows that contain column values after the update.
Source: http://www.cnblogs.com/gaizai/p/3479731.html
Publisher: full stack programmer, stack length, please indicate the source for Reprint: https://javaforall.cn/119724.html Original link: https://javaforall.cn