本文概述
- 源和目标的结构非常相似
- 源和目标具有不同的结构
- 源和目标具有相同或非常相似的结构
- 绩效基准
- 源和目标具有不同的结构
- 比较解决方案
数据可用性和一致性可以通过数据复制和数据同步过程来实现。数据复制是为了容错或提高可访问性而创建数据库的一个或多个冗余副本的过程。数据同步是在两个或多个数据库之间建立数据一致性以及随后的连续更新以保持所述一致性的过程。
![Microsoft SQL Server中的数据同步指南](http://www.srcmini.com/wp-content/uploads/2020/04/image-1582846219497-1cee362c67bb5b29e3c1ba0a14e55170.png)
文章图片
在许多组织中, 跨各种系统执行数据同步既是理想的又是挑战。我们可以找到许多需要执行数据同步的用例:
- 数据库迁移
- 信息系统之间的定期同步
- 将数据从一个信息系统导入另一个系统
- 在不同阶段或环境之间移动数据集
- 从非数据库源导入数据
我们可以使用Microsoft Sync Framework手动实现用于数据同步任务的体系结构, 或者可以从用于管理Microsoft SQL Server的工具中已经创建的解决方案中受益。我们将尝试描述可用于解决Microsoft SQL Server数据库上的数据同步的最常用方法和工具, 并尝试给出一些建议。
根据源和目标的结构(例如数据库, 表), 我们可以区分结构相似或不同的用例。
源和目标的结构非常相似 当我们在软件开发生命周期的各个阶段使用数据时, 通常就是这种情况。例如, 测试和生产环境中的数据结构非常相似。常见的要求是比较测试数据库和生产数据库之间的数据, 并将数据从生产数据导入到测试数据库中。
源和目标具有不同的结构 如果结构不同, 则同步会更加复杂。这也是一个更频繁发生的任务。一种常见的情况是从一个数据库导入另一个数据库。最常见的情况是某个软件需要从另一家公司维护的另一软件中导入数据。通常, 导入需要按计划自动运行。
使用的方法取决于个人喜好和你需要解决的问题的复杂性。
无论结构如何相似, 我们都可以选择四种不同的方法来解决数据同步:
- 使用手动创建的SQL脚本进行同步
- 使用数据比较方法进行同步(仅当源和目标具有相似的结构时才可以使用)
- 使用自动生成的SQL脚本进行同步-需要商业产品
最简单, 最繁琐的解决方案是手动编写用于同步的SQL脚本。
优点
- 可以通过免费和开源(FOSS)工具执行。
- 如果表具有索引, 则速度非常快。
- 可以将SQL脚本保存到存储过程中, 或作为SQL Server的作业定期运行。
- 即使连续更改的数据也可以用作自动导入。
- 创建这样的SQL脚本非常繁琐, 因为每个表通常需要三个脚本:INSERT, UPDATE和DELETE。
- 你只能同步可通过SQL查询获得的数据, 因此无法从CSV和XML文件等来源导入。
- 很难维护-更改数据库结构时, 有必要修改两个或三个脚本(INSERT, UPDATE和DELETE)。
如果表具有相同的主键, 而目标表没有自动递增(标识)主键, 则可以执行以下同步脚本。
-- 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脚本仅包含差异, 因此无法重新用于自动同步将来的数据。
![Microsoft SQL Server中的数据同步指南](http://www.srcmini.com/wp-content/uploads/2020/04/image-1582846785581-0ce477725f7f8b9b19788106122d4fee.png)
文章图片
ApexSQL数据差异
![Microsoft SQL Server中的数据同步指南](http://www.srcmini.com/wp-content/uploads/2020/04/image-1582846803395-75fc4bb5c72a2f676930ce420f0c77b4.png)
文章图片
RedGate SQL比较
![Microsoft SQL Server中的数据同步指南](http://www.srcmini.com/wp-content/uploads/2020/04/image-1582846933716-7a1322da6d3ea5e1f02088b297e0a108.png)
文章图片
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个步骤:
- 比较
- 生成脚本
- 在目标数据库上运行脚本
- 比较
- 一步生成脚本并运行脚本
案例A:许多INSERT | 案例A:许多INSERT(单个步骤) | 情况B。更新一行 | 情况B。更新一行(各个步骤) | |
---|---|---|---|---|
SQL数据库工作室 | 47 | 5 | ||
RedGate数据比较 | 317 | 13+92+212 | 23 | 22+0+1 |
ApexSQL数据差异 | 188 | 18+170 | 26 | 25+ |
![Microsoft SQL Server中的数据同步指南](http://www.srcmini.com/wp-content/uploads/2020/04/image-1582847041998-736832b28d7da4c3bbb4d78822bf8d50.png)
文章图片
相同的测试, 但是数据库位于不同的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 |
![Microsoft SQL Server中的数据同步指南](http://www.srcmini.com/wp-content/uploads/2020/04/image-1582847068722-57fde0dd07c7b062b3574a63c1a248c6.png)
文章图片
摘要
从结果可以明显看出, RedGate和Apex不在乎数据库是否在同一SQL服务器上, 因为同步算法不依赖于SQL Server。 SQL Database Studio使用SQL Server的本机功能。因此, 当数据库位于同一服务器上时, 结果会更好。
源和目标具有不同的结构 在某些情况下, 必须将一个宽表同步到许多小相关表中。
【Microsoft SQL Server中的数据同步指南】此示例由一个宽表SourceData组成, 需要将其同步到小表Continent, Country和City中。该方案如下。
![Microsoft SQL Server中的数据同步指南](http://www.srcmini.com/wp-content/uploads/2020/04/image-1582847128039-52e7db6b5a5643f2553b5f872ed41388.png)
文章图片
SourceData中的数据可能类似于下图中的数据。
![Microsoft SQL Server中的数据同步指南](http://www.srcmini.com/wp-content/uploads/2020/04/image-1582847167331-69563b0417181ff9859a61f58bc927d6.png)
文章图片
使用手动创建的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中有一个称为” 数据同步编辑器” 的元素。
![Microsoft SQL Server中的数据同步指南](http://www.srcmini.com/wp-content/uploads/2020/04/image-1582847200134-9fa6bd46d13508d89f5c135b6d914fce.png)
文章图片
编辑器看起来像著名的” 查询” 构建器, 并且以非常相似的方式工作。每个表都需要有一个定义的同步密钥, 但是表之间也有定义的关系。在上图中, 也有用于同步的映射。在列列表(图像的下部)中, 有表城市的列(对于其他表则类似)。
列
- Id-此列未映射, 因为它是主键(自动生成)。
- CountryId-此列定义为表格的参考。
- 名称-从源表(宽表)中的城市列填充此列。
同步后, 表如下所示:
![Microsoft SQL Server中的数据同步指南](http://www.srcmini.com/wp-content/uploads/2020/04/image-1582847215522-a48ee34e020afb513b4d330d963a7413.png)
文章图片
在上面的示例中, 有一个宽表作为来源。当源数据存储在几个相关表中时, 还有一种常见的情况。 SQL Database Studio中的关系不是使用外键定义的, 而是使用列名定义的。这样, 还可以从CSV或Excel文件导入(文件已加载到临时表中, 并从该表运行同步)。拥有唯一的列名是一种很好的做法。如果不可能, 则可以为这些列定义别名。
优点
- 轻松快速地创建
- 易于维护
- 可以保存到存储过程中(该存储过程与稍后在编辑器中打开同步所必需的数据一起保存)
- 商业解决方案
- dbForge SQL Server的数据比较
- RedGate SQL数据比较
- Apex SQL数据差异
- SQL数据库工作室
- 由于各种错误, 数据库不同步。
- 在环境之间传输数据时, 你需要避免复制。
- 需要Excel或HTML中的数据比较报告。
第四个工具SQL Database Studio的工作方式略有不同。它生成包含同步逻辑而不是更改的SQL脚本。性能也很好, 因为所有工作都直接在数据库服务器上完成, 因此不需要在数据库服务器和同步工具之间进行数据传输。该工具对于以下用例很有用:
- 数据库结构不同的自动数据库迁移
- 导入多个相关表
- 从外部来源导入XML, CSV, MS Excel
推荐阅读
- WordPress开发人员最容易犯的12个错误
- 适用于你的应用的轻松AI(与Salesforce爱因斯坦会面)
- Web可访问性(为什么经常会忽略W3C标准)
- 使用Aho-Corasick算法征服字符串搜索
- 探索监督机器学习算法
- 对冲基金深度学习交易简介
- Salesforce爱因斯坦AI(API教程)
- Android|Android随笔-Room简单使用
- 如何修复Windows 10文件系统错误2147219196(解决办法)