|
|
51CTO旗下网站
|
|
移动端

SQL Server 2008数据库中CDC的功能使用及说明

本文我们主要对SQL Server 2008数据库中CDC的功能使用进行了详细地说明与解释,希望能够对您有所帮助。

作者:陈希章来源:博客园|2011-08-30 17:06

SQL Server 2008数据库中CDC的功能使用及说明的相关知识是本文我们主要要介绍的内容,那么什么是CDC呢?CDC(Change Data Capture:变更数据捕获)这个功能是SQL Server 2008企业版的功能,它提供了一种新的机制,对表格数据的更新进行跟踪,在数据仓库的建设过程中,通过这种技术,可以简化从业务数据库导入数据的复杂度。

1. 准备一个数据库,里面准备一个表,Orders

SQL Server 2008数据库中CDC的功能使用及说明

2. 启用数据库级别的CDC选项

--在数据库级别启用CDC功能

EXEC sys.sp_cdc_enable_db

这个命令执行完之后,会在系统表里面添加6个表格

SQL Server 2008数据库中CDC的功能使用及说明

3.在需要做数据捕获的表上面启用CDC选项

  1. EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='Orders',@capture_instance='Orders',@supports_net_changes=0,@role_name=null 
  2. http://msdn.microsoft.com/en-us/library/bb522475.aspx 

执行之后,会有如下的输出消息

SQL Server 2008数据库中CDC的功能使用及说明

这个提示的意思是说,要启动SQL Server Agent。因为CDC功能是要通过一个两个作业来自动化完成的

SQL Server 2008数据库中CDC的功能使用及说明

与此同时,执行上面的命令还将在系统表中添加一个表格

SQL Server 2008数据库中CDC的功能使用及说明

还会添加一个函数

SQL Server 2008数据库中CDC的功能使用及说明

4.插入或者更新数据测试CDC功能

--插入或者更新数据测试CDC功能

  1. INSERT Orders(CustomerID) VALUES('Microsoft');  
  2. INSERT Orders(CustomerID) VALUES('Google');  
  3. UPDATE Orders SET CustomerID='Yahoo' WHERE OrderID=1 
  4. DELETE FROM Orders WHERE OrderID=2 

这个范例插入两行数据,紧接着又对第一行更新,然后还删除了第二行,所以最终只有一行数据

SQL Server 2008数据库中CDC的功能使用及说明

那么,我们来看看CDC做了什么事情呢?

SELECT * FROM cdc.Orders_CT

SQL Server 2008数据库中CDC的功能使用及说明

我们可以来解释一下上面结果的含义:

__$operation=2的情况,表示新增

__$operation=3或者4,表示更新,3表示旧值,4表示新值

__$operation=1的情况,表示删除

很好理解,不是吗?

但是,我们一般都是需要按照时间范围进行检索,对吧,所以,需要使用下面的语法进行查询

--按照时间范围查询CDC结果

  1. DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10)  
  2. DECLARE @start_time DATETIME = '2011-8-10 00:00:00' 
  3. DECLARE @end_time DATETIME ='2011-8-11 00:00:00' 
  4. SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)  
  5. SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time)  
  6. 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

查询的结果如下:

SQL Server 2008数据库中CDC的功能使用及说明

如果需要包含更新操作的旧值,则可以以下的语法

  1. DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10)  
  2. DECLARE @start_time DATETIME = '2011-8-10 00:00:00' 
  3. DECLARE @end_time DATETIME ='2011-8-11 00:00:00' 
  4. SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)  
  5. SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time)  
  6. SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all update old') 

SQL Server 2008数据库中CDC的功能使用及说明

通常,为了方便起见,我们会将这个查询定义为一个存储过程,如下

--定义存储过程来进行查询

  1. CREATE PROC GetOrdersCDCResult(@start_time DATETIME,@end_time DATETIME)  
  2. AS  
  3. BEGIN  
  4. DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10)  
  5. SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)  
  6. SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time)  
  7. SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all')  
  8. END 

然后,每次需要用的时候,就直接调用即可。

--执行存储过程

EXEC GetOrdersCDCResult '2011-8-10','2011-8-11'

5.结合SSIS实现事实表的增量更新

下面展示了一个SSIS 包的设计,这里面读取CDC的数据,先进行一些查找,然后按照__$operation的值拆分成为三个操作,分别进行插入,更新和删除,这样就可以实现对事实表的增量更新

SQL Server 2008数据库中CDC的功能使用及说明

本文所有的代码如下:

  1. USE SampleDatabase  
  2. GO  
  3. --在数据库级别启用CDC功能  
  4. EXEC sys.sp_cdc_enable_db   
  5. --在需要做数据捕获的表格上面启用CDC功能  
  6. EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='Orders',@capture_instance='Orders',@supports_net_changes=0,@role_name=null 
  7. --插入或者更新数据测试CDC功能  
  8. INSERT Orders(CustomerID) VALUES('Microsoft');  
  9. INSERT Orders(CustomerID) VALUES('Google');  
  10. UPDATE Orders SET CustomerID='Yahoo' WHERE OrderID=1 
  11. DELETE FROM Orders WHERE OrderID=2 
  12. --查询CDC的结果  
  13. SELECT * FROM cdc.Orders_CT  
  14. --按照时间范围查询CDC结果  
  15. DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10)  
  16. DECLARE @start_time DATETIME = '2011-8-10 00:00:00' 
  17. DECLARE @end_time DATETIME ='2011-8-11 00:00:00' 
  18. SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)  
  19. SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time)  
  20. SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all')  
  21. --定义存储过程来进行查询  
  22. CREATE PROC GetOrdersCDCResult(@start_time DATETIME,@end_time DATETIME)  
  23. AS  
  24. BEGIN  
  25. DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10)  
  26. SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)  
  27. SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time)  
  28. SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all')  
  29. END  
  30. --执行存储过程  
  31. EXEC GetOrdersCDCResult '2011-8-10','2011-8-11' 

以上就是SQL Server 2008数据库中CDC的功能使用及说明的全部内容,本文我们就介绍到这里了,希望本次的介绍能够对您有所收获!

【编辑推荐】

  1. SQL Server数据库链接查询的方式详解
  2. SQL Server 2005导入Oracle 10g的C#源码
  3. SQL Server数据库没有JOIN条件导致笛卡尔乘积
  4. SQL Server 2008中使用FileStream存取大文件实例介绍
  5. SQL Server数据库row_number() over() 来自动产生行号
【责任编辑:赵鹏 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

Redis运维秘籍

Redis运维秘籍

运维标配技术
共15章 | one叶孤舟

39人订阅学习

活学活用 Ubuntu Server

活学活用 Ubuntu Server

实战直通车
共35章 | UbuntuServer

233人订阅学习

Java EE速成指南

Java EE速成指南

掌握Java核心
共30章 | 51CTO王波

89人订阅学习

读 书 +更多

系统分析师考试辅导(2007版)

《系统分析师考试辅导(2007版)》内容涵盖了最新的系统分析师考试大纲信息系统综合知识的所有知识点,分析了近3年信息系统分析与设计案例...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO播客