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

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

背景

我们有一个测试需求,需把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 中字母区分大小写且有不同的含义,详情查看官方文档;

 

posted @ 2019-02-20 15:36  jil.wen  阅读(680)  评论(0编辑  收藏  举报