將 SQL Server 資料庫移轉至 Azure SQL DatabaseSQL Server database migration to 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 a single or pooled database in Azure SQL Database. 如需移轉至受控執行個體的資訊,請參閱將 SQL Server 執行個體移轉至 Azure SQL Database 受控執行個體For information on migrating to a Managed Instance, see Migrate to SQL Server instance to Azure SQL Database Managed Instance.

移轉至單一資料庫或集區資料庫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 a single or pooled database in 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 V12 的功能正逐漸與 SQL Server 的功能相等SQL Database V12 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 are 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. 如需移轉至「受控執行個體」,請參閱移轉至受控執行個體For migration to Managed Instance, see Migration to a 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 Azure SQL Database by using the Data Migration Assistant.

注意

您也可以不使用 DMA,而是使用 BACPAC 檔案。Rather than using DMA, you can also use a BACPAC file. 請參閱將 BACPAC 檔案匯入到新的 Azure SQL DatabaseSee Import a BACPAC file to a new 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 auto-statistics 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 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 cannot 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 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 Azure SQL Database. 同步處理完成且您已準備好進行移轉之後,請將您應用程式的連接字串變更成指向您的 Azure SQL Database。Once the synchronization is complete and you are ready to migrate, change the connection string of your applications to point them to your Azure SQL 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 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 以便與 Microsoft Azure 及 SQL Database 更新保持同步。Use the latest version of SQL Server Management Studio to remain synchronized with updates to Microsoft 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 is 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 are 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:

除了搜尋網際網路和使用這些資源,另請使用 MSDN SQL Server 社群論壇StackOverflowIn addition to searching the Internet and using these resources, use the MSDN SQL Server community forums or StackOverflow.

重要

「SQL Database 受控執行個體」可在將相容性問題降到最低至零的情況下,讓您移轉現有的 SQL Server 執行個體及其資料庫。SQL Database Managed Instance enables you to migrate an existing SQL Server instance and its databases with minimal to no compatibility issues. 請參閱什麼是受控執行個體See What is an Managed Instance.

後續步驟Next steps