Microsoft SQL Server中的数据同步指南 _数据科学

本文概述

源和目标的结构非常相似
源和目标具有不同的结构
源和目标具有相同或非常相似的结构
绩效基准
源和目标具有不同的结构
比较解决方案

在隔离的系统之间共享相关信息对于组织而言变得越来越重要, 因为它使组织能够提高数据的质量和可用性。在许多情况下, 拥有一个以上的目录服务器中可用且一致的数据集很有用。因此, 了解执行SQL Server数据同步的常用方法很重要。
数据可用性和一致性可以通过数据复制和数据同步过程来实现。数据复制是为了容错或提高可访问性而创建数据库的一个或多个冗余副本的过程。数据同步是在两个或多个数据库之间建立数据一致性以及随后的连续更新以保持所述一致性的过程。

文章图片
在许多组织中, 跨各种系统执行数据同步既是理想的又是挑战。我们可以找到许多需要执行数据同步的用例：

数据库迁移
信息系统之间的定期同步
将数据从一个信息系统导入另一个系统
在不同阶段或环境之间移动数据集
从非数据库源导入数据

没有唯一的方法或一致同意的数据同步方法。这项任务因情况而异, 由于数据结构的复杂性, 即使乍一看就应该很简单的数据同步也可能很复杂。在实际情况下, 数据同步包含许多复杂的任务, 这些任务可能需要很长时间才能执行。当提出新要求时, 数据库专家通常必须重新实现整个同步过程。由于没有标准的方法可以做到这一点, 除了复制, 数据同步的实现很少是最优的。这导致难以维护和更高的费用。数据同步的实现和维护是一个非常耗时的过程, 它本身可以是一个全职工作。
我们可以使用Microsoft Sync Framework手动实现用于数据同步任务的体系结构, 或者可以从用于管理Microsoft SQL Server的工具中已经创建的解决方案中受益。我们将尝试描述可用于解决Microsoft SQL Server数据库上的数据同步的最常用方法和工具, 并尝试给出一些建议。
根据源和目标的结构(例如数据库, 表), 我们可以区分结构相似或不同的用例。
源和目标的结构非常相似当我们在软件开发生命周期的各个阶段使用数据时, 通常就是这种情况。例如, 测试和生产环境中的数据结构非常相似。常见的要求是比较测试数据库和生产数据库之间的数据, 并将数据从生产数据导入到测试数据库中。
源和目标具有不同的结构如果结构不同, 则同步会更加复杂。这也是一个更频繁发生的任务。一种常见的情况是从一个数据库导入另一个数据库。最常见的情况是某个软件需要从另一家公司维护的另一软件中导入数据。通常, 导入需要按计划自动运行。
使用的方法取决于个人喜好和你需要解决的问题的复杂性。
无论结构如何相似, 我们都可以选择四种不同的方法来解决数据同步：

使用手动创建的SQL脚本进行同步
使用数据比较方法进行同步(仅当源和目标具有相似的结构时才可以使用)
使用自动生成的SQL脚本进行同步-需要商业产品

源和目标具有相同或非常相似的结构使用手动创建的SQL脚本
最简单, 最繁琐的解决方案是手动编写用于同步的SQL脚本。
优点

可以通过免费和开源(FOSS)工具执行。
如果表具有索引, 则速度非常快。
可以将SQL脚本保存到存储过程中, 或作为SQL Server的作业定期运行。
即使连续更改的数据也可以用作自动导入。

缺点

创建这样的SQL脚本非常繁琐, 因为每个表通常需要三个脚本：INSERT, UPDATE和DELETE。
你只能同步可通过SQL查询获得的数据, 因此无法从CSV和XML文件等来源导入。
很难维护-更改数据库结构时, 有必要修改两个或三个脚本(INSERT, UPDATE和DELETE)。

例子我们将在具有ID和Value列的表Source与具有相同列的表Target之间进行同步。
如果表具有相同的主键, 而目标表没有自动递增(标识)主键, 则可以执行以下同步脚本。

-- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = http://www.srcmini.com/Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID-- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID)

使用数据比较方法
在这种方法中, 我们可以使用工具在源数据和目标数据之间进行比较。比较过程生成SQL脚本, 这些脚本将源数据库中的差异应用到目标数据库中。
有许多用于数据比较和同步的程序。这些程序大多使用相同的方法。用户选择源数据库和目标数据库, 但是其他选择可能是数据库备份, 带有SQL脚本的文件夹, 甚至是到源控制系统的连接。
以下是使用数据比较方法的最受欢迎的工具：

dbForge SQL Server的数据比较
RedGate SQL数据比较
Apex SQL数据差异

第一步, 读取数据, 或者仅读取来自源和目标的较大数据的校验和。然后执行比较过程。
这些工具还提供了用于同步的其他设置。
我们需要设置以下数据同步所需的配置选项：
同步键默认情况下, 使用主键或UNIQUE约束。如果没有主键, 则可以选择列的组合。 Sync键用于将源行与目标行配对。
表配对默认情况下, 表按名称配对。你可以更改此设置, 然后根据自己的需要进行配对。在dbForge数据比较软件中, 可以选择SQL查询作为源或目标。
同步过程确认后, 该工具将比较源数据和目标数据。整个过程包括下载所有源数据和目标数据, 并根据指定的标准对它们进行比较。默认情况下, 将比较同名表和列中的值。所有工具都支持映射列名和表名。此外, 还可以排除IDENTITY(自动递增)列或在比较值之前进行一些转换(舍入浮动类型, 忽略字符大小写, 将NULL视为空字符串等)。优化了数据下载。如果数据量很大, 则仅下载校验和。这种优化在大多数情况下很有用, 但是执行操作的时间要求随数据量的增加而增加。
下一步, 将提供一个带有生成的迁移的SQL脚本。该脚本可以保存或直接运行。为了安全起见, 我们甚至可以在运行此脚本之前进行数据库备份。 ApexSQL Data Diff工具可以创建一个可执行程序, 该程序在选定的数据库上运行脚本。该脚本包含需要更改的数据, 而不是更改逻辑的逻辑。这意味着该脚本无法自动运行以提供重复导入。那是这种方法的最大缺点。
优点

不需要SQL的高级知识, 可以通过GUI完成。
你可以在同步之前直观地检查数据库之间的差异。

缺点

这是商业产品的高级功能。
传输大量数据时, 性能会降低。
生成的SQL脚本仅包含差异, 因此无法重新用于自动同步将来的数据。

在下面, 你可以看到这些工具的典型UI。

文章图片
ApexSQL数据差异

文章图片
RedGate SQL比较

文章图片
dbForge数据比较中的更改列表
与自动生成的SQL同步
此方法与数据比较方法非常相似。与以前的方法相比, 唯一的区别是没有数据比较, 并且生成的SQL脚本不包含数据差异, 而是同步逻辑。生成的脚本可以轻松地保存到存储过程中, 并且可以定期运行(例如, 每晚)。此方法对于数据库之间的自动导入很有用。该方法的性能比数据比较方法好得多。
通过自动生成的SQL进行的同步仅由SQL Database Studio提供。
SQL Database Studio为数据比较方法提供了类似的接口。我们需要选择源和目标(数据库或表)。然后, 我们需要设置选项(同步密钥, 配对和映射)。具有用于设置所有参数的图形查询构建器功能。
优点

不需要SQL的高级知识。
你可以非常快速地在GUI中设置所有内容。
生成的SQL脚本可以保存到存储过程中。
可以用作自动导入-作为SQL Server的作业。

缺点

这是商业产品的高级功能。
同步之前无法手动检查差异, 因为整个过程是一步完成的。

绩效基准测试用例
两个数据库(A和B), 每个数据库包含一个包含2, 000, 000行的表。这些表位于同一SQL Server上的两个不同数据库中。此测试涵盖两种极端情况：1)源表包含所有2, 000, 000行, 而目标表为空。同步需要提供许多插入。 2)源表和目标表包含2, 000, 000行。区别仅在一排。同步只需要提供一个UPDATE。
RedGate数据比较需要3个步骤：

比较
生成脚本
在目标数据库上运行脚本

ApexSQL Data Diff需要2个步骤：

比较
一步生成脚本并运行脚本

SQL Database Studio一步完成整个同步。以下是同步时间(以秒为单位)。在标记为” 各个步骤” 的列中是上面列出的同步步骤的持续时间。

	案例A：许多INSERT	案例A：许多INSERT(单个步骤)	情况B。更新一行	情况B。更新一行(各个步骤)
SQL数据库工作室	47		5
RedGate数据比较	317	13+92+212	23	22+0+1
ApexSQL数据差异	188	18+170	26	25+

越低越好。

文章图片
相同的测试, 但是数据库位于不同的SQL服务器上, 这些服务器未通过链接服务器连接。

	案例A：许多INSERT	案例A：许多INSERT(单个步骤)	情况B。更新一行	情况B。更新一行(各个步骤)
SQL数据库工作室	78		44
RedGate数据比较	288	17+82+179	25	24+0+1
ApexSQL数据差异	203	18+185	25	24+1
dbForge数据比较	326	11+315	16	16+0

越低越好。

文章图片
摘要
从结果可以明显看出, RedGate和Apex不在乎数据库是否在同一SQL服务器上, 因为同步算法不依赖于SQL Server。 SQL Database Studio使用SQL Server的本机功能。因此, 当数据库位于同一服务器上时, 结果会更好。
源和目标具有不同的结构在某些情况下, 必须将一个宽表同步到许多小相关表中。
【Microsoft SQL Server中的数据同步指南】此示例由一个宽表SourceData组成, 需要将其同步到小表Continent, Country和City中。该方案如下。

文章图片
SourceData中的数据可能类似于下图中的数据。

文章图片
使用手动创建的SQL脚本
脚本同步大陆表

INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent;

脚本同步城市表

INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id;

该脚本更加复杂。这是因为必须在” 国家/地区” 表中找到记录。该脚本将缺少的记录插入到City中, 并正确填充ContryId。
如果需要, UPDATE和DELETE脚本也可以用相同的方式编写。
优点

你不需要任何商业产品。
SQL脚本可以保存到存储过程中, 也可以作为SQL Server的作业定期运行。

缺点

创建这样的SQL脚本既困难又复杂(对于每个表, 通常需要三个脚本-INSERT, UPDATE和DELETE)。
很难维护。

使用外部工具
数据比较方法无法完成这种同步(将宽表转换为许多相关的表), 因为它专注于不同的用例。由于数据比较方法生成带有要插入的数据的SQL脚本, 因此它没有直接的能力来查找相关表中的引用。因此, 无法使用使用此方法的应用程序(用于SQL Server的dbForge数据比较, RedGate SQL数据比较, Apex SQL数据差异)。
但是, SQL Database Studio可以帮助你自动创建同步脚本。在下图中, SQL Database Studio中有一个称为” 数据同步编辑器” 的元素。

文章图片
编辑器看起来像著名的” 查询” 构建器, 并且以非常相似的方式工作。每个表都需要有一个定义的同步密钥, 但是表之间也有定义的关系。在上图中, 也有用于同步的映射。在列列表(图像的下部)中, 有表城市的列(对于其他表则类似)。
列

Id-此列未映射, 因为它是主键(自动生成)。
CountryId-此列定义为表格的参考。
名称-从源表(宽表)中的城市列填充此列。

选择CountryId和Name列作为同步键。同步键是一组列, 它们唯一地标识源表和目标表中的一行。你不能将主键ID用作同步键, 因为它不在源表中。
同步后, 表如下所示：

文章图片
在上面的示例中, 有一个宽表作为来源。当源数据存储在几个相关表中时, 还有一种常见的情况。 SQL Database Studio中的关系不是使用外键定义的, 而是使用列名定义的。这样, 还可以从CSV或Excel文件导入(文件已加载到临时表中, 并从该表运行同步)。拥有唯一的列名是一种很好的做法。如果不可能, 则可以为这些列定义别名。
优点

轻松快速地创建
易于维护
可以保存到存储过程中(该存储过程与稍后在编辑器中打开同步所必需的数据一起保存)

缺点

商业解决方案

比较解决方案数据同步由一系列INSERT, UPDATE或DELETE命令组成。有多种方法可以创建这些命令的序列。在本文中, 我们研究了用于创建同步SQL脚本的三个选项。第一种选择是手动创建所有内容。这是可行的(但要花费很多时间), 需要对SQL有复杂的理解, 并且很难创建和维护。第二种选择是使用商业工具。我们研究了以下工具：

dbForge SQL Server的数据比较
RedGate SQL数据比较
Apex SQL数据差异
SQL数据库工作室

前三个工具的工作原理非常相似。他们比较数据, 让用户分析差异, 并可以同步选定的差异(甚至自动同步或从命令行)。它们对于以下使用场景很有帮助：

由于各种错误, 数据库不同步。
在环境之间传输数据时, 你需要避免复制。
需要Excel或HTML中的数据比较报告。

每个工具之所以受人欢迎是因为一个或另一个原因：dbForge具有出色的UI和许多选项, ApexSQL的性能要优于其他工具, 而RedGate是最受欢迎的工具。
第四个工具SQL Database Studio的工作方式略有不同。它生成包含同步逻辑而不是更改的SQL脚本。性能也很好, 因为所有工作都直接在数据库服务器上完成, 因此不需要在数据库服务器和同步工具之间进行数据传输。该工具对于以下用例很有用：