将 SQL Server 数据库迁移到 Azure SQL 数据库SQL Server database migration to Azure SQL Database

适用于:是Azure SQL 数据库 APPLIES TO: yesAzure SQL Database

本文介绍将 SQL Server 2005 或更高版本的数据库迁移到 Azure SQL 数据库的主要方法。In this article, you learn about the primary methods for migrating a SQL Server 2005 or later database to Azure SQL Database. 有关迁移到 Azure SQL 托管实例的信息,请参阅将 SQL Server 实例迁移到 Azure SQL 托管实例For information on migrating to Azure SQL Managed Instance, see Migrate a SQL Server instance to Azure SQL Managed Instance. 有关从其他平台迁移的迁移信息,请参阅 Azure 数据库迁移指南For migration information about migrating from other platforms, see Azure Database Migration Guide.

迁移到单一数据库或共用数据库Migrate to a single database or a pooled database

将 SQL Server 2005 或更高版本的数据库迁移到 Azure SQL 数据库有两种主要方法。There are two primary methods for migrating a SQL Server 2005 or later database to Azure SQL Database. 第一种方法相对简单,但迁移过程中需要一段时间(可能较长)的停机。The first method is simpler but requires some, possibly substantial, downtime during the migration. 第二种方法更复杂,但在迁移过程中的停机时间大大缩短。The second method is more complex, but substantially eliminates downtime during the migration.

两种方法均需使用 Data Migration Assistant (DMA) 确保源数据库与 Azure SQL 数据库兼容。In both cases, you need to ensure that the source database is compatible with Azure SQL Database using the Data Migration Assistant (DMA). SQL 数据库除了要解决服务器级操作和跨数据库操作的相关问题之外,还要解决与 SQL Server 的功能奇偶一致性问题。SQL Database is approaching feature parity with SQL Server, other than issues related to server-level and cross-database operations. 依赖部分支持或不受支持的函数的数据库和应用程序需要进行某种程度的重新设计来修复这些不兼容性,然后才能迁移 SQL Server 数据库。Databases and applications that rely on partially supported or unsupported functions need some re-engineering to fix these incompatibilities before the SQL Server database can be migrated.

备注

要将非 SQL Server 数据库(包括 Microsoft Access、Sybase、MySQL Oracle 和 DB2)迁移到 Azure SQL 数据库,请参阅 SQL Server 迁移助手To migrate a non-SQL Server database, including Microsoft Access, Sybase, MySQL Oracle, and DB2 to Azure SQL Database, see SQL Server Migration Assistant.

方法 1:在迁移过程中需停机的迁移Method 1: Migration with downtime during the migration

如果可以承受一定的停机时间,或者正在针对以后的迁移执行生产数据库的测试迁移,请使用此方法迁移到单一数据库或共用数据库。Use this method to migrate to a single or a pooled database if you can afford some downtime or you're performing a test migration of a production database for later migration. 有关教程,请参阅迁移 SQL Server 数据库For a tutorial, see Migrate a SQL Server database.

下面的列表包含使用此方法将 SQL Server 数据库迁移到单一数据库或共用数据库的常规工作流。The following list contains the general workflow for a SQL Server database migration of a single or a pooled database using this method. 有关迁移到 SQL 托管实例的信息,请参阅迁移到 SQL 托管实例For migration to SQL Managed Instance, see Migration to SQL Managed Instance.

VSSSDT 迁移示意图

  1. 使用最新版数据迁移助手 (DMA)评估数据库的兼容性。Assess the database for compatibility by using the latest version of the Data Migration Assistant (DMA).
  2. 以 Transact-SQL 脚本形式准备任何所需的修补程序。Prepare any necessary fixes as Transact-SQL scripts.
  3. 进行迁移时,创建正在迁移的源数据库的事务一致副本,或暂停源数据库中发生的新事务。Make a transactionally consistent copy of the source database being migrated or halt new transactions from occurring in the source database while migration is occurring. 实现后一种选择的方法包括禁用客户端连接或创建数据库快照Methods to accomplish this latter option include disabling client connectivity or creating a database snapshot. 迁移后,可以使用事务复制来更新已迁移的数据库,该数据库包含迁移的截止时间点后发生的更改。After migration, you may be able to use transactional replication to update the migrated databases with changes that occur after the cutoff point for the migration. 请参阅使用事务迁移进行迁移See Migrate using Transactional Migration.
  4. 部署 Transact-SQL 脚本,将修补程序应用到数据库副本。Deploy the Transact-SQL scripts to apply the fixes to the database copy.
  5. 通过使用数据迁移助手,将数据库副本迁移到 Azure SQL 数据库中的新数据库。Migrate the database copy to a new database in Azure SQL Database by using the Data Migration Assistant.

备注

还可以使用 BACPAC 文件,而不是 DMA。Rather than using DMA, you can also use a BACPAC file. 请参阅将 BACPAC 文件导入到 Azure SQL 数据库中的新数据库See Import a BACPAC file to a new database in Azure SQL Database.

优化迁移过程中的数据传输性能Optimizing data transfer performance during migration

以下列表包含的建议可帮助你在导入过程中获得最佳性能。The following list contains recommendations for best performance during the import process.

  • 若要获得最高的传输性能,请在预算允许范围内选择最高的服务层级和计算大小。Choose the highest service tier and compute size that your budget allows to maximize the transfer performance. 为了节省资金,可以在迁移完成后缩减规模。You can scale down after the migration completes to save money.
  • 尽量缩短 BACPAC 文件和目标数据中心的距离。Minimize the distance between your BACPAC file and the destination data center.
  • 在迁移过程中禁用自动统计Disable autostatistics during migration
  • 将表和索引分区Partition tables and indexes
  • 删除已编制索引的视图,在完成后重新创建这些视图Drop indexed views, and recreate them once finished
  • 将很少查询的历史数据转移到其他数据库,将这些历史数据迁移到 Azure SQL 数据库中的单独数据库。Remove rarely queried historical data to another database and migrate this historical data to a separate database in Azure SQL Database. 然后,可以使用 弹性查询来查询这些历史数据。You can then query this historical data using elastic queries.

迁移完成后优化性能Optimize performance after the migration completes

在迁移完成后更新统计信息并进行完全扫描。Update statistics with full scan after the migration is completed.

方法 2:使用事务复制Method 2: Use Transactional Replication

如果在发生迁移时你无法承受从生产中删除 SQL Server 数据库的后果,可以使用 SQL Server 事务复制作为迁移解决方案。When you can't afford to remove your SQL Server database from production while the migration is occurring, you can use SQL Server transactional replication as your migration solution. 若要使用此方法,源数据库必须满足事务复制要求且兼容 Azure SQL 数据库。To use this method, the source database must meet the requirements for transactional replication and be compatible for Azure SQL Database. 有关使用 AlwaysOn 的 SQL 复制的信息,请参阅配置 AlwaysOn 可用性组 (SQL Server) 的复制For information about SQL replication with Always On, see Configure Replication for Always On Availability Groups (SQL Server).

要使用此解决方案,请将 Azure SQL 数据库中的数据库配置为要迁移的 SQL Server 实例的订阅服务器。To use this solution, you configure your database in Azure SQL Database as a subscriber to the SQL Server instance that you wish to migrate. 在新的事务不断发生时,事务复制分发器将对要同步的数据库(发布服务器)中的数据进行同步。The transactional replication distributor synchronizes data from the database to be synchronized (the publisher) while new transactions continue occur.

使用事务复制时,对数据或架构所做的所有更改都会显示在 Azure SQL 数据库中的数据库中。With transactional replication, all changes to your data or schema show up in your database in Azure SQL Database. 同步完成后,如果你已准备好进行迁移,则可更改应用程序的连接字符串,使其指向数据库。Once the synchronization is complete and you're ready to migrate, change the connection string of your applications to point them to your database. 一旦事务复制清空保留在源数据库中的任何更改,并且所有应用程序都指向 Azure DB,即可卸载事务复制。Once transactional replication drains any changes left on your source database and all your applications point to Azure DB, you can uninstall transactional replication. Azure SQL 数据库中的数据库现在是生产系统。Your database in Azure SQL Database is now your production system.

SeedCloudTR 示意图

提示

还可以使用事务复制来迁移源数据库的子集。You can also use transactional replication to migrate a subset of your source database. 复制到 Azure SQL 数据库的发布可以限制为复制的数据库中表的子集。The publication that you replicate to Azure SQL Database can be limited to a subset of the tables in the database being replicated. 对于所复制的每一个表,可以将数据限制为行的子集和/或列的子集。For each table being replicated, you can limit the data to a subset of the rows and/or a subset of the columns.

使用事务复制工作流迁移到 SQL 数据库Migration to SQL Database using Transaction Replication workflow

重要

使用最新版本的 SQL Server Management Studio 以与 Azure 和 SQL 数据库的更新保持同步。Use the latest version of SQL Server Management Studio to remain synchronized with updates to Azure and SQL Database. 较旧版本的 SQL Server Management Studio 不能将 SQL 数据库设置为订阅服务器。Older versions of SQL Server Management Studio cannot set up SQL Database as a subscriber. 更新 SQL Server Management StudioUpdate SQL Server Management Studio.

  1. 设置分发Set up Distribution

  2. 创建发布Create Publication

  3. 创建订阅Create Subscription

有关迁移到 SQL 数据库的一些提示和差异Some tips and differences for migrating to SQL Database

  • 使用本地分发服务器Use a local distributor
    • 这会对服务器产生性能影响。Doing so causes a performance impact on the server.
    • 如果对性能的影响不可接受,可以使用另一台服务器,但这又会增大管理的复杂性。If the performance impact is unacceptable, you can use another server but it adds complexity in management and administration.
  • 选择快照文件夹时,请确保选择的文件夹足够大,可以保存想要复制的每个表的 BCP。When selecting a snapshot folder, make sure the folder you select is large enough to hold a BCP of every table you want to replicate.
  • 快照创建操作在完成之前会锁定关联的表,因此,请适当地计划好快照。Snapshot creation locks the associated tables until it's complete, so schedule your snapshot appropriately.
  • Azure SQL 数据库中仅支持推送订阅。Only push subscriptions are supported in Azure SQL Database. 只能从源数据库添加订阅服务器。You can only add subscribers from the source database.

解决数据库迁移的兼容性问题Resolving database migration compatibility issues

根据源数据库中的 SQL Server 版本以及正在迁移的数据库复杂性,可能会发现各种不同的不兼容性问题。There are a wide variety of compatibility issues that you might encounter, depending both on the version of SQL Server in the source database and the complexity of the database you're migrating. 旧版 SQL Server 的兼容性问题更多。Older versions of SQL Server have more compatibility issues. 除了使用所选搜索引擎的目标 Internet 搜索以外,还可以使用以下资源:Use the following resources, in addition to a targeted Internet search using your search engine of choices:

除了搜索 Internet 和使用这些资源,还可以使用 MSDN SQL Server 社区论坛StackOverflowIn addition to searching the Internet and using these resources, use the MSDN SQL Server community forums or StackOverflow.

重要

使用 Azure SQL 托管实例可迁移现有 SQL Server 实例及其数据库,而几乎不会出现兼容性问题。Azure SQL Managed Instance enables you to migrate an existing SQL Server instance and its databases with minimal to no compatibility issues. 请参阅什么是托管实例See What is a managed instance.

后续步骤Next steps