Devart数据库工具【教程】：如何为只读数据库创建自定义SQL Server复制-控件新闻-慧都网

Devart数据库工具【教程】：如何为只读数据库创建自定义SQL Server复制

翻译|使用教程|编辑：吴园园|2020-03-24 15:47:43.023|阅读 281 次

概述：在本文中，我们将使用JobEmpl招聘服务数据库来演示如何使用T-SQL在主从关系上创建数据库复制。

相关链接：

dbForge SQL Complete是一款用于Microsoft SQL Server Management Studio和Microsoft Visual Studio的功能强大的插件。SQL代码格式化程序（免费版和高级付费版）能够提供可以取代原生的Microsoft T-SQL Intellisense特性的T-SQL代码自动实现和格式化功能。

点击下载dbForge SQL Complete最新试用版

通常，需要创建SQL Server数据库的只读副本。例如，出于分离分析任务和操作任务的目的，可能需要这样做。第一个导致数据库上的高负载，为了减少负载，创建了主数据库的副本以执行分析性只读查询。

通常，可以使用内置DBMS工具创建以下只读副本：

日志传送
SQL Server复制
AlwaysOn可用性组。

但是，如果您不需要整个数据库，而只需要其中的几个表怎么办？在这种情况下，您可以自己创建复制。只要数据采样是主要目标，那么一个方向（主从）的数据库复制就足够了。可以使用包括SSIS和.NET在内的几种方法来执行这种复制。

在本文中，我们将使用JobEmpl招聘服务数据库来演示如何使用T-SQL在主从关系上创建数据库复制。

SQL Complete是帮助我构建这些脚本的主要工具。该工具还允许代码格式化以及重命名对象及其所有引用。

使用T-SQL在一个方向上创建SQL Server复制

首先，让我们描述此复制的主要原理和算法。在每次迭代期间，我们需要比较Source数据库和Target数据库之间所选表中的数据。这意味着我们需要输入唯一的代理键来比较表。为了加快比较过程，我们还需要在该键上创建一个索引。并且还需要为每个复制表添加一个计算字段，以便为每一行计算CHECKSUM。

同样重要的是，选择数据的固定部分，例如一次（每次迭代）一次特定数量的行。

因此，我们需要执行以下步骤：

在源表上，创建一个REPL_GUID列和一个唯一的REPL_GUID索引，以在源表和目标表之间建立一对一的关系。您还应该创建一个计算出的CheckSumVal列，该列将为每一行计算CHECKSUM值。
创建一个名为Target的新目标数据库。
跨源数据库和目标数据库同步复制表的架构，并删除对不存在对象的所有引用。
禁用目标数据库的外键。
运行复制并监视源数据库和目标数据库之间有多少行不同。

现在，让我们使用为雇用员工而创建的JobEmpl数据库详细查看每个步骤。

Devart数据库工具【教程】：如何为只读数据库创建自定义SQL Server复制（上）

图-求职者数据库的架构

我们只需要复制Employee和JobHistory表。

从脚本中，您可以看到它必须在源JobEmpl数据库上运行，并且应该在@src和@sch变量中相应地指定源数据库和架构。@sql变量对于构建动态SQL是必需的，同时，@ name对于保存复制表的名称也是必需的。

首先，我们将复制的表名称收集到临时#tbl表中。接下来，我们使用光标遍历每个表名，并将表名提取到@name变量中。之后，对于每个表，将形成非IDENTITY类型的列列表，并将结果插入带有“ +”号的@listcols变量中。

值得一提的是，首先，首先使用CAST函数将每个表名转换为NVACHAR（MAX）类型，然后使用COALESCE函数（[<ColumnName>]，N''）。从每一行的所有列值中形成一个字符串。

接下来，将创建计算出的CheckSumVal字段，REPL_GUID字段及其唯一的indREPL_GUID索引。

复制的表也位于此处，对于每个表，都将删除indREPL_GUID索引以及REPL_GUID和CheckSumVal列。

在我们的例子中，创建了以下T-SQL代码。

DROP INDEX [indREPL_GUID] ON [dbo].[Employee];
ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [Employee_DEF_REPL_GUID], COLUMN [CheckSumVal], COLUMN [REPL_GUID];

DROP INDEX [indREPL_GUID] ON [dbo].[JobHistory];
ALTER TABLE [dbo].[JobHistory] DROP CONSTRAINT [JobHistory_DEF_REPL_GUID], COLUMN [CheckSumVal], COLUMN [REPL_GUID];

现在，根据上述算法的第二步，创建一个新的JobEmplRead数据库来接收数据。然后，我们同步复制表的架构。要执行同步，请使用DbForge架构比较工具：选择JobEmpl作为数据源，选择jobEmplRead作为数据目标。