教程:配置两个托管实例之间的复制Tutorial: Configure replication between two managed instances

适用于:是Azure SQL 托管实例 APPLIES TO: yesAzure SQL Managed Instance

通过事务复制可以将数据从一个数据库复制到 SQL Server 或 Azure SQL 托管实例中托管的另一个数据库。Transactional replication allows you to replicate data from one database to another hosted on either SQL Server or Azure SQL Managed Instance. SQL 托管实例可以是复制拓扑中的发布服务器、分发服务器或订阅服务器。SQL Managed Instance can be a publisher, distributor or subscriber in the replication topology. 有关可用配置,请参阅事务复制配置See transactional replication configurations for available configurations.

事务复制目前提供了用于 SQL 托管实例的公共预览版。Transactional replication is currently in public preview for SQL Managed Instance.

在本教程中,你将了解如何执行以下操作:In this tutorial, you learn how to:

  • 将托管实例配置为复制发布服务器和分发器。Configure a managed instance as a replication publisher and distributor.
  • 将托管实例配置为复制分发服务器。Configure a managed instance as a replication distributor.

在两个托管实例之间复制

本教程适用于经验丰富的受众,并假定用户熟悉如何在 Azure 中部署和连接到这两个托管实例和 SQL Server VM。This tutorial is intended for an experienced audience and assumes that the user is familiar with deploying and connecting to both managed instances and SQL Server VMs within Azure.

备注

要求Requirements

要配置 SQL 托管实例来充当发布服务器和/或分发服务器,需满足以下要求:Configuring SQL Managed Instance to function as a publisher and/or a distributor requires:

  • 发布服务器托管实例与分发服务器和订阅服务器位于同一虚拟网络中,或者已在所有三个实体的虚拟网络之间配置虚拟网络对等互连That the publisher managed instance is on the same virtual network as the distributor and the subscriber, or virtual network peering has been configured between the virtual networks of all three entities.
  • 连接时,在复制参与者之间使用 SQL 身份验证。Connectivity uses SQL Authentication between replication participants.
  • 适用于复制工作目录的 Azure 存储帐户共享。An Azure storage account share for the replication working directory.
  • 需要在托管实例的 NSG 安全规则中打开端口 445(TCP 出站)才能访问 Azure 文件共享。Port 445 (TCP outbound) is open in the security rules of NSG for the managed instances to access the Azure file share. 如果遇到错误 failed to connect to azure storage \<storage account name> with os error 53,则需要将出站规则添加到相应 SQL 托管实例子网的 NSG。If you encounter the error failed to connect to azure storage \<storage account name> with os error 53, you will need to add an outbound rule to the NSG of the appropriate SQL Managed Instance subnet.

1 - 创建资源组1 - Create a resource group

使用 Azure 门户创建名为 SQLMI-Repl 的资源组。Use the Azure portal to create a resource group with the name SQLMI-Repl.

2 - 创建托管实例2 - Create managed instances

使用 Azure 门户在同一虚拟网络和子网中创建两个 SQL 托管实例Use the Azure portal to create two SQL Managed Instances on the same virtual network and subnet. 例如,将两个托管实例命名为:For example, name the two managed instances:

  • sql-mi-pub(以及一些用于随机化的字符)sql-mi-pub (along with some characters for randomization)
  • sql-mi-sub(以及一些用于随机化的字符)sql-mi-sub (along with some characters for randomization)

还需要配置 Azure VM 来连接到托管实例。You will also need to configure an Azure VM to connect to your managed instances.

3 - 创建 Azure 存储帐户3 - Create an Azure storage account

为工作目录创建 Azure 存储帐户,并在存储帐户中创建文件共享Create an Azure storage account for the working directory, and then create a file share within the storage account.

复制采用 \\storage-account-name.file.core.chinacloudapi.cn\file-share-name 格式的文件共享路径Copy the file share path in the format of: \\storage-account-name.file.core.chinacloudapi.cn\file-share-name

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

复制采用 DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.chinacloudapi.cn 格式的存储访问密钥Copy the storage access keys in the format of: DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.chinacloudapi.cn

示例: DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;EndpointSuffix=core.chinacloudapi.cnExample: DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;EndpointSuffix=core.chinacloudapi.cn

有关详细信息,请参阅管理存储帐户访问密钥For more information, see Manage storage account access keys.

4 - 创建发布服务器数据库4 - Create a publisher database

使用 SQL Server Management Studio 连接到 sql-mi-pub 托管实例,然后运行以下 Transact-SQL (T-SQL) 代码来创建发布服务器数据库:Connect to your sql-mi-pub managed instance using SQL Server Management Studio and run the following Transact-SQL (T-SQL) code to create your publisher database:

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 - 创建订阅服务器数据库5 - Create a subscriber database

使用 SQL Server Management Studio 连接到 sql-mi-sub 托管实例,然后运行以下 T-SQL 代码来创建空的订阅服务器数据库:Connect to your sql-mi-sub managed instance using SQL Server Management Studio and run the following T-SQL code to create your empty subscriber database:

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 - 配置分发6 - Configure distribution

使用 SQL Server Management Studio 连接到 sql-mi-pub 托管实例,然后运行以下 T-SQL 代码来配置分发数据库。Connect to your sql-mi-pub managed instance using SQL Server Management Studio and run the following T-SQL code to configure your distribution database.

USE [master]
GO

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

7 - 将发布服务器配置为使用分发服务器7 - Configure publisher to use distributor

在发布服务器 SQL 托管实例 sql-mi-pub 上,将查询执行更改为 SQLCMD 模式,然后运行以下代码,将新的分发服务器注册到发布服务器。On your publisher SQL Managed Instance sql-mi-pub, change the query execution to SQLCMD mode and run the following code to register the new distributor with your publisher.

: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=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;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 参数仅使用反斜杠 (\)。Be sure to use only backslashes (\) for the file_storage parameter. 在连接到文件共享时使用正斜杠(/)可能会导致错误。Using a forward slash (/) can cause an error when connecting to the file share.

此脚本将在托管实例上配置一个本地发布服务器、添加链接服务器,并为 SQL Server 代理创建一组作业。This script configures a local publisher on the managed instance, adds a linked server, and creates a set of jobs for the SQL Server agent.

8 - 创建发布和订阅服务器8 - Create publication and subscriber

使用 SQLCMD 模式运行以下 T-SQL 脚本以便为数据库启用复制,然后在发布服务器、分发服务器和订阅服务器之间配置复制。Using SQLCMD mode, run the following T-SQL script to enable replication for your database, and configure replication between your publisher, distributor, and subscriber.

-- 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-sub.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 - 修改代理参数9 - Modify agent parameters

目前,Azure SQL 托管实例在与复制代理连接时会遇到一些后端问题。Azure SQL Managed Instance is currently experiencing some backend issues with connectivity with the replication agents. 我们正在处理中,暂时可通过调高复制代理的登录超时值来解决它们。While this issue is being addressed, the workaround is to increase the login timeout value for the replication agents.

在发布服务器上运行以下 T-SQL 命令以增大登录超时值:Run the following T-SQL command on the publisher to increase the login timeout:

-- 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 命令,将登录超时值重置为默认值:Run the following T-SQL command again to set the login timeout back to the default value, should you need to do so:

-- 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 %'

重启所有三个代理以应用这些更改。Restart all three agents to apply these changes.

10 - 测试复制10 - Test replication

配置复制后,可以通过以下方式测试复制:在发布服务器上插入新项,然后观察更改是否会传播到订阅服务器。Once replication has been configured, you can test it by inserting new items on the publisher and watching the changes propagate to the subscriber.

运行以下 T-SQL 代码片段以查看订阅服务器上的行:Run the following T-SQL snippet to view the rows on the subscriber:

select * from dbo.ReplTest

运行以下 T-SQL 代码片段以在发布服务器上插入附加的行,然后再次在订阅服务器上检查这些行。Run the following T-SQL snippet to insert additional rows on the publisher, and then check the rows again on the subscriber.

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

清理资源Clean up resources

若要删除发布,请运行以下 T-SQL 命令:To drop the publication, run the following T-SQL command:

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

若要从数据库中删除复制选项,请运行以下 T-SQL 命令:To remove the replication option from the database, run the following T-SQL command:

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

若要禁用发布和分发,请运行以下 T-SQL 命令:To disable publishing and distribution, run the following T-SQL command:

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

可通过从资源组中删除 SQL 托管实例资源,然后删除资源组 SQLMI-Repl 来清理 Azure 资源。You can clean up your Azure resources by deleting the SQL Managed Instance resources from the resource group and then deleting the resource group SQLMI-Repl.

后续步骤Next steps

你还可详细了解 Azure SQL 托管实例的事务复制的,或者了解如何在 SQL 托管实例发布服务器/分发服务器与 Azure VM 订阅服务器上的 SQL 之间配置复制。You can also learn more information about transactional replication with Azure SQL Managed Instance or learn to configure replication between a SQL Managed Instance publisher/distributor and a SQL on Azure VM subscriber.