将 SQL Server 数据库迁移到 Azure VM 中的 SQL ServerMigrate a SQL Server database to SQL Server in an Azure VM

将本地 SQL Server 用户数据库迁移到 Azure VM 中的 SQL Server 的方法有很多。There are a number of methods to migrate an on-premises SQL Server user database to SQL Server in an Azure VM. 本文简要讨论各种方法,并针对各种场景建议最佳方法。This article will briefly discuss various methods and recommend the best method for various scenarios.

Note

Azure 具有用于创建和处理资源的两个不同的部署模型:资源管理器部署模型和经典部署模型Azure has two different deployment models for creating and working with resources: Resource Manager and classic. 这篇文章介绍了如何使用这两种模型,但 Azure 建议大多数最新部署使用 Resource Manager 模型。This article covers using both models, but Azure recommends that most new deployments use the Resource Manager model.

主要迁移方法有哪些?What are the primary migration methods?

主要迁移方法包括:The primary migration methods are:

  • 使用压缩功能执行本地备份并将备份文件手动复制到 Azure 虚拟机中Perform on-premises backup using compression and manually copy the backup file into the Azure virtual machine
  • 执行“备份到 URL”并从该 URL 还原到 Azure 虚拟机Perform a backup to URL and restore into the Azure virtual machine from the URL
  • 拆离后,将数据和日志文件复制到 Azure Blob 存储,并从 URL 附加到 Azure VM 中的 SQL ServerDetach and then copy the data and log files to Azure blob storage and then attach to SQL Server in Azure VM from URL
  • 将本地物理计算机转换为 Hyper-V VHD,上传到 Azure Blob 存储,并使用上传的 VHD 部署为新 VMConvert on-premises physical machine to Hyper-V VHD, upload to Azure Blob storage, and then deploy as new VM using uploaded VHD
  • 使用 Windows 导入/导出服务运送硬盘驱动器Ship hard drive using Windows Import/Export Service
  • 如果在本地有 AlwaysOn 可用性组部署,请使用添加 Azure 副本向导在 Azure 中创建副本,然后进行故障转移,并将用户指向 Azure 数据库实例If you have an AlwaysOn Availability Group deployment on-premises, use the Add Azure Replica Wizard to create a replica in Azure and then failover, pointing users to the Azure database instance
  • 使用 SQL Server 事务复制将 Azure SQL Server 实例配置为订阅服务器,禁用复制,并将用户指向 Azure 数据库实例Use SQL Server transactional replication to configure the Azure SQL Server instance as a subscriber and then disable replication, pointing users to the Azure database instance

Tip

也可使用这些相同的技术在 Azure 的 SQL Server VM 之间移动数据库。You can also use these same techniques to move databases between SQL Server VMs in Azure. 例如,无法使用支持的方法将 SQL Server 库映像 VM 从一个版本升级到另一个版本。For example, there is no supported way to upgrade a SQL Server gallery-image VM from one version/edition to another. 在这种情况下,应使用新的版本创建新的 SQL Server VM,并使用本文中的一项迁移技术移动数据库。In this case, you should create a new SQL Server VM with the new version/edition, and then use one of the migration techniques in this article to move your databases.

选择迁移方法Choosing your migration method

要获得最出色的数据传输性能,请使用压缩后的备份文件将数据库文件迁移到 Azure VM。For optimum data transfer performance, migrate the database files into the Azure VM using a compressed backup file.

若要在数据库迁移过程中最大限度地减少停机时间,请使用 AlwaysOn 选项或事务复制选项。To minimize downtime during the database migration process, use either the AlwaysOn option or the transactional replication option.

如果不能使用上述方法,请手动迁移数据库。If it is not possible to use the above methods, manually migrate your database. 使用此方法时,通常先进行数据库备份,接下来将数据库备份复制到 Azure,并执行数据库还原。Using this method, you will generally start with a database backup followed by a copy of the database backup into Azure and then perform a database restore. 用户还可以将数据库文件本身复制到 Azure,并附加这些文件。You can also copy the database files themselves into Azure and then attach them. 可以通过多种方法完成将数据库迁移到 Azure VM 的这一手动流程。There are several methods by which you can accomplish this manual process of migrating a database into an Azure VM.

Note

从较旧版本的 SQL Server 升级到 SQL Server 2014 或 SQL Server 2016 时,应考虑是否需要做一些更改。When you upgrade to SQL Server 2014 or SQL Server 2016 from older versions of SQL Server, you should consider whether changes are needed. 建议在迁移项目时处理好不受新版 SQL Server 支持的功能上的所有依赖项。We recommend that you address all dependencies on features not supported by the new version of SQL Server as part of your migration project. 有关受支持的版本和方案的详细信息,请参阅升级到 SQL ServerFor more information on the supported editions and scenarios, see Upgrade to SQL Server.

下表列出了各种主要迁移方法,并讨论了最适合使用该方法的场合。The following table lists each of the primary migration methods and discusses when the use of each method is most appropriate.

方法Method 源数据库版本Source database version 目标数据库版本Destination database version 源数据库备份大小限制Source database backup size constraint 注释Notes
使用压缩功能执行本地备份并将备份文件手动复制到 Azure 虚拟机中Perform on-premises backup using compression and manually copy the backup file into the Azure virtual machine SQL Server 2005 或更高版本SQL Server 2005 or greater SQL Server 2005 或更高版本SQL Server 2005 or greater Azure VM 存储限制Azure VM storage limit 这是一项很简单且经过严格测试的技术,适用于跨计算机移动数据库。This is a very simple and well-tested technique for moving databases across machines.
执行“备份到 URL”并从该 URL 还原到 Azure 虚拟机Perform a backup to URL and restore into the Azure virtual machine from the URL SQL Server 2012 SP1 CU2 或更高版本SQL Server 2012 SP1 CU2 or greater SQL Server 2012 SP1 CU2 或更高版本SQL Server 2012 SP1 CU2 or greater 小于 12.8 TB 用于 SQL Server 2016,否则大于 1 TB< 12.8 TB for SQL Server 2016, otherwise < 1 TB 此方法是通过另一种方式使用 Azure 存储将备份文件移至 VM。This method is just another way to move the backup file to the VM using Azure storage.
拆离后,将数据和日志文件复制到 Azure Blob 存储,并从 URL 附加到 Azure 虚拟机中的 SQL ServerDetach and then copy the data and log files to Azure blob storage and then attach to SQL Server in Azure virtual machine from URL SQL Server 2005 或更高版本SQL Server 2005 or greater SQL Server 2014 或更高版本SQL Server 2014 or greater Azure VM 存储限制Azure VM storage limit 如果计划使用 Azure Blob 存储服务存储这些文件并将它们附加到 Azure VM 中运行的 SQL Server,尤其是对于非常大的数据库,可以使用此方法。Use this method when you plan to store these files using the Azure Blob storage service and attach them to SQL Server running in an Azure VM, particularly with very large databases
将本地计算机转换为 Hyper-V VHD,上传到 Azure Blob 存储,并使用上传的 VHD 部署一个新虚拟机Convert on-premises machine to Hyper-V VHDs, upload to Azure Blob storage, and then deploy a new virtual machine using uploaded VHD SQL Server 2005 或更高版本SQL Server 2005 or greater SQL Server 2005 或更高版本SQL Server 2005 or greater Azure VM 存储限制Azure VM storage limit 在以下场合使用: 使用你自己的 SQL Server 许可证时;迁移的数据库会在较旧版本的 SQL Server 上运行时;或者将系统数据库和用户数据库一起作为依赖于其他用户数据库和/或系统数据库的数据库的一部分进行迁移时。Use when bringing your own SQL Server license, when migrating a database that you will run on an older version of SQL Server, or when migrating system and user databases together as part of the migration of database dependent on other user databases and/or system databases.
使用 Windows 导入/导出服务运送硬盘驱动器Ship hard drive using Windows Import/Export Service SQL Server 2005 或更高版本SQL Server 2005 or greater SQL Server 2005 或更高版本SQL Server 2005 or greater Azure VM 存储限制Azure VM storage limit 当手动复制方法速度太慢时使用 Windows 导入/导出服务,比如复制非常大的数据库Use the Windows Import/Export Service when manual copy method is too slow, such as with very large databases
使用“添加 Azure 副本”向导Use the Add Azure Replica Wizard SQL Server 2012 或更高版本SQL Server 2012 or greater SQL Server 2012 或更高版本SQL Server 2012 or greater Azure VM 存储限制Azure VM storage limit 最大程度减少故障时间,在具有 AlwaysOn 本地部署时使用Minimizes downtime, use when you have an Always On on-premises deployment
使用 SQL Server 事务复制Use SQL Server transactional replication SQL Server 2005 或更高版本SQL Server 2005 or greater SQL Server 2005 或更高版本SQL Server 2005 or greater Azure VM 存储限制Azure VM storage limit 在需要尽量减少故障时间且不具备 AlwaysOn 本地部署时使用Use when you need to minimize downtime and do not have an Always On on-premises deployment

备份和还原Backup and restore

通过压缩方式备份数据库,将备份复制到 VM,并还原数据库。Back up your database with compression, copy the backup to the VM, and then restore the database. 如果备份文件大于 1 TB,则必须对其进行条带化,因为 VM 磁盘的最大大小是 1 TB。If your backup file is larger than 1 TB, you must stripe it because the maximum size of a VM disk is 1 TB. 使用此手动方法按照下列常规步骤迁移用户数据库:Use the following general steps to migrate a user database using this manual method:

  1. 执行到本地位置的完整数据库备份。Perform a full database backup to an on-premises location.
  2. 创建或上传具有所需 SQL Server 版本的虚拟机。Create or upload a virtual machine with the version of SQL Server desired.
  3. 根据要求设置连接。Setup connectivity based on your requirements. 请参阅连接到 Azure 上的 SQL Server 虚拟机 (Resource Manager)See Connect to a SQL Server Virtual Machine on Azure (Resource Manager).
  4. 使用远程桌面、Windows 资源管理器或命令提示符处的复制命令将备份文件复制到 VM。Copy your backup file(s) to your VM using remote desktop, Windows Explorer or the copy command from a command prompt.

备份到 URL 并还原Backup to URL and restore

可以使用备份到 URL 的方式,并从 URL 还原到 VM,而不必备份到本地文件。Instead of backing up to a local file, you can use the backup to URL and then restore from URL to the VM. 在 SQL Server 2016 中,条带备份集受到支持,出于性能考虑,建议使用条带备份集,但它们必须超出每个 blob 的大小限制。With SQL Server 2016, striped backup sets are supported, are recommended for performance, and required to exceed the size limits per blob. 对于非常大的数据库,建议使用 Windows 导入/导出服务For very large databases, the use of the Windows Import/Export Service is recommended.

从 URL 拆离和附加Detach and attach from URL

拆离数据库和日志文件,并将其传输到 Azure Blob 存储Detach your database and log files and transfer them to Azure Blob storage. 然后在 Azure VM 上从 URL 附加数据库。Then attach the database from the URL on your Azure VM. 若需让物理数据库文件驻留在 Blob 存储中,请使用此选项。Use this if you want the physical database files to reside in Blob storage. 对于极大型数据库,这可能很有用。This might be useful for very large databases. 使用此手动方法按照下列常规步骤迁移用户数据库:Use the following general steps to migrate a user database using this manual method:

  1. 从本地数据库实例拆离数据库文件。Detach the database files from the on-premises database instance.
  2. 使用 AZCopy 命令行实用工具将分离的数据库文件复制到 Azure Blob 存储。Copy the detached database files into Azure blob storage using the AZCopy command-line utility.
  3. 从 Azure URL 将数据库文件附加到 Azure VM 中的 SQL Server 实例。Attach the database files from the Azure URL to the SQL Server instance in the Azure VM.

转换为 VM、上传到 URL 并部署为新的 VMConvert to VM and upload to URL and deploy as new VM

使用此方法可将本地 SQL Server 实例中的所有系统数据库和用户数据库迁移到 Azure 虚拟机。Use this method to migrate all system and user databases in an on-premises SQL Server instance to Azure virtual machine. 请使用此手动方法并按照下列常规步骤迁移整个 SQL Server 实例:Use the following general steps to migrate an entire SQL Server instance using this manual method:

  1. 将物理机或虚拟机转换为 Hyper-V VHD。Convert physical or virtual machines to Hyper-V VHDs.
  2. 使用 Add-AzureVHD cmdlet 将 VHD 文件上传到 Azure 存储。Upload VHD files to Azure Storage by using the Add-AzureVHD cmdlet.
  3. 使用上传的 VHD 部署新的虚拟机。Deploy a new virtual machine by using the uploaded VHD.

Note

若要迁移整个应用程序,请考虑使用 Azure Site RecoveryTo migrate an entire application, consider using Azure Site Recovery].

运送硬盘驱动器Ship hard drive

在通过网络上传成本过高或不可行时,可以使用 Windows 导入/导出服务方法将大量文件数据传输到 Azure Blob 存储中。Use the Windows Import/Export Service method to transfer large amounts of file data to Azure Blob storage in situations where uploading over the network is prohibitively expensive or not feasible. 借助此服务,可以将包含这些数据的一个或多个硬盘驱动器运送到 Azure 数据中心,在那里,你的数据会上传到你的存储帐户中。With this service, you send one or more hard drives containing that data to an Azure data center, where your data will be uploaded to your storage account.

后续步骤Next Steps

有关在 Azure 虚拟机中运行 SQL Server 的详细信息,请参阅 Azure 虚拟机中的 SQL Server 概述For more information about running SQL Server on Azure Virtual Machines, see SQL Server on Azure Virtual Machines overview.

Tip

如果对 SQL Server 虚拟机有任何疑问,请参阅常见问题解答If you have questions about SQL Server virtual machines, see the Frequently Asked Questions.

有关从捕获的映像创建 Azure SQL Server 虚拟机的说明,请参阅 CSS SQL Server 工程师博客上的 Tips & Tricks on 'cloning' Azure SQL virtual machines from captured images(有关从捕获的映像“克隆”Azure SQL 虚拟机的提示和技巧)。For instructions on creating an Azure SQL Server Virtual Machine from a captured image, see Tips & Tricks on 'cloning' Azure SQL virtual machines from captured images on the CSS SQL Server Engineers blog.