微信登录建设银行网站,建立个网站需要多少钱,推广软件公司,企业展示网站如何建在大型的数据库应用中#xff0c;经常会遇到部分数据的脱机和多个数据库的合并问题。比如现在有一个全省范围使用的应用程序#xff0c;每个市都部署了单独的相同的应用程序服务器和数据库服务器#xff0c;每个月需要将全省所有市的数据全部汇总起来用于出全省的报表#…在大型的数据库应用中经常会遇到部分数据的脱机和多个数据库的合并问题。比如现在有一个全省范围使用的应用程序每个市都部署了单独的相同的应用程序服务器和数据库服务器每个月需要将全省所有市的数据全部汇总起来用于出全省的报表这是一种很常见的数据库合并问题。再比如我们做了一个SmartClient的应用程序每个客户端都有应用程序和数据库另外还有一个中心数据库用于汇总所有客户端的数据。每个智能客户端上都可以对自己的数据库进行增删改查一旦智能客户端连接到网络上时系统就将客户端数据库中的数据更改全部应用到中心数据库中这种偶尔连接的应用程序也是需要数据库的同步的。对于前面说到的这些应用最简单的同步方法就是删除原有数据然后重新填充新的数据对于小数据量的表来说这并没有什么问题但是如果每个市都有几百万几千万条数据那么要将省数据库中的数据删除了再把每个市中的数据全部填充到省数据库中显然是不可行的。这种情况下应该使用跟踪数据更改的方法将每个市这个月的数据更改应用到省数据库中感觉有点像是差异备份一样只记录更改的。在SQL Server 2008中提供了两种跟踪数据更改的方案变更数据捕获Change Data Capture更改跟踪Chang Tracking 今天我主要说的是更改跟踪变更数据捕获在以后进行讲解。启用更改跟踪更改跟踪是SQL Server 2008的一个新特性默认情况下是没启用的。更改跟踪可以应用跟踪到具体一个数据库中的具体表甚至是具体的列。更改跟踪并不会创建触发器之类的对象只是在用户对启用了更改跟踪的表进行了增加、修改和删除操作时系统自动将该操作生成一个版本号记录下操作的时间戳、操作的类型、受影响的数据的主键等信息。启用更改跟踪后对数据操作的性能影响不是很大。这些信息是记录到SQL Server系统表中的系统自动负责清理和维护。要使用更改跟踪需要启用数据库的更改跟踪功能和表的更改跟踪功能。在SSMS中数据库的属性窗口中可以启用数据库的更改跟踪这里将更改跟踪选项设置为true既可启用更改跟踪。另外3个选项就是跟踪的数据自动清理的开关和清理的时间这个自动清理的时间必须大于我们要同步数据的周期比如我们的数据是一个月同步一次那么这个保持期就应该大于31天如果设置保持期太短那么我们的跟踪数据还没来得及同步就被自动清理了。这里只是启用了数据库的更改跟踪接下来是要启用表的更改跟踪。这里我们创建一个新的表t1并初始化几条数据CREATETABLEt1( c1 INTIDENTITYPRIMARYKEY, c2 VARCHAR(50)NOTNULL, c3 DATETIMENOTNULL, c4 VARCHAR(max))GOINSERTINTO t1 VALUES(test1,2009-1-1,www.cnblogs.com/studyzy)INSERTINTO t1 VALUES(test2,2009-1-1,www.cnblogs.com/studyzy)INSERTINTO t1 VALUES(test3,2009-1-2,www.cnblogs.com/studyzy)复制接下来在SSMS中查看表t1的属性窗口可以在属性窗口中启用该表的更改跟踪功能其中第二个选项“跟踪已更新的列”是表示是否将更改跟踪细化到列上。对于一般的表来说我们只需要知道具体哪些行进行了更改然后在合并数据时将整行数据更新到中心数据库既可但是如果表中有大对象列text image varchar(max) varbinary(max) xml等数据类型的列时将整行进行更新可能非常慢所以我们可以启用“跟踪已更新的列”将具体更新了哪些列记录下来这样在合并数据时就直接更新这些列既可。更改跟踪常用函数在更改跟踪中最重要的一点就是版本号版本号从0开始一直递增对表的每一次更改操作都会产生一个新的版本号。使用SELECTCHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(dbo.t1))复制可以获得t1表最小版本号由于是刚创建更改跟踪所以这里返回的是0如果我们进行了大量的操作以后而且这些操作的时间已经超过了数据库更改跟踪中设置的保持期时间那么过期的版本就会被系统自动清理清理后最小版本就不是0了而是保留的可用的最早版本。SELECT CHANGE_TRACKING_CURRENT_VERSION()可以获得当前数据库的更改跟踪的最新版本。这里由于我们启用更改跟踪后还没有进行数据库操作所以返回的也是0。现在我们向表t1中插入一条数据然后查看当前最新版本INSERTINTO t1 VALUES(test,2009-1-4,www.cnblogs.com/studyzy)SELECTCHANGE_TRACKING_CURRENT_VERSION()--返回1复制现在返回的版本号就是1了。接下来我们再修改2条数据和删除1条数据再查看版本号UPDATE t1 SET c3GETDATE()WHERE c13--受影响2条数据
DELETEFROM t1 WHERE c2test3--受影响1条数据
SELECTCHANGE_TRACKING_CURRENT_VERSION()--返回3复制这里我们总共影响了4条数据但是版本号为3说明版本号并不是以受影响的行实来定的一次更新操作中不管影响了好多条数据当然这里不能为0条版本号只增加1。现在版本号有了接下来就是查询出这段时间t1的更改情况需要使用表值函数CHANGETABLE(CHANGES [要查询更改跟踪的表名], 从哪个版本开始的更改)。这里要查询t1表从0版本开始到现在的所有数据更改那么对应的查询语句是SELECT*FROMCHANGETABLE(CHANGES dbo.t1,0)as ct复制系统返回结果SYS_CHANGE_VERSIONSYS_CHANGE_CREATION_VERSIONSYS_CHANGE_OPERATIONSYS_CHANGE_COLUMNSSYS_CHANGE_CONTEXTc12NULLU0x0000000003000000NULL12NULLU0x0000000003000000NULL23NULLDNULLNULL311INULLNULL4这里每个列的数据类型、含义等在联机丛书里面解释的很清楚我这里只简单介绍下返回的这个表在版本号为1的数据更改操作中是插入了一条数据插入数据的主键c14在版本号2的操作中更新了2条数据分别是c11和c12的行在版本3的操作中删除了c13的一条数据。根据更改跟踪同步数据现在所有的更改已经查询出来了接下来就可以根据查询出来的这个结果同步数据了。为了演示方便我这里将在同一个实例中建立TestDB1数据库并初始化t1表用于表示中心数据库。那么同步数据的操作应该是--首先将新增的数据插入到中心数据库中
SETIDENTITY_INSERT TestDB1.dbo.t1 ONINSERTINTO TestDB1.dbo.t1(c1,c2,c3,c4)SELECT t1.*FROMCHANGETABLE(CHANGES dbo.t1,0)AS ct
INNERJOIN t1
ON ct.c1t1.c1
WHERE ct.SYS_CHANGE_OPERATIONI--接下来将更改的数据应用到中心数据库中
UPDATE TestDB1.dbo.t1
SET c2newt1.c2,c3newt1.c3,c4newt1.c4
FROMCHANGETABLE(CHANGES dbo.t1,0)AS ct
INNERJOIN dbo.t1 AS newt1
ON ct.c1newt1.c1
WHERE ct.SYS_CHANGE_OPERATIONUAND t1.c1newt1.c1 --将删除的数据从中心数据库删除
DELETEFROM TestDB1.dbo.t1
WHERE c1 IN(SELECT c1 FROMCHANGETABLE(CHANGES dbo.t1,0)AS ct WHERE ct.SYS_CHANGE_OPERATIOND)复制这样我们就使用更改跟踪实现了数据库的同步。该同步操作时的版本号是3这个版本号必须要单独记下来那么下次再进行同步是就从3开始查询。通过更改跟踪更新列前面的同步脚本中关于数据update操作是UPDATE TestDB1.dbo.t1
SET c2newt1.c2,c3newt1.c3,c4newt1.c4复制由于c4是大对象数据类型如果里面存放了几十兆或者更大的数据而实际上我们更新的并不是c4列那么这种更新方式必然很浪费时间和资源。前面我们对t1表已经启用了“跟踪已更新的列”那么就可以根据实际更新的列来更新数据。使用CHANGE_TRACKING_IS_COLUMN_IN_MASK()函数可以判断一个列是否发生了更改如果发生了更改则返回1没有更改则返回0。比如查询c2是否发生更改SELECT*,CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(dbo.t1),c2,ColumnId),SYS_CHANGE_COLUMNS)FROMCHANGETABLE(CHANGES dbo.t1,0)AS ct
WHERE ct.SYS_CHANGE_OPERATIONU复制这里返回0说明没有更改c2列同样的方法可以判断出c3列发生了更改。既然可以判断哪些列发生了更改那么就可以根据发生更改的列来更新该列的数据比如对于c2的更新语句就是UPDATE TestDB1.dbo.t1
SET c2newt1.c2 --更新c2列
FROMCHANGETABLE(CHANGES dbo.t1,0)AS ct
INNERJOIN dbo.t1 AS newt1
ON ct.c1newt1.c1
WHERE ct.SYS_CHANGE_OPERATIONUAND t1.c1newt1.c1
ANDCHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(dbo.t1),c2,ColumnId), ct.SYS_CHANGE_COLUMNS)1--发生更改时才更新复制同样的方法可以写出c3列、c4列的更新语句。如果觉得这样重复的写很麻烦那么可以写一个存储过程传入列名检查该列是否更改如果更改了则更新。总结更改跟踪是在偶尔连接的数据库应用和同步数据时非常有用的一个特性。更改跟踪里面的核心就是版本号每次在同步数据时记录下当前的版本号下次再同步时CHANGETABLE函数就传入上次同步的版本号这样可以避免重复同步。更改跟踪的跟踪记录数据是保存到系统表中的由系统来维护在开启数据库的更改跟踪时可以设置自动清除的时间从而保证系统不会因为记录太多的跟踪数据而导致数据库文件大小急剧膨胀。更改跟踪启用后对一般的DML操作(增删改)是不会有影响的所有的DML SQL语句照常使用而且启用更改跟踪后并不会对系统性能造成明细影响。
