Shulou(Shulou.com)06/03 Report--

This article introduces the knowledge of "how SQL SERVER opens CDC". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Catalogue

1. Environmental inspection

1.1 version check

1.2 check the CDC service open status

two。 Turn on CDC

2.1 enable SQL server agent service

2.2 enable CDC at the database level

2.3 add filegroups and files specific to CDC

2.4 enable table-level CDC

2.5 single table open test example (for reference only, can be skipped)

2.6 description of successful opening

2.7 DDL operation: the DDL operation needs to re-collect the information of the table (take the test table test_hht as an example)

3. Close CDC

1. Environment check version 1.1 check SELECT @ @ VERSION

Microsoft SQL Server 2016 (SP2-GDR)

1.2 check that the CDC service is enabled. Select is_cdc_enabled from sys.databases where name='dbname';--0 is disabled, and 1 is enabled. The database is named dbname2. Enable CDC2.1 to enable SQL server agent service sp_configure 'show advanced options', 1 / go-- 2.1.1 2.1.2sp_configure' Agent XPs', 1 / go-- 2.1.3RECONFIGUREGO-2.1.42.2 enable database-level CDC function ALTER AUTHORIZATION ON DATABASE:: [dbname] TO [sa] -- 2.2.1 change to the permission of sa, and the database name is dbnameif exists (select 1 from sys.databases where name='dbname' and is_cdc_enabled=0) begin exec sys.sp_cdc_enable_dbend;-- 2.2.2 Open statement select is_cdc_enabled from sys.databases where name='dbname' -- 2.2.3 check whether it is enabled successfully. If 1, enable / *-- do not look at the comments in this paragraph or USE ERPGO-- enable: EXEC sys.sp_cdc_enable_db-- close: EXEC sys.sp_cdc_disable_dbGO Note: if many capture instances are defined for the database when you disable change data capture, running transactions for a long time may cause sys.sp_cdc_disable_db execution to fail. This problem can be avoided by using sys.sp_cdc_disable_table to disable a single capture instance before running sys.sp_cdc_disable_db. Example: USE AdventureWorks2012; GO EXECUTE sys.sp_cdc_disable_table @ source_schema = nonexistent HumanResources databases, @ source_name = denominated employees employees, @ capture_instance = CDC dedicated filegroups and files SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID ('dbname');-- 2.3.1 query the physical file ALTER DATABASE dbname ADD FILEGROUP CDC1 of the dbname library -- 2.3.2 add a filegroup named CDC1 to the library ALTER DATABASE dbnameADD FILE (NAME= 'dbname_CDC1', FILENAME =' D:\ DATA\ dbname_CDC1.ndf') TO FILEGROUP CDC1;-- 2.3.3 will add files and map to filegroups. Repeat 2.3.1 query operation 2.4 enable table-level CDC

SELECT name,is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 0 2.4.1 query the unopened table IF EXISTS (SELECT 1 FROM sys.tables WHERE name='AccountBase' AND is_tracked_by_cdc = 0) BEGIN EXEC sys.sp_cdc_enable_table @ source_schema = 'dbo',-- source_schema @ source_name =' AccountBase',-- table_name @ capture_instance = NULL,-- capture_instance @ supports_net_changes = 1 -- supports_net_changes @ role_name = NULL,-- role_name @ index_name = NULL,-- index_name @ captured_column_list = NULL,-- captured_column_list @ filegroup_name = 'CDC1'-- filegroup_nameEND -- 2.4.2 enable table-level CDC for dbname.dbo.AccountBase, filegroup is CDC1DECLARE @ tableName nvarchar (36)-- declare variable DECLARE My_Cursor CURSOR-- define cursor FOR (SELECT 'new_srv_workorderBase' nameunion select' tablename1'union select 'tablename2'union select' tablename3')-- find needed collections and put OPEN My_Cursor; in cursors-- Open cursor FETCH NEXT FROM My_Cursor INTO @ tableName WHILE @ @ FETCH_STATUS = 0BEGIN EXEC sys.sp_cdc_enable_table @ source_schema = 'dbo',-- source_schema @ source_name = @ tableName,-- table_name @ capture_instance = NULL,-- capture_instance @ supports_net_changes = 1,-- supports_net_changes @ role_name = NULL,-- role_name @ index_name = NULL -- index_name @ captured_column_list = NULL,-- captured_column_list @ filegroup_name = 'CDC1'-- filegroup_name FETCH NEXT FROM My_Cursor INTO @ tableName;ENDCLOSE My_Cursor;-- close cursor DEALLOCATE My_Cursor;-- release cursor-- 2.4.3 cursor batch open table SELECT name,is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 1 ORDER BY NAME;-- 2.4.4 query open table 2.5 single table open test example (for reference only, can be skipped)

Create table test_hht (id varchar (36) not null primary key,city_name varchar (20), userid bigint,useramount decimal (18je 6), ismaster bit,createtime datetime default getdate ()) -- Test table test_hhtIF EXISTS (SELECT 1 FROM sys.tables WHERE name='test_hht' AND is_tracked_by_cdc = 0) BEGIN EXEC sys.sp_cdc_enable_table @ source_schema = 'dbo',-- source_schema @ source_name =' test_hht',-- table_name @ capture_instance = NULL,-- capture_instance @ supports_net_changes = 1 -- supports_net_changes @ role_name = NULL,-- role_name @ index_name = NULL,-- index_name @ captured_column_list = NULL,-- captured_column_list @ filegroup_name = 'CDC1'-- filegroup_nameEND -- turn on table-level CDCinsert into test_hht (id,city_name,userid,useramount,ismaster) values ('1BLY, 10pr 1000.25pr. 1); insert into test_hht (id,city_name,userid,useramount,ismaster) values (' 1A, pr. 11000.35pr. 0); insert into test_hht (id,city_name,userid,useramount,ismaster) values ('1BLY, 12pr 12000.45p0). -- insert data test select * from dbname.dbo.test_hht;-- data table SELECT * FROM [cdc]. [dbo_test_hht_CT];-- description of successful opening of CDC log table 2.6

The dbname library appears in cdc mode and has a list of CT departments.

/ * cdc._CT can see that the table so named is the table used to record changes to the source table. For insert/delete operations, there will be a corresponding row of records, while for update, there will be two rows of records. For _ _ $operation column: 1 = delete, 2 = insert, 3 = update (old value), 4 = update (new value) for _ $start_lsn column: since the change is the transaction log of the source and database, the start sequence number (LSN) of its transaction log is saved here * / 2.7Operation: the DDL operation needs to re-collect the information of the table (take the test table test_hht as an example) alter table test_hht add product_count decimal (18pc2) -- 2.7.1 add a new list of tests insert into test_hht (id,city_name,userid,useramount,ismaster,product_count) values. [dbo_test_hht_CT] -- 2.7.3 there is no new column in the CT table, and CDC normally captures the previous column changes EXEC sys.sp_cdc_enable_table@source_schema = 'dbo',@source_name =' test_hht',@capture_instance = 'dbo_test_hht_v2'-- give a new name, @ supports_net_changes = 1 NULL,@filegroup_name = NULL,@captured_column_list = NULL,@filegroup_name =' CDC1' -- 2.7.4 start a new CDC capture insert into test_hht (id,city_name,userid,useramount,ismaster,product_count) values for table dbo.test_hht. (21 121000.35); 2.7.5 insert data test EXEC sys.sp_cdc_disable_table @ source_schema = 'dbo',@source_name =' test_hht', @ capture_instance = 'dbo_test_hht' 2.7.6 SQL SERVER allows a maximum of two capture tables, so if you change multiple times, you need to disable the previous table 3. Close CDCEXEC sys.sp_cdc_enable_table@source_schema = 'dbo',@source_name =' test_hht',@capture_instance = 'dbo_test_hht_v2'-- 3.1 single table disable USE dbnameGOEXEC sys.sp_cdc_disable_dbGO-- 3.2 whole library disable (after disabling the mode of cdc disappears) "SQL SERVER how to open CDC" content is introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.