SQL SERVERオープンCDC実践講座詳細

-bash-4.1$ jps
Java HotSpot(TM) 64-Bit Server VM warning: Insufficient space for shared memory file:
   /tmp/hsperfdata_kf01/4830
Try using the -Djava.io.tmpdir= option to select an alternate temp location.
4830 Jps

select is_cdc_enabled from sys.databases where name='dbname';
--0 is off, 1 is on. The database name is dbname

sp_configure 'show advanced options', 1;
GO -- 2.1.1
RECONFIGURE;
GO -- 2.1.2
sp_configure 'Agent XPs', 1;
GO -- 2.1.3
RECONFIGURE
GO -- 2.1.4

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [sa];
-- 2.2.1 Change to sa permissions, database name dbname
if exists(select 1 from sys.databases where name='dbname' and is_cdc_enabled=0)
begin
    exec sys.sp_cdc_enable_db
end
;
-- 2.2.2 Open statements
select is_cdc_enabled from sys.databases where name='dbname';
-- 2.2.3 Check if enabled, if 1 then enabled
/* -- This comment may not be read
or
USE ERP
GO  
-- Enable.
EXEC sys.sp_cdc_enable_db  
-- Disable.
EXEC sys.sp_cdc_disable_db
GO  
 
Note: If you define many capture instances for the database when you disable change data capture, running the transaction for a long time may cause the execution of sys.sp_cdc_disable_db to fail.
This problem can be avoided by disabling a single capture instance with sys.sp_cdc_disable_table before running sys.sp_cdc_disable_db.
 
Example.
 
USE AdventureWorks2012; 
GO 
EXECUTE sys.sp_cdc_disable_table 
@source_schema = N'HumanResources', 
@source_name = N'Employee', 
@capture_instance = N'HumanResources_Employee';
*/

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('dbname');
-- 2.3.1 Query the physical files of the dbname library
ALTER DATABASE dbname ADD FILEGROUP CDC1;
-- 2.3.2 Add a file group named CDC1 to the library
ALTER DATABASE dbname
ADD FILE
(
  NAME= 'dbname_CDC1',
  FILENAME = 'D:\DATA\dbname_CDC1.ndf'
)
TO FILEGROUP CDC1;
-- 2.3.3 Add the new file and map it to a file group. Repeat the 2.3.1 query operation

SELECT name,is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 0;
-- 2.4.1 Querying unopened tables
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_name
END;
-- 2.4.2 Enable table level CDC for dbname.dbo.AccountBase with file group CDC1
DECLARE @tableName nvarchar(36) -- declare variable
DECLARE My_Cursor CURSOR -- Define cursor
    FOR (SELECT 'new_srv_workorderBase' name
union select 'tablename1'
union select 'tablename2'
union select 'tablename3'
 ) ---Find the required collection and put it in the cursor
OPEN My_Cursor; --open the cursor
FETCH NEXT FROM My_Cursor INTO @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    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;
END
CLOSE My_Cursor; -- close the cursor
DEALLOCATE My_Cursor; -- free the 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 Querying Opened Tables

create table test_hht
(id varchar(36) not null primary key,
city_name varchar(20),
userid bigint,
useramount decimal(18,6),
ismaster bit,
createtime datetime default getdate()); -- test table test_hht
IF 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_name
END; -- open table level CDC
insert into test_hht(id,city_name,userid,useramount,ismaster)values('1','wuhan', 10,1000.25,1);
insert into test_hht(id,city_name,userid,useramount,ismaster)values('1A','xiangyang', 11,11000.35,0);
insert into test_hht(id,city_name,userid,useramount,ismaster)values('1B','yichang', 12,12000.45,0); -- insert data test
select * from dbname.dbo.test_hht; -- data table
SELECT * FROM [cdc]. [dbo_test_hht_CT]; -- CDC log table

/*
cdc.<capture_instance>_CT As you can see, the table named this way is the table used to record changes to the source table.

For insert/delete operations, there will be a corresponding row of records, and for update, there will be two rows of records.
For the __$operation column: 1 = delete, 2 = insert, 3 = update (old value), 4 = update (new value)
For the __$start_lsn column: Since the changes are sourced from the transaction log with the database, the start sequence number (LSN) of its transaction log will be stored here
*/

alter table test_hht add product_count decimal(18,2);
-- 2.7.1 Add a new column to test
insert into test_hht(id,city_name,userid,useramount,ismaster,product_count)values('2','wuhan', 20,2000.25,1,2.5);
-- 2.7.2 Insert data test
SELECT * FROM [cdc]. [dbo_test_hht_CT];
-- 2.7.3 No new column in CT table, CDC captures previous column change normally
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
,@role_name = NULL
,@index_name = NULL
,@captured_column_list = NULL
,@filegroup_name = 'CDC1';
-- 2.7.4 Open a new CDC capture for table dbo.test_hht
insert into test_hht(id,city_name,userid,useramount,ismaster,product_count)values('2A','xiangyang',21,121000.35,0,12.5);
-- 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 up to two capture tables, so you need to disable the previous table first when changing multiple times

EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo'
,@source_name = 'test_hht'
,@capture_instance = 'dbo_test_hht_v2'
-- 3.1 Single Table Disable
USE dbname
GO
EXEC sys.sp_cdc_disable_db
GO
-- 3.2 Full library disable (cdc's schema disappears after disabling)