使用SQL Server事务复制将SQL Server数据库迁移到Azure SQL数据库

In this guide, we’ll discuss more about migrating a SQL Server database to Azure SQL Database using SQL Server Transactional Replication.

在本指南中，我们将讨论有关使用SQL Server事务复制将SQL Server数据库迁移到Azure SQL数据库的更多信息。

With Azure SQL database, it is possible to replicate your on-premise database(s) with database(s) on the cloud in order to ensure application availability. In this article, we will see how we set up SQL Server transactional replication between an on-premise database and a database on Azure. We will deal with setting up SQL Server transactional replication—only supported feature of Azure SQL database.

使用Azure SQL数据库，可以将内部部署数据库与云上的数据库复制，以确保应用程序可用性。 在本文中，我们将了解如何在本地数据库和Azure上的数据库之间设置SQL Server事务复制。 我们将处理设置SQL Server事务复制-Azure SQL数据库仅受支持的功能。

数据库迁移 (Database Migration)

In this case, we are performing a database migration with a very short downtime using SQL Server transactional replication. I’ll discuss more in detail to setup and configure replication.

在这种情况下，我们将使用SQL Server事务复制在非常短的停机时间内执行数据库迁移。 我将详细讨论设置和配置复制。

1. Note:注意：
2. Only transactional replication is supported on Azure SQL Database. Other types such as Peer-to-peer or Merge Replication topology aren’t supported
3. Azure SQL数据库仅支持事务复制。 不支持其他类型，例如点对点或合并复制拓扑
4. Azure SQL database can only be used as a subscriber. Setting up publications is not allowed on Azure
5. Azure SQL数据库只能用作订阅服务器。 在Azure上不允许设置发布

先决条件 (Prerequisites )

To be able to set up SQL Server transactional replication on Azure, you need to have some familiarity with Microsoft SQL Server Transactional Replication as well as the Azure SQL database cloud services.

为了能够在Azure上设置SQL Server事务复制，您需要熟悉Microsoft SQL Server事务复制以及Azure SQL数据库云服务。

版本号 (Versions)

It is mandatory that you meet the following requirements in order to set up SQL Server transactional replication on Microsoft Azure SQL Database.

为了在Microsoft Azure SQL数据库上设置SQL Server事务复制，必须满足以下要求。

• • SQL Server 2017
    SQL Server 2017
  • SQL Server 2016
    SQL Server 2016
  • SQL Server 2014 SP1 CU3
    SQL Server 2014 SP1 CU3
  • SQL Server 2014 RTM CU10
    SQL Server 2014 RTM CU10
  • SQL Server 2012 SP2 CU8 or SP3
    SQL Server 2012 SP2 CU8或SP3
• Use the latest versions of SQL Server Management Studio as well as SQL Server Data Tools in order to be able to use all the features of Azure SQL Database
  使用最新版本SQL Server Management Studio以及SQL Server数据工具，以便能够使用Azure SQL数据库的所有功能
• You can use SQL Server Management Studio to configure replication, or execute T-SQL statements on the publisher. Azure Portal cannot be used to configure replication
• 您可以使用SQL Server Management Studio配置复制，或在发布服务器上执行T-SQL语句。 Azure门户不能用于配置复制
• The only way you can authenticate on Azure for replication is use SQL Server authentication logins
  您可以在Azure上进行复制身份验证的唯一方法是使用SQL Server身份验证登录名
• Tables that are being replicated must have a primary key
  要复制的表必须具有主键
• Of course, you should already have an Azure subscription
  当然，您应该已经有一个Azure订阅
• The Azure SQL database subscriber is region-agnostic
  Azure SQL数据库订阅者与区域无关
• You can use a single publication on SQL Server, to support both, Azure as well as SQL Server (on-premises and SQL Server in an Azure virtual machine) subscriptions
  您可以在SQL Server上使用单个发布来支持Azure和SQL Server（本地和Azure虚拟机中SQL Server）订阅
• The on-premise SQL Server must be used to manage, monitor and troubleshoot replication
  本地SQL Server必须用于管理，监视复制并对其进行故障排除
• Only push subscriptions to Azure are supported
  仅支持对Azure的推送订阅
• sp_addsubscription sp_addsubscription中仅支持@subscriber_type = 0
• Bi-directional, immediate, updatable, or peer to peer replication are not supported by Azure SQL Database
  Azure SQL数据库不支持双向，即时，可更新或对等复制

数据库迁移方案 (Database Migration Scenario)

1. Use SQL Server transactional replication to replicate data from your on-premise SQL Server database to Azure
   使用SQL Server事务复制将数据从本地SQL Server数据库复制到Azure
2. Redirect the client applications to update the Azure SQL database copy
   重定向客户端应用程序以更新Azure SQL数据库副本
3. Stop updating the SQL Server version of the table after the redirection by removing the publication
   通过删除发布，重定向后停止更新表SQL Server版本

设置SQL Server事务复制 (Setting up SQL Server Transactional Replication)

Let’s set up SQL Server transactional replication to Azure. In this guide, I will use the Adventure Works database for setting up the replication from SQL Server to Azure SQL Database. You can