教程:在两个 SQL 托管实例之间配置复制

适用于:Azure SQL 托管实例

通过事务复制可以将数据从一个数据库复制到 SQL Server 或 Azure SQL 托管实例中托管的另一个数据库。 SQL 托管实例可以是复制拓扑中的发布服务器、分发服务器或订阅服务器。 有关可用配置,请参阅事务复制配置

在本教程中,你将了解如何执行以下操作:

  • 将 SQL 托管实例配置为复制发布服务器和分发服务器。
  • 将 SQL 托管实例配置为复制订阅服务器。

显示两个 SQL 托管实例之间的复制的关系图。

本教程面向经验丰富的受众,并假定用户熟悉在 Azure 中部署和连接到 SQL 托管实例和 SQL Server VM。

备注

本文介绍了如何在 Azure SQL 托管实例中使用事务复制。 它与 故障转移组无关,这是一项 Azure SQL 托管实例功能,可用于创建单个实例的完整可读副本。 使用故障转移组配置事务复制时,还有其他注意事项。

要求

要配置 SQL 托管实例来充当发布服务器和/或分发服务器,需满足以下要求:

  • 发布服务器 SQL 托管实例与分发服务器和订阅服务器位于同一虚拟网络上,或者已在所有三个实体的虚拟网络之间配置 VNet 对等互连VPN 网关
  • 连接时,在复制参与者之间使用 SQL 身份验证。
  • 适用于复制工作目录的 Azure 存储帐户共享。
  • 端口 445(TCP 出站)在 NSG 的安全规则中打开,供 SQL 托管实例访问 Azure 文件共享。 如果遇到错误 failed to connect to azure storage <storage account name> with os error 53,则需要将出站规则添加到相应 SQL 托管实例子网的 NSG。

1 - 创建资源组

使用 Azure 门户创建名为 SQLMI-Repl 的资源组。

2 - 创建 SQL 托管实例

使用 Azure 门户 在同一虚拟网络和子网上创建两个 SQL 托管实例 。 例如,将两个 SQL 托管实例命名为:

  • sql-mi-publisher(以及一些用于随机化的字符)
  • sql-mi-subscriber(以及一些用于随机化的字符)

还需要 配置 Azure VM 以连接到 SQL 托管实例。

3 - 创建 Azure 存储帐户

为工作目录创建 Azure 存储帐户,并在存储帐户中创建文件共享

复制采用 \\storage-account-name.file.core.chinacloudapi.cn\file-share-name 格式的文件共享路径

示例: \\replstorage.file.core.chinacloudapi.cn\replshare

复制采用 DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.chinacloudapi.cn 格式的存储访问密钥

示例: DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.chinacloudapi.cn

有关详细信息,请参阅管理存储帐户访问密钥

4 - 创建发布服务器数据库

使用 SQL Server Management Studio 连接到发布服务器 SQL 托管实例(sql-mi-publisher),并运行以下 Transact-SQL (T-SQL) 代码来创建发布服务器数据库:

USE [master]
GO

CREATE DATABASE [ReplTran_PUB]
GO

USE [ReplTran_PUB]
GO
CREATE TABLE ReplTest (
  ID INT NOT NULL PRIMARY KEY,
  c1 VARCHAR(100) NOT NULL,
  dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO

USE [ReplTran_PUB]
GO

INSERT INTO ReplTest (ID, c1) VALUES (6, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (2, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (3, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (4, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (5, 'pub')
GO
SELECT * FROM ReplTest
GO

5 - 创建订阅服务器数据库

使用 SQL Server Management Studio 连接到订阅服务器 SQL 托管实例(sql-mi-subscriber),并运行以下 T-SQL 代码来创建空订阅服务器数据库:

USE [master]
GO

CREATE DATABASE [ReplTran_SUB]
GO

USE [ReplTran_SUB]
GO
CREATE TABLE ReplTest (
  ID INT NOT NULL PRIMARY KEY,
  c1 VARCHAR(100) NOT NULL,
  dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO

6 - 配置分发

使用 SQL Server Management Studio 连接到发布服务器 SQL 托管实例(sql-mi-publisher),并运行以下 T-SQL 代码来配置分发数据库。

USE [master]
GO

EXEC sp_adddistributor @distributor = @@ServerName;
EXEC sp_adddistributiondb @database = N'distribution';
GO

7 - 将发布服务器配置为使用分发服务器

在发布服务器 SQL 托管实例(sql-mi-publisher)上,将查询执行更改为 SQLCMD 模式,并运行以下代码向发布服务器注册新分发服务器。

:setvar username loginUsedToAccessSourceManagedInstance
:setvar password passwordUsedToAccessSourceManagedInstance
:setvar file_storage "\\storage-account-name.file.core.chinacloudapi.cn\file-share-name"
-- example: file_storage "\\replstorage.file.core.chinacloudapi.cn\replshare"
:setvar file_storage_key "DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.chinacloudapi.cn"
-- example: file_storage_key "DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.chinacloudapi.cn"

USE [master]
EXEC sp_adddistpublisher
  @publisher = @@ServerName,
  @distribution_db = N'distribution',
  @security_mode = 0,
  @login = N'$(username)',
  @password = N'$(password)',
  @working_directory = N'$(file_storage)',
  @storage_connection_string = N'$(file_storage_key)'; -- Remove this parameter for on-premises publishers

备注

请确保对 file_storage 参数仅使用反斜杠 (\)。 在连接到文件共享时使用正斜杠(/)可能会导致错误。

此脚本在 SQL 托管实例上配置本地发布服务器,添加链接服务器,并为 SQL Server 代理创建一组作业。

8 - 创建发布和订阅服务器

使用 SQLCMD 模式运行以下 T-SQL 脚本以便为数据库启用复制,然后在发布服务器、分发服务器和订阅服务器之间配置复制。

-- Set variables
:setvar username sourceLogin
:setvar password sourcePassword
:setvar source_db ReplTran_PUB
:setvar publication_name PublishData
:setvar object ReplTest
:setvar schema dbo
:setvar target_server "sql-mi-subscriber.wdec33262scj9dr27.database.chinacloudapi.cn"
:setvar target_username targetLogin
:setvar target_password targetPassword
:setvar target_db ReplTran_SUB

-- Enable replication for your source database
USE [$(source_db)]
EXEC sp_replicationdboption
  @dbname = N'$(source_db)',
  @optname = N'publish',
  @value = N'true';

-- Create your publication
EXEC sp_addpublication
  @publication = N'$(publication_name)',
  @status = N'active';

-- Configure your log reader agent
EXEC sp_changelogreader_agent
  @publisher_security_mode = 0,
  @publisher_login = N'$(username)',
  @publisher_password = N'$(password)',
  @job_login = N'$(username)',
  @job_password = N'$(password)';

-- Add the publication snapshot
EXEC sp_addpublication_snapshot
  @publication = N'$(publication_name)',
  @frequency_type = 1,
  @publisher_security_mode = 0,
  @publisher_login = N'$(username)',
  @publisher_password = N'$(password)',
  @job_login = N'$(username)',
  @job_password = N'$(password)';

-- Add the ReplTest table to the publication
EXEC sp_addarticle
  @publication = N'$(publication_name)',
  @type = N'logbased',
  @article = N'$(object)',
  @source_object = N'$(object)',
  @source_owner = N'$(schema)';

-- Add the subscriber
EXEC sp_addsubscription
  @publication = N'$(publication_name)',
  @subscriber = N'$(target_server)',
  @destination_db = N'$(target_db)',
  @subscription_type = N'Push';

-- Create the push subscription agent
EXEC sp_addpushsubscription_agent
  @publication = N'$(publication_name)',
  @subscriber = N'$(target_server)',
  @subscriber_db = N'$(target_db)',
  @subscriber_security_mode = 0,
  @subscriber_login = N'$(target_username)',
  @subscriber_password = N'$(target_password)',
  @job_login = N'$(username)',
  @job_password = N'$(password)';

-- Initialize the snapshot
EXEC sp_startpublication_snapshot
@publication = N'$(publication_name)';

9 - 修改代理参数

目前,Azure SQL 托管实例在与复制代理连接时会遇到一些后端问题。 我们正在处理中,暂时可通过调高复制代理的登录超时值来解决它们。

在发布服务器上运行以下 T-SQL 命令以增大登录超时值:

-- Increase login timeout to 150s
update msdb..sysjobsteps set command = command + N' -LoginTimeout 150'
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '%-LoginTimeout %'

如果需要将登录超时设置回默认值,请再次运行以下 T-SQL 命令:

-- Increase login timeout to 30
update msdb..sysjobsteps set command = command + N' -LoginTimeout 30'
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '%-LoginTimeout %'

若要应用这些更改,请重启所有三个代理。

10 - 测试复制

配置复制后,可以通过在发布服务器上插入新项并监视更改传播到订阅服务器来测试它。

运行以下 T-SQL 代码片段以查看订阅服务器上的行:

select * from dbo.ReplTest

运行以下 T-SQL 代码片段,在发布服务器上插入更多行,然后在订阅服务器上再次检查这些行。

INSERT INTO ReplTest (ID, c1) VALUES (15, 'pub')

清理资源

若要删除发布,请运行以下 T-SQL 命令:

-- Drops the publication
USE [ReplTran_PUB]
EXEC sp_droppublication @publication = N'PublishData'
GO

若要从数据库中删除复制选项,请运行以下 T-SQL 命令:

-- Disables publishing of the database
USE [ReplTran_PUB]
EXEC sp_removedbreplication
GO

若要禁用发布和分发,请运行以下 T-SQL 命令:

-- Drops the distributor
USE [master]
EXEC sp_dropdistributor @no_checks = 1
GO

可通过从资源组中删除 SQL 托管实例资源,然后删除资源组 SQLMI-Repl 来清理 Azure 资源。