教程:使用 DMS 将 SQL Server 脱机迁移到 Azure SQL 托管实例Tutorial: Migrate SQL Server to an Azure SQL Managed Instance offline 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. 有关需要一些手动操作的其他方法,请参阅将 SQL Server 实例迁移到 SQL 托管实例一文。For additional methods that may require some manual effort, see the article SQL Server instance migration to 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 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 by using Azure Database Migration Service.
  • 运行迁移。Run the migration.
  • 监视迁移。Monitor the migration.
  • 下载迁移报告。Download a migration report.

Important

对于从 SQL Server 到 SQL 托管实例的脱机迁移,Azure 数据库迁移服务可以为你创建备份文件。For offline migrations from SQL Server to SQL Managed Instance, Azure Database Migration Service can create the backup files for you. 或者可以在 SMB 网络共享中提供服务将用于迁移数据库的最新完整数据库备份。Alternately, you can provide the latest full database backup in the SMB network share that the service will use to migrate your databases. 请勿将多个备份追加到单个备份介质中;请在单独的备份文件上进行每一次备份。Do not append multiple backups into a single backup media; take each backup on a separate backup file. 请注意,也可以使用压缩的备份来减少在迁移大型备份时出现潜在问题的可能性。Note that you can use compressed backups as well, to reduce the likelihood of experiencing potential issues with migrating large backups.

Tip

使用 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 offline migration from SQL Server to a SQL Managed Instance. 有关联机迁移,请参阅使用 DMS 将 SQL Server 联机迁移到 SQL 托管实例For an online migration, see Migrate SQL Server to an SQL Managed Instance online 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.

    Note

    在设置虚拟网络期间,如果将 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.

  • 确保虚拟网络网络安全组规则未阻止到 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 target managed instance are members of the sysadmin server role.

    Note

    默认情况下,Azure 数据库迁移服务仅支持迁移 SQL 登录名。By default, Azure Database Migration Service only supports migrating SQL logins. 但是,可通过以下方式启用迁移 Windows 登录名的功能:However, you can enable the ability to migrate Windows logins by:

    • 确保目标 SQL 托管实例具有 AAD 读取访问权限,这可由具有“公司管理员”或“全局管理员”角色的用户通过 Azure 门户进行配置。Ensuring that the target SQL Managed Instance has AAD read access, which can be configured via the Azure portal by a user with the Company Administratoror a Global Administrator" role.
    • 配置 Azure 数据库迁移服务实例以启用 Windows 用户/组登录名迁移,这通过 Azure 门户在“配置”页上进行设置。Configuring your Azure Database Migration Service instance to enable Windows user/group login migrations, which is set up via the Azure portal, on the Configuration page. 启用此设置后,重启服务以使更改生效。After enabling this setting, restart the service for the changes to take effect.

    重启服务后,Windows 用户/组登录名将出现在可用于迁移的登录名列表中。After restarting the service, Windows user/group logins appear in the list of logins available for migration. 对于迁移的所有 Windows 用户/组登录名,系统都会提示提供关联的域名。For any Windows user/group logins you migrate, you are prompted to provide the associated domain name. 不支持服务用户帐户(域名为 NT AUTHORITY 的帐户)和虚拟用户帐户(域名为 NT SERVICE 的帐户)。Service user accounts (account with domain name NT AUTHORITY) and virtual user accounts (account name with domain name NT SERVICE) are not supported.

  • 创建网络共享,供 Azure 数据库迁移服务用来备份源数据库。Create a network share that Azure Database Migration Service can use to back up the source database.

  • 确保运行源 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 Blob 存储资源一文中的步骤创建 Blob 容器并检索其 SAS URI。创建 SAS URI 时,请务必在策略窗口中选择所有权限(读取、写入、删除、列出)。Create a blob container and retrieve its SAS URI by using the steps in the article Manage Azure Blob Storage resources with Storage Explorer, be sure to select all permissions (Read, Write, Delete, List) on the policy window while creating the SAS URI. 此详细信息可为 Azure 数据库迁移服务提供你的存储帐户容器的访问权限,以便将用于迁移数据库的备份文件上传到 SQL 托管实例。This detail provides Azure Database Migration Service with access to your storage account container for uploading the backup files used for migrating databases to SQL Managed Instance.

    Note

    配置迁移设置步骤中配置存储帐户设置时,Azure 数据库迁移服务不支持使用帐户级别 SAS 令牌。Azure Database Migration Service does not support using an account level SAS token when configuring the Storage Account settings during the Configure Migration Settings step.

注册 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 数据库迁移服务迁移 Azure SQL DB 托管实例的网络拓扑一文。For additional detail, see the article Network topologies for Azure SQL DB managed instance migrations using Azure Database Migration Service.

  6. 选择定价层。Select a pricing 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 Offline data migration.

    创建 DMS 项目

  5. 选择“创建”来创建项目。Select Create 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.

    Caution

    使用自签名证书加密的 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.

    选择源数据库

    Important

    如果使用 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. 在“迁移目标详细信息”屏幕上,指定目标的连接详细信息,该目标是要将“AdventureWorks2012”数据库迁移到其中的已提前预配的 SQL 托管实例 。On the Migration target details screen, specify the connection details for the target, which is the pre-provisioned SQL Managed Instance to which you're migrating the AdventureWorks2012 database.

    如果尚未预配 SQL 托管实例,请选择相应的链接来帮助预配实例。If you haven't already provisioned the SQL Managed Instance, select the link to help you provision the instance. 仍可继续进行项目创建,然后在 SQL 托管实例准备就绪后返回到此特定项目以执行迁移。You can still continue with project creation and then, when the SQL Managed Instance is ready, return to this specific project to execute the migration.

    选择目标

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

选择源数据库Select source databases

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

    选择源数据库

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

选择登录名Select logins

  1. 在“选择登录名”屏幕上,选择要迁移的登录名。On the Select logins screen, select the logins that you want to migrate.

    Note

    默认情况下,Azure 数据库迁移服务仅支持迁移 SQL 登录名。By default, Azure Database Migration Service only supports migrating SQL logins. 若要启用对迁移 Windows 登录名的支持,请参阅本教程的先决条件部分。To enable support for migrating Windows logins, see the Prerequisites section of this tutorial.

    选择登录名

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

配置迁移设置Configure migration settings

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

    选择源备份选项Choose source backup option 如果已经有一个完整备份文件供 DMS 用于数据库迁移,请选择选项“我将提供最新备份文件”。Choose the option I will provide latest backup files when you already have full backup files available for DMS to use for database migration. 如果希望 DMS 先进行源数据库完整备份,然后用它来进行迁移,请选择选项“我将让 Azure 数据库迁移服务创建备份文件”。Choose the option I will let Azure Database Migration Service create backup files when you want DMS to take the source database full backup at first and use it for migration.
    网络位置共享Network location share 可让 Azure 数据库迁移服务备份源数据库的本地 SMB 网络共享。The local SMB network share that Azure Database Migration Service can take the source database backups to. 运行源 SQL Server 实例的服务帐户必须在此网络共享中拥有写入特权。The service account running source SQL Server instance must have 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'.
    用户名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. 如果选择已启用 TDE 的数据库进行迁移,则上述 Windows 用户必须是内置的管理员帐户,且必须对 Azure 数据库迁移服务禁用用户帐户控制,才能上传和删除证书文件。If TDE-enabled databases are selected for migration, the above windows user must be the built-in administrator account and User Account Control must be disabled for Azure Database Migration Service to upload and delete the certificates files.)
    密码Password 用户密码。Password for the user.
    存储帐户设置Storage account settings 可让 Azure 数据库迁移服务访问你的存储帐户容器,以便将用于迁移数据库的备份文件上传到 SQL 托管实例的 SAS URI。The SAS URI that provides Azure Database Migration Service with access to your storage account container to which the service uploads the backup files and that is used for migrating databases to SQL Managed Instance. 了解如何获取 Blob 容器的 SAS URILearn how to get the SAS URI for blob container. 此 SAS URI 必须用于 blob 容器,而不能用于存储帐户。This SAS URI must be for the blob container, not for the storage account.
    TDE 设置TDE Settings 若要迁移启用了透明数据加密 (TDE) 的源数据库,必须拥有目标 SQL 托管实例的写入权限。If you're migrating the source databases with Transparent Data Encryption (TDE) enabled, you need to have write privileges on the target SQL Managed Instance. 从下拉菜单中选择预配了 SQL 托管实例的订阅。Select the subscription in which the SQL Managed Instance provisioned from the drop-down menu. 在下拉菜单中选择目标 Azure SQL 数据库托管实例Select the target Azure SQL Database Managed Instance in the drop-down menu.

    配置迁移设置

  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. 展开“验证选项”部分以显示“选择验证选项”屏幕,指定是否要验证已迁移数据库的查询正确性,然后选择“保存” 。Expand the Validation option section to display the Choose validation option screen, specify whether to validate the migrated database for query correctness, and then select Save.

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

    迁移项目摘要

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

运行迁移Run the migration

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

    迁移活动窗口随即出现,活动的状态为“挂起”。The migration activity window appears, and the status of the activity is Pending.

监视迁移Monitor the migration

  1. 在“迁移活动”屏幕中,选择“刷新”以更新显示。In 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.

    正在进行的迁移活动

  2. 迁移完成后,选择“下载报告”可获取报告,其上列出了与迁移过程相关的详细信息。After the migration completes, select Download report to get a report listing the details associated with the migration process.

  3. 验证目标 SQL 托管实例环境上的目标数据库。Verify that the target database on the target SQL Managed Instance environment.

后续步骤Next steps