【SQL Server】数据库归档日志开放

操作条件：

1. 需要DBA权限；

2. 需要重启数据库；

3. 确保所需集成的表具有主键（物理或逻辑）；

4. 操作⼈必须熟悉当前数据库的⽇常管理操作；

5. 操作之前请做好备份⼯作。

注意事项：

1. 下列脚本中若遇 <...>内容，需要替换所需内容；

2. 所创建对象名称请按学校所需规范进⾏命名；

3. 由于环境和版本差异，若不能正常运⾏时，会涉及进⼀步的微调设置；

4. 此⽂档仅是要求和指引，未涉及详解数据库的各项指令操作，具体可查阅相关数据库的管理操作 。

注：SQL Server表结构发⽣变化后，需要重建表级CDC后重新⽣效。

一. 设置 
1. 开启数据库级CDC特性 
USE <库名>
GO 
EXEC sys.sp_cdc_enable_db 
GO 

select is_cdc_enabled from sys.databases where name='库名'

----授权日志表 sa 用户下面操作

ALTER AUTHORIZATION ON DATABASE::[库名] TO [sa] 


2. 创建访问⽤户并授权 
USE <库名>
GO 
CREATE LOGIN usr_cdc WITH PASSWORD = '<密码>' 
GO 
CREATE USER usr_cdc FOR LOGIN usr_cdc WITH DEFAULT_SCHEMA = <schema>
GO 
EXEC sp_addrolemember N'db_datareader', N'usr_cdc' 
GO 
3. 开启表级CDC特性 
USE <库名>
GO 
-- 多个表请分别执⾏ 
EXEC sys.sp_cdc_enable_table 
@source_schema = N'<schema>', 
@source_name = N'<table>', 
@role_name = N'usr_cdc',
@supports_net_changes = 0 
GO 
二. 验证 
⽤上述usr_cdc账号登录，执⾏以下SQL：select count(*) from <schema>.<table1>;-- 检查是否成功执⾏，其中 
，<schema>.<table1>是所需同步的表 
EXEC sys.sp_cdc_help_change_data_capture;-- 检查返回结果是否包含<s 
chema>.<table1>
SELECT sys.fn_cdc_get_max_lsn();-- 检查是否成功执⾏ 
SELECT count(*) FROM cdc.lsn_time_mapping;-- 检查是否成功执⾏ 
SELECT sys.fn_cdc_get_min_lsn('#');-- 检查是否成功执⾏ 
SELECT count(*) FROM cdc.change_tables;-- 检查是否成功执⾏

开启表级别配置。 如果不设置用户可以role_name = NULL

IF EXISTS(SELECT 1 FROM sys.tables WHERE name='ba' AND is_tracked_by_cdc = 0)
BEGIN
    EXEC sys.sp_cdc_enable_table
        @source_schema = 'dbo', -- source_schema
        @source_name = 'ba', -- table_name
        @capture_instance = NULL, -- capture_instance
        @supports_net_changes = 1, -- supports_net_changes
        @role_name = NULL -- role_name
END