教程:使用 DMS 将 RDS SQL Server 联机迁移到 Azure SQL 数据库或 Azure SQL 托管实例Tutorial: Migrate RDS SQL Server to Azure SQL Database or an Azure SQL Managed Instance online using DMS

可以使用 Azure 数据库迁移服务以最少停机时间将数据库从 RDS SQL Server 实例迁移到 Azure SQL 数据库Azure SQL 托管实例You can use the Azure Database Migration Service to migrate the databases from an RDS SQL Server instance to Azure SQL Database or an Azure SQL Managed Instance with minimal downtime. 在本教程中,我们使用 Azure 数据库迁移服务将还原到 SQL Server 2012(或更高版本)RDS SQL Server 实例的 Adventureworks2012 数据库迁移到 SQL 数据库或 SQL 托管实例。In this tutorial, you migrate the Adventureworks2012 database restored to an RDS SQL Server instance of SQL Server 2012 (or later) to SQL Database or a SQL Managed Instance by using the Azure Database Migration Service.

本教程介绍如何执行下列操作:In this tutorial, you learn how to:

  • 在 Azure SQL 数据库或 SQL 托管实例中创建数据库。Create a database in Azure SQL Database or a SQL Managed Instance.
  • 通过使用数据迁移助手迁移示例架构。Migrate the sample schema by using the Data Migration Assistant.
  • 创建 Azure 数据库迁移服务的实例。Create an instance of the Azure Database Migration Service.
  • 使用 Azure 数据库迁移服务创建迁移项目。Create a migration project by using the Azure Database Migration Service.
  • 运行迁移。Run the migration.
  • 监视迁移。Monitor the migration.
  • 下载迁移报告。Download a migration report.

备注

使用 Azure 数据库迁移服务执行联机迁移需要基于“高级”定价层创建实例。Using the Azure Database Migration Service to perform an online migration requires creating an instance based on the Premium pricing tier. 有关详细信息,请参阅 Azure 数据库迁移服务定价页。For more information, see the Azure Database Migration Service pricing page.

重要

为获得最佳迁移体验,Azure 建议在目标数据库所在的 Azure 区域中创建 Azure 数据库迁移服务的实例。For an optimal migration experience, Azure recommends creating an instance of the Azure Database Migration Service in the same Azure region as the target database. 跨区域或地理位置移动数据可能会减慢迁移过程并引入错误。Moving data across regions or geographies can slow down the migration process and introduce errors.

提示

使用 Azure 数据库迁移服务将数据库迁移到 Azure 时,可以进行脱机或联机迁移。 When you migrate databases to Azure by using Azure Database Migration Service, you can do an offline or an online migration. 使用脱机迁移时,应用程序停机时间从迁移开始时算起。With an offline migration, application downtime starts when the migration starts. 使用联机迁移时,停机时间仅限在迁移结束时进行转换的那段时间。With an online migration, downtime is limited to the time to cut over at the end of migration. 建议对脱机迁移进行测试,以便确定其停机时间是否可以接受;如果不能接受,请进行联机迁移。We suggest that you test an offline migration to determine whether the downtime is acceptable; if not, do an online migration.

本文介绍如何从 RDS SQL Server 联机迁移到 SQL 数据库或 SQL 托管实例。This article describes an online migration from RDS SQL Server to SQL Database or a SQL Managed Instance.

先决条件Prerequisites

要完成本教程,需要:To complete this tutorial, you need to:

  • 创建 RDS SQL Server 数据库Create an RDS SQL Server database.

  • 在 Azure 门户的 Azure SQL 数据库中创建数据库在 SQL 托管实例中创建数据库,然后创建名为“AdventureWorks2012”的空数据库。Create a database in Azure SQL Database in the Azure portal or Create a database in SQL Managed Instance, and then create an empty database named AdventureWorks2012.

  • 下载并安装数据迁移助手 (DMA) v3.3 或更高版本。Download and install the Data Migration Assistant (DMA) v3.3 or later.

  • 使用 Azure 资源管理器部署模型为 Azure 数据库迁移服务创建 Azure 虚拟网络。Create a Azure Virtual Network for Azure Database Migration Service by using the Azure Resource Manager deployment model. 若要迁移到 SQL 托管实例,请确保在用于 SQL 托管实例的同一虚拟网络的不同子网中创建 DMS 实例。If you're migrating to a SQL Managed Instance, make sure to create the DMS instance in the same virtual network used for the SQL Managed Instance, but in a different subnet. 或者,如果对 DMS 使用不同的虚拟网络,则需要在两个虚拟网络之间创建虚拟网络对等互连。Alternately, if you use a different virtual network for DMS, you need to create a virtual network peering between the two virtual networks. 有关创建虚拟网络的详细信息,请参阅虚拟网络文档,尤其是提供了分步详细信息的快速入门文章。For more information about creating a virtual network, see the Virtual Network Documentation, and especially the quickstart articles with step-by-step details.

    备注

    在设置虚拟网络期间,如果将 ExpressRoute 与 Azure 的网络对等互连一起使用,请将以下服务终结点添加到将在其中预配服务的子网:During virtual network setup, if you use ExpressRoute with network peering to Azure, add the following service endpoints to the subnet in which the service will be provisioned:

    • 目标数据库终结点(例如,SQL 终结点、Cosmos DB 终结点等)Target database endpoint (for example, SQL endpoint, Cosmos DB endpoint, and so on)
    • 存储终结点Storage endpoint
    • 服务总线终结点Service bus endpoint

    Azure 数据库迁移服务缺少 Internet 连接,因此必须提供此配置。This configuration is necessary because the Azure Database Migration Service lacks internet connectivity.

  • 确保虚拟网络网络安全组规则未阻止到 Azure 数据库迁移服务的以下入站通信端口:443、53、9354、445、12000。Ensure that your virtual network Network Security Group rules don't block the following inbound communication ports to Azure Database Migration Service: 443, 53, 9354, 445, 12000. 有关虚拟网络 NSG 流量筛选的更多详细信息,请参阅使用网络安全组筛选网络流量一文。For more detail on virtual network NSG traffic filtering, see the article Filter network traffic with network security groups.

  • 配置针对数据库引擎访问的 Windows 防火墙Configure your Windows Firewall for database engine access.

  • 打开 Windows 防火墙,使 Azure 数据库迁移服务能够访问源 SQL Server(默认情况下为 TCP 端口 1433)。Open your Windows firewall to allow the Azure Database Migration Service to access the source SQL Server, which by default is TCP port 1433.

  • 对于 SQL 数据库,请创建服务器级防火墙规则,以允许 Azure 数据库迁移服务访问目标数据库。For SQL Database, create a server-level firewall rule to allow the Azure Database Migration Service access to the target database. 提供用于 Azure 数据库迁移服务的虚拟网络子网范围。Provide the subnet range of the virtual network used for the Azure Database Migration Service.

  • 确保用于连接到源 RDS SQL Server 实例的凭据与属于“Processadmin”服务器角色的帐户相关联,并与属于要迁移的所有数据库上的“db_owner”数据库角色的帐户相关联。Ensure that the credentials used to connect to the source RDS SQL Server instance are associated with an account that is a member of “Processadmin” server role and a member of the “db_owner” database roles on all databases that are to be migrated.

  • 确保用于连接到目标数据库的凭据具有 SQL 数据库中目标数据库的 CONTROL DATABASE 权限;如果迁移到 SQL 托管实例中的数据库,该帐户需是 sysadmin 角色的成员。Ensure that the credentials used to connect to target database have CONTROL DATABASE permission on the target database in SQL Database and a member of the sysadmin role if migrating to a database in SQL Managed Instance.

  • 源 RDS SQL Server 版本必须为 SQL Server 2012 和更高版本。The source RDS SQL Server version must be SQL Server 2012 and above. 若要确定 SQL Server 实例正在运行的版本,请参阅如何确定 SQL Server 及其组件的版本、版本类别和更新级别一文。To determine the version that you SQL Server instance is running, see the article How to determine the version, edition, and update level of SQL Server and its components.

  • 在选择迁移的 RDS SQL Server 数据库和所有用户表中启用变更数据捕获 (CDC)。Enable Change Data Capture (CDC) on the RDS SQL Server database and all user table(s) selected for migration.

    备注

    可使用以下脚本在 RDS SQL Server 数据库中启用 CDC。You can use the script below to enable CDC on an RDS SQL Server database.

    exec msdb.dbo.rds_cdc_enable_db 'AdventureWorks2012'
    

    可使用以下脚本在所有表中启用 CDC。You can use the script below to enable CDC on all tables.

    use <Database name>
    go
    exec sys.sp_cdc_enable_table 
    @source_schema = N'Schema name', 
    @source_name = N'table name', 
    @role_name = NULL, 
    @supports_net_changes = 1 --for PK table 1, non PK tables 0
    GO
    
  • 禁用目标数据库的数据库触发器。Disable database triggers on the target database.

    备注

    可以使用以下查询查找目标数据库的数据库触发器:You can find the database triggers on the target database by using the following query:

    Use <Database name>
    go
    select * from sys.triggers
    DISABLE TRIGGER (Transact-SQL)
    

    有关详细信息,请参阅 DISABLE TRIGGER (Transact-SQL) 一文。For more information, see the article DISABLE TRIGGER (Transact-SQL).

迁移示例架构Migrate the sample schema

请使用 DMA 来迁移架构。Use DMA to migrate the schema.

备注

在 DMA 中创建迁移项目之前,请确保已按照先决条件中的说明在 SQL 数据库或 SQL 托管实例中预配了数据库。Before you create a migration project in DMA, be sure that you have already provisioned a database in SQL Database or SQL Managed Instance as mentioned in the prerequisites. 本教程假设数据库的名称是“AdventureWorks2012”,但你可以根据需要提供任意名称。For purposes of this tutorial, the name of the database is assumed to be AdventureWorks2012, but you can provide whatever name you wish.

若要迁移 AdventureWorks2012 架构,请执行以下步骤:To migrate the AdventureWorks2012 schema, perform the following steps:

  1. 在数据迁移助手中,选择“新建 (+)”图标,然后在“项目类型”下选择“迁移” 。In the Data Migration Assistant, select the New (+) icon, and then under Project type, select Migration.

  2. 指定项目名称,在“源服务器类型”文本框中,选择“SQL Server”,然后在“目标服务器类型”文本框中,选择“Azure SQL 数据库”。Specify a project name, in the Source server type text box, select SQL Server, and then in the Target server type text box, select Azure SQL Database.

    备注

    对于目标服务器类型,请选择“Azure SQL 数据库”以同时迁移到 Azure SQL 数据库和 SQL 托管实例。For Target server type, select Azure SQL Database for migrating to both Azure SQL Database and as well as to SQL Managed Instance.

  3. 在“迁移范围”下,选择“仅架构”。Under Migration Scope, select Schema only.

    在执行前面的步骤后,DMA 界面应如下图所示:After performing the previous steps, the DMA interface should appear as shown in the following graphic:

    创建数据迁移助手项目

  4. 选择“创建”来创建项目。Select Create to create the project.

  5. 在 DMA 中指定 SQL Server 的源连接详细信息,选择“连接”,然后选择“AdventureWorks2012”数据库。In DMA, specify the source connection details for your SQL Server, select Connect, and then select the AdventureWorks2012 database.

    数据迁移助手源连接详细信息

  6. 在“连接到目标服务器”下选择“下一步”,指定 SQL 数据库或 SQL 托管实例中的数据库的目标连接详细信息,选择“连接”,然后选择提前预配的“AdventureWorksAzure”数据库。 Select Next, under Connect to target server, specify the target connection details for the database in SQL Database or SQL Managed Instance, select Connect, and then select the AdventureWorksAzure database you pre-provisioned.

    数据迁移助手目标连接详细信息

  7. 选择“下一步”,以转到“选择对象”屏幕,可以在其中指定需要部署的“AdventureWorks2012”数据库中的架构对象。Select Next to advance to the Select objects screen, on which you can specify the schema objects in the AdventureWorks2012 database that need to be deployed.

    默认情况下,选择所有对象。By default, all objects are selected.

    生成 SQL 脚本

  8. 选择“生成 SQL 脚本”以创建 SQL 脚本,然后检查脚本是否有任何错误。Select Generate SQL script to create the SQL scripts, and then review the scripts for any errors.

    架构脚本

  9. 选择“部署架构”以部署架构,并在部署架构后检查目标是否存在任何异常情况。Select Deploy schema to deploy the schema, and then after the schema is deployed, check the target for any anomalies.

    部署架构

注册 Microsoft.DataMigration 资源提供程序Register the Microsoft.DataMigration resource provider

  1. 登录到 Azure 门户,选择“所有服务”,然后选择“订阅”。Sign in to the Azure portal, select All services, and then select Subscriptions.

    显示门户订阅

  2. 选择要在其中创建 Azure 数据库迁移服务实例的订阅,再选择“资源提供程序”。Select the subscription in which you want to create the instance of the Azure Database Migration Service, and then select Resource providers.

    显示资源提供程序

  3. 搜索“迁移”,然后选择“注册”。Search for migration, and then select Register.

    注册资源提供程序

创建实例Create an instance

  1. 在 Azure 门户中,选择 + 创建资源,搜索 Azure 数据库迁移服务,然后从下拉列表选择Azure 数据库迁移服务In the Azure portal, select + Create a resource, search for Azure Database Migration Service, and then select Azure Database Migration Service from the drop-down list.

    Azure 市场

  2. 在“Azure 数据库迁移服务”屏幕上,选择“创建” 。On the Azure Database Migration Service screen, select Create.

    创建 Azure 数据库迁移服务实例

  3. 在“创建迁移服务”屏幕上,为服务、订阅以及新的或现有资源组指定名称。On the Create Migration Service screen, specify a name for the service, the subscription, and a new or existing resource group.

  4. 选择要在其中创建 Azure 数据库迁移服务实例的位置。Select the location in which you want to create the instance of the Azure Database Migration Service.

  5. 选择现有虚拟网络或新建一个。Select an existing virtual network or create a new one.

    虚拟网络为 Azure 数据库迁移服务提供源 SQL Server 和目标 SQL 数据库或 SQL 托管实例的访问权限。The virtual network provides Azure Database Migration Service with access to the source SQL Server and the target SQL Database or SQL Managed Instance.

    有关如何在 Azure 门户中创建虚拟网络的详细信息,请参阅使用 Azure 门户创建虚拟网络一文。For more information about how to create a virtual network in the Azure portal, see the article Create a virtual network using the Azure portal.

  6. 选择定价层;对于此联机迁移,请务必选择“高级”定价层。Select a pricing tier; for this online migration, be sure to select the Premium pricing tier.

    有关成本和定价层的详细信息,请参阅价格页For more information on costs and pricing tiers, see the pricing page.

    配置 Azure 数据库迁移服务实例设置

  7. 选择“创建”来创建服务。Select Create to create the service.

创建迁移项目Create a migration project

创建服务后,在 Azure 门户中找到并打开它,然后创建一个新的迁移项目。After the service is created, locate it within the Azure portal, open it, and then create a new migration project.

  1. 在 Azure 门户中,选择“所有服务”,搜索 Azure 数据库迁移服务,然后选择“Azure 数据库迁移服务”。In the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.

    查找 Azure 数据库迁移服务的所有实例

  2. 在“Azure 数据库迁移服务”屏幕上,搜索你创建的 Azure 数据库迁移服务实例名称,然后选择该实例。On the Azure Database Migration Services screen, search for the name of the Azure Database Migration Service instance that you created, and then select the instance.

    查找 Azure 数据库迁移服务实例

  3. 选择“+ 新建迁移项目”。Select + New Migration Project.

  4. 在“新建迁移项目”屏幕上指定项目名称,在“源服务器类型”文本框中选择“AWS RDS for SQL Server”,在“目标服务器类型”文本框中选择“Azure SQL 数据库”。 On the New migration project screen, specify a name for the project, in the Source server type text box, select AWS RDS for SQL Server, in the Target server type text box, select Azure SQL Database.

    备注

    对于目标服务器类型,请选择“Azure SQL 数据库”以同时迁移到 SQL 数据库和 SQL 托管实例。For Target server type, select Azure SQL Database for migrating to both SQL Database and as well as to SQL Managed Instance.

  5. 在“选择活动类型”部分选择“联机数据迁移”。 In the Choose type of activity section, select Online data migration.

    重要

    请确保选择“联机数据迁移”;此方案不支持脱机迁移。Be sure to select Online data migration; offline migrations are not supported for this scenario.

    创建数据库迁移服务项目

    备注

    也可以现在就选择“仅创建项目”来创建迁移项目,在以后再执行迁移。Alternately, you can choose Create project only to create the migration project now and execute the migration later.

  6. 选择“保存” 。Select Save.

  7. 选择“创建并运行活动”,以便创建项目并运行迁移活动。Select Create and run activity to create the project and run the migration activity.

    创建并运行数据库迁移服务活动

指定源详细信息Specify source details

  1. 在“迁移源详细信息”屏幕上,指定源 SQL Server 实例的连接详细信息。On the Migration source detail screen, specify the connection details for the source SQL Server instance.

    请确保为源 SQL Server 实例名称使用完全限定的域名 (FQDN)。Make sure to use a Fully Qualified Domain Name (FQDN) for the source SQL Server instance name.

  2. 如果尚未在源服务器上安装受信任的证书,请选中“信任服务器证书”复选框。If you haven't installed a trusted certificate on your source server, select the Trust server certificate check box.

    如果没有安装受信任的证书,SQL Server 会在实例启动时生成自签名证书。When a trusted certificate isn't installed, SQL Server generates a self-signed certificate when the instance is started. 此证书用于加密客户端连接的凭据。This certificate is used to encrypt the credentials for client connections.

    注意

    使用自签名证书加密的 TLS 连接不提供强安全性。TLS connections that are encrypted using a self-signed certificate do not provide strong security. 它们易遭受中间人攻击。They are susceptible to man-in-the-middle attacks. 在生产环境中或在连接到 Internet 的服务器上不应依赖使用自签名证书的 TLS。You should not rely on TLS using self-signed certificates in a production environment or on servers that are connected to the internet.

    源详细信息

指定目标详细信息Specify target details

  1. 选择“保存”,然后在“迁移目标详细信息”屏幕上,为 Azure 中的目标数据库指定连接详细信息。Select Save, and then on the Migration target details screen, specify the connection details for the target database in Azure.

    选择目标

  2. 选择“保存”,然后在“映射到目标数据库”屏幕上,映射源和目标数据库以进行迁移。 Select Save, and then on the Map to target databases screen, map the source and the target database for migration.

    如果目标数据库包含的数据库名称与源数据库的相同,则 Azure 数据库迁移服务会默认选择目标数据库。If the target database contains the same database name as the source database, the Azure Database Migration Service selects the target database by default.

    映射到目标数据库

  3. 在“选择表”屏幕上选择“保存”,展开表列表,如何查看受影响字段的列表 。Select Save, on the Select tables screen, expand the table listing, and then review the list of affected fields.

    Azure 数据库迁移服务会自动选择目标数据库上存在的所有空源表。The Azure Database Migration Service auto selects all the empty source tables that exist on the target database. 若要重新迁移已包含数据的表,需要在此屏幕上显式选择表。If you want to remigrate tables that already include data, you need to explicitly select the tables on this screen.

    选择表

  4. 设置以下“高级联机迁移设置”后,选择“保存”。 Select Save, after setting the following Advanced online migration settings.

    设置Setting 说明Description
    可并行加载的最大表数目Maximum number of tables to load in parallel 指定 DMS 在迁移期间并行执行的表数。Specifies the number of tables that DMS executes in parallel during the migration. 默认值为 5,但可以根据任何 POC 迁移方案的具体迁移需求,将其设置为最佳值。The default value is 5, but it can be set to an optimal value to meet specific migration needs based on any POC migrations.
    截断源表时When source table is truncated 指定 DMS 在迁移期间是否截断目标表。Specifies whether DMS truncates the target table during migration. 如果在迁移过程中截断了一个或多个表,此设置可能有所帮助。This setting can be helpful if one or more tables are truncated as part of the migration process.
    配置大型对象(LOB)数据设置Configure settings for large objects (LOB) data 指定 DMS 是要迁移不受限制的 LOB 数据,还是将迁移的 LOB 数据限制为特定的大小。Specifies whether DMS migrates unlimited LOB data or limits the LOB data migrated to a specific size. 对迁移的 LOB 数据施加限制后,将截断超出该限制的任何 LOB 数据。When there's a limit on the LOB data migrated, any LOB data beyond that limit is truncated. 对于生产迁移,我们建议选择“允许不受限制的 LOB 大小”以防止数据丢失。For production migrations, it's recommended to select Allow unlimited LOB size to prevent data loss. 指定允许不受限制的 LOB 大小时,请选中“当 LOB 大小小于指定的 KB 时以单个块的形式迁移 LOB 数据”复选框,以提高性能。When specifying to allow unlimited LOB size, select the Migrate LOB data in a single block when the LOB size is less than (KB) specified check box to improve performance.

    设置高级联机迁移设置

  5. 选择“保存”,在“迁移摘要”屏幕上的“活动名称”文本框中指定迁移活动的名称,然后查看摘要,确保源和目标详细信息与此前指定的信息相符 。Select Save, on the Migration summary screen, in the Activity name text box, specify a name for the migration activity, and then review the summary to ensure that the source and target details match what you previously specified.

    迁移摘要

运行迁移Run the migration

  • 选择“运行迁移”。Select Run migration.

    迁移活动窗口随即出现,活动的“状态”为“正在初始化” 。The migration activity window appears, and the Status of the activity is Initializing.

    活动状态 - 正在初始化

监视迁移Monitor the migration

  1. 在迁移活动屏幕上选择“刷新”,以便更新显示,直到迁移的“状态”显示为“正在运行”。On the migration activity screen, select Refresh to update the display until the Status of the migration shows as Running.

  2. 单击特定数据库即可转到“完整数据加载”和“增量数据同步”操作的迁移状态。 Click on a specific database to get to the migration status for Full data load and Incremental data sync operations.

    活动状态 - 正在进行

执行迁移直接转换Perform migration cutover

完成初始的完整加载以后,数据库会被标记为“直接转换可供执行”。After the initial Full load is completed, the databases are marked Ready to cutover.

  1. 如果准备完成数据库迁移,请选择“启动直接转换”。When you're ready to complete the database migration, select Start Cutover.

    启动直接转换

  2. 确保停止传入源数据库的所有事务;等到“挂起的更改”计数器显示 0Make sure to stop all the incoming transactions to the source database; wait until the Pending changes counter shows 0.

  3. 选择“确认”,然后选择“应用”。Select Confirm, and the select Apply.

  4. 当数据库迁移状态显示“已完成”后,请将应用程序连接到新的目标数据库。When the database migration status shows Completed, connect your applications to the new target database.

    活动状态 - 已完成

后续步骤Next steps