將 SQL Server 資料庫移轉至 Azure SQL DatabaseSQL Server database migration to Azure SQL Database

適用於: Azure SQL Database

在本文中,您將瞭解將 SQL Server 2005 或更新版本的資料庫移轉至 Azure SQL Database 的主要方法。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 SQL 的指引,請參閱 遷移至 AZURE sqlFor guidance on choosing migration options and tools to migrate to Azure SQL, see Migrate to Azure SQL

移轉至單一資料庫或集區資料庫Migrate to a single database or a pooled database

有兩種主要方法可將 SQL Server 2005 或更新版本的資料庫移轉至 Azure SQL Database。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 Database 相容。In both cases, you need to ensure that the source database is compatible with Azure SQL Database using the Data Migration Assistant (DMA). SQL Database 正接近與 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 Database,請參閱 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 DatabaseFor 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. 使用最新版的 Data Migration Assistant (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. 使用 Data Migration Assistant 將資料庫複本遷移至 Azure SQL Database 中的新資料庫。Migrate the database copy to a new database in Azure SQL Database by using the Data Migration Assistant.

注意

您也可以不使用 DMA,而是使用 BACPAC 檔案。Rather than using DMA, you can also use a BACPAC file. 請參閱 Azure SQL Database 中的將 BACPAC 檔案匯入至新的資料庫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.
  • 在遷移期間停用 autostatisticsDisable autostatistics during migration
  • 分割區資料表與索引Partition tables and indexes
  • 捨棄索引檢視表,然後於移轉完成後重新建立Drop indexed views, and recreate them once finished
  • 將很少查詢的歷程記錄資料移至另一個資料庫,並將此歷程記錄資料移轉至 Azure SQL Database 中的個別資料庫。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 Database 相容。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 Database 中的資料庫設定為您想要遷移之 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 Database 的資料庫中。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 Database 中的資料庫現在是您的生產系統。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 Database 的發佈可以限制為複寫的資料庫中資料表的子集。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 DatabaseMigration to SQL Database using Transaction Replication workflow

重要

使用 SQL Server Management Studio 的最新版本,以與 Azure 和 SQL Database 的更新保持同步。Use the latest version of SQL Server Management Studio to remain synchronized with updates to Azure and SQL Database. 舊版 SQL Server Management Studio 無法將 SQL Database 設定為訂閱者。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 Database 的差異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 Database 僅支援推送訂用帳戶。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. 除了使用您選擇之搜尋引擎的目標網際網路搜尋之外,請使用下列資源︰Use the following resources, in addition to a targeted Internet search using your search engine of choices:

除了搜尋網際網路和使用這些資源,另請使用 Azure SQL Database 的 Microsoft 問答頁 (英文) 或 StackOverflow (英文)。In addition to searching the Internet and using these resources, use the Microsoft Q&A question page for Azure SQL Database 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