SQLServer利用CDC功能实时同步表数据。
一、适用环境
仅在SQLServer2008企业版、开发版和评估版以后可用。
确保SQLServer数据库已经开启SQL Server代理。
二、CDC功能介绍
CDC（change data capture）功能主要捕获SQLServer指定表的增删改操作，由于任何操作都会写日志（哪怕truncate），所以CDC的捕获来源于日志文件。日志文件会把更改应用到数据文件中，同时也会标记符合要求的数据标记为需要添加跟踪的项。然后通过一些配套函数，最后写入到数据仓库中。
三、具体同步步骤
1、确定哪些表进行CDC同步
2、对数据库开启CDC功能
USE [Finance]
GO
EXEC sys . sp_cdc_enable_db
GO
查看CDC是否开启：
SELECT is_cdc_enabled ,
CASE WHEN is_cdc_enabled = 0 THEN 'CDC 功能禁用 '
ELSE 'CDC 功能启用 '
END 描述
FROM sys . databases
WHERE NAME = 'finance'
1表示开启。
发现数据库安全性多了cdc，架构多了cdc
出现6个系统表：
cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping
dbo.systranschemas
对某些表开启捕获：
USE [Finance] ;
GO
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo'
, @source_name = N'DO'
, @role_name = N'cdc_Admin'--可以自动创建
, @capture_instance=DEFAULT
GO
检查是否开启成功：
SELECT name ,
is_tracked_by_cdc ,
CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用'
ELSE 'CDC功能启用'
END 描述
FROM sys.tables
WHERE OBJECT_ID IN( OBJECT_ID('dbo.DO'))
系统表会增加开启CDC功能的表。
SQLServer代理出现作业：
相关存储过程：
Sys.sp_cdc_disable_db： 建议先禁用表，再禁用库
函数：
可以对表insert数据，查看数据变化：
select * from [cdc].[dbo_DO_CT];
如果有数据的插入、更新、删除会在这里记录。
对于__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）
对于__$start_lsn列：由于更改是来源与数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）
但是微软不建议直接查询这类表，建议使用cdc.fn_cdc_get_all_changes_<捕获实例> 和cdc.fn_cdc_get_net_changes_
查询已经开启的捕获：
EXECUTE sys.sp_cdc_help_change_data_capture;
GO
USE Finance;
GO
cdc.fn_cdc_get_all_changes_<捕获实例>用法：
DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn =
sys.fn_cdc_get_min_lsn('dbo_DO')
SET @to_lsn = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_dept
(@from_lsn, @to_lsn, N'all');
GO
--返回所有表的变更捕获配置信息
EXECUTE sys.sp_cdc_help_change_data_capture;
GO
查看对某个实例(即表)的哪些列做了捕获监控:
EXEC sys.sp_cdc_get_captured_columns
@capture_instance = 'HumanResources_Department' -- sysname
也可以从下面中查找配置信息：
SELECT * FROM msdb.dbo.cdc_jobs
启用cdc之后会自动创建了两个作业，可以先使用以下语句来查看：
sp_cdc_help_jobs
--显示原有配置：
EXEC sp_cdc_help_jobs
GO
--更改数据保留时间为分钟
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention=100
GO
--停用作业
EXEC sys.sp_cdc_stop_jobN'cleanup'
GO
--启用作业
EXEC sys.sp_cdc_start_jobN'cleanup'
GO
--再次查看
EXEC sp_cdc_help_jobs
GO
停止/开始作业，可以使用以下语句：
--停用作业
EXEC sys.sp_cdc_stop_jobN'cleanup'
GO
--启用作业
EXEC sys.sp_cdc_start_jobN'cleanup'
GO
删除作业：
EXEC sys.sp_cdc_drop_job@job_type = N'cleanup' -- nvarchar(20)
GO
--查看作业
EXEC sys.sp_cdc_help_jobs
GO
创建作业：
EXEC sys.sp_cdc_add_job
@job_type = N'cleanup',
@start_job = 0,
@retention = 5760
--查看作业
EXEC sys.sp_cdc_help_jobs
GO
DDL变更捕获：
CDC除了捕获数据变更之外，还能捕获DDL操作的变化。前提是先要确保SQLServer 代理的启用，其实CDC功能都需要确保sql 代理正常运行，因为所有操作都通过代理中的两个作业来实现的。
现在先来对HumanResources.Department 表修改一下，把name的长度加长：
ALTER TABLE HumanResources.Department ALTER COLUMN Name NVARCHAR(120) ;
GO
然后查询ddl记录表：
SELECT * FROM cdc.ddl_history
使用CDC的函数来获取更改：
A、使用cdc.fn_cdc_get_all_changes_HumanResources_Department 函数报告捕获实例HumanResources_Department 的当前所有可用更改：
DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn =
sys.fn_cdc_get_min_lsn('HumanResources_Department')
SET @to_lsn = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Department
(@from_lsn, @to_lsn, N'all update old');
GO
B、获取某个时间段的更改信息：
先根据日志序列号（logsequence number ,LSN）来获取跟踪变更数据：
Sys.fn_cdc_map_time_to_lsn获取变更范围内的最大、最小LSN值。可以使用：
Smallest greater than;smallest greater than orequal;largest less than;largest less than or equal.
如查询某个时间段插入的数据：
--插入数据
INSERT INTO HumanResources.Department(name,GroupName,ModifiedDate)
VALUES('test','abc',GETDATE())
INSERT INTO HumanResources.Department(name,GroupName,ModifiedDate)
VALUES('test1','abc1',GETDATE())
go
--检查数据
DECLARE @bglsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal','2012-10-12 12:00:00.997')
DECLARE @edlsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('largest less than or equal',GETDATE())
SELECT DepartmentID,GroupName,Name
FROM cdc.HumanResources_Department_CT
WHERE [__$operation]=2 AND [__$start_lsn] BETWEEN @bglsn AND @edlsn
C、sys.fn_cdc_map_lsn_to_time 查询变更时间：
SELECT [__$operation] ,
CASE [__$operation] WHEN 1 THEN '删除' WHEN 2 THEN '插入' WHEN 3 THEN '更新（捕获的列值是执行更新操作前的值）'
WHEN 4 THEN '更新（捕获的列值是执行更新操作后的值）' END [类型],
sys.fn_cdc_map_lsn_to_time([__$start_lsn]) [更改时间] ,
name ,
DepartmentID ,
GroupName ,
ModifiedDate
FROM cdc.HumanResources_Department_CT
注意,由于该表刚好有一个modfieddate字段,所以和更改时间相同.
D、获取LSN边界：
SELECT sys.fn_cdc_get_max_lsn()[数据库级别的最大LSN],
sys.fn_cdc_get_min_lsn('cdc.HumanResources_Department_CT')[捕获实例的lsn]
增加删除字段时候开启关闭表级CDC：
查询capture_instance：
EXECUTE sys.sp_cdc_help_change_data_capture;
GO
关闭：
USE [Sales] ;
GO
EXECUTE sys.sp_cdc_disable_table
@source_schema = N'dbo'
, @source_name = N'SO'
-- , @role_name = N'cdc_Admin'--可以自动创建
, @capture_instance=N'dbo_SO'
GO
开启：
USE [Sales] ;
GO
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo'
, @source_name = N'SO'
, @role_name = N'cdc_Admin'--可以自动创建
, @capture_instance=N'dbo_SO'
GO