今天就说说SQL Server 增量数据同步。当公司的业务数据量越来越多的时候,数据分析部门总想用来做报表,或者提炼出有用的运营数据。通常,相关负责人希望将各个业务系统、各种不同结构的数据同步到数据仓库、数据湖等。
若是其他类型数据库,多部署在 Linux 环境中,同步数据有较多及成熟的方案。
SQL Server 有哪些增量同步方案呢?
方案一:更改跟踪(Chang Tracking)
更改跟踪是表级别的跟踪,记录的只是行已更改的事实,而不是行更改的次数或任何中间更改的值。因为只存储的行标识符,这就要求表必须有主键。当对表进行DML操作时,系统表都会将主键记录下来,生成自己的行版本。
那具体的行数据在哪呢?当然还是在原来的表了,只要将跟踪的主键与原表关联,可以找到新增和最后一次更改的行记录。对于删除的记录,原表没有了,我们知道主键就可以同步到目标表进行删除。通过记录版本,每次可同步最近的操作。
CT原理图
不过,更改跟踪在实际工作中,很少人会使用。我想有几个原因:
更改的中间值没有记录,对数据有严格的事务属性不适用。
DML频繁,也影响到跟踪表记录的DML频繁,也会影响一些性能。
了解的人很少,毕竟几乎所有的第三方同步工具,都是基于变更数据捕获(CDC),而不是更改跟踪。
更改跟踪不算增量同步一种,相当于同步当前一次快照记录。
方案二:变更数据捕获(Change Data Capture,CDC)
CDC 应该是多少大数据开发人员了解的,CDC 也是表级别的数据同步。CDC 同样有一张表记录跟踪,当对源表进行 DML 时,在事务日志会记录相关操作信息。变更数据捕获代理使用异步进程读取事务日志,将相关操作结果应用到副本表(捕获实例表)中,这样就完成了对源表操作的记录跟踪。即使数据库恢复模式处于简单模式,也同样适用。CDC 原理如下图。
CDC原理
对于增量跟踪,应用程序在每次操作时,记录相应的时间或者事务日志序列号(LSN),下次读取大于该 LSN 即可。既然是读取数据库的事务日志,CDC 进程逻辑嵌入在存储过程 sp_replcmds中(事务复制也使用相同的进程)。
CDC 是比较常用的,但是当对表新增字段的时候,新字段是不会跟踪的。一般的解决方法是:新增字段后,对原表再启用一个CDC跟踪实例表,待原来的跟踪记录同步完成后,删除原跟踪实例表。字段新增方案其实还有一种,我这有一个脚本(如下链接)。当新增字段后,执行链接中脚本,将生产新的一个临时CDC跟踪实例,根据该实例生成的存储过程信息,替换原来的存储过程,然后禁用该新实例。这样增加字段后就不必等数据同步完成,继续使用表第一次启用CDC的系统对象。
/hzc/SQLServer/blob/master/Scripts/Sync_CDC_Table_Columns_To_CT
方案三:UpdatedTime 字段
该方案也是非常普遍的同步方案。表中新增一个时间字段,默认值为当前时间 GETDATE()。然而该字段不像 MySQL 可以自动更新。只能添加一个更新触发器,当行数据被更改时,UpdatedTime 更新为当前时间。如下示例。
CREATE TABLE [dbo].[TestTab](IDINTIDENTITY(1,1)NOTNULL,NameVARCHAR(50)NOTNULL,UpdatedTime DATETIME NOT NULL DEFAULT(GETDATE()))GOCREATE TRIGGER tr_TestTab_update ON dbo.TestTab AFTER UPDATE ASBEGINUPDATE t SET UpdatedTime=GETDATE() FROM dbo.TestTab t INNER JOIN inserted i ON t.id=i.idENDGO
数据的同步则按照时间进行增量查询,捕获相应的变更数据。对于触发器,相信大家都比较抵制,触发器每次都得进行一次表关联更新,增加了系统资源开销。若每次更新表数据都更新一个时间字段也不合适。那如何是好啊!下面,我们使用另一种方案!
方案四:行版本 rowversion / timestamp
该方案在大多数同学中还不被熟悉,估计只有较专业的 SQL Server DBA 有可能了解。说起 timestamp 可能很多同学都听过,在 SQL Server 中,timestamp 只是 rowversion 的一个同义词(可以说是别名)。timestamp 是 SQL Server 早期版本使用的名称, timestamp已过时,在以后的 SQL Server 版本中将被删除。接下来,我们就使用 rowversion 吧。
rowversion 是数据库中自动生成的唯一二进制数字的数据类型。rowversion 通常用作给表行加版本戳的机制。存储大小为 8 个字节。rowversion 数据类型只是递增的数字,不保留日期或时间。不可为空的 rowversion 列在语义上等同于 binary(8) 列。可为空的 rowversion 列在语义上等同于varbinary(8) 列。
我们做一个示例,插入5行数据。
CREATE TABLE TestTab (id INT NOT NULL,rv ROWVERSION );GOINSERT INTO TestTab(id) VALUES(1)GO 5SELECT * FROM TestTabGOid rv1 0x00000000000007D51 0x00000000000007D61 0x00000000000007D71 0x00000000000007D81 0x00000000000007D9SELECT @@DBTS AS 当前RV,MIN_ACTIVE_ROWVERSION() AS 下一个可用RV0x00000000000007D9 0x00000000000007DA
使用系统变量和函数@@DBTS 和 MIN_ACTIVE_ROWVERSION() 查看数据库的行版本号。也可以创建多个表进行验证,行版本是数据库范围级别的,不过增量同步数据时,还是按表将行版本存储起来,待下一次增量读取表记录。(是不是类似方案三?)
总结:
SQL Serve 中的增量数据同步方案,相对其他数据库来说是有点损耗性能的,毕竟闭源,只能连接数据库进行数据查询操作。不过,也有较多的第三方工具做的不错,也是基于上面的几种方案进行数据增量抽取。如 Oracle GoldenGate、Debezium 是基于CDC的,而 alibaba/DataX 对于SQL Server 是基于查询抽取数据的。根据不同的业务场景或架构情况,选择符合自己的方案才是最好的。
如果觉得《SQL Server 增量数据同步》对你有帮助,请点赞、收藏,并留下你的观点哦!