教程：在Azure SQL 托管实例和SQL Server之间配置事务复制

applies to：Azure SQL 托管实例

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

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

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

先决条件

若要完成本教程，请确保具备以下先决条件：

• Azure 订阅。
• 体验在同一虚拟网络中部署两个 SQL 托管实例。
• 本地或Azure VM 上的SQL Server订阅服务器。 本教程使用Azure VM。
• SQL Server Management Studio （SSMS） 18.0 或更高版本。
• 最新版本的 Azure PowerShell。
• 端口 445 和 1433 允许Azure防火墙和Windows防火墙上的 SQL 流量。

所需的名称和网络设置

本教程使用以下资源名称和设置：

创建资源组

使用以下 PowerShell 代码片段创建新的资源组。

设置变量：

$ResourceGroupName = "SQLMI-Repl"
$Location = "East US 2"

创建资源组：

New-AzResourceGroup -Name $ResourceGroupName -Location $Location

验证是否已创建资源组：

Get-AzResourceGroup -Name $ResourceGroupName | Select-Object ResourceGroupName, Location

创建两个 SQL 托管实例

使用 Azure 门户在此新资源组中创建两个 SQL 托管实例。

• 发布服务器 SQL 托管实例的名称应为 sql-mi-publisher （以及用于随机化的几个字符），虚拟网络的名称应为 vnet-sql-mi-publisher。

• 分发服务器 SQL 托管实例的名称应为 sql-mi-distributor （以及用于随机化的几个字符），它应 与发布服务器 SQL 托管实例位于同一虚拟网络中。

  

有关创建 SQL 托管实例的详细信息，请参阅 Quickstart：创建 Azure SQL 托管实例。

创建SQL Server VM

使用 Azure 门户创建SQL Server虚拟机。 SQL Server虚拟机应具有以下特征：

• 名称：sql-vm-subscriber
• 图像：支持与 Azure SQL 托管实例进行事务复制的 SQL Server 版本，依据支持性矩阵。
• 资源组：与 SQL 托管实例相同
• 虚拟网络：sql-vm-subscriber-vnet

有关将SQL Server VM 部署到Azure的详细信息，请参阅 Quickstart：在 Azure 门户中的 Windows 虚拟机上创建SQL Server。

配置 VNet 对等互连

配置 VNet 对等互连，以启用两个 SQL 托管实例的虚拟网络与 SQL Server 虚拟网络之间的通信。

设置变量：

$SubscriptionId = '<SubscriptionID>'
$resourceGroup = 'SQLMI-Repl'
$pubvNet = 'sql-mi-publisher-vnet'
$subvNet = 'sql-vm-subscriber-vnet'
$pubsubName = 'Pub-to-Sub-Peer'
$subpubName = 'Sub-to-Pub-Peer'

检索虚拟网络：

$virtualNetwork1 = Get-AzVirtualNetwork -ResourceGroupName $resourceGroup -Name $pubvNet
$virtualNetwork2 = Get-AzVirtualNetwork -ResourceGroupName $resourceGroup -Name $subvNet

配置从发布服务器到订阅服务器的 VNet 对等互连：

Add-AzVirtualNetworkPeering `
  -Name $pubsubName `
  -VirtualNetwork $virtualNetwork1 `
  -RemoteVirtualNetworkId $virtualNetwork2.Id

配置从订阅服务器到发布服务器的 VNet 对等互连：

Add-AzVirtualNetworkPeering `
  -Name $subpubName `
  -VirtualNetwork $virtualNetwork2 `
  -RemoteVirtualNetworkId $virtualNetwork1.Id

验证发布者 VNet 上的对等互连状态（应返回 Connected）：

Get-AzVirtualNetworkPeering -ResourceGroupName $resourceGroup -VirtualNetworkName $pubvNet | Select-Object PeeringState

验证订阅者 VNet 上的对等连接状态（应返回 Connected）：

Get-AzVirtualNetworkPeering -ResourceGroupName $resourceGroup -VirtualNetworkName $subvNet | Select-Object PeeringState

建立 VNet 对等互连后，通过在SQL Server主机上启动SQL Server Management Studio（SSMS）并连接到这两个 SQL 托管实例来测试连接。 有关使用 SSMS 连接到SQL managed instance的详细信息，请参阅 使用 SSMS 连接到 SQL 托管实例。

创建专用 DNS 区域

专用 DNS 区域允许 SQL 托管实例与SQL Server之间的 DNS 路由。

创建专用区域

1. 登录到 Azure 门户。

2. 选择 创建资源创建新的Azure资源。

3. 在Azure 市场上搜索 private dns zone。

4. 选择Microsoft发布的 专用 DNS 区域资源，然后选择 Create 以创建 DNS 区域。

5. 从下拉列表中选择订阅和资源组。

6. 为 DNS 区域提供任意名称，如 repldns.com。

   截图显示在 Azure portal 中创建专用 DNS 区域。

7. 选择“查看 + 创建”。 查看专用 DNS 区域的参数，然后选择“ 创建 ”以创建资源。

创建 A 记录

1. 转到新的 专用 DNS 区域并选择 Overview。

2. 选择+ 记录集以创建新的 A 记录。

3. 提供SQL Server VM 的名称以及专用内部 IP 地址。

   

4. 选择“确定”以创建 A 记录。

链接虚拟网络

1. 转到新的 专用 DNS 区域并选择虚拟网络链接。

2. 选择“+ 添加”。

3. 提供链接的名称，如 Pub-link。

4. 从下拉列表中选择订阅，然后选择发布者 SQL 托管实例的虚拟网络。

5. 选中“启用自动注册”旁边的框。

   

6. 选择“确定”以链接虚拟网络。

7. 重复这些步骤，为订阅服务器虚拟网络添加一个链接，并对其命名，例如 Sub-link。

创建Azure存储帐户

为工作目录创建Azure存储帐户，然后在存储帐户中创建文件共享。

存储配置值

配置分发时需要以下值：

• 工作目录路径格式： \\<storage-account-name>.file.core.windows.net\<file-share-name>
• 存储连接字符串格式：DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=<key>;EndpointSuffix=core.windows.net

本教程中使用的示例值：

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

创建数据库

在发布服务器 SQL 托管实例上创建新数据库。 为此，请执行下列步骤：

1. 在 SQL Server 上启动SQL Server Management Studio。
2. 连接到发布服务器 SQL 托管实例（sql-mi-publisher）。
3. 打开 “新建查询” 窗口，并执行以下 T-SQL 查询。

如果数据库存在，则删除它并创建一个新数据库。

USE [master];
GO

IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name = 'ReplTutorial')
BEGIN
    DROP DATABASE ReplTutorial;
END
GO

CREATE DATABASE [ReplTutorial];
GO

创建复制测试表：

USE [ReplTutorial];
GO

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

插入示例数据：

USE [ReplTutorial];
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

预期输出：ID 为 2、3、4、5 和 6 的 5 行。

配置分发

建立连接并拥有示例数据库后，可以在分发服务器 SQL 托管实例（sql-mi-distributor）上配置分发。

分发配置参数

在配置分发之前收集以下值：

配置分发服务器实例

1. 在 SQL Server 上启动SQL Server Management Studio。
2. 连接到分发服务器 SQL 托管实例（sql-mi-distributor）。
3. 打开 “新建查询” 窗口并运行以下命令。

添加分销商：

EXECUTE sp_adddistributor
    @distributor = 'sql-mi-distributor.b6bf57.database.windows.net',
    @password = '<distributor_admin_password>';

创建分发数据库：

EXECUTE sp_adddistributiondb @database = N'distribution';

验证是否已创建分发数据库：

SELECT name FROM sys.databases WHERE name = 'distribution';

将发布者添加到分销商：

EXECUTE sp_adddistpublisher
    @publisher = 'sql-mi-publisher.b6bf57.database.windows.net',
    @distribution_db = N'distribution',
    @security_mode = 0,
    @login = N'azureuser',
    @password = N'<publisher_password>',
    @working_directory = N'\\replstorage.file.core.windows.net\replshare',
    @storage_connection_string = N'DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net';

在发布商处注册分销商

1. 连接到发布服务器 SQL 托管实例（sql-mi-publisher）。
2. 打开 “新建查询 ”窗口并运行以下命令以注册分发服务器：

USE master;
GO

EXECUTE sys.sp_adddistributor
    @distributor = 'sql-mi-distributor.b6bf57.database.windows.net',
    @password = '<distributor_admin_password>';

验证分发服务器是否已注册：

SELECT * FROM sys.servers WHERE is_distributor = 1;

创建出版物

配置分发后，可以创建发布项。 为此，请执行下列步骤：

1. 在 SQL Server 上启动SQL Server Management Studio。

2. 连接到发布服务器 SQL 托管实例（sql-mi-publisher）。

3. 在 对象资源管理器 中，展开 Replication 节点，然后右键单击 Local Publication 文件夹。 单击“新建发布...”。

4. 选择“下一步”，离开“欢迎”页。

5. 在“发布数据库”页上，选择之前创建的 数据库。 选择“下一页”。

6. 在“发布类型”页上，选择“事务发布” 。 选择“下一页”。

7. 在“文章”页面上，勾选“表格”旁边的框。 选择“下一页”。

8. 在“筛选器表行”页上，选择“下一步”而不添加任何筛选器 。

9. 在 快照代理 页上，选中立即创建快照旁边的框，并保留快照以初始化订阅。 选择“下一页”。

10. 在 Agent Security 页上，选择“安全设置...”。提供用于快照代理并连接到发布服务器的SQL Server登录凭据。 选择 OK 关闭 快照代理 Security 页。 选择“下一页”。

    

11. 在“向导操作”页上，选择“创建发布”并（视情况）选择“生成包含创建发布的步骤的脚本文件”（如果要保存此脚本以供以后使用） 。

12. 在 “完成向导 ”页上，为出版物 ReplTest命名，然后选择“ 下一步 ”以创建出版物。

13. 创建发布后，刷新复制节点在对象资源管理器中，然后展开本地发布以查看新发布。

创建订阅

创建发布后，可以创建订阅。 为此，请执行下列步骤：

1. 在 SQL Server 上启动SQL Server Management Studio。
2. 连接到发布服务器 SQL 托管实例（sql-mi-publisher）。
3. 打开 New Query 窗口并运行以下Transact-SQL命令。 使用专用 DNS 区域中配置的 DNS 名称作为订阅者名称的一部分。

订阅参数

添加订阅：

USE [ReplTutorial];
GO

EXEC sp_addsubscription
    @publication = N'ReplTest',
    @subscriber = N'sql-vm-subscriber.repldns.com',
    @destination_db = N'ReplSub',
    @subscription_type = N'Push',
    @sync_type = N'automatic',
    @article = N'all',
    @update_mode = N'read only',
    @subscriber_type = 0;

添加推送订阅代理：

EXEC sp_addpushsubscription_agent
    @publication = N'ReplTest',
    @subscriber = N'sql-vm-subscriber.repldns.com',
    @subscriber_db = N'ReplSub',
    @job_login = N'azureuser',
    @job_password = '<Complex Password>',
    @subscriber_security_mode = 0,
    @subscriber_login = N'azureuser',
    @subscriber_password = '<Complex Password>',
    @dts_package_location = N'Distributor';
GO

验证是否已创建订阅：

SELECT * FROM distribution.dbo.MSsubscriptions;

测试复用

配置复制后，可对其进行测试，方法是：在发布服务器上插入新项并监视更改传播到订阅服务器。

查看订阅服务器上的初始数据

连接到SQL Server订阅服务器并运行以下查询：

USE ReplSub;
GO

SELECT * FROM dbo.ReplTest;

预期输出：ID 为 2、3、4、5 和 6 的 5 行（发布者的初始数据）。

在发布者处插入新数据

连接到发布服务器 SQL 托管实例（sql-mi-publisher）并插入一个新行：

USE ReplTutorial;
GO

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

验证发布服务器上的插入：

SELECT * FROM ReplTest WHERE ID = 15;

验证向订阅者的复制

片刻后，连接到订阅者并验证已复制的新行是否正确：

USE ReplSub;
GO

SELECT * FROM dbo.ReplTest WHERE ID = 15;

预期输出：1 行记录，ID 为 15，c1 值为 'pub'。

清理资源

1. 请在Azure 门户中进入您的资源组。
2. 选择 SQL 托管实例，然后选择“ 删除”。 键入 yes 文本框以确认要删除资源，然后选择“ 删除”。 此过程可能需要一些时间才能在后台完成，在完成之前，将无法删除 虚拟群集 或任何其他依赖资源。 监视 “活动 ”选项卡中的删除，以确认 SQL 托管实例已删除。
3. 删除 SQL 托管实例后，请在资源组中选择 虚拟群集 ，然后选择 “删除”。 键入 yes 文本框以确认要删除资源，然后选择“ 删除”。
4. 删除任何剩余资源。 键入 yes 文本框以确认要删除资源，然后选择“ 删除”。
5. 选择“删除资源组”，键入资源组的名称，然后选择“myResourceGroup”来删除资源组。

已知错误

不支持Windows登录

Exception Message: Windows logins are not supported in this version of SQL Server.

代理配置了Windows登录名，需要改用SQL Server登录名。 使用Agent Security 页面的 Publication properties 更改登录凭据为 SQL Server 登录名。

无法连接到Azure 存储

Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare' Failed to connect to Azure Storage '' with OS error: 53.
2019-11-19 02:21:05.07 Obtained Azure Storage Connection String for replstorage
2019-11-19 02:21:05.07 Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare'
2019-11-19 02:21:31.21 Failed to connect to Azure Storage '' with OS error: 53.

这可能是因为端口 445 在Azure防火墙、Windows防火墙或两者中关闭。

Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare' Failed to connect to Azure Storage '' with OS error: 55.

在文件共享的文件路径中使用正斜杠而不是反斜杠也可能导致此错误。

• 这是可接受的： \\replstorage.file.core.windows.net\replshare
• 这可能会导致 OS 55 错误：\\replstorage.file.core.windows.net/replshare

无法连接到订阅用户

The process could not connect to Subscriber 'SQL-VM-SUBSCRIBER
Could not open a connection to SQL Server [53].
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

可能的解决方法：

• 确保端口 1433 处于打开状态。
• 确保在订阅服务器上启用 TCP/IP。
• 确认在创建订阅服务器时使用了 DNS 名称。
• 验证虚拟网络是否已正确链接到专用 DNS 区域。
• 验证是否已正确配置 A 记录。
• 验证 VNet 对等连接配置是否正确。

没有可以订阅的出版物。

在使用“新建订阅”向导添加新订阅时，在发布页面，你可能会发现没有列出为可用选项的数据库和发布，并且你可能会看到以下错误消息。

There are no publications to which you can subscribe, either because this server has no publications or because you do not have sufficient privileges to access the publications.

虽然此错误消息可能准确，实际上您连接的发行商没有可用的出版物，或者您缺少足够的权限，较旧版本的 SQL Server Management Studio 也可能导致此错误。 尝试升级到 SQL Server Management Studio 18.0 或更高版本，将其排除为根本原因。

相关内容

• 什么是Azure SQL 托管实例？
• 在 Azure SQL 托管实例中进行 SQL Server 审计
• Always Encrypted
• 在 Azure SQL 托管实例中配置高级威胁防护
• 动态数据屏蔽
• 行级安全
• 透明数据加密 (TDE)