迁移指南:SQL Server 到 Azure SQL 数据库Migration guide: SQL Server to Azure SQL Database

适用于: Azure SQL 数据库

在本指南中,了解如何将 SQL Server 实例迁移到 Azure SQL 数据库。In this guide, you learn how to migrate your SQL Server instance to Azure SQL Database.

你可以迁移在本地或以下位置运行的 SQL Server:You can migrate SQL Server running on-premises or on:

  • 虚拟机上的 SQL ServerSQL Server on Virtual Machines
  • Amazon Web Services (AWS) EC2Amazon Web Services (AWS) EC2
  • Amazon 关系数据库服务 (AWS RDS)Amazon Relational Database Service (AWS RDS)
  • 计算引擎 (Google Cloud Platform - GCP)Compute Engine (Google Cloud Platform - GCP)
  • Cloud SQL for SQL Server (Google Cloud Platform - GCP)Cloud SQL for SQL Server (Google Cloud Platform – GCP)

如需更多迁移信息,请参阅迁移概述For more migration information, see the migration overview. 有关其他迁移指南,请参阅数据库迁移For other migration guides, see Database Migration.

迁移流程

先决条件Prerequisites

若要将 SQL Server 迁移到 Azure SQL 数据库,请确保满足以下先决条件:For your SQL Server migration to Azure SQL Database, make sure you have the following prerequisites:

迁移前Pre-migration

验证你的源环境是否受支持后,开始预迁移阶段。After you've verified that your source environment is supported, start with the pre-migration stage. 发现所有现有数据源,评估迁移可行性,确定可能会妨碍迁移的任何阻碍性问题。Discover all of the existing data sources, assess migration feasibility, and identify any blocking issues that might prevent your migration.

发现Discover

在“发现”阶段,扫描网络以查明你的组织使用的所有 SQL Server 实例和功能。In the Discover phase, scan the network to identify all SQL Server instances and features used by your organization.

你可以使用  Microsoft 评估和规划工具包(“MAP 工具包”)来评估你当前的 IT 基础结构。You can use the Microsoft Assessment and Planning Toolkit (the "MAP Toolkit") to assess your current IT infrastructure. 该工具包提供了功能强大的清单、评估和报告工具,可以简化迁移规划过程。The toolkit provides a powerful inventory, assessment, and reporting tool to simplify the migration planning process.

若要详细了解可用于“发现”阶段的工具,请参阅可用于数据迁移方案的服务和工具For more information about tools available to use for the Discover phase, see Services and tools available for data migration scenarios.

评估Assess

发现数据源后,评估可迁移到 Azure SQL 数据库的任何本地 SQL Server 数据库,以确定迁移阻碍或兼容性问题。After data sources have been discovered, assess any on-premises SQL Server database(s) that can be migrated to Azure SQL Database to identify migration blockers or compatibility issues.

你可以使用数据迁移助手(4.1 及更高版本)来评估要获取的数据库:You can use the Data Migration Assistant (version 4.1 and later) to assess databases to get:

若要使用“数据库迁移评估”来评估你的环境,请执行以下步骤:To assess your environment using the Database Migration Assessment, follow these steps:

  1. 打开数据迁移助手 (DMA)Open the Data Migration Assistant (DMA).
  2. 选择“文件”,然后选择“新建评估”。Select File and then choose New assessment.
  3. 指定一个项目名称,选择“SQL Server”作为源服务器类型,然后选择“Azure SQL 数据库”作为目标服务器类型。Specify a project name, select SQL Server as the source server type, and then select Azure SQL Database as the target server type.
  4. 选择要生成的评估报告的类型。Select the type(s) of assessment reports that you want to generate. 例如,数据库兼容性和功能奇偶一致性。For example, database compatibility and feature parity. 根据评估类型,源 SQL Server 上所需的权限可能有所不同。Based on the type of assessment, the permissions required on the source SQL Server can be different. 在运行评估之前,DMA 会突出显示所选顾问所需的权限。DMA will highlight the permissions required for the chosen advisor before running the assessment.
    • “功能奇偶一致性”类别提供了一套全面的建议、Azure 中可用的替代项以及缓解步骤来帮助你计划迁移项目The feature parity category provides a comprehensive set of recommendations, alternatives available in Azure, and mitigating steps to help you plan your migration project. (需要 sysadmin 权限)。(sysadmin permissions required)
    • “兼容性问题”类别标识了可能会阻止迁移的“部分支持或完全不支持”功能兼容性问题,以及用于解决这些问题的建议(需要 CONNECT SQLVIEW SERVER STATEVIEW ANY DEFINITION 权限)。The compatibility issues category identifies partially supported or unsupported feature compatibility issues that might block migration as well as recommendations to address them (CONNECT SQL, VIEW SERVER STATE, and VIEW ANY DEFINITION permissions required).
  5. 指定 SQL Server 的源连接详细信息并连接到源数据库。Specify the source connection details for your SQL Server and connect to the source database.
  6. 选择“开始评估”。Select Start assessment.
  7. 完成此过程后,选择并查看针对妨碍迁移的问题和功能奇偶一致性问题的评估报告。After the process completes, select and review the assessment reports for migration blocking and feature parity issues. 还可以将评估报告导出到文件,以便与组织中的其他团队或人员共享。The assessment report can also be exported to a file that can be shared with other teams or personnel in your organization.
  8. 确定可以最大程度地减少迁移后工作的数据库兼容性级别。Determine the database compatibility level that minimizes post-migration efforts.
  9. 确定适合你的本地工作负荷的最佳 Azure SQL 数据库 SKU。Identify the best Azure SQL Database SKU for your on-premises workload.

若要了解详细信息,请参阅使用数据迁移助手进行 SQL Server 迁移评估To learn more, see Perform a SQL Server migration assessment with Data Migration Assistant.

如果评估遇到多个阻碍,确认你的数据库未准备好进行 Azure SQL 数据库迁移,则还可以考虑:If the assessment encounters multiple blockers to confirm that your database it not ready for an Azure SQL Database migration, then alternatively consider:

规模化评估和分析Scaled Assessments and Analysis

数据迁移助手支持执行规模化评估以及对评估报告进行合并以方便分析。Data Migration Assistant supports performing scaled assessments and consolidation of the assessment reports for analysis.

如果你有多个服务器和数据库需要进行规模化评估和分析(用于提供更广泛的数据资产视图),请参阅以下链接来了解详细信息:If you have multiple servers and databases that need to be assessed and analyzed at scale to provide a wider view of the data estate, see the following links to learn more:

迁移Migrate

完成与预迁移阶段相关的任务后,便可以执行架构和数据迁移。After you have completed tasks associated with the Pre-migration stage, you are ready to perform the schema and data migration.

使用所选的迁移方法迁移你的数据。Migrate your data using your chosen migration method.

本指南介绍了两个最常用的选项 - 数据迁移助手和 Azure 数据库迁移服务。This guide describes the two most popular options - Data Migration Assistant and Azure Database Migration Service.

数据迁移助手 (DMA)Data Migration Assistant (DMA)

若要使用 DMA 将数据库从 SQL Server 迁移到 Azure SQL 数据库,请执行以下步骤:To migrate a database from SQL Server to Azure SQL Database using DMA, follow these steps:

  1. 下载并安装数据库迁移助手Download and install the Database Migration Assistant.
  2. 创建一个新项目,并选择“迁移”作为项目类型。Create a new project and select Migration as the project type.
  3. 将源服务器类型设置为“SQL Server”,将目标服务器类型设置为“Azure SQL 数据库”,选择“架构和数据”作为迁移范围,然后选择“创建”。Set the source server type to SQL Server and the target server type to Azure SQL Database, select the migration scope as Schema and data and select Create.
  4. 在迁移项目中,指定源服务器详细信息(例如服务器名称)、用于连接到服务器的凭据,以及要迁移的源数据库。In the migration project, specify the source server details such as the server name, credentials to connect to the server and the source database to migrate.
  5. 在目标服务器详细信息中,指定 Azure SQL 数据库服务器名称、用于连接到服务器的凭据,以及要迁移到的目标数据库。In the target server details, specify the Azure SQL Database server name, credentials to connect to the server and the target database to migrate to.
  6. 选择架构对象并将其部署到目标 Azure SQL 数据库。Select the schema objects and deploy them to the target Azure SQL Database.
  7. 最后,选择“开始数据迁移”并监视迁移进度。Finally, select Start data migration and monitor the progress of migration.

有关详细教程,请参阅使用数据迁移助手将本地 SQL Server 或 Azure VM 上的 SQL Server 迁移到 Azure SQL 数据库For a detailed tutorial, see Migrate on-premises SQL Server or SQL Server on Azure VMs to Azure SQL Database using the Data Migration Assistant.

备注

  • 通过提供更多资源,在导入过程中将数据库扩展到更高的服务层级和更大的计算大小,以最大程度地提高导入速度。Scale your database to a higher service tier and compute size during the import process to maximize import speed by providing more resources. 然后,可以在导入成功后进行缩减。You can then scale down after the import is successful.
  • 导入后的数据库的兼容性级别基于源数据库的兼容性级别。The compatibility level of the imported database is based on the compatibility level of your source database.

Azure 数据库迁移服务 (DMS)Azure Database Migration Service (DMS)

若要使用 DMS 将数据库从 SQL Server 迁移到 Azure SQL 数据库,请执行以下步骤:To migrate databases from SQL Server to Azure SQL Database using DMS, follow the steps below:

  1. 在订阅中注册 Microsoft.DataMigration 资源提供程序(如果尚未这样做)。If you haven't already, register the Microsoft.DataMigration resource provider in your subscription.
  2. 在选定的所需位置创建 Azure 数据库迁移服务实例(最好与目标 Azure SQL 数据库位于同一区域)。Create an Azure Database Migration Service Instance in a desired location of your choice (preferably in the same region as your target Azure SQL Database). 选择一个现有虚拟网络或新建一个虚拟网络来承载 DMS 实例。Select an existing virtual network or create a new one to host your DMS instance.
  3. 创建 DMS 实例后,创建一个新的迁移项目,将源服务器类型指定为“SQL Server”,将目标服务器类型指定为“Azure SQL 数据库”。After your DMS instance is created, create a new migration project and specify the source server type as SQL Server and the target server type as Azure SQL Database. 在迁移项目创建边栏选项卡中选择“脱机数据迁移”作为活动类型。Choose Offline data migration as the activity type in the migration project creation blade.
  4. 在“迁移源”详细信息页上指定源 SQL Server 详细信息,在“迁移目标”详细信息页上指定目标 Azure SQL 数据库详细信息。Specify the source SQL Server details on the Migration source details page and the target Azure SQL Database details on the Migration target details page.
  5. 映射用于迁移的源数据库和目标数据库,然后选择要迁移的表。Map the source and target databases for migration and then select the tables you want to migrate.
  6. 复查迁移摘要,然后选择“运行迁移”。Review the migration summary and select Run migration. 然后,你可以监视迁移活动,并检查数据库迁移进度。You can then monitor the migration activity and check the progress of your database migration.

有关详细教程,请参阅使用 DMS 将 SQL Server 迁移到 Azure SQL 数据库For a detailed tutorial, see Migrate SQL Server to an Azure SQL Database using DMS.

数据同步和直接转换Data sync and cutover

当使用将数据更改持续从源复制/同步到目标的迁移选项时,源数据和架构可能会变化并偏离目标。When using migration options that continuously replicate / sync data changes from source to the target, the source data and schema can change and drift from the target. 请确保在数据同步过程中捕获对源的所有更改,并在迁移过程中将其应用到目标。During data sync, ensure that all changes on the source are captured and applied to the target during the migration process.

验证源和目标上的数据是否相同后,可以从源环境直接转换到目标环境。After you verify that data is same on both the source and the target, you can cutover from the source to the target environment. 请务必与业务/应用程序团队一起计划直接转换过程,以确保在直接转换时的最小中断不会影响业务连续性。It is important to plan the cutover process with business / application teams to ensure minimal interruption during cutover does not affect business continuity.

重要

有关与使用 DMS 进行迁移时执行直接转换相关的特定步骤的详细信息,请参阅执行直接转换迁移For details on the specific steps associated with performing a cutover as part of migrations using DMS, see Performing migration cutover.

迁移建议Migration recommendations

若要加快迁移到 Azure SQL 数据库的速度,应考虑以下建议:To speed up migration to Azure SQL Database, you should consider the following recommendations:

资源争用Resource contention 建议Recommendation
源(通常在本地)Source (typically on premises) 迁移期间源中的主要瓶颈是 DATA 文件的延迟和 DATA I/O,需要仔细监视。Primary bottleneck during migration in source is DATA I/O and latency on DATA file which needs to be monitored carefully. 根据 DATA 文件的延迟时间和 DATA IO,以及它是虚拟机还是物理服务器,你必须与存储管理员联系,并探索各种选项来缓解瓶颈。Based on DATA IO and DATA file latency and depending on whether it's a virtual machine or physical server, you will have to engage storage admin and explore options to mitigate the bottleneck.
目标(Azure SQL 数据库)Target (Azure SQL Database) 最大的限制因素是日志生成速率和日志文件的延迟。Biggest limiting factor is the log generation rate and latency on log file. 使用 Azure SQL 数据库时,日志生成速率最高可达 96-MB/秒。With Azure SQL Database, you can get a maximum of 96-MB/s log generation rate. 若要加快迁移速度,请将目标 SQL DB 纵向扩展到业务关键 Gen5 8 vCore,以获取 96 MB/秒的最大日志生成速率,并实现日志文件的低延迟。To speed up migration, scale up the target SQL DB to Business Critical Gen5 8 vCore to get the maximum log generation rate of 96 MB/s and also achieve low latency for log file. 无论选择哪种服务级别,超大规模服务层都提供 100-MB/秒的日志速率The Hyperscale service tier provides 100-MB/s log rate regardless of chosen service level
NetworkNetwork 所需网络带宽等于最大日志引入速率 96 MB/秒(768 Mb/秒)Network bandwidth needed is equal to max log ingestion rate 96 MB/s (768 Mb/s) 根据本地数据中心到 Azure 的网络连接情况,检查网络带宽(通常是 Azure ExpressRoute),使其适应最大日志引入速率。Depending on network connectivity from your on-premises data center to Azure, check your network bandwidth (typically Azure ExpressRoute) to accommodate for the maximum log ingestion rate.
用于数据迁移助手 (DMA) 的虚拟机Virtual machine used for Data Migration Assistant (DMA) 对于运行 DMA 的虚拟机来说,主要的瓶颈是 CPUCPU is the primary bottleneck for the virtual machine running DMA 通过以下项来加速数据迁移时的注意事项Things to consider to speed up data migration by using
- Azure 计算密集型 VM- Azure compute intensive VMs
- 使用至少 F8s_v2 (8 vcore) VM 运行 DMA- Use at least F8s_v2 (8 vcore) VM for running DMA
- 确保 VM 在与目标相同的 Azure 区域中运行- Ensure the VM is running in the same Azure region as target
Azure 数据库迁移服务 (DMS)Azure Database Migration Service (DMS) DMS 的计算资源争用和数据库对象注意事项Compute resource contention and database objects consideration for DMS 使用高级 4 vCore。Use Premium 4 vCore. DMS 会自动处理数据库对象(如外键、触发器、约束和非聚集索引),无需手动干预。DMS automatically takes care of database objects like foreign keys, triggers, constraints, and non-clustered indexes and doesn't need manual intervention.

迁移后Post-migration

成功完成迁移阶段后,执行一系列的迁移后任务,确保一切顺利高效地运行。After you have successfully completed the migration stage, go through a series of post-migration tasks to ensure that everything is functioning smoothly and efficiently.

迁移后阶段对于协调任何数据准确性问题、验证完整性以及解决工作负载的性能问题至关重要。The post-migration phase is crucial for reconciling any data accuracy issues and verifying completeness, as well as addressing performance issues with the workload.

修正应用程序Remediate applications

将数据迁移到目标环境后,以前使用源的所有应用程序都需要开始使用目标。After the data is migrated to the target environment, all the applications that formerly consumed the source need to start consuming the target. 在某些情况下,实现这一点需要对应用程序进行更改。Accomplishing this will, in some cases, require changes to the applications.

执行测试Perform tests

数据库迁移的测试方法包括以下活动:The test approach for database migration consists of the following activities:

  1. 开发验证测试:要测试数据库迁移,需要使用 SQL 查询。Develop validation tests: To test database migration, you need to use SQL queries. 必须创建针对源数据库和目标数据库运行的验证查询。You must create the validation queries to run against both the source and the target databases. 验证查询应涵盖已定义的范围。Your validation queries should cover the scope you have defined.
  2. 设置测试环境:测试环境应包含源数据库和目标数据库的副本。Set up test environment: The test environment should contain a copy of the source database and the target database. 请确保隔离测试环境。Be sure to isolate the test environment.
  3. 运行验证测试:针对源和目标运行验证测试,然后分析结果。Run validation tests: Run the validation tests against the source and the target, and then analyze the results.
  4. 运行性能测试:针对源和目标运行性能测试,然后分析和比较结果。Run performance tests: Run performance test against the source and the target, and then analyze and compare the results.

利用高级功能Leverage advanced features

请确保利用 Azure SQL 数据库提供的基于云的高级功能,例如内置高可用性威胁检测以及监视和优化工作负荷Be sure to take advantage of the advanced cloud-based features offered by SQL Database, such as built-in high availability, threat detection, and monitoring and tuning your workload.

只有将数据库兼容性级别更改为最新的兼容性级别 (150) 后,某些 SQL Server 功能才可用。Some SQL Server features are only available once the database compatibility level is changed to the latest compatibility level (150).

若要了解详细信息,请参阅在迁移后管理 Azure SQL 数据库To learn more, see managing Azure SQL Database after migration

后续步骤Next steps