数据库版本要求2005以上并且sp2，企业版，支持cdc功能
查看数据库版本：
SELECT @@VERSION

1 源库配置

1.1 修改配置文件

1.1.1 配置数据库恢复模式

检查恢复模式，需要开启数据库恢复模式为完整模式，简单模式会频繁自动清理日志导致数据丢失，修改完整模式后做一次全量备份

查看恢复模式
SELECT name, recovery_model_desc FROM sys.databases WHERE name = '库名' ;
GO
修改数据恢复模式为"完整"
alter database dbname set recovery full;
GO

## Full模式 的旧称叫”Checkpoint without truncate log“，也就是SQL Server不主动截断log，只有备份log之后，才可以截断log，否则log文件会一直增大，直到撑爆硬盘，因此需要部署一个job定时备份log。
## Full的好处是可以做point-in-time恢复，最大限度的保证数据不丢失，一般用于critical的业务环境里。缺点就是DBA需要维护log，增加人员成本(其实也就是多了定时备份log这项工作而已)。

1.1.2 大字段业务排查(新版本以支持，此步骤可忽略)

同步前必须排除业务上对大字段做WRITETEXT和UPDATETEXT的表，因为开启cdc会影响WRITETEXT和UPDATETEXT的使用，导致生产库业务异常

开启cdc，执行WRITETEXT和UPDATETEXT报错：
7138 因为正在用 Data Transformation Services (DTS)复制该列，或变更数据捕获正在跟踪该列，所以不允许使用 WRITETEXT 语句。

WRITETEXT功能说明：
WRITETEXT对现有列类型为text,ntext,image执行最小日志记录的更新操作，将覆盖列数据。
一般医院的EMR库（电子病历库）业务会用到。

注：
（1）在线模式和辅助库模式，writetext和updatetext功能已支持（zcbus-7.6-12-20211223.tar.gz）
（2）辅助库模式，zcbus_extended_logging存储过程已支持非大字段列级开启cdc，不会影响writetext和updatetext功能使用
（3）在线模式程序和zcbus_extended_logging存储过程打开cdc的时候，先检查是否有pk/uk，如果有pk uk的话，@captured_column_list就填pk uk的列，如果没有pk uk的话，@captured_column_list就填表中不是text next image类型的第一个列

1.2 启动SQLSERVER 代理服务

net start SQLSERVERAGENT

1.3 创建用户及赋权

1.3.1 创建数据库用户

USE [master]
go
CREATE LOGIN [zcbus] WITH PASSWORD=N'zcbus', DEFAULT_DATABASE=[数据库名]
use 数据库名;
go
create user [zcbus] for login [zcbus]
go

1.3.2 数据库用户赋权

use 数据库名;
go
exec master..sp_addsrvrolemember @loginame = N'zcbus', @rolename = N'sysadmin'
go

参考: https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-help-transact-sql?view=sql-server-ver15

1.3.4 开启数据库CDC功能

--开启库cdc功能
use 数据库名;
go
exec sp_cdc_enable_db 
go
--查看数据库CDC功能是否开启成功
SELECT name,is_cdc_enabled FROM sys.databases WHERE name = '数据库名';
go

1.3.5 创建schema zcbus

IF NOT EXISTS(select * from sys.schemas where name ='zcbus')
EXEC sp_executesql N'CREATE SCHEMA zcbus';
GO

1.3.6 创建zcbus.ddl_event_tbl表用于存储DDL变化信息

CREATE TABLE [zcbus].[ddl_event_tbl](
  [logid] [int] IDENTITY(1,1) PRIMARY KEY,
  [op_time] smalldatetime,
  [event_type] varchar(128),
  [schema_name] varchar(128),
  [object_name] varchar(128),
  [object_type] varchar(64),
  [sql_text] varchar(max)
);
GO

1.3.7 创建zcbus_extended_logging用于开启表的cdc功能的存储过程

create procedure zcbus.zcbus_extended_logging
(
  @owner  sysname,
  @tabname sysname,
  @enable bit = 1
)
as
begin
 set nocount on 
 declare @retcode int
 declare @sname sysname
 declare @event sysname
 declare @cname sysname
 declare @stypeid int
 declare @utypeid int
 declare @pkey int
 declare @keycols varchar(512)
 declare @ctname varchar(256)
 declare @funname varchar(256)
 declare @sqlstr nvarchar(1024)
 declare @iname varchar(256)
 declare @stname sysname
 set @stname = @owner+'.['+@tabname+']';

 if @enable=0
 begin
   if exists (select 1 from sys.tables where schema_name(schema_id)=@owner
            and name = @tabname and is_replicated = 1)
   begin
     exec @retcode =sys.sp_cdc_disable_table 
                 @source_schema = @owner, 
                 @source_name = @tabname, 
                 @capture_instance = 'all';
     if (@retcode <> 0)
     begin 
       raiserror ('sp_extended_logging table:%s error', 16, -1, @tabname)  
       return 1
     end
   end
   return 0
 end
 if exists (select 1 from sys.tables where schema_name(schema_id)=@owner
           and name = @tabname and is_replicated = 1)
 begin
  return 0
 end  
 set @ctname = @owner+'_'+@tabname+'_CT'
 if exists (select 1 from sys.tables where schema_name(schema_id)='cdc' and name = @ctname)
 begin
   set @sqlstr = 'drop table cdc.'+@ctname
   exec sp_executesql  @sqlstr
 end
 set @iname = @owner+'_'+@tabname
 if exists (select 1 from sys.objects where schema_name(schema_id)='cdc'
           and name = 'change_tables')
 begin
       delete cdc.change_tables  where capture_instance = @iname
 end
 set @funname = 'fn_cdc_get_all_changes_'+@owner+'_'+@tabname
 if exists (select 1 from sys.objects where schema_name(schema_id)='cdc'
           and name = @funname)
 begin
   set @sqlstr = 'drop function cdc.'+@funname
   exec sp_executesql  @sqlstr
 end   
 set @funname = 'fn_cdc_get_net_changes_'+@owner+'_'+@tabname
 if exists (select 1 from sys.objects where schema_name(schema_id)='cdc'
           and name = @funname)
 begin
   set @sqlstr = 'drop function cdc.'+@funname
   exec sp_executesql  @sqlstr
 end     

 if exists (select 1 from sys.objects where schema_name(schema_id)=@owner
           and name = @tabname)
 begin 
 if @enable = 1  
   if exists (select 1 from sys.tables where schema_name(schema_id)=@owner
            and name = @tabname)
   begin
     exec @retcode =sys.sp_cdc_enable_table 
             @source_schema = @owner, 
             @source_name = @tabname,
             @role_name = 'NULL'; 
     if (@retcode <> 0)
     begin 
       raiserror ('sp_extended_logging table:%s error', 16, -1, @tabname)  
       return 1
     end
   end
   return 0
 end
end
GO

1.3.8 开启同步表的cdc功能

exec zcbus.zcbus_extended_logging [schema名],[表名]
--查看表cdc功能是否开启
SELECT name,is_tracked_by_cdc FROM sys.tables WHERE name='table_name'

注：如遇到开启表级别cdc时报错(返回的错误为 22836/返回的错误为 14234)

详细报错信息：无法更新元数据来指示已对表 [dbo].[Table_1] 启用了变更数据捕获。执行命令 '[sys].[sp_cdc_add_job] @job_type = N'capture'' 时失败。返回的错误为 22836: '无法更新数据库 test 的元数据来指示已添加某变更数据捕获作业。执行命令 'sp_add_jobstep_internal' 时失败。返回的错误为 14234: '指定的 '@srv' 无效(有效值由 sp_helpserver 返回)。'。请使用此操作和错误来确定失败的原因并重新提交请求。'。请使用此操作和错误来确定失败的原因并重新提交请求。
报错原因：SqlServer安装后修改了主机名，导致实例名与主机名不一致
SELECT * FROM master.dbo.sysservers;
SELECT SERVERPROPERTY('ServerName');

解决方式：执行下方sql进行修改：
IF serverproperty('servername')<>@@servername    
  BEGIN  
  DECLARE  @server SYSNAME  
  SET   @server=@@servername      
  EXEC  sp_dropserver @server=@server    
  SET   @server=cast(serverproperty('servername') AS SYSNAME)   
  EXEC  sp_addserver @server=@server,@local='LOCAL'     
  END  
  ELSE  
    PRINT '实例名与主机名一致，无需修改！' 

1.3.9 禁用tr_MScdc_ddl_event触发器

disable trigger tr_MScdc_ddl_event on database

注：关闭触发器后，将停止CDC功能，从而不再生成CDC表及相应的变化数据记录，以减轻对SQL Server的性能影响。

1.3.10 禁用cdc的job

declare @jobname sysname
select @jobname='cdc.'+db_name()+'_capture'
exec msdb.dbo.sp_update_job @job_name= @jobname, @enabled = 0
select @jobname='cdc.'+db_name()+'_cleanup'
exec msdb.dbo.sp_update_job @job_name= @jobname, @enabled = 0
GO

注：cdc须打开，cdc.库名cleanup和cdc.库名capture两个job与tr_MScdc_ddl_event触发器可以禁用，禁用job和触发器后cdc下的%_CT这类表不会产生记录

1.3.11 创建捕获DDL变化的触发器

create TRIGGER [zcbus_ddl_dblog_trigger]
ON DATABASE 
FOR DDL_TABLE_EVENTS,DDL_INDEX_EVENTS,RENAME,DDL_EXTENDED_PROPERTY_EVENTS
AS 
BEGIN
    SET NOCOUNT ON;

    declare @EventData xml
    set @EventData=EVENTDATA();

    if exists(select 1 from sys.tables where name = 'ddl_event_tbl')
    begin
     insert into [zcbus].[ddl_event_tbl]([op_time],[event_type],[schema_name],
       [object_name],[object_type],[sql_text]) values
       (
       @EventData.value('(/EVENT_INSTANCE/PostTime)[1]','SMALLDATETIME'),
       @EventData.value('(/EVENT_INSTANCE/EventType)[1]','varchar(64)'),
       @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(128)'),
       @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(128)'),
       @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(64)'),
       @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)')
       );
    end  
END;
GO

2 补充说明

2.1 关闭cdc参考

2.1.1 先开启库级cdc（关闭库级cdc之前未关闭表级cdc时）

USE 库名
GO
EXECUTE sys.sp_cdc_enable_db;
GO

2.1.2 查询指定schema名下的所有关闭表级cdc的批量sql：

USE 库名
GO
SELECT 'EXEC sys.sp_cdc_disable_table @source_schema = N''dbo'', @source_name = N'''+name+''',@capture_instance = N''all''; '
FROM sys.tables where schema_id=SCHEMA_ID('schema名');

注意：系统运行期间，在运行的表，不允许关闭CDC，否则更新操作将出现数据错误的情况

2.1.3 拷贝生成的批量语句，并执行

USE 库名
GO
执行2.1.2中生成的批量语句

2.1.4 最后关闭库级cdc

USE 库名
GO
EXEC sys.sp_cdc_disable_db
GO

注：库级cdc关闭后，无法再关闭表级cdc，关闭库级cdc之前未关闭表级cdc，使用WRITETEXT和UPDATETEXT功能还会报错：
消息7138，级别16，状态1，第5 行
因为正在用Data Transformation Services (DTS)复制该列，或变更数据捕获正在跟踪该列，所以不允许使用WRITETEXT 语句。

2.2 SQL参考

• 查询zcbus用户的object权限

  exec sp_helprotect NULL, 'zcbus'
  go

• 查询zcbus用户拥有的role

  exec sp_helpuser 'zcbus'
  go

• 查询哪些用户拥有指定的系统role

  exec sp_helpsrvrolemember 'sysadmin'
  go

2.3 数据日志截断操作

ALTER DATABASE testdb SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE testdb SET RECOVERY SIMPLE   --简单模式
DBCC SHRINKFILE (N'testdb_Log' , 11 ,TRUNCATEONLY)   -- 11是大小  11M
ALTER DATABASE testdb SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE testdb SET RECOVERY FULL  --还原为完全模式

2.4 数据类型支持列表