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 = 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 脚本的文件夹，甚至是与源控制系统的连接。

以下是使用数据比较方法的最流行的工具：

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

第一步，读取数据，或者仅读取来自源和目标的较大数据的校验和。 然后执行比较过程。

这些工具还提供了额外的同步设置。

我们需要设置以下数据同步所需的配置选项：

同步键

默认情况下，使用主键或UNIQUE约束。 如果没有主键，可以选择列组合。 Sync 键用于将源行与目标行配对。

表配对

默认情况下，表按名称配对。 您可以更改此设置，并根据自己的需要将它们配对。 在 dbForge 数据比较软件中，您可以选择 SQL 查询作为源或目标。

同步过程

确认后，该工具会比较源数据和目标数据。 整个过程包括下载所有源数据和目标数据，并根据指定的标准进行比较。 默认情况下，比较同名表和列中的值。 所有工具都支持映射列名和表名。 此外，还可以排除IDENTITY （自动增量）列或在比较值之前进行一些转换（圆形浮点类型、忽略字符大小写、将NULL视为空字符串等）优化数据下载。 如果数据量很大，则只下载校验和。 这种优化在大多数情况下很有帮助，但执行操作的时间要求会随着数据量的增加而增加。

在下一步中，有一个带有生成迁移的 SQL 脚本。 该脚本可以保存或直接运行。 为了安全起见，我们甚至可以在运行此脚本之前进行数据库备份。 ApexSQL Data Diff 工具可以创建在选定数据库上运行脚本的可执行程序。 该脚本包含需要更改的数据，而不是如何更改它的逻辑。 这意味着脚本无法自动运行以提供重复导入。 这是这种方法的最大缺点。

优点

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

缺点

• 这是商业产品的高级功能。
• 传输大量数据时性能会下降。
• 生成的 SQL 脚本仅包含差异，因此不能重复用于自动同步未来的数据。

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

与自动生成的 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 行，而目标表为空。 同步需要提供很多INSERTS 。 2) 源表和目标表包含 2,000,000 行。 区别仅在一排。 同步只需要提供一个UPDATE 。

RedGate 数据比较需要 3 个步骤：

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

ApexSQL 数据差异需要 2 个步骤：

• 比较
• 生成脚本和运行脚本一步到位

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

越低越好。

相同的测试，但数据库位于不同的 SQL 服务器上，它们没有通过链接服务器连接。

越低越好。

概括

从结果来看，很明显 RedGate 和 Apex 并不关心数据库是否在同一个 SQL Server 上，因为同步算法不依赖于 SQL Server。 SQL Database Studio 使用 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;

这个脚本比较复杂。 这是因为必须找到Country和Continent表中的记录。 此脚本将缺失的记录插入City并正确填写ContryId 。

如果需要，也可以以相同的方式编写UPDATE和DELETE脚本。

优点

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

缺点

• 创建这样的 SQL 脚本既困难又复杂（对于每个表，通常需要三个脚本—— INSERT 、 UPDATE和DELETE ）。
• 很难维护。

使用外部工具

这种同步（宽表到许多相关表）不能用数据比较方法完成，因为它侧重于不同的用例。 由于数据比较方法会生成一个带有要插入数据的 SQL 脚本，因此它无法直接在相关表中查找引用。 因此，无法使用使用此方法的应用程序（dbForge Data Compare for SQL Server、RedGate SQL Data Compare、Apex SQL Data Diff）。

但是，SQL Database Studio 可以帮助您自动创建同步脚本。 在下图中，SQL Database Studio 中有一个名为 Editor for Data Synchronization 的元素。

Editor 看起来像著名的 Query builder 并且以非常相似的方式工作。 每个表都需要有定义的同步键，但表之间也有定义的关系。 在上图中，还有用于同步的映射。 在列列表（图像的下部）中，有表City的列（对于其他表，它是类似的）。

列

• Id — 此列未映射，因为它是主键（自动生成）。
• CountryId — 此列定义为表的引用。
• 名称- 此列从源表（宽表）中的 City 列填充。

选择列CountryId和Name作为同步键。 同步键是一组唯一标识源表和目标表中的行的列。 您不能使用主键Id作为同步键，因为它不在源表中。

同步后，表如下所示：

在上面的示例中，有一个宽表作为源。 当源数据存储在几个相关的表中时，还有一个常见的场景。 SQL Database Studio 中的关系不是使用外键定义的，而是使用列名定义的。 这样，也可以从 CSV 或 Excel 文件导入（文件被加载到临时表中，并从该表运行同步）。 拥有唯一的列名是一种很好的做法。 如果这不可能，您可以为这些列定义别名。

优点

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

缺点

• 商业解决方案

比较解决方案

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

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

前三个工具的工作方式非常相似。 他们比较数据，让用户分析差异，并可以同步选择的差异（甚至自动或从命令行）。 它们有利于这些使用场景：

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

每个工具都因某种原因而受到喜爱：dbForge 具有出色的 UI 和许多选项，ApexSQL 的性能优于其他工具，而 RedGate 是最受欢迎的。

第四个工具 SQL Database Studio 的工作方式略有不同。 它生成包含同步逻辑而不是更改的 SQL 脚本。 性能也很棒，因为所有工作都直接在数据库服务器上完成，因此不需要在数据库服务器和同步工具之间进行数据传输。 此工具适用于以下用例：

• 数据库具有不同结构的自动数据库迁移
• 导入多个相关表
• 从外部源导入 XML、CSV、MS Excel