SQL Server 2008 CDC functionality implements data change capture scripts _mssql

Cdc:change Data Capture

Copy Code code as follows:

--Step: Take Gposdb as an example in this paper

--first step, explicitly enable CDC on target library:
--use sys.sp_cdc_enable_db in the current library. Returns 0 (Success) or 1 (failed).
-Note that this feature cannot be enabled on the system database and the distribution database. And the performer needs to use sysadmin role permissions.
-the scope of the stored procedure is the entire target library. Contains metadata, DDL triggers, CDC architecture, and CDC users.
--Enable using the following code:
Use Gposdb--to enable the CDC database
Go
EXECUTE sys.sp_cdc_enable_db;
Go
--An error message appears at the beginning of a direct execution:
--Message 22830, Level 16, State 1, Process sp_cdc_enable_db_internal, line 193th
--The metadata cannot be updated to indicate that change data capture has been enabled on the database AdventureWorks.
--Failed while executing command ' setcdctracked (Value = 1) '.
--The error returned is 15517: ' cannot be executed as a database principal because the principal ' dbo '
--does not exist, cannot impersonate this type of body, or you do not have the required permissions. '。 Use this action and error to determine the cause of the failure and resubmit your request.
Here's another point of knowledge: Error number 15517.
-This error can occur in many places, such as when restoring a database.
In common: some/some stored procedures use an option with Withexecute as.
--Make it have a schema in the current library, but when executed elsewhere, because there is no such schema, so the error, resolution:
ALTER AUTHORIZATION on Database::[gposdb] to [SA]

--After checking, uspupdateemployeehireinfo This stored procedure does: with EXECUTE as CALLER
--the SA is used because the SA still exists even if the SA is disabled. So there is no error.
--Now re-executing:
Use Gposdb
Go
EXECUTE sys.sp_cdc_enable_db;
Go
-Enable success, and then check for success with the following statement:
SELECT
Is_cdc_enabled,
Case when Is_cdc_enabled=0 THEN ' CDC feature disabled ' ELSE ' CDC feature enabled ' end description
From sys.databases
WHERE NAME = ' Gposdb '

--After a successful creation, the CDC user and CDC architecture are automatically added.
--The CDC user and CDC architecture can be seen below the user and schema

--the reason for creating these two users and schemas is that the CDC requires exclusive use of both schemas, so create them separately.
-If you have a CDC user or schema created by non-CDC functionality, you need to remove the CDC-named schema before you can open it.

--second step, enable CDC on target table:
--Use a member of the db_owner role to execute sys.sp_cdc_enable_table to create a capture instance for each table that needs to be tracked.
-then determine whether the creation succeeds by sys.tables the IS_TRACKED_BY_CDC column in the catalog view.
-All columns of the table are caught by default. If you only need to catch some columns,
--You can specify these columns using the @captured_column_list parameter.
If you want to put the change table in a filegroup, it's a good idea to create a separate filegroup (at least independent of the source table).

--If you do not want to control the access role, @role_name must be explicitly set to NULL.
Sys.sp_cdc_enable_table
[@source_schema =] ' Source_schema ',
[@source_name =] ' Source_name ',
[@role_name =] ' Role_name '
[, [@capture_instance =] ' capture_instance ']
[, [@supports_net_changes =] supports_net_changes]
[, [@index_name =] ' index_name ']
[, [@captured_column_list =] ' captured_column_list ']
[, [@filegroup_name =] ' filegroup_name ']
[, [@partition_switch =] ' Partition_switch ']

--Example:
--Turn the Systempara table on the change capture.
Use Gposdb
Go
EXEC sys.sp_cdc_enable_table @source_schema = ' DBO ',
@source_name = ' Systempara ', @role_name = NULL

--and then the query is successful:
SELECT name,
IS_TRACKED_BY_CDC,
case when IS_TRACKED_BY_CDC = 0 THEN ' CDC feature disabled '
ELSE ' CDC feature enabled '
End Description
From Sys.tables
WHERE object_id = object_id (' Dbo.systempara ')
-After the table is opened, you can see many more tables in the following image that begin with the CDC schema:
--Refresh the GPOSDB database and see more of the following tables below the system table
[CDC]. [DBO_SYSTEMPARA_CT]
[CDC]. [Change_tables]
[CDC]. [Captured_columns]
[CDC]. [Ddl_history]
[CDC]. [Index_columns]
[CDC]. [Lsn_time_mapping]
[dbo]. [Systranschemas]
[dbo]. [Dtproperties]

--After startup, you can see the jobs inside the SQL Server Agent, and these two jobs appear:
[CDC. Gposdb_capture]
[CDC. Gposdb_cleanup]

--In the programmability-"function-" table-valued function, there are two more functions
[CDC]. [Fn_cdc_get_all_changes_dbo_systempara]
[CDC]. [Fn_cdc_get_net_changes_dbo_systempara]

--The related stored procedures are listed below:
--sys.sp_cdc_add_job
--Description and example
--sys.sp_cdc_generate_wrapper_function
--Description and example
- -sys.sp_cdc_change_job
--Description and example
--sys.sp_cdc_get_captured_columns
--Description and example
--sys.sp_cdc_cleanup_ Change_table
--Descriptions and examples
--sys.sp_cdc_get_ddl_history
--Descriptions and examples
--sys.sp_cdc_disable_db
--Descriptions and examples   It is recommended that you disable the table and then disable the library
--sys.sp_cdc_help_change_data_capture
--Description and example
--sys.sp_cdc_disable_table
--Description and example
-- Sys.sp_cdc_help_jobs
--Descriptions and examples
--sys.sp_cdc_drop_job
--Descriptions and examples
--sys.sp_cdc_scan
--Descriptions and examples
-- sys.sp_cdc_enable_db
--Description and example
--sys.sp_cdc_start_job
--Description and example
--sys.sp_cdc_enable_table
--Description and examples
--sys.sp_cdc_stop_job
--Description and example

--Function:
--cdc.fn_cdc_get_all_changes_<capture_instance>
--Description and examples
--sys.fn_cdc_has_column_changed
--Description and examples
--cdc.fn_cdc_get_net_changes_<capture_instance>
--Description and examples
--sys.fn_cdc_increment_lsn
--Description and examples
--sys.fn_cdc_decrement_lsn
--Description and examples
--sys.fn_cdc_is_bit_set
--Description and examples
--sys.fn_cdc_get_column_ordinal
--Description and examples
--sys.fn_cdc_map_lsn_to_time
--Description and examples
--sys.fn_cdc_get_max_lsn
--Description and examples
--sys.fn_cdc_map_time_to_lsn
--Description and examples
--sys.fn_cdc_get_min_lsn
--Description and examples

--------------------to do a real case from beginning to end-------------------------
--Here's a real case from beginning to end.

--Step One: explicitly enable the CDC on the target library
Use Gposdb--to enable the CDC database
Go
EXECUTE sys.sp_cdc_enable_db;
Go

--Some databases may have some stored procedures that contain statements such as execute AS, and an error occurs at this time:

--Text Description:
--Message 22830, Level 16, State 1, Process sp_cdc_enable_db_internal, line 186th
--The metadata cannot be updated to indicate that change data capture has been enabled on the database AdventureWorks. Failed while executing command ' setcdctracked (Value = 1) '. The error returned is 15517: ' cannot be executed as a database principal because the principal ' dbo ' does not exist, cannot impersonate this type of body, or you do not have the required permissions. '。 Use this action and error to determine the cause of the failure and resubmit your request.
--Message 266, Level 16, State 2, Process sp_cdc_enable_db_internal, line No. 0
The transaction count after--execute indicates that the number of begin and commit statements does not match. Last count = 0, current count = 1.
--Message 266, Level 16, State 2, Process sp_cdc_enable_db, line No. 0
The transaction count after--execute indicates that the number of begin and commit statements does not match. Last count = 0, current count = 1.
--Message 3998, Level 16, State 1, line 1th
-An uncommitted transaction was detected at the end of the batch process. The transaction is rolled back.
If this error occurs, the current workaround is to execute the following statement, which is explained at the beginning, and generally does not have a problem with libraries that do not use EXECUTE AS:

ALTER AUTHORIZATION on Database::[gposdb] to [SA]

--Now re-executing:
Use Gposdb
Go
EXECUTE sys.sp_cdc_enable_db;
Go
--Check for success with the following statement:
SELECT
Is_cdc_enabled,
Case when Is_cdc_enabled=0 THEN ' CDC feature disabled ' ELSE ' CDC feature enabled ' end description
From sys.databases
WHERE NAME = ' Gposdb '

--Step Two: enable CDC for tables
Use Gposdb
Go
EXEC sys.sp_cdc_enable_table
@source_schema = ' DBO ',
@source_name = ' Systempara ',
@role_name = NULL,
@capture_instance =default
Go

--and then the query is successful:
SELECT name,
IS_TRACKED_BY_CDC,
case when IS_TRACKED_BY_CDC = 0 THEN ' CDC feature disabled '
ELSE ' CDC feature enabled '
End Description
From Sys.tables
WHERE object_id = object_id (' Dbo.systempara ')

-You can see the [CDC] added to the system table in the GPOSDB database. [DBO_SYSTEMPARA_CT] Table

--Step three: test, the following to change the data
--Check the DBO_SYSTEMPARA_CT table first
SELECT * from CDC. [DBO_SYSTEMPARA_CT]

--you can see a record is not, because just created, and did not do any additions and deletions to the original table Systempara

--Inserts a record into the [Systempara] table
INSERT into [dbo]. [Systempara]
([Paravalue],
[Name],
[Description]
)
VALUES (' China ',--Paravalue-varchar (50)
' China ',--Name-varchar (50)
' China '--Description-varchar (50)
)

--Check the DBO_SYSTEMPARA_CT table, you can see one more record
SELECT * from CDC. [DBO_SYSTEMPARA_CT]

--Update a record in the [Systempara] table
UPDATE [dbo]. [Systempara] SET [paravalue]= ' Germany ' WHERE [description]= ' China '

--Check the DBO_SYSTEMPARA_CT table, you can see more than two records
SELECT * from CDC. [DBO_SYSTEMPARA_CT]

--Delete a record from the [Systempara] table
DELETE from [dbo].  [Systempara] WHERE [description]= ' China '

--Check the DBO_SYSTEMPARA_CT table, you can see one more record
SELECT * from CDC. [DBO_SYSTEMPARA_CT]

--now to analyze the DBO_SYSTEMPARA_CT table
--can be viewed in Books Online:
--cdc.<capture_instance>_ct
--you can see that this named table is the table used to record the changes made to the source table.
-For the insert/delete operation, there will be a corresponding row of records, and for update, there will be two rows.
--For __$operation column: 1 = delete, 2 = INSERT, 3 = Update (old value), 4 = update (new value)
The value of the __$operation column of the--update statement is 3 and 4, so an UPDATE statement corresponds to two records

--For __$START_LSN columns: Since the change is the transaction log of the source and the database, the start sequence number (LSN) of its transaction log is saved here
--but Microsoft does not check this kind of table directly, recommends using
--cdc.fn_cdc_get_all_changes_< Capture Instance >
--cdc.fn_cdc_get_net_changes_<capture_instance>
--To inquire

----------------------------------------------------------

-Start below to familiarize yourself with the use of functions, stored procedures, and try out some unusual operations.

--Daily use scenario:
--1, query already open capture instance:
--Returns change capture configuration information for all tables
Use [Gposdb]
Go
EXECUTE sys.sp_cdc_help_change_data_capture;
Go

--View which columns of an instance (that is, a table) are captured and monitored
Use [Gposdb]
Go
EXEC Sys.sp_cdc_get_captured_columns
@capture_instance = ' Systempara '--sysname

--You can also find configuration information from the following
SELECT * from Msdb.dbo.cdc_jobs

--2, view current configuration using Sp_cdc_help_jobs:
--as you can see from the above, two jobs are created automatically after the CDC is enabled, and you can use the following statements to view:
Sp_cdc_help_jobs

-For a large OLTP system, there will be a lot more data in the change table because of the very frequent changes in the data.
If it's been stored for a long time (up to 100 years), that's a big challenge for database space.
--You can adjust the CDC in the above diagram at this time. Adventureworks_cleanup in retention (in minutes).

--3, modify configuration: Sp_cdc_change_job
--Displays the original configuration
EXEC Sp_cdc_help_jobs
Go
--change data retention time to 100 minutes
EXECUTE Sys.sp_cdc_change_job
@job_type = N ' cleanup ',
@retention =100
Go

--Restart the job for the settings to take effect
--Deactivate the job
EXEC sys.sp_cdc_stop_job N ' cleanup '
Go
--Enable Job
EXEC sys.sp_cdc_start_job N ' cleanup '
Go
--See again
EXEC Sp_cdc_help_jobs
Go
--you can see that the value of retention (in minutes) becomes 100.

--4, stop/enable, delete/create job

--Deactivate the job
EXEC sys.sp_cdc_stop_job N ' cleanup '
Go
--Enable Job
EXEC sys.sp_cdc_start_job N ' cleanup '
Go

--Delete Job
EXEC sys.sp_cdc_drop_job @job_type = N ' cleanup '--nvarchar (20)
Go
--View Jobs
EXEC Sys.sp_cdc_help_jobs
Go
--you can see that there's only one job left: the CDC. Gposdb_capture

--Create Job
EXEC Sys.sp_cdc_add_job
@job_type = N ' cleanup ',
@start_job = 0,
@retention = 5760

--View Jobs
EXEC Sys.sp_cdc_help_jobs
Go

--5, DDL change capture:
In addition to capturing data changes,--CDC can capture changes in DDL operations.
--If you want to make sure that SQL Server Agent is enabled, the CDC feature needs to be sure that it works
--because all operations are implemented through two jobs in the broker.
--Now to modify the Systempara table, the length of the Paravalue lengthened
Use [Gposdb]
Go
ALTER TABLE [dbo]. [Systempara] ALTER COLUMN paravalue VARCHAR (120);
Go

--then query the DDL record table
SELECT * from Cdc.ddl_history

--6, using the CDC function to get changes
--A, use [CDC]. [Fn_cdc_get_all_changes_dbo_systempara]
--function reports all currently available changes to capture an instance
DECLARE @from_lsn BINARY (10),
@to_lsn BINARY (10)
SET @from_lsn = sys.fn_cdc_get_min_lsn (' Systempara ')
SET @to_lsn = SYS.FN_CDC_GET_MAX_LSN ()
SELECT *
From Cdc.fn_cdc_get_all_changes_dbo_systempara (@from_lsn, @to_lsn, N ' all update old ');
Go

--b, get change information for a time period:
--Get trace change data based on log sequence number (logsequence numbers, LSN) first
--sys.fn_cdc_map_time_to_lsn gets the maximum, minimum LSN value within the range of the change. can use
smallest greater than;
smallest greater than orequal;
Largest less than;
Largest less than or equal;

--such as querying for data inserted in a time period
INSERT into [dbo]. [Systempara]
([Paravalue],
[Name],
[Description]
)
VALUES (' China ',--Paravalue-varchar (50)
' China ',--Name-varchar (50)
' China '--Description-varchar (50)
)

Go

--Check data
--1 Delete
--2 Insert
--3, 4 change
--the records that were inserted can be queried if they are delete
DECLARE @bglsn VARBINARY = sys.fn_cdc_map_time_to_lsn (' smallest greater than or equal '),
' 2013-10-21 12:00:00.997 ')
DECLARE @edlsn VARBINARY = SYS.FN_CDC_MAP_TIME_TO_LSN (' largest less than or equal '),
GETDATE ())
SELECT *
from [CDC]. [DBO_SYSTEMPARA_CT]
WHERE [__$operation] = 2
and [__$START_LSN] BETWEEN @bglsn and @edlsn

--C, sys.fn_cdc_map_lsn_to_time query change time:
SELECT [__$operation],
case [__$operation]
When 1 THEN ' delete '
When 2 THEN ' Insert '
When 3 THEN ' update (the captured column value is the value before the update operation) '
When 4 THEN ' update (the captured column value is the value after the update operation) '
End [Type],
Sys.fn_cdc_map_lsn_to_time ([__$START_LSN]) [Change time],
*
from [CDC]. [DBO_SYSTEMPARA_CT]

--d, obtaining LSN boundaries
SELECT sys.fn_cdc_get_max_lsn () [Maximum LSN at database level],
SYS.FN_CDC_GET_MIN_LSN (' CDC. Dbo_systempara_ct ') [LSN of Capture instance]

These two values can be used to filter the data in the function mentioned above.

----------------------------------------------------------
--1. What is the purpose of the CDC?

--CDC is a mechanism that greatly facilitates our access to a table's data update. It through an independent process,
--Read the log file asynchronously, rather than the way the trigger works. And its data is persisted to a system table.
--2. is the CDC specific to SQL Server 2008 and does it work for other databases or earlier versions?

--CDC is a feature that is unique to SQL Server 2008 and is unique to the Enterprise Edition. The development version also has this feature, but only for testing scenarios.

--3. The CDC reads the log, so what happens if the log is truncated?

If a part of the log, the CDC process has not been read, then the truncation of the log will be ignored this section, can not be truncated!!

--The capture process is a standalone, and it starts as the agent service starts. Two scans are spaced between 5 minutes.

--4. Does the data in the system table exist permanently? --No, it will be kept for 3 days. There will be a cleaning operation to be scanned every night 2 o'clock.
-Finally, it adds that the CDC function relies on the agent service because it has two operations that are started by the job.