SQL Server 增量同步方案探索

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

1. 背景

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

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

2.1 核心思路

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

2.2 步骤
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
# 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

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

img

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

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    -----------------发布: 要在发布服务器“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操作,即目标表发生结构变化后,同步能正常运行。我测试了增加字段、修改字段、删除字段等操作,皆可以正常同步。根据事务发布的原理,我大胆的猜测,只有在删除主键时才会导致问题,于是我尝试了删除主键

1
alter table aaa drop constraint PK_Table_1

得到了显式的报错信息

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

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