不同实例下同构表数据同步验证
不同实例下同构表数据同步验证
背景
我们有一个测试需求,需把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 Files\Microsoft SQL Server\140\COM\tablediff.exe" -sourceserver [DESKTOP-0LI9ARF] -sourcedatabase test_db1 -sourcetable test_table -destinationserver [DESKTOP-0LI9ARF] -destinationdatabase test_db2 -destinationtable test_table -f D:\4_9\Diff';
打开内容如下:
看来一个表的数据是没有问题,我们试试在目标表提前插入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 Files\Microsoft SQL Server\140\COM\tablediff.exe" -sourceserver [DESKTOP-0LI9ARF] -sourcedatabase test_db1 -sourcetable test_table -destinationserver [DESKTOP-0LI9ARF] -destinationdatabase test_db2 -destinationtable test_table -f D:\4_9\Diff';
测试一下无主键无自增字段的场景
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 Files\Microsoft SQL Server\140\COM\tablediff.exe" -sourceserver [DESKTOP-0LI9ARF] -sourcedatabase test_db1 -sourcetable test_table2 -destinationserver [DESKTOP-0LI9ARF] -destinationdatabase test_db2 -destinationtable test_table2 -f D:\4_9\Diff1';
看来没有唯一的字段是不行的,没有唯一字段的堆表,如需数据同步最少需手动添加一个自增字段才行;
已生成的差异脚本如下图,剩下的步骤就是使用sqlcmd执行差异脚本。
查询对应的目标表验证正常;
为了与之前的数据比对测试一致,使用xp_cmdshell 调用sqlcmd如下
EXEC master..xp_cmdshell '"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\sqlcmd.exe" -S DESKTOP-0LI9ARF -d test_db2 -i D:\4_9\Diff.sql -o D:\4_9\error1.txt';
效果与 Command prompt一样;
结论
- 从上述测试来看基本验证此方案是可行,不过使用到生产环境就要做充分测试验证,如磁盘的IO到时是否会有瓶颈,网络情况等;
- 注意tablediff的限制,满足比对的前提是要有【primary key】或【identity】或【ROWGUID 】,如没有就需手工增加一个,注意使用-strict 选项 会有一些限制注意查看官方文档;
- 注意开启xp_cmdshell会有一定的安全风险;
- 注意在sqlcmd 中字母区分大小写且有不同的含义,详情查看官方文档;