您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

将 SQL Server 数据库迁移到 AzureMigrate SQL Server databases to Azure

本文演示了如何将虚拟公司 Contoso 评估、计划和迁移到 Azure 的各种本地 SQL Server 数据库。This article demonstrates how a fictional company Contoso assessed, planned and migrated their various on-premises SQL Server databases to Azure.

考虑迁移到 Azure 时,Contoso 公司需要进行技术和财务评估,以确定它的本地工作负荷是否是适合迁移到云中的候选项。As Contoso considers migrating to Azure, the company needs a technical and financial assessment to determine whether its on-premises workloads are good candidates for cloud migration. 具体而言,Contoso 团队想要评估进行迁移时计算机和数据库的兼容性。In particular, the Contoso team wants to assess machine and database compatibility for migration. 此外,它还需要估算在 Azure 中运行 Contoso 资源的容量和成本。Additionally, it wants to estimate capacity and costs for running Contoso's resources in Azure.

业务驱动因素Business drivers

Contoso 在维护网络上存在的 SQL Server 工作负荷的所有版本中都存在着各种问题。Contoso is having various issues with maintaining all the wide array of versions of SQL Server workloads that exist on their network. 在最新的投资者会议后,首席财务官和 CTO 已经决定将所有这些工作负荷移到 Azure。After the latest investor's meeting, the CFO and CTO have made the decision to move all these workloads to Azure. 这样,他们就可以将结构化的资本支出模型转换为流畅的操作费用模型。This will allow them to shift from a structured capital expense model to a fluid operating expense model.

IT 领先团队与业务合作伙伴密切合作,以了解业务和技术要求:The IT leadership team has worked closely with business partners to understand the business and technical requirements:

  • 提高安全性: Contoso 需要能够以更及时、更有效的方式监视和保护所有数据资源。Increase security: Contoso needs to be able to monitor and protect all data resources in a more timely and efficient manner. 他们还想要对数据库访问模式进行更集中的报表系统设置。They would also like to get a more centralized reporting system setup on database access patterns.

  • 优化计算资源: Contoso 部署了大型本地服务器基础结构。Optimize compute resources: Contoso has deployed a large on-premises server infrastructure. 它们具有几个 SQL Server 实例,这些实例使用但并不真正地使用基础 CPU、内存和磁盘分配方式。They have several SQL Server instances that consume but do not really use the underlying CPU, memory and disk allocated in efficient ways.

  • 提高效率: Contoso 需要删除不必要的过程,并为开发人员和用户简化流程。Increase efficiency: Contoso needs to remove unnecessary procedures, and streamline processes for developers and users. 业务要求 IT 反应迅速,不浪费时间金钱,从而更快满足客户需求。The business needs IT to be fast and not waste time or money, thus delivering faster on customer requirements. 迁移后,应减少和/或最大程度地减少数据库管理。Database administration should be reduced and/or minimized after the migration.

  • 提高灵活性: Contoso 需要更好地响应业务需求。Increase agility: Contoso IT needs to be more responsive to the needs of the business. 它必须能够抢在市场变化之前作出反应,这样才能在全球经济中取得成功。It must be able to react faster than the changes in the marketplace, to enable the success in a global economy. 同时它不能阻碍发展,成为业务的绊脚石。It mustn't get in the way, or become a business blocker.

  • 规模: 当业务成功发展时,Contoso IT 必须提供能够以相同步调增长的系统。Scale: As the business grows successfully, Contoso IT must provide systems that are able to grow at the same pace. 有几个旧的硬件环境不能进一步升级,且已超过或接近支持结束。There are several legacy hardware environments that cannot be upgraded any further and are past or near end of support.

  • 成本: 与在本地运行应用程序相比,业务和应用程序所有者希望知道他们不会像在本地运行应用程序那样降低云成本。Costs: Business and applications owners want to know they won't be stuck with high cloud costs as compared to running the applications on-premises.

迁移目标Migration goals

Contoso 云团队已将各种迁移的目标固定下来。The Contoso cloud team has pinned down goals for the various migrations. 这些目标用于确定最佳迁移方法。These goals were used to determine the best migration methods.

要求Requirements 详细信息Details
“性能”Performance 迁移后,Azure 中的应用程序应具有与在 Contoso 的本地环境中的应用程序目前相同的性能功能。After migration, applications in Azure should have the same performance capabilities that applications have today in Contoso's on-premises environment. 迁移到云并不意味着应用程序性能不太重要。Moving to the cloud doesn't mean that application performance is less critical.
兼容性Compatibility Contoso 需要了解其应用程序和数据库与 Azure 的兼容性。Contoso needs to understand the compatibility of its applications and databases with Azure. Contoso 还需要了解其 Azure 托管选项。Contoso also needs to understand its Azure hosting options.
数据源Data sources 所有数据库都将移动到 Azure,无例外。All databases will be moved to Azure with no exceptions. 根据所使用的 SQL 功能的数据库和应用程序分析,它们将移到 PaaS、IaaS 或托管实例。Based on the database and application analysis of the SQL features being used, they will move to PaaS, IaaS or managed instances. 所有数据库都必须移动。All databases must move.
应用程序Application 必须尽可能将应用程序移到云中。Applications must be moved to the cloud wherever possible. 如果无法移动,则允许用户仅通过专用连接连接到 Azure 网络上的已迁移数据库。If they cannot move, then they will be allowed to connect to the migrated database over the Azure network through private connections only.
成本Costs Contoso 不仅想要了解其迁移选项,还想要了解迁移到云后与基础结构相关的成本。Contoso wants to understand not only its migration options, but also the costs associated with the infrastructure after it moves to the cloud.
ManagementManagement 必须为各个部门以及资源组创建资源管理组,以管理所有迁移的 SQL 数据库。Resource management groups must be created for the various departments along with resource groups to managed all SQL databases that are migrated. 所有资源都必须使用部门信息进行标记,以满足费用分摊要求。All resources must be tagged with department information for chargeback requirements.
限制Limitations 最初,并非所有运行应用程序的分支机构都有到 Azure 的直接 ExpressRoute 链接,因此这些办公室需要通过虚拟网络网关进行连接。Initially, not all branch offices that run applications will have a direct ExpressRoute link to Azure, so these offices will need to connect through virtual network gateways.

解决方案设计Solution design

Contoso 已经使用Azure Migrate对其数字领域进行了迁移评估Contoso has already performed a migration assessment of their digital estate using Azure Migrate.

该评估将导致多个工作负荷分散在多个部门。The assessment results in multiple workloads spread across multiple departments. 迁移项目的总体大小需要 (PMO) ,以管理通信、资源和计划规划的具体内容。The overall size of the migration project will require a full project management office (PMO), to manage the specifics of communication, resources and schedule planning.

迁移过程

解决方案评审Solution review

Contoso 通过将利弊清单放置在一起来评估其建议的设计。Contoso evaluates their proposed design by putting together a pros and cons list.

注意事项Consideration 详细信息Details
优点Pros Azure 将为数据库工作负荷提供单个窗格Azure will provide a single pane of glass into the database workloads

成本将通过 Azure 成本管理 + 计费进行监视。Costs will be monitored via Azure Cost Management + Billing.

使用 Azure 计费 API 可以轻松地对业务计费计费。Business chargeback billing will be easy to perform with the Azure Billing APIs.

服务器和软件维护将减少到仅基于 IaaS 的环境。Server and software maintenance will be reduced to only the IaaS-based environments.
缺点Cons 由于需要基于 IaaS 的虚拟机,因此仍需要在这些计算机上管理软件。Due to the requirement of IaaS-based virtual machines, there will still need to be management of the software on those machines.

预算和管理Budget and management

在进行迁移之前,需要具备必要的 Azure 结构才能支持解决方案的管理和计费。Before the migration can occur, the necessary Azure structure is required to be in place to support the administration and billing aspects of the solution.

对于管理要求,创建了多个 管理组 以支持组织结构。For the management requirements, several management groups were created to support the organizational structure.

对于计费要求,每个 Azure 资源都用适当的计费标记进行 标记For the billing requirements, each of the Azure resources are then tagged with the appropriate billing tags.

迁移过程Migration process

数据迁移遵循标准的可重复模式。Data migrations follow a standard repeatable pattern. 这涉及到以下基于 Microsoft 最佳实践的步骤:This involves the following steps based on Microsoft best practices:

  • 预迁移:Pre-migration:
    • 发现: 清点数据库资产和应用程序堆栈。Discovery: Inventory database assets and application stack.
    • 评估: 评估工作负荷并修复建议。Assess: Assess workloads and fix recommendations.
    • 转换: 转换源架构以在目标中工作。Convert: Convert source schema to work in the target.
  • 迁移:Migration:
    • 迁移: 将源架构、源数据和对象迁移到目标。Migrate: Migrate the source schema, source data and objects to target.
    • 同步数据: 同步数据 (以在最短的停机时间) 。Sync data: Sync data (for minimal downtime).
    • 切换 在源和目标之间剪切。Cutover: Cut over the source to target.
  • 迁移后:Post-migration:
    • 修正应用程序: 对应用程序进行迭代和必要的更改。Remediate applications: Iteratively make and necessary changes to your applications.
    • 执行测试: 反复运行功能测试和性能测试。Perform tests: Iteratively run functional and performance tests.
    • 优化: 根据测试,解决性能问题,然后重新测试以确认性能有所改进。Optimize: Based on tests, address performance issues and then retest to confirm performance improvements.
    • 停用资产: 旧的 Vm 和宿主环境将备份并停用。Retire assets: Old VMs and hosting environments are backed up and retired.

步骤1:发现Step 1: Discovery

Contoso 使用 Azure Migrate 在 Contoso 环境中呈现依赖关系。Contoso used Azure Migrate to surface the dependencies across the Contoso environment. Azure Migrate 自动发现 Windows 和 Linux 系统上的应用程序组件,并映射服务之间的通信。Azure Migrate automatically discovered application components on Windows and Linux systems and mapped the communication between services. Azure Migrate 还显示了 Contoso 服务器、进程、入站和出站连接延迟与 TCP 连接的体系结构之间的连接。Azure Migrate also surfaced the connections between Contoso servers, processes, inbound and outbound connection latency, and ports across their TCP-connected architecture.

Contoso 还向其 Azure Migrate 项目添加了数据迁移助手。Contoso also added Data Migration Assistant to their Azure Migrate project. 选择此工具后,他们就能够评估要迁移到 Azure 的数据库。By selecting this tool they're able to assess the databases for migration to Azure.

数据迁移助手

步骤2:应用程序评估Step 2: Application assessment

评估确定 Contoso 主要使用。基于网络的应用程序。The assessment determined that Contoso uses mainly .NET-based applications. 但是,某些项目使用了其他技术,例如 PHP 和 Node.js。However, some projects have used other technologies such as PHP and Node.js. 供应商购买的系统还引入了不基于 .NET 的应用程序。Vendor-purchased systems also introduced applications not based on .NET. Contoso 识别了以下应用程序:Contoso identified the following applications:

  • ~ 800 Windows .NET 应用程序~800 Windows .NET applications
  • ~ 50 PHP 应用程序~50 PHP applications
  • 25个 Node.js 应用程序25 Node.js applications
  • 10个 Java 应用程序10 Java applications

步骤3:数据库评估Step 3: Database assessment

发现每个数据库工作负荷后,将运行数据迁移助手 (DMA) 工具来确定所使用的功能。As each database workload was discovered, Data Migration Assistant (DMA) tool was run to determine which features were being used. DMA 通过检测可能会影响新版 SQL Server 或 Azure SQL 数据库中的数据库功能的兼容性问题,帮助 Contoso 评估将数据库迁移到 Azure 的过程。DMA helps Contoso assess their database migrations to Azure by detecting compatibility issues that can impact database functionality in a new version of SQL Server or Azure SQL Database.

Contoso 按照以下步骤评估其数据库,然后将结果数据上载到 Azure Migrate:Contoso followed these steps to assess their databases and then upload results data to Azure Migrate:

  1. 下载 DMA。Download DMA.
  2. 创建评估项目。Create an assessment project.
  3. 在 DMA 中,登录到 Azure Migrate 项目并同步评估摘要。In DMA, logon to the Azure Migrate project and sync the assessment summary.

Azure Migrate 和 DMA

DMA 为目标环境建议性能和可靠性,并允许它们将其架构、数据和非包含对象从源服务器移动到目标服务器。DMA recommends performance and reliability improvements for your target environment and allows them to move their schema, data, and uncontained objects from a source server to a target server.

详细了解 数据迁移助手Learn more about Data Migration Assistant

Contoso 使用 DMA 来运行评估,然后将数据直接上传到 Azure Migrate。Contoso used the DMA to run the assessment and then uploaded the data directly to Azure Migrate.

将 DMA 上传到 Azure Migrate

由于数据库信息现在已加载到 Azure Migrate 中,Contoso 已识别出超过1000个必须迁移的数据库实例。With the database information now loaded into Azure Migrate, Contoso has identified over 1,000 database instances that must be migrated. 在这些实例中,大约40% 可移至适用于 Azure 的 SQL 数据库。Of these instances, roughly 40 percent can be moved to SQL Database for Azure. 剩余的60% 必须移动到 SQL Server 在 Azure 虚拟机上运行,或者移动到 Azure SQL 托管实例。The remaining 60 percent must be moved either to either SQL Server running on Azure Virtual Machines or to Azure SQL Managed Instance. 在60% 中,大约10% 需要基于虚拟机的方法,其余实例将移动到 Azure SQL 托管实例。Of those 60 percent, about 10 percent require a virtual machine-based approach, the remaining instances will be moved to Azure SQL Managed Instance.

如果无法在数据源上执行 DMA,则数据库迁移后将遵循以下准则。When DMA was not able to be executed on a data source, the following guidelines were followed on the database migrations.

备注

Contoso 在评估阶段发现了各种开放源代码数据库。Contoso discovered various open-source databases during the assessment phase. 它们分别按照将 开源数据库迁移到 Azure 中的指南进行迁移规划。Separately, they followed the guidance in migrate open-source databases to Azure for their migration planning.

步骤4:迁移规划Step 4: Migration planning

使用此信息时,Contoso 使用以下准则来确定要用于每个数据库的迁移方法。With the information at hand, Contoso uses the following guidelines to determine which migration method to use for each database.

目标Target 数据库使用情况Database usage 详细信息Details 联机迁移Online migration 脱机迁移Offline migration 最大大小Max size 迁移指南Migration guide
Azure SQL 数据库 (PaaS)Azure SQL Database (PaaS) 仅 SQL Server (数据) SQL Server (data only) 这些数据库只使用基本的表、列、存储过程和函数These databases simply use basic tables, columns, stored procedures and functions 数据迁移助手事务复制Data Migration Assistant, transactional replication BACPACbcpBACPAC, bcp 1 TiB1 TiB 链接Link
Azure SQL 托管实例Azure SQL Managed Instance (高级功能 SQL Server) SQL Server (advanced features) 这些数据库使用触发器和其他 高级概念 ,如自定义 .net 类型、service broker 等。These databases use triggers and other advanced concepts such as custom .NET types, service brokers, and so on. 数据迁移助手事务复制Data Migration Assistant, transactional replication BACPACbcp本机备份/还原BACPAC, bcp, native backup/restore 2 TiB-8 TiB2 TiB - 8 TiB 链接Link
(IaaS 上的 Azure 虚拟机上的 SQL Server) SQL Server on Azure Virtual Machines (IaaS) SQL Server (第三方集成) SQL Server (third-party integrations) SQL Server 必须具有 不受支持的 SQL 托管实例功能 (跨实例服务代理、加密提供程序、缓冲池、兼容级别低于100、数据库镜像、FILESTREAM、PolyBase、需要访问文件共享的任何内容、外部脚本、扩展存储过程,以及安装的其他) 或第三方软件,以支持数据库的活动。The SQL Server must have non-supported SQL Managed Instance features (cross-instance service brokers, cryptographic providers, buffer pool, compatibility levels below 100, database mirroring, FILESTREAM, PolyBase, anything that requires access to file shares, external scripts, extended stored procedures, and others) or third-party software installed to support the activities of the database. 事务复制Transactional replication BACPACbcp快照复制本机备份/还原,将物理计算机转换为 VMBACPAC, bcp, snapshot replication, native backup/restore, convert physical machine to VM 4 GiB-64 TiB4 GiB - 64 TiB 链接Link

由于数据库数量巨大,Contoso 创建了一个项目管理办公室 (PMO) 来跟踪每个数据库迁移实例。Due to the large number of databases, Contoso created a project management office (PMO) to keep track of every database migration instance. 为每个业务和应用团队分配了责任和责任Accountability and responsibilities were assigned to each business and application team.

Contoso 还执行了 工作负荷就绪状态检查Contoso also performed a workload readiness review. 此回顾检查了基础结构、数据库和网络组件。This review examined the infrastructure, database and network components.

步骤5:测试迁移Step 5: Test migrations

迁移准备的第一部分涉及到将每个数据库的测试迁移到预安装环境。The first part of the migration preparation involved a test migration of each of the databases to the pre-setup environments. 为了节省时间,它们编写了所有迁移操作的脚本,并记录了每个操作的时间。In order to save time, they scripted all of the operations for the migrations and recorded the timings for each. 为了加快迁移速度,他们确定了可以并发运行哪些迁移操作。In order to speed up the migration, they identified what migration operations could be run concurrently.

为每个数据库工作负荷标识了任何回滚过程,以防出现某些意外故障。Any rollback procedures were identified for each of the database workloads in case of some unexpected failures.

对于基于 IaaS 的工作负载,它们预先设置了所需的所有第三方软件。For the IaaS-based workloads, they set up all the required third-party software beforehand.

在测试迁移之后,Contoso 可以使用各种 Azure 成本估算工具 来更准确地了解其迁移的未来操作成本。After the test migration, Contoso was able to use the various Azure cost estimation tools to get a more accurate picture of the future operational costs of their migration.

步骤6:迁移Step 6: Migration

对于生产迁移,Contoso 识别了所有数据库迁移的时间范围,并可以在周末 (午夜到星期日午夜) 完成,但业务停机时间最短。For the production migration, Contoso identified the time frames for all database migrations and what could be sufficiently executed in a weekend window (midnight Friday through midnight Sunday) with minimal downtime to the business.

根据记录的测试过程,它们会尽可能多地通过脚本执行每个迁移,同时限制任何手动任务来最大程度地减少错误。Based on their documented test procedures, they execute each migration via scripting as much as possible, limiting any manual tasks to minimize errors.

如果任何迁移在窗口过程中失败,则会在下一个迁移窗口中回滚并重新计划。If any migrations fail during the window, they're rolled back and re-scheduled in the next migration window.

迁移后的清理Clean up after migration

Contoso 识别了所有数据库工作负荷的存档时段。Contoso identified the archival window for all database workloads. 窗口过期后,资源将从本地基础结构中停用。As the window expires, the resources will be retired from the on-premises infrastructure.

这包括:This includes:

  • 正在从本地服务器删除生产数据。Removing the production data from on-premises servers.
  • 上次工作负荷窗口过期后停用宿主服务器。Retiring the hosting server when the last workload window expires.

查看部署Review the deployment

Azure 显示已迁移的资源后,Contoso 需要积极行动、全面保护新的基础结构。With the migrated resources in Azure, Contoso needs to fully operationalize and secure their new infrastructure.

安全性Security

  • Contoso 需要确保其新的 Azure 数据库工作负荷安全。Contoso needs to ensure that their new Azure database workloads are secure. 了解详细信息Learn more.
  • 特别是,Contoso 应查看防火墙和虚拟网络配置。In particular, Contoso should review the firewall and virtual network configurations.
  • 设置 专用链接 ,以便将所有数据库流量保存在 Azure 和本地网络中。Setup Private Link so that all database traffic is kept inside Azure and the on-premises network.
  • 为 Azure SQL 数据库启用 Microsoft Defender 标识Enable Microsoft Defender for Identity for Azure SQL Database.

备份Backups

  • 确保使用异地还原备份 Azure 数据库。Ensure that the Azure databases are backed up using geo-restore. 这使得在发生区域性服务中断时,可以在配对的区域中使用备份。This allows backups to be used in a paired region in case of a regional outage.
  • 重要提示: 确保 Azure 资源具有 资源锁 ,以防止它被删除。Important: Ensure that the Azure resource has a resource lock to prevent it from being deleted. 已删除的服务器无法还原。Deleted servers cannot be restored.

许可和成本优化Licensing and cost optimization

  • 许多 Azure 数据库工作负荷可以向上或向下扩展,因此服务器和数据库的性能监视非常重要,可确保满足您的需求,同时还能保持成本最低。Many Azure database workloads can be scaled up or down, therefore performance monitoring of the server and databases is important to ensure you're meeting your needs but also keeping costs at a minimum.
  • CPU 和存储都有相关的成本。Both CPU and storage have costs associated. 可以选择多个定价层。There are several pricing tiers to select from. 请确保为数据工作负荷选择适当的定价计划。Be sure the appropriate pricing plan is selected for the data workloads.
  • 对于具有兼容资源使用模式的数据库,将实现弹性池Elastic pools are to be implemented for databases that have compatible resource utilization patterns.
  • 每个读取副本根据所选的计算和存储进行计费Each read replica is billed based on the compute and storage selected
  • 使用保留容量节省成本。Use reserved capacity to save on costs.

结论Conclusion

在本文中,Contoso 评估、计划并将其 Microsoft SQL Server 工作负荷迁移到 Azure。In this article, Contoso assessed, planned, and migrated their Microsoft SQL Server workloads to Azure.

已开发 Azure DevOps 项目,以便在 SQL 迁移旅程中进行研究,并与云采用框架一致。An Azure DevOps project has been developed for you to study in your SQL migration journey, and it aligns with the Cloud Adoption Framework. 此项目将指导你完成所需的重要决策。This project will guide you through the key decisions required. 选择此链接 以导航到 Azure DevOps 项目。Select this link to navigate to the Azure DevOps project.