教程:使用 DMS 将 SQL Server 联机迁移到 Azure SQL 托管实例Tutorial: Migrate SQL Server to an Azure SQL Managed Instance online using DMS

可使用 Azure 数据库迁移服务以最少停机时间将数据库从 SQL Server 实例迁移到 Azure SQL 托管实例You can use Azure Database Migration Service to migrate the databases from a SQL Server instance to an Azure SQL Managed Instance with minimal downtime. 有关需要一些手动操作的其他方法,请参阅将 SQL Server 实例迁移到 Azure SQL 托管实例一文。For additional methods that may require some manual effort, see the article SQL Server instance migration to Azure SQL Managed Instance.

本教程介绍如何使用 Azure 数据库迁移服务,以最少停机时间将 Adventureworks2012 数据库从 SQL Server 的本地实例迁移到 SQL 托管实例。In this tutorial, you migrate the Adventureworks2012 database from an on-premises instance of SQL Server to a SQL Managed Instance with minimal downtime by using Azure Database Migration Service.

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

  • 创建 Azure 数据库迁移服务的实例。Create an instance of Azure Database Migration Service.
  • 使用 Azure 数据库迁移服务创建迁移项目并开始联机迁移。Create a migration project and start online migration by using Azure Database Migration Service.
  • 监视迁移。Monitor the migration.
  • 准备就绪后执行迁移交接。Perform the migration cutover when you are ready.

重要

若要使用 Azure 数据库迁移服务从 SQL Server 联机迁移到 SQL 托管实例,必须在 SMB 网络共享中提供完整的数据库备份和后续日志备份,供服务用来迁移数据库。For online migrations from SQL Server to SQL Managed Instance using Azure Database Migration Service, you must provide the full database backup and subsequent log backups in the SMB network share that the service can use to migrate your databases. Azure 数据库迁移服务不启动任何备份,而是使用现有备份进行迁移。你可能已经在灾难恢复计划中有了这些备份。Azure Database Migration Service does not initiate any backups, and instead uses existing backups, which you may already have as part of your disaster recovery plan, for the migration. 确保使用 WITH CHECKSUM 选项进行备份Be sure that you take backups using the WITH CHECKSUM option. 另外,请确保不要将多个备份(即完整备份和 t-log 备份)追加到单个备份介质中;请在单独的备份文件上进行每一次备份。Also, make sure not to append multiple backups (i.e. full and t-log) into a single backup media; take each backup on a separate backup file. 最后,可以使用压缩的备份来减少遇到与迁移大型备份相关的潜在问题的可能性。Finally, you can use compressed backups to reduce the likelihood of experiencing potential issues associated with migrating large backups.

备注

使用 Azure 数据库迁移服务执行联机迁移需要基于“高级”定价层创建实例。Using Azure Database Migration Service to perform an online migration requires creating an instance based on the Premium pricing tier.

重要

为获得最佳迁移体验,Azure 建议在目标数据库所在的 Azure 区域中创建 Azure 数据库迁移服务的实例。For an optimal migration experience, Azure recommends creating an instance of 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.

重要

尽量缩短联机迁移过程的持续时间,将实例重新配置或计划维护造成的中断风险降到最低。Reduce the duration of the online migration process as much as possible to minimize the risk of interruption caused by instance reconfiguration or planned maintenance. 如果发生此类事件,迁移过程将从头开始。In case of such an event, migration process will start from the beginning. 如果是计划内维护,则在重新启动迁移过程之前,会有 36 小时的宽限期。In case of planned maintenance, there is a grace period of 36 hours before migration process is restarted.

提示

使用 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.

本文介绍如何从 SQL Server 联机迁移到 SQL 托管实例。This article describes an online migration from SQL Server to a SQL Managed Instance. 有关脱机迁移,请参阅使用 DMS 将 SQL Server 脱机迁移到 SQL 托管实例For an offline migration, see Migrate SQL Server to a SQL Managed Instance offline using DMS.

先决条件Prerequisites

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

  • 使用 Azure 资源管理器部署模型创建 Azure 数据库迁移服务的 Azure 虚拟网络,它将使用 ExpressRouteVPN 为本地源服务器提供站点到站点连接。Create an Azure Virtual Network for Azure Database Migration Service by using the Azure Resource Manager deployment model, which provides site-to-site connectivity to your on-premises source servers by using either ExpressRoute or VPN. 了解使用 Azure 数据库迁移服务迁移 SQL 托管实例的网络拓扑Learn network topologies for SQL Managed Instance migrations using Azure Database Migration Service. 有关创建虚拟网络的详细信息,请参阅虚拟网络文档,尤其是提供了分步详细信息的快速入门文章。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 Azure Database Migration Service lacks internet connectivity.

    重要

    关于在迁移过程中使用的存储帐户,必须执行以下操作之一:Regarding the storage account used as part of the migration, you must either:

    • 选择允许所有网络访问该存储器帐户。Choose to allow all network to access the storage account.
    • 在 MI 子网中启用子网委托,并更新存储帐户防火墙规则以允许此子网。Turn on subnet delegation on MI subnet and update the Storage Account firewall rules to allow this subnet.
  • 确保虚拟网络网络安全组规则未阻止到 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 source database engine access.

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

  • 如果使用动态端口运行多个命名 SQL Server 实例,则可能需要启用 SQL Browser 服务并允许通过防火墙访问 UDP 端口 1434,以便 Azure 数据库迁移服务可连接到源服务器上的命名实例。If you're running multiple named SQL Server instances using dynamic ports, you may wish to enable the SQL Browser Service and allow access to UDP port 1434 through your firewalls so that Azure Database Migration Service can connect to a named instance on your source server.

  • 如果在源数据库的前面使用了防火墙设备,可能需要添加防火墙规则以允许 Azure 数据库迁移服务访问要迁移的源数据库,并通过 SMB 端口 445 访问文件。If you're using a firewall appliance in front of your source databases, you may need to add firewall rules to allow Azure Database Migration Service to access the source database(s) for migration, as well as files via SMB port 445.

  • 按照在 Azure 门户中创建 SQL 托管实例一文中的详述创建 SQL 托管实例。Create a SQL Managed Instance by following the detail in the article Create a SQL Managed Instance in the Azure portal.

  • 确保用于连接源 SQL Server 和目标托管实例的登录名是 sysadmin 服务器角色的成员。Ensure that the logins used to connect the source SQL Server and the target managed instance are members of the sysadmin server role.

  • 提供一个 SMB 网络共享,其中包含可由 Azure 数据库迁移服务用来执行数据库迁移的所有完整数据库备份文件和后续事务日志备份文件。Provide an SMB network share that contains all your database full database backup files and subsequent transaction log backup files, which Azure Database Migration Service can use for database migration.

  • 确保运行源 SQL Server 实例的服务帐户对你创建的网络共享拥有写入权限,并且源服务器的计算机帐户具有对同一共享的读/写访问权限。Ensure that the service account running the source SQL Server instance has write privileges on the network share that you created and that the computer account for the source server has read/write access to the same share.

  • 请记下在前面创建的网络共享中拥有完全控制权限的 Windows 用户(和密码)。Make a note of a Windows user (and password) that has full control privilege on the network share that you previously created. Azure 数据库迁移服务可模拟用户凭据,将备份文件上传到 Azure 存储容器,以执行还原操作。Azure Database Migration Service impersonates the user credential to upload the backup files to Azure Storage container for restore operation.

  • 创建一个 Azure Active Directory 应用程序 ID,用于生成可由 Azure 数据库迁移服务用来连接目标 Azure 数据库托管实例和 Azure 存储容器的应用程序 ID 密钥。Create an Azure Active Directory Application ID that generates the Application ID key that Azure Database Migration Service can use to connect to target Azure Database managed instance and Azure Storage Container. 有关详细信息,请参阅使用门户创建可访问资源的 Azure Active Directory 应用程序和服务主体一文。For more information, see the article Use portal to create an Azure Active Directory application and service principal that can access resources.

    备注

    Azure 数据库迁移服务需要对指定的应用程序 ID 的订阅具有参与者权限。Azure Database Migration Service requires the Contributor permission on the subscription for the specified Application ID. 或者,你可以创建自定义角色,以授予 Azure 数据库迁移服务所需的特定权限。Alternatively, you can create custom roles that grant the specific permissions that Azure Database Migration Service requires. 有关使用自定义角色的分步指导,请参阅用于 SQL Server 到 SQL 托管实例联机迁移的自定义角色一文。For step-by-step guidance about using custom roles, see the article Custom roles for SQL Server to SQL Managed Instance online migrations.

  • 创建或记下可让 DMS 服务将数据库备份文件上传到的并可用来迁移数据库的标准性能层 Azure 存储帐户。Create or make a note of Standard Performance tier, Azure Storage Account, that allows DMS service to upload the database backup files to and use for migrating databases. 请务必在创建 Azure 数据库迁移服务实例的同一区域创建 Azure 存储帐户。Make sure to create the Azure Storage Account in the same region as the Azure Database Migration Service instance is created.

注册 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 Azure Database Migration Service, and then select Resource providers.

    显示资源提供程序

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

    注册资源提供程序

创建 Azure 数据库迁移服务实例Create an Azure Database Migration Service 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. 选择要在其中创建 DMS 实例的位置。Select the location in which you want to create the instance of DMS.

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

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

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

    有关更多详细信息,请参阅使用 Azure 数据库迁移服务迁移 SQL 托管实例的网络拓扑一文。For additional detail, see the article Network topologies for SQL Managed Instance migrations using Azure Database Migration Service.

  6. 从“高级”定价层选择 SKU。Select a SKU from the Premium pricing tier.

    备注

    仅当使用“高级”层时,才支持联机迁移。Online migrations are supported only when using the Premium tier.

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

    创建 DMS 服务

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

创建迁移项目Create a migration project

创建服务实例后,在 Azure 门户中找到并打开它,然后创建一个新的迁移项目。After an instance of 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 数据库迁移服务”屏幕上,搜索创建的实例名称,然后选择该实例。On the Azure Database Migration Service screen, search for the name of the instance that you created, and then select the instance.

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

  4. 在“新建迁移项目”屏幕上指定项目名称,在“源服务器类型”文本框中选择“SQL Server”,在“目标服务器类型”文本框中选择“Azure SQL 托管实例”,然后在“选择活动类型”中选择“联机数据迁移”。 On the New migration project screen, specify a name for the project, in the Source server type text box, select SQL Server, in the Target server type text box, select Azure SQL Managed Instance, and then for Choose type of activity, select Online data migration.

    创建 Azure 数据库迁移服务项目

  5. 选择“创建并运行活动”以创建项目。Select Create and run activity to create the project.

指定源详细信息Specify source details

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

  2. 如果尚未在服务器上安装受信任的证书,请选中“信任服务器证书”复选框。If you haven't installed a trusted certificate on your 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 does 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.

    源详细信息

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

  4. 在“选择源数据库”屏幕上,选择用于迁移的“Adventureworks2012”数据库 。On the Select source databases screen, select the Adventureworks2012 database for migration.

    选择源数据库

    重要

    如果使用 SQL Server Integration Services (SSIS),则 DMS 目前不支持将 SSIS 项目/包的目录数据库 (SSISDB) 从 SQL Server 迁移到 SQL 托管实例。If you use SQL Server Integration Services (SSIS), DMS does not currently support migrating the catalog database for your SSIS projects/packages (SSISDB) from SQL Server to SQL Managed Instance. 但是,你可以在 Azure 数据工厂 (ADF) 中预配 SSIS 并将 SSIS 项目/包重新部署到由 SQL 托管实例托管的目标 SSISDB。However, you can provision SSIS in Azure Data Factory (ADF) and redeploy your SSIS projects/packages to the destination SSISDB hosted by SQL Managed Instance. 有关如何迁移 SSIS 包的详细信息,请参阅将 SQL Server Integration Services 包迁移到 AzureFor more information about migrating SSIS packages, see the article Migrate SQL Server Integration Services packages to Azure.

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

指定目标详细信息Specify target details

  1. 在“迁移目标详细信息”屏幕上,指定可由 DMS 实例用来连接 SQL 托管实例目标实例和 Azure 存储帐户的“应用程序 ID”和“密钥”。On the Migration target details screen, specify the Application ID and Key that the DMS instance can use to connect to the target instance of SQL Managed Instance and the Azure Storage Account.

    有关详细信息,请参阅使用门户创建可访问资源的 Azure Active Directory 应用程序和服务主体一文。For more information, see the article Use portal to create an Azure Active Directory application and service principal that can access resources.

  2. 选择包含 SQL 托管实例目标实例的“订阅”,然后选择目标实例。Select the Subscription containing the target instance of SQL Managed Instance, and then select the target instance.

    如果尚未预配 SQL 托管实例,请选择相应的链接来帮助预配实例。If you haven't already provisioned the SQL Managed Instance, select the link to help you provision the instance. SQL 托管实例准备就绪后,请返回到此特定项目以执行迁移。When the SQL Managed Instance is ready, return to this specific project to execute the migration.

  3. 提供用于连接到 SQL 托管实例的“SQL 用户”和“密码”。Provide SQL User and Password to connect to the SQL Managed Instance.

    选择目标

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

选择源数据库Select source databases

  1. 在“选择源数据库”屏幕上,选择要迁移的源数据库。On the Select source databases screen, select the source database that you want to migrate.

    选择源数据库

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

配置迁移设置Configure migration settings

  1. 在“配置迁移设置”屏幕上,提供以下详细信息:On the Configure migration settings screen, provide the following detail:

    SMB 网络位置共享SMB Network location share 本地 SMB 网络共享或 Azure 文件共享,其中包含可由 Azure 数据库迁移服务用来执行迁移的完整数据库备份文件和事务日志备份文件。The local SMB network share or Azure file share that contains the Full database backup files and transaction log backup files that Azure Database Migration Service can use for migration. 运行源 SQL Server 实例的服务帐户必须对此网络共享拥有读/写特权。The service account running the source SQL Server instance must have read\write privileges on this network share. 在网络共享中提供服务器的 FQDN 或 IP 地址,例如“'\\servername.domainname.com\backupfolder”或“\\IP address\backupfolder”。Provide an FQDN or IP addresses of the server in the network share, for example, '\\servername.domainname.com\backupfolder' or '\\IP address\backupfolder'. 为提高性能,建议为每个要迁移的数据库使用单独的文件夹。For improved performance, it's recommended to use separate folder for each database to be migrated. 可以使用“高级设置”选项提供数据库级文件共享路径。You can provide the database level file share path by using the Advanced Settings option.
    用户名User name 确保 Windows 用户具有对上面提供的网络共享的完全控制权限。Make sure that the Windows user has full control privilege on the network share that you provided above. Azure 数据库迁移服务将模拟用户凭据,将备份文件上传到 Azure 存储容器,以执行还原操作。Azure Database Migration Service will impersonate the user credential to upload the backup files to Azure Storage container for restore operation. 如果使用 Azure 文件共享,请将带 AZURE\ 前缀的存储帐户名称用作用户名。If using Azure File share, use the storage account name pre-pended with AZURE\ as the username.
    密码Password 用户密码。Password for the user. 如果使用 Azure 文件共享,请将存储帐户密钥用作密码。If using Azure file share, use a storage account key as the password.
    Azure 存储帐户的订阅Subscription of the Azure Storage Account 选择包含 Azure 存储帐户的订阅。Select the subscription that contains the Azure Storage Account.
    Azure 存储帐户Azure Storage Account 选择可让 DMS 将备份文件从 SMB 网络共享上传到的并用于数据库迁移的 Azure 存储帐户。Select the Azure Storage Account that DMS can upload the backup files from the SMB network share to and use for database migration. 为获得最佳文件上传性能,我们建议在 DMS 服务所在的同一区域中选择存储帐户。We recommend selecting the Storage Account in the same region as the DMS service for optimal file upload performance.

    配置迁移设置

    备注

    如果 Azure 数据库迁移服务显示“系统错误 53”或“系统错误 57”错误,原因可能是 Azure 数据库迁移服务无法访问 Azure 文件共享。If Azure Database Migration Service shows error 'System Error 53' or 'System Error 57', the cause might result from an inability of Azure Database Migration Service to access Azure file share. 如果遇到这其中的一个错误,请按此处的说明操作,授予从虚拟网络访问存储帐户的权限。If you encounter one of these errors, please grant access to the storage account from the virtual network using the instructions here.

    重要

    如果启用了环回检查功能,并且源 SQL Server 和文件共享位于同一台计算机上,则源将无法使用 FQDN 访问文件共享。If loopback check functionality is enabled and the source SQL Server and file share are on the same computer, then source won't be able to access the files hare using FQDN. 若要解决此问题,请按照此处的说明禁用环回检查功能。To fix this issue, disable loopback check functionality using the instructions here.

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

查看迁移摘要Review the migration summary

  1. 在“迁移摘要”屏幕的“活动名称”文本框中指定迁移活动的名称。 On the Migration summary screen, in the Activity name text box, specify a name for the migration activity.

  2. 查看并验证与迁移项目关联的详细信息。Review and verify the details associated with the migration project.

    迁移项目摘要

运行并监视迁移Run and monitor the migration

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

  2. 在“迁移活动”屏幕上,选择“刷新”以更新显示。On the migration activity screen, select Refresh to update the display.

    正在进行的迁移活动

    可以进一步展开数据库和登录类别,以监视相应服务器对象的迁移状态。You can further expand the databases and logins categories to monitor the migration status of the respective server objects.

    正在进行的迁移活动

执行迁移交接Performing migration cutover

在 SQL 托管实例的目标实例上还原整个数据库备份之后,可以使用该数据库执行迁移交接。After the full database backup is restored on the target instance of SQL Managed Instance, the database is available for performing a migration cutover.

  1. 如果已准备好完成联机数据库迁移,请选择“开始交接”。When you're ready to complete the online database migration, select Start Cutover.

  2. 停止所有传入源数据库的流量。Stop all the incoming traffic to source databases.

  3. 执行 [结尾日志备份],使备份文件在 SMB 网络共享中可用,然后等到还原这最后一个事务日志备份。Take the [tail-log backup], make the backup file available in the SMB network share, and then wait until this final transaction log backup is restored.

    此时,会看到“挂起的更改”设置为 0。At that point, you'll see Pending changes set to 0.

  4. 依次选择“确认”、“应用” 。Select Confirm, and then select Apply.

    准备完成交接

  5. 当数据库迁移状态显示为“已完成”后,请将应用程序连接到 SQL 托管实例的新目标实例。When the database migration status shows Completed, connect your applications to the new target instance of SQL Managed Instance.

    交接完成

后续步骤Next steps