在孤立的系统之间共享相关信息对组织来说变得越来越重要, 因为它允许他们提高数据的质量和可用性. 在许多情况下，在多个目录服务器中拥有可用且一致的数据集是很有用的. 这就是为什么要知道表演的常用方法 SQL Server 数据同步很重要.

数据的可用性和一致性可以通过数据复制和数据同步过程来实现. 数据复制是为了容错或改进可访问性而创建数据库的一个或多个冗余副本的过程. 数据同步是在两个或多个数据库之间建立数据一致性的过程, 以及随后的持续更新以保持上述一致性.

在很多组织中, 跨不同系统执行数据同步既令人满意又具有挑战性. 我们可以找到许多需要执行数据同步的用例:

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

数据同步没有唯一的方式或一致同意的方法. 这项任务因情况而异, 而且，即使是初看起来很简单的数据同步也可能很复杂, 由于数据结构的复杂性. 在真实场景中, 数据同步包括许多复杂的任务, 这需要很长时间才能完成. 当出现新的需求时, 数据库专家通常必须重新实现整个同步过程. 因为做这件事没有标准的方法, 除了复制, 数据同步的实现很少是最佳的. 这导致了维护困难和更高的费用. 数据同步的实现和维护是一个非常耗时的过程, 它本身就是一份全职工作.

我们可以手动实现数据同步任务的体系结构, 可能使用微软同步框架, 或者我们可以从管理Microsoft SQL Server的工具中已经创建的解决方案中受益. 我们将尝试描述可用于解决Microsoft SQL Server数据库数据同步的最常见方法和工具，并尝试给出一些建议.

根据源和目的的结构(e.g.(数据库、表)，当结构相似或不同时，我们可以区分用例.

源和目的具有非常相似的结构

当我们在软件开发生命周期的各个阶段使用数据时，这种情况非常常见. 例如，测试环境和生产环境中的数据结构非常相似. 常见的需求是比较测试和生产数据库之间的数据，并将生产数据库中的数据导入到测试数据库中.

源和目的具有不同的结构

如果结构不同，则同步更为复杂. 这也是一个更频繁重复的任务. 常见的情况是从一个数据库导入到另一个数据库. 最常见的情况是，当一个软件需要从另一个由另一家公司维护的软件导入数据时. 通常，导入需要在计划的基础上自动运行.

所使用的方法取决于个人偏好和需要解决的问题的复杂程度.

不管结构有多相似, 我们可以选择四种不同的方法来解决数据同步:

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

源和目的具有相同或非常相似的结构

使用手动创建的SQL脚本

最直接和乏味的解决方案是手动编写用于同步的SQL脚本.

优势

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

缺点

• 创建这样一个SQL脚本是相当乏味的, 因为每个表通常需要三个脚本: 插入, 更新, 删除.
• 您只能同步通过SQL查询可用的数据, 因此，您不能从CSV和XML文件等源导入.
• 当数据库结构改变时，它很难维护, 需要修改两三个脚本(插入, 更新，有时也 删除).

例子

我们将在表之间进行同步 Source，有列 ID 和 价值和桌子 目标，列数相同.

如果表具有相同的主键, 并且目标表没有自动递增的(标识)主键, 您可以执行以下同步脚本.

 ——插入
插入目标(ID, 价值)
从源中选择ID，值
WHERE不存在(SELECT * FROM 目标 WHERE 目标 ?.ID =来源.ID);

- - -更新
更新目标
SET 价值 = Source.价值
从目标内部连接源到目标.ID =来源.ID

——删除
从目标删除
WHERE不存在(SELECT * FROM Source WHERE 目标 ?.ID =来源.ID)

使用数据比较方法

在这种方法中，我们可以使用一个工具来比较源数据和目标数据. 比较过程生成SQL脚本，将源数据库的差异应用到目标数据库中.

有许多用于数据比较和同步的程序. 这些程序大多使用相同的方法. 用户选择源数据库和目标数据库, 但其他选择可能是数据库备份, 存放SQL脚本的文件夹, 甚至连到源代码控制系统.

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

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

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

这些工具还提供了用于同步的附加设置.

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

同步的关键

缺省情况下，主键或a 独特的 使用了约束. 如果没有主键，则可以选择列的组合. Sync键用于将源的行与目标的行配对.

表配对

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

同步过程

确认后，该工具将比较源数据和目标数据. 整个过程包括下载所有源数据和目标数据，并根据指定的标准对它们进行比较. 默认情况下，比较来自相同名称的表和列的值. 所有工具都支持映射列名和表名. 此外，还存在排除的可能性 身份 (自动递增)列或在比较值之前进行一些转换(浮点类型), 忽略字符大小写, 治疗 零 作为空字符串，等等.)优化数据下载. 如果数据量很大，则只下载校验和. 这种优化在大多数情况下都是有用的, 但是，执行操作所需的时间随着数据量的增加而增加.

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

优势

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

缺点

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

下面可以看到这些工具的典型UI.

与自动生成的SQL同步

这种方法与数据比较法非常相似. 与以前的方法相比，唯一的区别是没有数据比较, 生成的SQL脚本中不存在数据差异, 但是同步逻辑. 生成的脚本可以很容易地保存到存储过程中，并且可以定期运行.g.，每晚). 此方法对于数据库之间的自动导入非常有用. 该方法的性能比数据比较法要好得多.

自动生成的SQL的同步只能由 SQL数据库工作室.

SQL数据库工作室为数据比较方法提供了类似的接口. 我们需要选择源和目标(数据库或表). 然后我们需要设置选项(同步键、配对和映射). 有一个图形查询生成器功能，用于设置所有参数.

优势

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

缺点

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

性能基准测试

测试用例

两个数据库(A和B)，每个数据库包含一个包含2,000,000行的表. 这些表位于同一个SQL Server的两个不同数据库中. 该测试涵盖两种极端情况: 1)源表包含所有2,000,000行，目标表为空. 同步需要提供很多 插入. 2)源表和目标表包含2,000,000行. 差别只在于一行. 同步只需要提供一个 更新.

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

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

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

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

SQL数据库工作室在一个步骤中执行整个同步. 下面是同步时间，以秒为单位. 在标记为“单个步骤”的列中是上面列出的同步步骤的持续时间.

 

越低越好.

同样的测试, 但是数据库在不同的SQL服务器上, 哪些没有通过链接服务器连接.

 

越低越好.

Summary

从结果来看, 很明显，RedGate和Apex并不关心数据库是否在同一个SQL服务器上, 因为同步算法不依赖于SQL Server. SQL数据库工作室 uses native functions of SQL Server; therefore, 当数据库位于同一台服务器上时，结果会更好.

源和目标具有不同的结构

还有一种情况是，必须将一个宽表同步到许多小的相关表中.

这个示例包含一个宽表SourceData，它需要同步到小表中 大陆, 国家, 城市. 方案如下.

SourceData中的数据可能如下图所示.

使用手动创建的SQL脚本

脚本同步大陆表

插入大陆(名称)
选择SourceData.大陆
从SourceData
在哪里(SourceData.大陆不是空的
        并且不存在(SELECT *)从大陆测试，在测试.名字。 = SourceData.大陆))
按源数据分组.大陆;

脚本同步城市表

插入城市(姓名，国家)
选择SourceData.的城市,
    国家.Id
从SourceData
    源数据上的左连接大陆.大陆=大陆.名字。
    在源数据上左侧加入国家.国家=国家.名称和大洲.Id =国家.大陆Id
在SourceData.城市不是空的
        和国家.Id不为空
        并且不存在(SELECT * FROM 城市 test)
            在哪里测试.名称= SourceData.城市和测试.国家Id =国家.Id)
集团
    SourceData.的城市,
    国家.Id;

这个脚本更复杂. 这是因为表中的记录 国家 和 大陆 必须被找到. 该脚本将丢失的记录插入到 城市 ,充满 ContryId 正确.

的 更新 和 删除 如果需要，也可以用同样的方式编写脚本.

优势

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

缺点

• 创建这样的SQL脚本既困难又复杂(每个表需要三个脚本)插入, 更新, 删除-通常是必要的).
• 它很难维护.

使用外部工具

这种同步(宽表到许多相关表)不能用数据比较方法完成, 因为它关注不同的用例. 因为数据比较方法会生成一个包含要插入数据的SQL脚本, 它不能直接查找相关表中的引用. 因为这个原因, 使用此方法的应用程序不能使用(dbForge数据比较SQL Server), RedGate SQL数据比较, Apex SQL数据差异.

但是，SQL数据库工作室可以帮助您自动创建同步脚本. 如下图所示, 在SQL数据库工作室中有一个名为“数据同步编辑器”的元素.

编辑器看起来很像众所周知的查询生成器，工作方式也非常相似. 每个表需要有一个定义的同步键, 但是表之间也有定义好的关系. 在上面的图片中也有同步的映射. 在列列表(图像的下部)中有表的列 城市 (对于其他表也是类似的).

列

• Id -该列不被映射，因为它是主键(自动生成).
• 国家Id —该列被定义为表的引用.
• 名字。 -这一列是从源表(宽表)中的城市列填充的.

列 国家Id 和 名字。 被选为同步键. 同步键是一组列，它们唯一地标识源表和目标表中的一行. 不能使用主键 Id 作为同步键，因为它不在源表中.

同步完成后，表是这样的:

在上面的示例中，有一个宽表作为源. 还有一种常见的情况是，源数据存储在几个相关的表中. SQL数据库工作室中的关系不是使用外键定义的，而是使用列名定义的. 这样也可以从CSV或Excel文件中导入(文件被加载到临时表中), 同步是从那个表运行的). 使用唯一的列名是一种很好的做法. 如果这是不可能的，您可以定义这些列的别名.

优势

• 容易和快速创建
• 易于维护
• 可以保存到存储过程中(存储过程中保存了稍后在编辑器中打开同步所需的数据)。

缺点

• 商业解决方案

方案比较

数据同步包括一系列的 插入, 更新, or 删除 命令. 有多种方法可以创建这些命令的序列. 在本文中，我们研究了创建同步SQL脚本的三个选项. 第一个选项是手动创建所有内容. 这是可行的(但花费太多时间)。, 它需要对SQL有复杂的理解, 而且很难创建和维护. 第二个选择是使用商业工具. 我们研究了以下工具:

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

前三个工具的工作原理非常相似. 他们比较数据, 让用户分析差异, 并且可以同步选择的差异(甚至可以自动或从命令行). 它们对以下使用场景是有益的:

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

每个工具都有这样或那样的原因:dbForge有很棒的UI和很多选项, ApexSQL的性能比其他的要好, RedGate是最受欢迎的一个.

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

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