SQL Server 2008中的CDC(Change Data Capture)功能使用及释疑,SQL Server 2008中的CDC(Change Data Capture)功能使用及释疑,SQL Server 2008中的CDC(Change Data Capture)功能使用及释疑
SQL Server 2008中的CDC(Change Data Capture)功能使用及释疑
关键词：CDC
原文：http://www.cnblogs.com/chenxizhang/archive/2011/08/10/2133408.html
SQL Server 2008中的CDC(Change Data Capture)功能使用及释疑
CDC（Change Data Capture:变更数据捕获）这个功能是SQL Server 2008企业版的功能，它提供了一种新的机制，对表格数据的更新进行跟踪，在数据仓库的建设过程中，通过这种技术，可以简化从业务数据库导入数据的复杂度。
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。
之前我有过两篇文章介绍，最近因为又在和有关客户介绍这方面的应用。发现之前的例子不是那么完整和清楚，特此再整理一篇出来，给大家参考
1. 准备一个数据库，里面准备一个表，Orders
2. 启用数据库级别的CDC选项
--在数据库级别启用CDC功能
EXEC sys.sp_cdc_enable_db
这个命令执行完之后，会在系统表里面添加6个表格
3.在需要做数据捕获的表上面启用CDC选项
EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='Orders',@capture_instance='Orders',@supports_net_changes=0,@role_name=null
【备注】关于这个存储过程的具体用法和有关参数的含义，请参考
http://msdn.microsoft.com/en-us/library/bb522475.aspx
执行之后，会有如下的输出消息
这个提示的意思是说，要启动SQL Server Agent。因为CDC功能是要通过一个两个作业来自动化完成的
与此同时，执行上面的命令还将在系统表中添加一个表格
还会添加一个函数
4.插入或者更新数据测试CDC功能
--插入或者更新数据测试CDC功能
INSERT Orders(CustomerID) VALUES('Microsoft'); INSERT Orders(CustomerID) VALUES('Google'); UPDATE Orders SET CustomerID='Yahoo' WHERE OrderID=1 DELETE FROM Orders WHERE OrderID=2
这个范例插入两行数据，紧接着又对第一行更新，然后还删除了第二行，所以最终只有一行数据
那么，我们来看看CDC做了什么事情呢？
SELECT * FROM cdc.Orders_CT
我们可以来解释一下上面结果的含义
__$operation=2的情况，表示新增
__$operation=3或者4，表示更新，3表示旧值，4表示新值
__$operation=1的情况，表示删除
很好理解，不是吗？
但是，我们一般都是需要按照时间范围进行检索，对吧，所以，需要使用下面的语法进行查询
--按照时间范围查询CDC结果
DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) DECLARE @start_time DATETIME = '2011-8-10 00:00:00' DECLARE @end_time DATETIME ='2011-8-11 00:00:00' SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all')
关于sys.fn_cdc_map_time_to_lsn这个函数，请参考
http://msdn.microsoft.com/en-us/library/bb500137.aspx
查询的结果如下
如果需要包含更新操作的旧值，则可以以下的语法
DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) DECLARE @start_time DATETIME = '2011-8-10 00:00:00' DECLARE @end_time DATETIME ='2011-8-11 00:00:00' SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all update old')
通常，为了方便起见，我们会将这个查询定义为一个存储过程，如下
--定义存储过程来进行查询
CREATE PROC GetOrdersCDCResult(@start_time DATETIME,@end_time DATETIME) AS BEGIN DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all') END
然后，每次需要用的时候，就直接调用即可
--执行存储过程
EXEC GetOrdersCDCResult '2011-8-10','2011-8-11'
5.结合SSIS实现事实表的增量更新
下面展示了一个SSIS 包的设计，这里面读取CDC的数据，先进行一些查找，然后按照__$operation的值拆分成为三个操作，分别进行插入，更新和删除，这样就可以实现对事实表的增量更新
本文所有的代码如下
USE SampleDatabase
GO --在数据库级别启用CDC功能 EXEC sys.sp_cdc_enable_db --在需要做数据捕获的表格上面启用CDC功能 EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='Orders',@capture_instance='Orders',@supports_net_changes=0,@role_name=null --插入或者更新数据测试CDC功能 INSERT Orders(CustomerID) VALUES('Microsoft'); INSERT Orders(CustomerID) VALUES('Google'); UPDATE Orders SET CustomerID='Yahoo' WHERE OrderID=1 DELETE FROM Orders WHERE OrderID=2 --查询CDC的结果 SELECT * FROM cdc.Orders_CT --按照时间范围查询CDC结果 DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) DECLARE @start_time DATETIME = '2011-8-10 00:00:00' DECLARE @end_time DATETIME ='2011-8-11 00:00:00' SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all') --定义存储过程来进行查询 CREATE PROC GetOrdersCDCResult(@start_time DATETIME,@end_time DATETIME) AS BEGIN DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all') END --执行存储过程 EXEC GetOrdersCDCResult '2011-8-10','2011-8-11',原文：http://www.cnblogs.com/chenxizhang/archive/2011/08/10/2133408.html
SQL Server 2008中的CDC(Change Data Capture)功能使用及释疑
CDC（Change Data Capture:变更数据捕获）这个功能是SQL Server 2008企业版的功能，它提供了一种新的机制，对表格数据的更新进行跟踪，在数据仓库的建设过程中，通过这种技术，可以简化从业务数据库导入数据的复杂度。
之前我有过两篇文章介绍，最近因为又在和有关客户介绍这方面的应用。发现之前的例子不是那么完整和清楚，特此再整理一篇出来，给大家参考
1. 准备一个数据库，里面准备一个表，Orders
2. 启用数据库级别的CDC选项
--在数据库级别启用CDC功能
EXEC sys.sp_cdc_enable_db
这个命令执行完之后，会在系统表里面添加6个表格
3.在需要做数据捕获的表上面启用CDC选项
EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='Orders',@capture_instance='Orders',@supports_net_changes=0,@role_name=null
【备注】关于这个存储过程的具体用法和有关参数的含义，请参考
http://msdn.microsoft.com/en-us/library/bb522475.aspx
执行之后，会有如下的输出消息
这个提示的意思是说，要启动SQL Server Agent。因为CDC功能是要通过一个两个作业来自动化完成的
与此同时，执行上面的命令还将在系统表中添加一个表格
还会添加一个函数
4.插入或者更新数据测试CDC功能
--插入或者更新数据测试CDC功能
INSERT Orders(CustomerID) VALUES('Microsoft'); INSERT Orders(CustomerID) VALUES('Google'); UPDATE Orders SET CustomerID='Yahoo' WHERE OrderID=1 DELETE FROM Orders WHERE OrderID=2
这个范例插入两行数据，紧接着又对第一行更新，然后还删除了第二行，所以最终只有一行数据
那么，我们来看看CDC做了什么事情呢？
SELECT * FROM cdc.Orders_CT
我们可以来解释一下上面结果的含义
__$operation=2的情况，表示新增
__$operation=3或者4，表示更新，3表示旧值，4表示新值
__$operation=1的情况，表示删除
很好理解，不是吗？
但是，我们一般都是需要按照时间范围进行检索，对吧，所以，需要使用下面的语法进行查询
--按照时间范围查询CDC结果
DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) DECLARE @start_time DATETIME = '2011-8-10 00:00:00' DECLARE @end_time DATETIME ='2011-8-11 00:00:00' SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all')
关于sys.fn_cdc_map_time_to_lsn这个函数，请参考
http://msdn.microsoft.com/en-us/library/bb500137.aspx
查询的结果如下
如果需要包含更新操作的旧值，则可以以下的语法
DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) DECLARE @start_time DATETIME = '2011-8-10 00:00:00' DECLARE @end_time DATETIME ='2011-8-11 00:00:00' SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all update old')
通常，为了方便起见，我们会将这个查询定义为一个存储过程，如下
--定义存储过程来进行查询
CREATE PROC GetOrdersCDCResult(@start_time DATETIME,@end_time DATETIME) AS BEGIN DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all') END
然后，每次需要用的时候，就直接调用即可
--执行存储过程
EXEC GetOrdersCDCResult '2011-8-10','2011-8-11'
5.结合SSIS实现事实表的增量更新
下面展示了一个SSIS 包的设计，这里面读取CDC的数据，先进行一些查找，然后按照__$operation的值拆分成为三个操作，分别进行插入，更新和删除，这样就可以实现对事实表的增量更新
本文所有的代码如下
USE SampleDatabase
GO --在数据库级别启用CDC功能 EXEC sys.sp_cdc_enable_db --在需要做数据捕获的表格上面启用CDC功能 EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='Orders',@capture_instance='Orders',@supports_net_changes=0,@role_name=null --插入或者更新数据测试CDC功能 INSERT Orders(CustomerID) VALUES('Microsoft'); INSERT Orders(CustomerID) VALUES('Google'); UPDATE Orders SET CustomerID='Yahoo' WHERE OrderID=1 DELETE FROM Orders WHERE OrderID=2 --查询CDC的结果 SELECT * FROM cdc.Orders_CT --按照时间范围查询CDC结果 DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) DECLARE @start_time DATETIME = '2011-8-10 00:00:00' DECLARE @end_time DATETIME ='2011-8-11 00:00:00' SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all') --定义存储过程来进行查询 CREATE PROC GetOrdersCDCResult(@start_time DATETIME,@end_time DATETIME) AS BEGIN DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all') END --执行存储过程 EXEC GetOrdersCDCResult '2011-8-10','2011-8-11',CDC（Change Data Capture:变更数据捕获）这个功能是SQL Server 2008企业版的功能，它提供了一种新的机制，对表格数据的更新进行跟踪，在数据仓库的建设过程中，通过这种技术，可以简化从业务数据库导入数据的复杂度。
之前我有过两篇文章介绍，最近因为又在和有关客户介绍这方面的应用。发现之前的例子不是那么完整和清楚，特此再整理一篇出来，给大家参考
1. 准备一个数据库，里面准备一个表，Orders
2. 启用数据库级别的CDC选项
--在数据库级别启用CDC功能
EXEC sys.sp_cdc_enable_db
这个命令执行完之后，会在系统表里面添加6个表格
3.在需要做数据捕获的表上面启用CDC选项
EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='Orders',@capture_instance='Orders',@supports_net_changes=0,@role_name=null
【备注】关于这个存储过程的具体用法和有关参数的含义，请参考
http://msdn.microsoft.com/en-us/library/bb522475.aspx
执行之后，会有如下的输出消息
这个提示的意思是说，要启动SQL Server Agent。因为CDC功能是要通过一个两个作业来自动化完成的
与此同时，执行上面的命令还将在系统表中添加一个表格
还会添加一个函数
4.插入或者更新数据测试CDC功能
--插入或者更新数据测试CDC功能
INSERT Orders(CustomerID) VALUES('Microsoft'); INSERT Orders(CustomerID) VALUES('Google'); UPDATE Orders SET CustomerID='Yahoo' WHERE OrderID=1 DELETE FROM Orders WHERE OrderID=2
这个范例插入两行数据，紧接着又对第一行更新，然后还删除了第二行，所以最终只有一行数据
那么，我们来看看CDC做了什么事情呢？
SELECT * FROM cdc.Orders_CT
我们可以来解释一下上面结果的含义
__$operation=2的情况，表示新增
__$operation=3或者4，表示更新，3表示旧值，4表示新值
__$operation=1的情况，表示删除
很好理解，不是吗？
但是，我们一般都是需要按照时间范围进行检索，对吧，所以，需要使用下面的语法进行查询
--按照时间范围查询CDC结果
DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) DECLARE @start_time DATETIME = '2011-8-10 00:00:00' DECLARE @end_time DATETIME ='2011-8-11 00:00:00' SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all')
关于sys.fn_cdc_map_time_to_lsn这个函数，请参考
http://msdn.microsoft.com/en-us/library/bb500137.aspx
查询的结果如下
如果需要包含更新操作的旧值，则可以以下的语法
DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) DECLARE @start_time DATETIME = '2011-8-10 00:00:00' DECLARE @end_time DATETIME ='2011-8-11 00:00:00' SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all update old')
通常，为了方便起见，我们会将这个查询定义为一个存储过程，如下
--定义存储过程来进行查询
CREATE PROC GetOrdersCDCResult(@start_time DATETIME,@end_time DATETIME) AS BEGIN DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all') END
然后，每次需要用的时候，就直接调用即可
--执行存储过程
EXEC GetOrdersCDCResult '2011-8-10','2011-8-11'
5.结合SSIS实现事实表的增量更新
下面展示了一个SSIS 包的设计，这里面读取CDC的数据，先进行一些查找，然后按照__$operation的值拆分成为三个操作，分别进行插入，更新和删除，这样就可以实现对事实表的增量更新
本文所有的代码如下
USE SampleDatabase
GO --在数据库级别启用CDC功能 EXEC sys.sp_cdc_enable_db --在需要做数据捕获的表格上面启用CDC功能 EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='Orders',@capture_instance='Orders',@supports_net_changes=0,@role_name=null --插入或者更新数据测试CDC功能 INSERT Orders(CustomerID) VALUES('Microsoft'); INSERT Orders(CustomerID) VALUES('Google'); UPDATE Orders SET CustomerID='Yahoo' WHERE OrderID=1 DELETE FROM Orders WHERE OrderID=2 --查询CDC的结果 SELECT * FROM cdc.Orders_CT --按照时间范围查询CDC结果 DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) DECLARE @start_time DATETIME = '2011-8-10 00:00:00' DECLARE @end_time DATETIME ='2011-8-11 00:00:00' SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all') --定义存储过程来进行查询 CREATE PROC GetOrdersCDCResult(@start_time DATETIME,@end_time DATETIME) AS BEGIN DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all') END --执行存储过程 EXEC GetOrdersCDCResult '2011-8-10','2011-8-11'
