SQL Server 增量同步方案探索

这是我早期写的一篇文章，搬运过来归档。现在增量同步已经有了发布订阅、alwayson、cdc等多种选择。

1. 背景

SQL Server增量同步是数据中心建设的一个关键点。可用的方案方剑总在[大话数据库增量备份]一文中有过系统的总结。本次讨论过程中我提出了一个新的方案，即基于SQL Server LDF 日志的增量同步。端午节期间针对这个方案及其他几个可尝试的方案进行了探索。

2.基于SQL Server LDF日志抓取增量内容并同步的方案探索

2.1 核心思路

使用fn_dblog函数获取事务日志，解析日志，抓取在特定表上的INSERT,UPDATE,DELETE操作，将事务在同步库中重现

2.2 步骤

# fn_dblog是一个未被官方文档记载的内置函数，官方不建议常规用户使用，他的使用方法没有系统介绍，必须依赖官方博客的只言片语，以及前人的探索。
SELECT * FROM fn_dblog(NULL, NULL) # 参数是时间范围
# 观看返回值，发现很多无关的日志内容，然后发现有AllocUnitId字段，查阅有关资料得知这是数据库对象的实例ID，这个实例ID不是一成不变的，会伴随着一些重大的schema变化而变化，可以使用下面的SQL动态获取。
SELECT allocunits.allocation_unit_id, objects.name, objects.id
FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3) 
 AND partitions.hobt_id = allocunits.container_id)
 OR (allocunits.type = 2 and partitions.partition_id = allocunits.container_id)
INNER JOIN sysobjects objects ON partitions.object_id = objects.id
 AND objects.type IN ('U', 'u')
WHERE partitions.index_id IN (0, 1)
# 观看 fn_dblog的返回结果，还能看到operate字段，这个字段明显是操作类型的含义。我们使用这两个限制条件抓取特定的日志。下面的语句抓取了我的测试表aaa的insert事务日志
SELECT * FROM fn_dblog(NULL, NULL)
WHERE AllocUnitId = 72057594039828480
AND Operation = 'LOP_INSERT_ROWS'
# 我们在数据同步的时候，其实只关注内容的变化。即'LCX_HEAP', 'LCX_CLUSTERED'动作。前者是针对堆的内容写入，后者是针对集群的内容写入。基于此，再次改进语句。
SELECT
 [Current LSN], 
 Operation,
 dblog.[Transaction ID],
 AllocUnitId,
 AllocUnitName,
 [Page ID],
 [Slot ID],
 [Num Elements],
 dblog1.[Begin Time],
 dblog1.[Transaction Name],
 [RowLog Contents 0],
 [Log Record]
FROM ::fn_dblog(NULL, NULL) dblog
 INNER JOIN 
 ( 
 SELECT allocunits.allocation_unit_id, objects.name, objects.id
 FROM sys.allocation_units allocunits 
 INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3) 
 AND partitions.hobt_id = allocunits.container_id) 
 OR (allocunits.type = 2 and partitions.partition_id = allocunits.container_id) 
 INNER JOIN sysobjects objects ON partitions.object_id = objects.id
 AND objects.type IN ('U', 'u')
 WHERE partitions.index_id IN (0, 1)
 ) allocunits ON dblog.AllocUnitID = allocunits.allocation_unit_id 
 INNER JOIN 
 (
 SELECT [Begin Time],[Transaction Name],[Transaction ID]
 FROM fn_dblog(NULL, NULL) x
 WHERE Operation = 'LOP_BEGIN_XACT' 
 ) dblog1 ON dblog1.[Transaction ID] = dblog.[Transaction ID]
WHERE [Page ID] IS NOT NULL AND [Slot ID] >= 0
 AND dblog.[Transaction ID] != '0000:00000000' 
 AND Context in ('LCX_HEAP', 'LCX_CLUSTERED')

到这一步，我们已经能够精准的获得指定表的事务日志。那内容在哪呢？经过探索，发现对于insert操作，内容在[RowLog Contents 0]字段中，对于update操作，老值在[RowLog Contents 0]，新值在[RowLog Contents 1]字段中。但是这个字段中存储的Ox开头的内容，即16进制。需要考虑如何解析。我对测试表进行了一次insert操作，得到该insert操作的RowLog Contets 0字段值如下：

0x3000140001000000779FFE0082A700002A000000050000020028003700446F6E27742050616E6963536861726520616E6420456E6A6F79

通过查阅外网的资料，得知RowLog Contents 0 字段，是由定长和可变长度的九段组成的。含义分别是：

状态位（2个字节）+定长部分长度（2个字节）+定长部分内容（由长度决定）+列数（2个字节）+空字节填充（列数长度/8）+可变部分长度（2个字节）+列偏移量（列数*2字节）+可变部分内容（由长度决定）

通过解析得到value的内容(十六进制）：446F6E27742050616E6963536861726520616E6420456E6A6F79

如何将该value解析为有意义的字符串？由于SQL Server是非开源的，无法查阅源码。搜索良久也没有找到可参考的资料。事情只能到此搁置。

外网有很多关于SQL Server日志解析工具，并且实现了value值的识别，我们能否直接采购这些软件来解决呢？我试用了比较出名的几款，如Apex SQL，Toad等，试用下来发现，这些工具的出发点都是运维，必须通过前台使用，场景主要是基于日志的选择性redo，undo或者recovery。并没有提供api，无法用在我们的场景。

2.3 结论

此路不通

3.基于大数据技术的方案探索

3.1 探索过程

大数据的解决方案中，关于数据的获取有实时和离线两种模式。

实时必须基于实时的源，如消息或者增量日志，我们的场景正是缺乏实时的源，所以此条路不通。

离线可以尝试。离线的常用技术是datax和sqoop，我可以尝试将离线的数据获取频率设置的极短，比如一分钟一次，实现近似的实时。二者都是开源的。

通过分析datax和sqoop的实现原理，发现此方案也不符合我们的场景。以datax为例，他实现增量同步的原理是通过传递动态参数给where条件进行过滤或者获取全量数据，通过强大的集群对比获取增量，显然无法解决我们的问题，因为我们即无法给定字段用以增量过滤，又无法每次都获取全量数据，这样会导致服务器的性能严重下降。

3.2 结论

此路不通

4.基于发布订阅的改进方案探索

4.1 核心思路

使用发布订阅的方式其实是最符合我们的场景的：性能开销最小，同步速度最快。但它的缺点是

不支持dml操作（数据结构更改同步），发布中的数据库表不能要更改表结构，要改要停止发布，之后要重建发布订阅。http://m.lanhusoft.com/Article/514.html

也就是会带来较大的维护工作量，基于此，我设计了一个自动化运维的方案，见下图。

4.2 步骤

基于TSQL实现自动创建发布和订阅。参阅 http://blog.csdn.net/kk185800961/article/details/44719685

下面是我在测试服务器上的脚本,实现WIN-SERVER-1服务器上test库的aaa表自动发布，WIN-SERVER-2服务器上订阅这个发布。

-----------------发布: 要在发布服务器“WIN-SERVER-1”上运行的脚本-----------------
use master
exec sp_adddistributor @distributor = N'WIN-SERVER-1', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO

use [distribution] 
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) 
	create table UIProperties(id int) 
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) 
	EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties' 
else 
	EXEC sp_addextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @publisher = N'WIN-SERVER-1', @distribution_db = N'distribution', @security_mode = 0, @login = N'sa', @password = N'', @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

use [test]
exec sp_replicationdboption @dbname = N'test', @optname = N'publish', @value = N'true'
GO
-- 添加事务发布
use [test]
exec sp_addpublication @publication = N'TEST_AAA', @description = N'来自发布服务器“WIN-SERVER-1”的数据库“test”的事务发布。', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'TEST_AAA', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N''
use [test]
exec sp_addarticle @publication = N'TEST_AAA', @article = N'aaa', @source_owner = N'dbo', @source_object = N'aaa', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'aaa', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboaaa', @del_cmd = N'CALL sp_MSdel_dboaaa', @upd_cmd = N'SCALL sp_MSupd_dboaaa'
GO
-----------------订阅: 要在发布服务器“WIN-SERVER-1”上运行的脚本-----------------
use [test]
exec sp_addsubscription @publication = N'TEST_AAA', @subscriber = N'WIN-SERVER-2', @destination_db = N'test', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'
GO
-----------------订阅: 要在订阅服务器“WIN-SERVER-2”上运行的脚本-----------------
use [test]
exec sp_addpullsubscription @publisher = N'WIN-SERVER-1', @publication = N'TEST_AAA', @publisher_db = N'test', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 1

exec sp_addpullsubscription_agent @publisher = N'WIN-SERVER-1', @publisher_db = N'test', @publication = N'TEST_AAA', @distributor = N'WIN-SERVER-1', @distributor_security_mode = 0, @distributor_login = N'sa', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20170530, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO
-----------------结束: 要在订阅服务器“WIN-SERVER-2”上运行的脚本-----------------

通过SQL Server的库级触发器监控表结构的DDL操作，参阅http://blog.csdn.net/sqlserverdiscovery/article/details/14447321 。因为比较简单，测试代码不再附上。

4.3 结论

此路可行。

5.意外收获

再探索过程中，发现基于我的测试环境（SQL Server 2008 R2，事务发布的推送模式），订阅发布是能够同步目标表的DDL操作，即目标表发生结构变化后，同步能正常运行。我测试了增加字段、修改字段、删除字段等操作，皆可以正常同步。根据事务发布的原理，我大胆的猜测，只有在删除主键时才会导致问题，于是我尝试了删除主键

alter table aaa drop constraint PK_Table_1

得到了显式的报错信息

消息 4929，级别 16，状态 1，第 1 行
无法更改 表'aaa'，因为正在为复制而发布它。
消息 3727，级别 16，状态 0，第 1 行
未能删除约束。请参阅前面的错误信息。

可见，复制的稳定性有着一定的保证。所以在SQL Server 2008 R2 版本，基于事务的发布订阅模式，具有可行性。