不同实例下同构表数据同步验证

背景

我们有一个测试需求，需把Azure sql database 下所有表数据同步到Sql server 下同构的表并保持一定时间间隔去自动同步数据，本想直接使用Azure portal 中复制数据库的功能，可惜测试发现该功能限制了scheme下超500个表，开Case的答复暂时产品无法改变这个现状只能待后续完善。为了能实现这个持续进行数据同步需求，我们选用tablediff 来生成差异脚本，使用sqlcmd来执行差异脚本,上述两个工具都是sql server 自带的工具，先验证是否可行。后续可以使用SSIS把上述两个步骤组合在一起最后通过JOB定期执行即可。

测试环境

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)

准备测试对象

--为了方便验证，我直接在同实例下新建了两个测试库
--准备同步的库以及表 
CREATE DATABASE test_db1;  --源库
GO
USE test_db1;
CREATE TABLE test_table   --需同步的源表
(
    id INT IDENTITY(1, 1),
    name NVARCHAR(20),
    location NVARCHAR(10)
);
GO


CREATE DATABASE test_db2;   --目标库
GO
USE test_db2;
CREATE TABLE test_table    --需同步的目标表
(
    id INT IDENTITY(1, 1),
    name NVARCHAR(20),
    location NVARCHAR(10)
);
GO

 
默认是在Sql Server中使用xp_cmdshell来调用tablediff ,当然也可以使用Windows Prompt 或者Powershell都可以调用；在SSMS开启xp_cmdshell

--开启xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

测试场景情况

USE test_db1;  --源库
INSERT INTO dbo.test_table   --源表
(   name,
    location
)
VALUES
(   N'1', -- name - nvarchar(20)
    N'2'  -- location - nvarchar(10)
);

执行如下语句

EXEC master..xp_cmdshell '"C:Program FilesMicrosoft SQL Server140COM	ablediff.exe" -sourceserver [DESKTOP-0LI9ARF] -sourcedatabase test_db1 -sourcetable test_table -destinationserver [DESKTOP-0LI9ARF] -destinationdatabase test_db2 -destinationtable test_table -f D:4_9Diff';

打开内容如下：

看来一个表的数据是没有问题，我们试试在目标表提前插入2条记录看比对结果如何，注意需提前删除已生成的Diff.sql文件，否怎会提示该文件已存在；

USE test_db2; --目标库
INSERT INTO dbo.test_table --目标表
(   name,
    location
)
VALUES
(   N'23', -- name - nvarchar(20)
    N'232' -- location - nvarchar(10)
);

INSERT INTO dbo.test_table --目标表
(   name,
    location
)
VALUES
(   N'232', -- name - nvarchar(20)
    N'2233' -- location - nvarchar(10)
);

EXEC master..xp_cmdshell '"C:Program FilesMicrosoft SQL Server140COM	ablediff.exe" -sourceserver [DESKTOP-0LI9ARF] -sourcedatabase test_db1 -sourcetable test_table -destinationserver [DESKTOP-0LI9ARF] -destinationdatabase test_db2 -destinationtable test_table -f D:4_9Diff';

测试一下无主键无自增字段的场景

USE test_db1;      
CREATE TABLE test_table2 --需同步的源表2
(
    name NVARCHAR(20),
    location NVARCHAR(10)
);
GO


USE test_db2;
CREATE TABLE test_table2 --需同步的目标表2
(
    name NVARCHAR(20),
    location NVARCHAR(10)
);
GO

先测试源表有记录，目标表无记录场景

USE test_db1; --目标库
INSERT INTO dbo.test_table2 --目标表
(   name,
    location
)
VALUES
(   N'23', -- name - nvarchar(20)
    N'232' -- location - nvarchar(10)
);

INSERT INTO dbo.test_table2 --目标表
(   name,
    location
)
VALUES
(   N'232', -- name - nvarchar(20)
    N'2233' -- location - nvarchar(10)
);

EXEC master..xp_cmdshell '"C:Program FilesMicrosoft SQL Server140COM	ablediff.exe" -sourceserver [DESKTOP-0LI9ARF] -sourcedatabase test_db1 -sourcetable test_table2 -destinationserver [DESKTOP-0LI9ARF] -destinationdatabase test_db2 -destinationtable test_table2 -f D:4_9Diff1';

看来没有唯一的字段是不行的，没有唯一字段的堆表，如需数据同步最少需手动添加一个自增字段才行；

已生成的差异脚本如下图，剩下的步骤就是使用sqlcmd执行差异脚本。

查询对应的目标表验证正常；

为了与之前的数据比对测试一致，使用xp_cmdshell 调用sqlcmd如下

EXEC master..xp_cmdshell '"C:Program FilesMicrosoft SQL ServerClient SDKODBC130ToolsBinnsqlcmd.exe" -S DESKTOP-0LI9ARF -d test_db2 -i D:4_9Diff.sql -o D:4_9error1.txt';

效果与 Command prompt一样；

结论

从上述测试来看基本验证此方案是可行，不过使用到生产环境就要做充分测试验证，如磁盘的IO到时是否会有瓶颈，网络情况等；
注意tablediff的限制，满足比对的前提是要有【primary key】或【identity】或【ROWGUID 】，如没有就需手工增加一个，注意使用-strict 选项会有一些限制注意查看官方文档；
注意开启xp_cmdshell会有一定的安全风险；
注意在sqlcmd 中字母区分大小写且有不同的含义，详情查看官方文档；