VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > sql数据库 >
  • SQL SERVER 开启CDC 实操详细

1. 环境检查

1.1 版本检查

1

SELECT @@VERSION;

Microsoft SQL Server 2016 (SP2-GDR)

1.2 检查CDC服务开启状态

1

2

select is_cdc_enabled from sys.databases where name='dbname';

--0为关闭,1为开启。数据库名为dbname

2. 开启CDC

2.1 开启SQL server agent服务

1

2

3

4

5

6

7

8

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

2.2 开启数据库级别的CDC功能

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [sa];

-- 2.2.1 变更为sa的权限,数据库名为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 开启语句

select is_cdc_enabled from sys.databases where name='dbname';

-- 2.2.3 检查是否开启成功,为1则开启

/* -- 本段注释可不看

或者

USE ERP

GO 

-- 开启:

EXEC sys.sp_cdc_enable_db 

-- 关闭:

EXEC sys.sp_cdc_disable_db

GO 

 

注释: 如果在禁用变更数据捕获时为数据库定义了很多捕获实例,则长时间运行事务可能导致 sys.sp_cdc_disable_db 的执行失败。

通过在运行 sys.sp_cdc_disable_db 之前使用 sys.sp_cdc_disable_table 禁用单个捕获实例,可以避免此问题。

 

示例:

 

USE AdventureWorks2012;

GO

EXECUTE sys.sp_cdc_disable_table

@source_schema = N'HumanResources',

@source_name = N'Employee',

@capture_instance = N'HumanResources_Employee';

*/

2.3 添加CDC专用的文件组和文件

1

2

3

4

5

6

7

8

9

10

11

12

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('dbname');

-- 2.3.1 查询dbname库的物理文件

ALTER DATABASE dbname ADD FILEGROUP CDC1;

-- 2.3.2 为该库添加名为CDC1的文件组

ALTER DATABASE dbname

ADD FILE

(

  NAME= 'dbname_CDC1',

  FILENAME = 'D:\DATA\dbname_CDC1.ndf'

)

TO FILEGROUP CDC1;

-- 2.3.3 将新增文件,并映射到文件组。重复2.3.1查询操作

2.4 开启表级别CDC

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

SELECT name,is_tracked_by_cdc FROM sys.tables WHERE  is_tracked_by_cdc = 0;

-- 2.4.1 查询未开启的表

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 为dbname.dbo.AccountBase开启表级别CDC,文件组为CDC1

DECLARE @tableName nvarchar(36)  -- 声明变量

DECLARE My_Cursor CURSOR --定义游标

    FOR (SELECT 'new_srv_workorderBase' name

union select 'tablename1'

union select 'tablename2'

union select 'tablename3'

 ) --查出需要的集合放到游标中

OPEN My_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; --关闭游标

DEALLOCATE My_Cursor; --释放游标

-- 2.4.3 游标批量开启表

SELECT name,is_tracked_by_cdc FROM sys.tables WHERE  is_tracked_by_cdc = 1 ORDER BY NAME;

-- 2.4.4 查询已开启的表

2.5 单表开启测试范例(仅供参考,可略过)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

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_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; -- 开启表级别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); -- 插入数据测试

select from dbname.dbo.test_hht; -- 数据表

SELECT * FROM [cdc].[dbo_test_hht_CT]; -- CDC日志表

2.6 开启成功说明

dbname库出现cdc模式,并有CT系列表。

1

2

3

4

5

6

7

/*

cdc.<capture_instance>_CT   可以看到,这样命名的表,是用于记录源表更改的表。

 

对于insert/delete操作,会有对应的一行记录,而对于update,会有两行记录。

对于__$operation列:1 = 删除、2= 插入、3= 更新(旧值)、4= 更新(新值)

对于__$start_lsn列:由于更改是来源与数据库的事务日志,所以这里会保存其事务日志的开始序列号(LSN)

*/

2.7 DDL操作:DDL操作需要重新收集表的信息(以测试表test_hht为例)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

alter  table test_hht add   product_count decimal(18,2);

-- 2.7.1 增加新的一列测试

insert into test_hht(id,city_name,userid,useramount,ismaster,product_count)values('2','wuhan',     20,2000.25,1,2.5);

-- 2.7.2 插入数据测试

SELECT * FROM [cdc].[dbo_test_hht_CT];

-- 2.7.3 CT表无新的一列,CDC正常捕获到之前的列变化

EXEC sys.sp_cdc_enable_table

@source_schema = 'dbo'

,@source_name = 'test_hht'

,@capture_instance ='dbo_test_hht_v2' -- 给一个新的名字

,@supports_net_changes = 1

,@role_name = NULL

,@index_name = NULL

,@captured_column_list = NULL

,@filegroup_name = 'CDC1';

-- 2.7.4 为表dbo.test_hht开启一个新的CDC捕获

insert into test_hht(id,city_name,userid,useramount,ismaster,product_count)values('2A','xiangyang',21,121000.35,0,12.5);

-- 2.7.5 插入数据测试

EXEC sys.sp_cdc_disable_table @source_schema = 'dbo',@source_name = 'test_hht', @capture_instance = 'dbo_test_hht';

-- 2.7.6 SQL SERVER最多允许两个捕获表,所以多次改变时需要先禁用之前的表

3. 关闭CDC

1

2

3

4

5

6

7

8

9

10

EXEC sys.sp_cdc_enable_table

@source_schema = 'dbo'

,@source_name = 'test_hht'

,@capture_instance ='dbo_test_hht_v2'

-- 3.1 单表禁用

USE dbname

GO

EXEC sys.sp_cdc_disable_db

GO

-- 3.2 全库禁用(禁用后cdc的模式消失)

到此这篇关于SQL SERVER CDC开启实操详细的文章就介绍到这了



原文:
https://www.jb51.net/article/225641.htm

相关教程