俄罗斯贵宾会-俄罗斯贵宾会官网
做最好的网站

SQL Server 2008中新增的变更数据捕获(CDC)和更改跟踪

有些场景下,需要隔离不同的DB,彼此DB之间不能互相访问,但实际的业务场景又需要从A DB访问B DB的情形,这时怎么办?我认为有如下常规的三种方案:

SQL Server 2008中SQL应用系列--目录索引

1.双方提供RESET API,需要访问不同DB数据时,可以通过API来获取指定数据;

  本文主要介绍SQL Server中记录数据变更的四个方法:触发器、Output子句、变更数据捕获(Change Data Capture 即CDC)功能、同步更改跟踪。其中后两个为SQL Server 2008所新增。

这种方案优点是隔离性、定制性强,统一出入口,只能通过指定的API访问指定的数据;缺点与优点是对立的,也就是定制性太强,导致每次业务发生变更,需要访问不同数据的时候,需要双方更改API的入参或返参,降低了开发效率;而且无法使用表JOIN,这样在某些情况下也会导致查询数据效率变低。目前主流的方案都是建议使用API方案

一、触发器

2.利用DB的同步技术(如:SQL SERVER的订阅复制、MYSQL的主从复制脚本等)来实现不同DB的数据同步共享

  在SQL Server的早期版本中,如果要记录某个表或视图的Insert/Update/Delete操作,我们可以借助触发器(Trigger)(http://msdn.microsoft.com/zh-cn/library/ms189799.aspx),这在数据量较小的情况下往往是有效的方式之一,其中后触发器(After Trigger)只能跟踪表的三个操作中的任意组合,而前触发器(Instead Of trigger)可以处理表和视图的更新(即使普通的Update View语句在某些列不明确的情况下报错)。我们看两个例子:

这种方案优点是可以在同一个DB访问到另一个DB中所需表的数据,可以直接JOIN,把原来的跨DB访问变成了同一个DB的事情;缺点是依赖DB的同步技术,而且两台DB服务器的网络必需互通,没有完全的隔离,且往往同步过来的表不允许直接修改,或需修改仍然需要跨DB修改或使用方案1的API来进行修改。

  准备基础数据:

3.通过程序代码实现两个DB的数据同步(增、删、改、查),如:可以定时轮询源DB的A表,然后获取变更的记录(一般是:增、删、改的记录),再通过程序代码把源DB的A表的变更记录批量更新(若是新增、则是插入,若是修改,则是更新,若是删除,则是删除)到目的DB的A表中。

图片 1图片 2View Code

这种方案的优点是:可以根据实际情况灵活定制同步的表数据,不局限于某一张表或某一个DB,可以保证不同DB间同步表的数据一致性,让本来跨DB操作表变成了同一个DB的事情,而且可以增、删、改、查,功能不受限;缺点是灵活性太强,程序代码实现可靠的跨DB的实时同步逻辑的实现复杂度较高,对于开发人员的要求较高,如果写的同步逻辑无法保证实时、可靠、高可用,那对于业务来讲是灾难性的。

USE testDb2
GO
--创建两个测试表
IF NOT OBJECT_ID('DepartDemo') IS NULL
DROP TABLE [DepartDemo]
GO
IF NOT OBJECT_ID('DepartChangeLogs') IS NULL
DROP TABLE [DepartChangeLogs]
GO
--测试表
CREATE TABLE [dbo].[DepartDemo](
[DID] [int] IDENTITY(101,1) NOT NULL PRIMARY KEY,
[DName] [nvarchar](200) NULL,
[DCode] [nvarchar](500) NULL,
[Manager] [nvarchar](50) NULL,
[ParentID] [int] NOT NULL DEFAULT ((0)),
[AddUser] [nvarchar](50) NULL,
[AddTime] [datetime] NULL,
[ModUser] [nvarchar](50) NULL,
[ModTime] [datetime] NULL,
[CurState] [smallint] NOT NULL DEFAULT ((0)),
[Remark] [nvarchar](500) NULL,
[F1] [int] NOT NULL DEFAULT ((0)),
[F2] [nvarchar](300) NULL
)
GO
--记录日志表
CREATE TABLE [DepartChangeLogs]
([LogID] [bigint] IDENTITY(1001,1) NOT NULL PRIMARY KEY,
[DID] [int] NOT NULL,
[DName] [nvarchar](200) NULL,
[DCode] [nvarchar](500) NULL,
[Manager] [nvarchar](50) NULL,
[ParentID] [int] NOT NULL DEFAULT ((0)),
[AddUser] [nvarchar](50) NULL,
[AddTime] [datetime] NULL,
[ModUser] [nvarchar](50) NULL,
[ModTime] [datetime] NULL,
[CurState] [smallint] NOT NULL DEFAULT ((0)),
[Remark] [nvarchar](500) NULL,
[F1] [int] NOT NULL DEFAULT ((0)),
[F2] [nvarchar](300) NULL,
[LogTime] DateTime Default(Getdate()) Not Null,
[InsOrUpd] char not null
)
GO

上述三种方案,第1、2方案基本都是定制化的常规方案,我(梦在旅途,http://www.zuowenjun.cn)今天要分享的是第3种方案:跨DB增量(增、改)同步两张表的数据,注意是增量同步,其中删除这个我没有说明,原因是如果DB表中记录是物理删除(即:真实的DELETE),那就无法简单的通过程序代码获取到删除的记录,除非在DB中加入DELETE触发器记录删除记录的主键到临时表或开启更改追踪(CHANGE_TRACKING)或DB日志分析,故本文讲的是不给表、DB增加额外负担的情况实时增量同步,至于删的同步这个我认为最好是逻辑标记删除(过期最后清理【真实删除】),而不要物理删除。

  创建触发器:

关于程序代码实现跨DB同步表数据方案,之前已有总结过,详见:https://www.cnblogs.com/zuowj/p/6264711.html  ---》4.利用BCP(sqlbulkcopy)来实现两个不同数据库之间进行数据差异传输(即:数据同步)

/*******   创建一个After DML触发器  ******/
/*********  3w@live.cn  邀月***************/
CREATE TRIGGER dbo.tri_LogDepartDemo
ON [dbo].[DepartDemo]
AFTER INSERT, Delete  /************此处使用update与“Insert,Delete”效果是一样的,邀月注  **********/
AS
SET NOCOUNT ON   --屏蔽触发器发送“受影响的行数”给应用程序

-- Inserted rows
INSERT [DepartChangeLogs]
(DID,[DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],
 LogTime, InsOrUPD)
SELECT DISTINCT DID,[DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],
 GETDATE(), 'I'
FROM inserted i

-- Deleted rows
INSERT [DepartChangeLogs]
(DID,[DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],
 LogTime, InsOrUPD)
SELECT DISTINCT DID,[DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],
 GETDATE(), 'D'
FROM deleted d
GO

INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
VALUES (N'国家统计局房产审计一科', N'0', N'胡不归', 0, N'DeomUser',
 CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),
 1, N'专业评估全国房价,为老百姓谋福祉', 0, N'')
GO

----该Update不会被触发器记录,但Update会生效
UPDATE departDemo SET [Manager]='任我行' WHERE DID=101
GO

DELETE FROM departDemo where DID=101
GO

SELECT * FROM [DepartChangeLogs]

 之前的文章同步主要是基于TranFlag标记字段 或触发器来实现同步,这种方式必需对表数据的增、删、改逻辑都有要求与规范,也就是增、改必需更改TranFlag=0,删必需记录表删除临进表中,这样才能实现同步逻辑,而今天是在这个同步基础上(BCP),不给表、DB增加额外负担的情况实时增量同步,对数据源的插入、改动没有要求。

  统计效果:
图片 3
如果你觉得触发器过于浪费,你可以试着根据某些字段以缩小触发器的范围

代码如下:(以下同步适用于SQL SERVER 不同DB的表增量同步)

/********* 使用DML触发器记录特定列的修改 ***/
/*********  3w@live.cn  邀月***************/
CREATE TRIGGER dbo.[tri_LogDepartDemo2]
ON [dbo].[DepartDemo]
AFTER Update
AS
IF Update([Manager])
    Begin
        print '该部门主管实行终身任免制,不得中途更改!'
        Rollback ----回滚Update操作
    End

GO
UPDATE departDemo SET [Manager]='任我行' WHERE DID=101
GO
            try
            {
                SqlConnection obConnSrc = new SqlConnection(connLMSStr);
                SqlConnection obConnDest = new SqlConnection(mconnCCSStr);

                string lastTamp = ClsDatabase.gGetFieldValue(obConnSrc, "update TS_SyncUptime set UPTime=GETDATE() OUTPUT (deleted.LastUPstamp) as oldtamp FROM TS_CCSUptime WHERE TableName=N'tableNameA'", "oldtamp");


                string selectSql = @"SELECT id,aaa,bbb,ccc,ddd,eee,fff  
                                  FROM tableNameA WHERE 其它同步过滤查询条件 AND CONVERT(bigint,sys_tamp)>{0}";

                selectSql = string.Format(selectSql, lastTamp);

                master.TransferBulkCopy(selectSql, obConnSrc,
                                "tableNameA", obConnDest,
                                 (stable) =>
                                 {
                                     var colMaps = new Dictionary<string, string>();
                                     foreach (DataColumn col in stable.Columns)
                                     {
                                         colMaps.Add(col.ColumnName, col.ColumnName);
                                     }
                                     return colMaps;
                                 },
                                 (tempTableName, stable, destConn, srcConn) =>
                                 {
                                     StringBuilder saveSqlBuilder = new StringBuilder("begin tran" + Environment.NewLine);

                                     string IUSql = master.BuildInsertOrUpdateToDestTableSql("tableNameA", tempTableName, new[] { "id" }, stable.ExtendedProperties[master.MapDestColNames_String], 2);
                                     saveSqlBuilder.Append(IUSql);

                                     saveSqlBuilder.AppendLine("commit");

                                     ClsDatabase.gExecCommand(destConn, saveSqlBuilder.ToString());


                                     ClsDatabase.gExecCommand(srcConn, "update TS_SyncUptime set UPTime=GETDATE(),LastUPstamp=CONVERT(bigint,sys_tamp) FROM TS_SyncUptime WHERE TableName=N'tableNameA'");

                                     return false;
                                 });


            }
            catch (Exception ex)
            {
                writeLog(ex);//记错误日志
            }

  执行结果:

 上述同步代码逻辑很简单,可以参照之前的文章,这里主要是说明几个重要点:

图片 4
  但触发器的缺陷也是显而易见的,使用触发器请注意以下几点:

1.TS_SyncUptime表用于记录与管理同步任务的信息,主要包含如下几个字段:

1、触发器通常很隐蔽,换句话说,易忘记,特别在检查性能和逻辑问题时。

 图片 5

2、长时间运行的触发器会严重减慢数据操作,特别是在数据频繁修改的数据库中。

TableName:要同步的表名,UPTime每一次同步的触发时间点(可更改),sys_tamp行变更时间戳(不可更改),LastUPstamp行最后有效变量时间戳(可以更新)

3、不记录日志的更新不会引起DML触发器的触发(如WRITETEXT、Trunacte table及批量插入操作)。

2.具体关键同步逻辑如下:

4、约束通常比触发器运行更快。

2.1先更新TS_SyncUptime表,以便触发sys_tamp行变更时间戳发生改变(相当于记录同步触发时间点),在更改的同时取出LastUPstamp行最后有效变更时间戳(相当于上次同步的触发时间点)

5、处理某些逻辑时,存储过程通常比触发器要更易维护和管理。

2.2使用LastUPstamp作为过滤条件,查询>源DB的源表中时间戳字段,这样就可以查询出自上一次同步触发点到当前时间待同步的记录(增、改)

6、不允许在触发器中使用Select返回结果集。

2.3利作BCP执行同步(详见之前文章说明)

  关于触发器的更多内容,请看MSDN(http://msdn.microsoft.com/zh-cn/library/ms189799.aspx

2.4确保同步成功后,再次更新TS_SyncUptime表,并把sys_tamp行变更时间戳(当前触发时间点)更新到LastUPstamp行最后有效变量时间戳(记住本次触发时间点)

 

如上步骤即可实现可靠的同步,有人可能有疑问,这样就能实现可靠同步吗?我这里解释一下:

二、使用Output子句

3.1同步触发时记录当前触发时间点,并取得上一次的触发时间点(这里的上一次触发时间点是指上一次开始准备同步的记录时间点,确保从上一次查询到同步完成之间的时间点都包括其中,防止漏数据)

  官方解释:OutPut子句(http://technet.microsoft.com/zh-cn/library/ms177564.aspx返回受 INSERT、UPDATE、DELETE 或 MERGE 语句影响的各行中的信息,或返回基于受这些语句影响的各行的表达式。 这些结果可以返回到处理应用程序,以供在确认消息、存档以及其他类似的应用程序要求中使用。 也可以将这些结果插入表或表变量。 另外,您可以捕获嵌入的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果,然后将这些结果插入目标表或视图。

3.2如果同步的任一环节失败(只要最终没有同步成功),那么再次同步触发时均取到的是同 一个时间点(LastUPstamp),而且即使重复执行同步逻辑,也不会出现重复(因为存在则更新不存在则插入原则),保证幂等,这样就确保了同步的可靠性

 举例:

3.3当然如果某个时间点的数据或某个DB有问题,导致一直同不不成功,可能会出现一直同步不过去的情况,这种情况可以加上预警+人工干预,这个是概率的事情。

 /********* 使用Output记录表记录的修改 *****/
/*********  3w@live.cn  邀月***************/

----删除前面的触发器
Drop TRIGGER dbo.[tri_LogDepartDemo]
DROP TRIGGER dbo.[tri_LogDepartDemo2]

INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
OUTPUT Inserted.*,getdate(),'I' ---注意这行是新增的
INTO DepartChangeLogs           ---注意这行是新增的
VALUES (N'发改委', N'0', N'向问天', 0, N'DeomUser',
 CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),
 1, N'油价,我说了算', 0, N'')
GO

SELECT * FROM [DepartChangeLogs]

好了,如果大家有什么好的意见或建议欢迎下方留言评论,谢谢!

图片 6

  注意:

  1、从OUTPUT 中返回的列反映 INSERT、UPDATE 或 DELETE 语句完成之后但在触发器执行之前的数据。

  2、SQL Server 并不保证由使用 OUTPUT 子句的 DML 语句处理和返回行的顺序。

  3、与触发器相比,OutPut子句可以直接处理Merge语句。

   以上两种方法各有千秋,在合适的情况下采取合适的方法才是明智的选择,令人惊喜的是,SQL Server 2008起,为我们提供了更为强大的内建的方法-变更数据捕获(CDC,http://msdn.microsoft.com/zh-cn/library/bb500244%28v=sql.100%29.aspx)和更改跟踪,下面我们隆重介绍它们。

三、使用“变更数据捕获”(CDC)功能

  SQL Server 2008提供了内建的方法变更数据捕获(Change Data Capture 即CDC)以实现异步跟踪用户表的数据修改,而且这一功能拥有最小的性能开销。可以用于其他数据源的持续更新,例如将OLTP数据库中的数据变更迁移到数据仓库数据库。

  要使用CDC功能,首先我们得在数据库中启用该功能。在此我们沿用上例中使用的数据库Testdb2

/**************异步跟踪数据更新演示*************/
/************* 3w@live.cn 邀月***************/
use master
GO

IF EXISTS (SELECT [name] FROM sys.databases WHERE name = 'TestDb2')
drop DATABASE TestDb2
Go
CREATE DATABASE TestDb2
GO

--查看是否启用CDC
SELECT is_cdc_enabled FROM sys.databases WHERE name = 'TestDb2'

USE TestDb2
GO
----启用当前数据库的CDC功能
EXEC sys.sp_cdc_enable_db
GO

  如果报15517错误,请换用其他owner,邀月注

SELECT is_cdc_enabled FROM sys.databases WHERE name = 'TestDb2'

/*
is_cdc_enabled
1
*/
USE testDb2
GO

CREATE TABLE [dbo].[DepartDemo](
[DID] [int] IDENTITY(101,1) NOT NULL PRIMARY KEY,
[DName] [nvarchar](200) NULL,
[DCode] [nvarchar](500) NULL,
[Manager] [nvarchar](50) NULL,
[ParentID] [int] NOT NULL DEFAULT ((0)),
[AddUser] [nvarchar](50) NULL,
[AddTime] [datetime] NULL,
[ModUser] [nvarchar](50) NULL,
[ModTime] [datetime] NULL,
[CurState] [smallint] NOT NULL DEFAULT ((0)),
[Remark] [nvarchar](500) NULL,
[F1] [int] NOT NULL DEFAULT ((0)),
[F2] [nvarchar](300) NULL
)
GO

/**********************************
需要启用SQL Server Agent服务,否则会报错,邀月注
SQLServerAgent is not currently running so it cannot be notified of this action.
***********************************/

/****** 捕获所有的行变更,只返回行的净变更,其他默认 *******/
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'DepartDemo',
@role_name = NULL,
@capture_instance = NULL,
@supports_net_changes = 1,
@index_name = NULL,
@captured_column_list = NULL,
@filegroup_name = default

  注意此时,SQL Server 自启动了两个job,一个捕获,一个清除,注意清除是默认凌晨2点,清除72小时以上的数据。如果同一数据库的表中CDC已经启用,不会重建job。

/*
Job 'cdc.TestDb2_capture' started successfully.
Job 'cdc.TestDb2_cleanup' started successfully.
*/

--确认表已经被跟踪
SELECT is_tracked_by_cdc FROM sys.tables
WHERE name = 'DepartDemo' and schema_id = SCHEMA_ID('dbo')
/*
is_tracked_by_cdc
1
*/

--确认
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'DepartDemo'

图片 7

可以看到,SQL Server 增加了一个表[cdc].[dbo_DepartDemo_CT]
相比源表多了个字段:
[__$start_lsn]
,[__$end_lsn]
,[__$seqval]
,[__$operation]
,[__$update_mask]

图片 8

不建议直接查询该表,而应该使用下面的技巧

USE TestDb2
GO

INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
VALUES (N'银监会', N'0', N'云中鹤', 0, N'DemoUser1',
CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),
1, N'监管汇率', 0, N'')

INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
VALUES (N'统计局', N'0', N'神算子', 0, N'DemoUser2',
CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),
1, N'统计数据', 0, N'')
GO

UPDATE [dbo].[DepartDemo]
SET Manager='段正淳'
WHERE DID =101

DELETE [dbo].[DepartDemo]
WHERE DID = 102

  要查询变更,我们需要借助大名鼎鼎的日志序列号(Log Sequence Numbers)即LSN(http://msdn.microsoft.com/zh-cn/library/ms190411%28v=sql.100%29.aspx)来实现LSN级别的跟踪数据变更。 下面示例中sys.fn_cdc_map_time_to_lsn(http://msdn.microsoft.com/zh-cn/library/bb500137%28v=sql.100%29.aspx)用于LSN转换为时间。

/******* 使用LSN 查看CDC记录 *********/

--http://msdn.microsoft.com/zh-cn/library/bb500137%28v=sql.100%29.aspx
SELECT sys.fn_cdc_map_time_to_lsn
( 'smallest greater than or equal' , '2012-04-09 16:09:30') as BeginLSN

/*
BeginLSN
0x0000002C000000AA0003
*/

SELECT sys.fn_cdc_map_time_to_lsn
( 'largest less than or equal' , '2012-04-09 23:59:59') as EndLSN

/*
EndLSN
0x0000002C000001C20005
*/

/**************查看所有CDC记录*************/
/************* 3w@live.cn 邀月***************/

DECLARE @FromLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'smallest greater than or equal' , '2012-04-09 16:09:30')

DECLARE @ToLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'largest less than or equal' , '2012-04-09 23:59:59')

SELECT
__$operation,
__$update_mask,
DID,
DName,
Manager
FROM [cdc].[fn_cdc_get_all_changes_dbo_DepartDemo]
(@FromLSN, @ToLSN, 'all')

/************查看所有更新*************************

__$operation __$update_mask DID DName Manager
2 0x1FFF 105 银监会 云中鹤
2 0x1FFF 106 统计局 神算子
1 0x1FFF 101 银监会 段正淳
1 0x1FFF 103 银监会 云中鹤
1 0x1FFF 104 统计局 神算子
1 0x1FFF 105 银监会 云中鹤
1 0x1FFF 106 统计局 神算子
2 0x1FFF 107 银监会 云中鹤
2 0x1FFF 108 统计局 神算子
4 0x0008 107 银监会 段正淳
1 0x1FFF 108 统计局 神算子
*/

/**************查看所有CDC记录*************/
/************* 3w@live.cn 邀月***************/
DECLARE @FromLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'smallest greater than or equal' , '2012-04-09 16:09:30')

DECLARE @ToLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'largest less than or equal' , '2012-04-09 23:59:59')

--解释一下Operation的具体含义
SELECT
CASE __$operation
WHEN 1 THEN 'DELETE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'Before UPDATE'
WHEN 4 THEN 'After UPDATE'
END Operation,
__$update_mask,
DID,
DName,
Manager
FROM [cdc].[fn_cdc_get_all_changes_dbo_DepartDemo]
(@FromLSN, @ToLSN, 'all update old')

图片 9

/**************查看净更改(Net changes)CDC记录*************/
/************* 3w@live.cn 邀月 ***************/

INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
VALUES (N'药监局', N'0', N'蝶谷医仙', 0, N'DemoUser3',
CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),
1, N'制定药价', 0, N'')
GO

UPDATE [dbo].[DepartDemo]
SET Manager='胡青牛'
WHERE DID =109

DECLARE @FromLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'smallest greater than or equal' , '2012-04-09 16:09:30')

DECLARE @ToLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'largest less than or equal' , '2012-04-09 23:59:59')

SELECT
CASE __$operation
WHEN 1 THEN 'DELETE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'Before UPDATE'
WHEN 4 THEN 'After UPDATE'
WHEN 5 THEN 'MERGE'
END Operation,
__$update_mask,
DID,
DName,
Manager
FROM [cdc].[fn_cdc_get_net_changes_dbo_DepartDemo]
(@FromLSN, @ToLSN, 'all with mask')

图片 10

  我们还可以通过转换CDC更新掩码获得更为直观的结果,这里需要借助于另外两个函数sys.fn_cdc_is_bit_set(http://msdn.microsoft.com/zh-cn/library/bb500241%28v=SQL.110%29.aspx)和sys.fn_cdc_get_column_ordinal(http://msdn.microsoft.com/zh-cn/library/bb522549%28v=SQL.100%29.aspx

 

/************** 转换CDC更新掩码 *************/
/************* 3w@live.cn 邀月 **************/

UPDATE dbo.[DepartDemo]
SET [Manager] = '东方不败'
WHERE DID =107

UPDATE dbo.[DepartDemo]
SET ParentID = 109
WHERE DID =107

DECLARE @FromLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'smallest greater than or equal' , '2012-04-09 16:09:30')

DECLARE @ToLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'largest less than or equal' , '2012-04-09 23:59:59')

SELECT
sys.fn_cdc_is_bit_set (
sys.fn_cdc_get_column_ordinal (
'dbo_DepartDemo' , 'Manager' ),
__$update_mask) Manager_Updated,
sys.fn_cdc_is_bit_set (
sys.fn_cdc_get_column_ordinal (
'dbo_DepartDemo' , 'ParentID' ),
__$update_mask) ParentID_Updated,
DID,
Manager,
ParentID
FROM cdc.fn_cdc_get_all_changes_dbo_DepartDemo
(@FromLSN, @ToLSN, 'all')
WHERE __$operation = 4

本文由俄罗斯贵宾会发布于编程,转载请注明出处:SQL Server 2008中新增的变更数据捕获(CDC)和更改跟踪

您可能还会对下面的文章感兴趣: