將 SQL Server 遷移至 Azure SQL Database 受控執行個體SQL Server instance migration to Azure SQL Database managed instance

在此文章中,您將了解用來將 SQL Server 2005 或更新版本執行個體移轉到 Azure SQL Database 受控執行個體的方法。In this article, you learn about the methods for migrating a SQL Server 2005 or later version instance to Azure SQL Database managed instance. 如需移轉至單一資料庫或彈性集區的相關資訊,請參閱移轉至單一或集區資料庫For information on migrating to a single database or elastic pool, see Migrate to a single or pooled database. 如需從其他平台移轉的移轉資訊,請參閱 Azure 資料庫移轉指南 (英文)。For migration information about migrating from other platforms, see Azure Database Migration Guide.


如果您想要快速啟動並試用受控執行個體,您可能想要前往快速入門指南而不是此頁面。If you want to quickly start and try Managed Instance, you might want to go to Quick-start guide instead of this page.

概括而言,資料庫移轉程序看起來像這樣:At a high level, the database migration process looks like:



若要將個別資料庫遷移至單一資料庫或彈性集區,請參閱將 SQL Server 資料庫遷移至 Azure SQL DatabaseTo migrate an individual database into either a single database or elastic pool, see Migrate a SQL Server database to Azure SQL Database.

評估受控執行個體的相容性Assess managed instance compatibility

首先,判斷受控執行個體是否與您應用程式的資料庫需求相容。First, determine whether managed instance is compatible with the database requirements of your application. 受控執行個體部署選項旨在為大部分內部部署或虛擬機器上使用 SQL Server 的現有應用程式,提供簡單的隨即轉移。The managed instance deployment option is designed to provide easy lift and shift migration for the majority of existing applications that use SQL Server on-premises or on virtual machines. 不過,有時候您可能需要尚不支援的功能,而且實作因應措施的成本非常高。However, you may sometimes require features or capabilities that are not yet supported and the cost of implementing a workaround are too high.

使用資料移轉小幫手 (DMA),可偵測影響 Azure SQL Database 資料庫功能的潛在相容性問題。Use Data Migration Assistant (DMA) to detect potential compatibility issues impacting database functionality on Azure SQL Database. DMA 尚不支援將受控執行個體做為移轉目的地,但建議您針對 Azure SQL Database 執行評估,並針對產品文件,仔細檢閱提報的功能同位和相容性問題清單。DMA does not yet support managed instance as migration destination, but it is recommended to run assessment against Azure SQL Database and carefully review list of reported feature parity and compatibility issues against product documentation. 請參閱 Azure SQL Database 功能,以檢查是否有一些回報的執行問題不是受控執行個體中造成作業無法繼續執行個問題,因為造成無法無移轉到 Azure SQL Database 的大部分問題在受控執行個體中都已移除。See Azure SQL Database features to check are there some reported blocking issues that not blockers in managed instance, because most of the blocking issues preventing a migration to Azure SQL Database have been removed with managed instance. 例如跨資料庫查詢、相同執行個體內的跨資料庫交易、其他 SQL 來源的連結伺服器、CLR、全域暫存資料表、執行個體層級檢視、Service Broker 等功能皆可在受控執行個體中使用。For instance, features like cross-database queries, cross-database transactions within the same instance, linked server to other SQL sources, CLR, global temp tables, instance level views, Service Broker and the like are available in managed instances.

若受控制執行個體部署選項並未移除一些回報的執行問題,您可能需要考慮替代選項,例如 Azure 虛擬機器上的 SQL ServerIf there are some reported blocking issues that are not removed with the managed instance deployment option, you might need to consider an alternative option, such as SQL Server on Azure virtual machines. 以下是一些範例:Here are some examples:

  • 如果您需要直接存取作業系統或檔案系統,例如在具有 SQL Server 的相同虛擬機器上安裝第三方或自訂代理程式。If you require direct access to the operating system or file system, for instance to install third party or custom agents on the same virtual machine with SQL Server.
  • 如果您的執行個體與尚不支援的功能有緊密相依性,例如 FileStream / FileTable、PolyBase 及跨執行個體交易等功能。If you have strict dependency on features that are still not supported, such as FileStream / FileTable, PolyBase, and cross-instance transactions.
  • 如果一定要將它保持在特定版本的 SQL Server (2012年執行個體)。If absolutely need to stay at a specific version of SQL Server (2012, for instance).
  • 如果您的計算需求遠低於受控執行個體提供的功能 (例如,只需要一個虛擬核心),而且資料庫彙總不是可接受的選項。If your compute requirements are much lower that managed instance offers (one vCore, for instance) and database consolidation is not acceptable option.

如果您已經解決所有識別移轉封鎖程式,並繼續移轉至受控執行個體,請注意,某些變更可能會影響工作負載的效能:If you have resolved all identified migration blockers and continuing the migration to Managed Instance, note that some of the changes might affect performance of your workload:

  • 強制性的完整復原模式並定期自動備份排程可能會影響您的工作負載或維護/ETL 動作的效能,如果您定期使用簡單/大量記錄模式或停止隨選的備份。Mandatory full recovery model and regular automated backup schedule might impact performance of your workload or maintenance/ETL actions if you have periodically used simple/bulk-logged model or stopped backups on demand.
  • 不同的伺服器或資料庫層級設定,例如追蹤旗標或相容性層級Different server or database level configurations such as trace flags or compatibility levels
  • 您使用例如透明資料庫加密 (TDE) 」 或 「 自動容錯移轉群組的新功能可能會影響 CPU 和 IO 使用量。New features that you are using such as Transparent Database Encryption (TDE) or auto-failover groups might impact CPU and IO usage.

管理的執行個體保證 99.99%可用性,即使在重要的案例中,因此無法停用這些功能所造成的額外負荷。Managed Instance guarantee 99.99% availability even in the critical scenarios, so overhead caused by these features cannot be disabled. 如需詳細資訊,請參閱 < 可能會導致不同的效能,在 SQL Server 和受控執行個體的根本原因For more information, see the root causes that might cause different performance on SQL Server and Managed Instance.

建立效能基準Create performance baseline

如果您需要比較您的受控執行個體與原始工作負載的 SQL Server 上執行的工作負載的效能,您必須建立要用於比較效能基準線。If you need to compare the performance of your workload on Managed Instance with your original workload running on SQL Server, you would need to create a performance baseline that will be used for comparison.

效能基準線是一組參數,例如平均/最大 CPU 使用量、 平均/最大磁碟 IO 延遲、 輸送量、 IOPS、 平均/最大頁面存留時間期望值,平均 tempdb 的大小上限。Performance baseline is a set of parameters such as average/max CPU usage, average/max disk IO latency, throughput, IOPS, average/max page life expectancy, average max size of tempdb. 您想要在移轉之後,有類似或變得更好的參數,因此請務必測量並記錄這些參數的基準值。You would like to have similar or even better parameters after migration, so it is important to measure and record the baseline values for these parameters. 除了系統參數,您必須選取一組代表性的查詢或最重要的查詢,在您的工作負載和量值最小/平均/最大持續期間,所選查詢的 CPU 使用量。In addition to system parameters, you would need to select a set of the representative queries or the most important queries in your workload and measure min/average/max duration, CPU usage for the selected queries. 這些值會讓您比較您的來源 SQL Server 上執行的受控執行個體的原始值的工作負載的效能。These values would enable you to compare performance of workload running on Managed Instance to the original values on your source SQL Server.

您必須在您的 SQL Server 執行個體量值的參數包括:Some of the parameters that you would need to measure on your SQL Server instance are:

  • 監視 SQL Server 執行個體上的 CPU 使用量並記錄平均值,並達到 CPU 使用率的尖峰。Monitor CPU usage on your SQL Server instance and record the average and peak CPU usage.
  • 監視您的 SQL Server 執行個體上的記憶體使用量,並判斷不同的元件,例如緩衝集區所使用的記憶體數量計劃快取中,資料行存放區集區記憶體內部 OLTP,依此類推。此外,您應該會發現 Page Life Expectancy memory 效能計數器的平均值和尖峰的值。Monitor memory usage on your SQL Server instance and determine the amount of memory used by different components such as buffer pool, plan cache, column-store pool, In-memory OLTP, etc. In addition, you should find average and peak values of Page Life Expectancy memory performance counter.
  • 監視來源 SQL Server 執行個體使用的磁碟 IO 使用量sys.dm_io_virtual_file_stats檢視或效能計數器Monitor disk IO usage on the source SQL Server instance using sys.dm_io_virtual_file_stats view or performance counters.
  • 藉由檢查動態管理檢視或查詢存放區,如果您從 SQL Server 2016 + 版本移轉,監視工作負載和查詢效能或您的 SQL Server 執行個體。Monitor workload and query performance or your SQL Server instance by examining Dynamic Management Views or Query Store if you are migrating from SQL Server 2016+ version. 找出平均持續時間和 CPU 使用量,您要與受控執行個體執行的查詢比較它們的工作負載中最重要的查詢。Identify average duration and CPU usage of the most important queries in your workload to compare them with the queries that are running on the Managed Instance.


如果您發現任何問題,SQL Server 上您工作負載,例如高 CPU 使用量、 固定的記憶體不足的壓力、 tempdb 或幾的問題,您應該嘗試解決您的來源 SQL Server 執行個體上進行的基準和移轉前。If you notice any issue with your workload on SQL Server such as high CPU usage, constant memory pressure, tempdb or parametrization issues, you should try to resolve them on your source SQL Server instance before taking the baseline and migration. 移轉知道任何新的系統 migh 問題會造成非預期的結果,並使其失效的任何效能比較。Migrating know issues to any new system migh cause unexpected results and invalidate any performance comparison.

此活動的結果為您應具有詳實記載和 CPU、 記憶體和 IO 使用量,在您的來源系統上的尖峰值,以及平均值和最大持續時間和平均 CPU 使用量,以及主控項的最重要的查詢工作負載中。As an outcome of this activity you should have documented average and peak values for CPU, memory, and IO usage on your source system, as well as average and max duration and CPU usage of the dominant and the most critical queries in your workload. 您應該稍後使用這些值,來比較您的受控執行個體的工作負載的來源 SQL Server 上的工作負載的基準效能的效能。You should use these values later to compare performance of your workload on Managed Instance with the baseline performance of the workload on the source SQL Server.

部署到最佳大小的受控執行個體Deploy to an optimally-sized managed instance

受控執行個體專為打算移至雲端的內部工作負載量身訂做。Managed instance is tailored for on-premises workloads that are planning to move to the cloud. 它引進新的購買模型,提供更大的彈性來選取適合您工作負載的正確資源層級。It introduces a new purchasing model that provides greater flexibility in selecting the right level of resources for your workloads. 在內部部署的環境中,您可能習慣使用實體核心數目與 IO 頻寬來調整這些工作負載大小。In the on-premises world, you are probably accustomed to sizing these workloads by using physical cores and IO bandwidth. 受控執行個體的購買模型是以虛擬核心 (vCore) 為基礎,再個別加上額外儲存體與可用 IO。The purchasing model for managed instance is based upon virtual cores, or “vCores,” with additional storage and IO available separately. 相對於目前使用的內部部署方案,VCore 模型可讓您較簡單地了解雲端中的計算需求。The vCore model is a simpler way to understand your compute requirements in the cloud versus what you use on-premises today. 這個新模型可讓您在雲端中具有正確大小的目的地環境。This new model enables you to right-size your destination environment in the cloud. 一些一般指導方針,以幫助您選擇的正確服務層和特性如下所示:Some general guidelines that might help you to choose the right service tier and characteristics are described here:

  • 根據您可以佈建受控執行個體符合您使用 SQL Server 的核心數目的 CPU 使用量的基準,需要記住該 CPU 特性可能需要進行調整以符合受控執行個體所在的 VM 特性安裝Based on the baseline CPU usage you can provision a Managed Instance that matches the number of cores that you are using on SQL Server, having in mind that CPU characteristics might need to be scaled to match VM characteristics where Managed Instance is installed.
  • 根據基準記憶體使用量選擇服務層有相符的記憶體Based on the baseline memory usage choose the service tier that has matching memory. 因此您必須選取具有相符的記憶體 (例如 5.1 GB/虛擬核心第 5 代中) 的虛擬核心數量的受控執行個體,就無法直接選擇的記憶體數量。The amount of memory cannot be directly chosen so you would need to select the Managed Instance with the amount of vCores that has matching memory (for example 5.1 GB/vCore in Gen5).
  • 根據基準 IO 延遲檔案子系統的一般用途 (大於 5 毫秒的延遲) 和業務關鍵服務層之間進行選擇 (延遲不超過 3 毫秒)。Based on the baseline IO latency of the file subsystem choose between General Purpose (latency greater than 5ms) and Business Critical service tiers (latency less than 3 ms).
  • 根據基準輸送量預先配置的資料大小,或記錄檔,以取得預期的 IO 效能。Based on baseline throughput pre-allocate the size of data or log files to get expected IO performance.

您可以選擇計算和儲存體資源,在部署時間以及之後進行變更,且不會造成您的應用程式使用的停機時間Azure 入口網站:You can choose compute and storage resources at deployment time and then change it afterwards without introducing downtime for your application using the Azure portal:


若要了解如何建立 VNet 基礎結構和受控執行個體,請參閱建立受控執行個體To learn how to create the VNet infrastructure and a managed instance, see Create a managed instance.


請務必讓您目的地 VNet 和子網路永遠符合受控執行個體的 VNet 需求It is important to keep your destination VNet and subnet always in accordance with managed instance VNet requirements. 任何相容性問題都可能會讓您無法建立新的執行個體,或使用已經建立的執行個體。Any incompatibility can prevent you from creating new instances or using those that you already created. 深入了解建立新的 設定現有的 網路。Learn more about creating new and configuring existing networks.

選取移轉方法並進行遷移Select migration method and migrate

受控執行個體部署選項鎖定的是需要將大量資料庫從內部部署或 IaaS 資料庫實作移轉的使用者案例。The managed instance deployment option targets user scenarios requiring mass database migration from on-premises or IaaS database implementations. 如果您需要隨即轉移定期使用執行個體層級和/或跨資料庫功能的應用程式後端,那麼這是最佳選擇。They are optimal choice when you need to lift and shift the back end of the applications that regularly use instance level and / or cross-database functionalities. 如果這是您的情況,您可以將整個執行個體移動至 Azure 中對應的環境,而不需要重新建構您的應用程式。If this is your scenario, you can move an entire instance to a corresponding environment in Azure without the need to re-architect your applications.

若要移動 SQL 執行個體,您需要謹慎規劃下列作業:To move SQL instances, you need to plan carefully:

  • 移轉必須共置的所有資料庫 (在相同執行個體上執行)The migration of all databases that need to be collocated (ones running on the same instance)
  • 移轉您應用程式依賴的執行個體層級物件,包括登入、認證、SQL Agent 作業和運算子,以及伺服器層級觸發程序。The migration of instance-level objects that your application depends on, including logins, credentials, SQL Agent Jobs and Operators, and server level triggers.

受控執行個體是受控的服務,可讓您將一些固定的 DBA 活動委派至平台,因為這些活動已內建。Managed instance is a managed service that allows you to delegate some of the regular DBA activities to the platform as they are built in. 因此,某些執行個體層級的資料就不需要遷移,例如,定期備份的維護作業或 Alwayson 組態,因為已內建高可用性Therefore, some instance level data does not need to be migrated, such as maintenance jobs for regular backups or Always On configuration, as high availability is built in.

受控執行個體支援下列資料庫移轉選項 (這些是目前唯一支援的移轉方法):Managed instance supports the following database migration options (currently these are the only supported migration methods):

  • Azure 資料庫移轉服務 - 幾乎零停機時間的移轉。Azure Database Migration Service - migration with near-zero downtime,
  • 原生RESTORE DATABASE FROM URL - 從 SQL Server 使用原生備份且需要一些停機時間。Native RESTORE DATABASE FROM URL - uses native backups from SQL Server and requires some downtime.

Azure 資料庫移轉服務Azure Database Migration Service

Azure 資料庫移轉服務 (DMS) 是一個完全受控的服務,能夠從多個資料庫來源無縫移轉到 Azure 資料平台,將停機時間降到最低。The Azure Database Migration Service (DMS) is a fully managed service designed to enable seamless migrations from multiple database sources to Azure Data platforms with minimal downtime. 此服務可簡化將現有第三方和 SQL Server 資料庫移動至 Azure 時所需的工作。This service streamlines the tasks required to move existing third party and SQL Server databases to Azure. 公開預覽中的部署選項包括 Azure SQL Database 中的資料庫,以及 Azure 虛擬機器中的 SQL Server 資料庫。Deployment options at public preview include databases in Azure SQL Database and SQL Server databases in an Azure Virtual Machine. DMS 是移轉企業工作負載的建議方法。DMS is the recommended method of migration for your enterprise workloads.

如果您在內部部署 SQL Server 上使用 SQL Server Integration Services (SSIS),DMS 尚未支援移轉 SSIS 目錄 (SSISDB),該目錄儲存 SSIS 套件,但是您可以在 Azure Data Factory (ADF) 中佈建 Azure-SSIS Integration Runtime (IR),這樣會在受控執行個體中建立新的 SSISDB,然後您可以將套件重新部署至其中,請參閱在 ADF 中建立 Azure-SSIS IRIf you use SQL Server Integration Services (SSIS) on your SQL Server on premises, DMS does not yet support migrating SSIS catalog (SSISDB) that stores SSIS packages, but you can provision Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF) that will create a new SSISDB in a managed instance and then you can redeploy your packages to it, see Create Azure-SSIS IR in ADF.

若要深入了解 DMS 的此案例和組態步驟,請參閱使用 DMS 將內部部署資料庫遷移至受控執行個體To learn more about this scenario and configuration steps for DMS, see Migrate your on-premises database to managed instance using DMS.

從 URL 原生還原Native RESTORE from URL

從 SQL Server 內部部署環境或虛擬機器上的 SQL Server 產生的原生備份 (.bak 檔案) (可從 Azure 儲存體取得) 進行還原,是受控執行個體部署選項的重要功能之一,讓您可以快速且輕鬆地進行離線資料庫移轉。RESTORE of native backups (.bak files) taken from SQL Server on-premises or SQL Server on Virtual Machines, available on Azure Storage, is one of key capabilities of the managed instance deployment option that enables quick and easy offline database migration.

下圖會提供程序的高階概觀:The following diagram provides a high-level overview of the process:


下表詳述根據您執行的來源 SQL Server 版本,可以使用的方法:The following table provides more information regarding the methods you can use depending on source SQL Server version you are running:

步驟Step SQL 引擎和版本SQL Engine and version 備份 / 還原方法Backup / Restore method
將備份放至 Azure 儲存體Put backup to Azure Storage SQL 2012 SP1 CU2 之前的版本Prior SQL 2012 SP1 CU2 直接將 .bak 檔案上傳到 Azure 儲存體Upload .bak file directly to Azure storage
2012 SP1 CU2 - 20162012 SP1 CU2 - 2016 使用已被取代的 WITH CREDENTIAL 語法直接備份Direct backup using deprecated WITH CREDENTIAL syntax
2016 和更新版本2016 and above 使用 WITH SAS CREDENTIAL 直接備份Direct backup using WITH SAS CREDENTIAL
從 Azure 儲存體還原至受控執行個體Restore from Azure storage to managed instance 使用 SAS 認證從 URL 還原RESTORE FROM URL with SAS CREDENTIAL


  • 使用原生還原選項將受到透明資料加密保護的資料庫遷移到受控執行個體時,來自內部部署或 IaaS SQL Server 的對應憑證必須在資料庫還原之前進行遷移。When migrating a database protected by Transparent Data Encryption to a managed instance using native restore option, the corresponding certificate from the on-premises or IaaS SQL Server needs to be migrated before database restore. 如需詳細步驟,請參閱將 TDE 憑證遷移至受控執行個體For detailed steps, see Migrate TDE cert to managed instance
  • 不支援系統資料庫還原。Restore of system databases is not supported. 若要移轉執行個體層級物件 (儲存在 master 或 msdb 資料庫中),我們建議透過指令碼來找出這些物件,並在目的地執行個體上執行 T-SQL 指令碼。To migrate instance level objects (stored in master or msdb databases), we recommend to script them out and run T-SQL scripts on the destination instance.

如需說明如何使用 SAS 認證將資料庫備份還原至受控執行個體的快速入門,請參閱從備份還原至受控執行個體For a quickstart showing how to restore a database backup to a managed instance using a SAS credential, see Restore from backup to a managed instance.

監視應用程式Monitor applications

當您完成移轉至受控執行個體時,您應該追蹤應用程式行為和工作負載的效能。Once you have completed the migration to Managed Instance, you should track the application behavior and performance of your workload. 此程序包含下列活動:This process includes the following activities:

比較效能基準線Compare performance with the baseline

您需要採取立即成功移轉後的第一個活動是比較的基準工作負載效能的工作負載的效能。The first activity that you would need to take immediately after successful migration is to compare the performance of the workload with the baseline workload performance. 此活動的目標是要確認您受控執行個體上的工作負載效能符合您的需求。The goal of this activity is to confirm that the workload performance on your Managed Instance meets your needs.

大部分的情況下將資料庫移轉至受控執行個體保留資料庫設定和其原始的相容性層級。Database migration to Managed Instance keeps database settings and its original compatibility level in majority of cases. 盡可能以降低的一些相較於您的來源 SQL Server 的效能降低的風險,就會保留原始的設定。The original settings are preserved where possible in order to reduce risk of some performance degradations compared to your source SQL Server. 如果使用者資料庫在移轉之前的相容性層級為 100 或以上,則移轉後相容性層級維持不變。If the compatibility level of a user database was 100 or higher before the migration, it remains the same after migration. 如果使用者資料庫在移轉之前的相容性層級為 90,那在升級後的資料庫中,相容性層級會設定為 100,這是受控執行個體中能支援的最低相容性層級。If the compatibility level of a user database was 90 before migration, in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in managed instance. 系統資料庫的相容性層級是 140。Compatibility level of system databases is 140. 因為移轉至受控執行個體實際上會移轉至 SQL Server Database Engine 的最新版本中,您應該注意,您需要重新測試您的工作負載,以避免一些令人驚訝的效能問題的效能。Since migration to Managed Instance is actually migrating to the latest version of SQL Server Database Engine, you should be aware that you need to re-test performance of your workload to avoid some surprising performance issues.

做為必要條件,請確定您已完成下列活動:As a prerequisite, make sure that you have completed the following activities:

  • 對齊的受控執行個體的設定,以從來源 SQL Server 執行個體設定,請調查各種執行個體、 資料庫、 temdb 設定和組態。Align your settings on Managed Instance with the settings from the source SQL Server instance by investigating various instance, database, temdb settings, and configurations. 請確定您已變更相容性層級或加密等的設定,然後再執行第一次的效能比較,或接受您所啟用的新功能的一些可能會影響某些查詢的風險。Make sure that you have not changed settings like compatibility levels or encryption before you run the first performance comparison, or accept the risk that some of the new features that you enabled might affect some queries. 若要減少移轉的風險,請只在進行效能監視後變更資料庫相容性層級。To reduce migration risks, change the database compatibility level only after performance monitoring.
  • 實作儲存體最佳作法指導方針一般用途例如預先配置大小的檔案,以便取得更佳的效能。Implement storage best practice guidelines for General Purpose such as pre-allocating the size of the files to get the better performance.
  • 深入了解主要環境差異可能會導致受控執行個體和 SQL Server 的效能差異並找出可能會影響效能的風險。Learn about the key environment differences that might cause the performance differences between Managed Instance and SQL Server and identify the risks that might affect the performance.
  • 請確定您保留已啟用的查詢存放區,並在您受控執行個體上的自動調整。Make sure that you keep enabled Query Store and Automatic tuning on your Managed Instance. 這些功能可讓您測量工作負載效能,並自動修正潛在的效能問題。These features enable you to measure workload performance and automatically fix the potential performance issues. 了解如何使用查詢存放區做為最佳的工具,可用來取得有關資料庫相容性層級變更之前和之後的工作負載效能的資訊中所述較新的 SQL Server 版本,在升級期間保持效能穩定性.Learn how to use Query Store as an optimal tool for getting information about workload performance before and after database compatibility level change, as explained in Keep performance stability during the upgrade to newer SQL Server version. 一旦您已備妥環境類似盡量使用您的內部部署環境,您可以開始執行您的工作負載並測量效能。Once you have prepared the environment that is comparable as much as possible with your on-premises environment, you can start running your workload and measure performance. 測量程序應該包含相同的參數,您測量當您建立基準效能,您的工作負載量值的來源 SQL Server 上Measurement process should include the same parameters that you measured while you create baseline performance of your workload measures on the source SQL Server. 如此一來,您應該比較的基準線的效能參數,並識別重要的差異。As a result, you should compare performance parameters with the baseline and identify critical differences.


在許多情況下,您就無法取得受控執行個體和 SQL Server 上完全相符的效能。In many cases, you would not be able to get exactly matching performance on Managed Instance and SQL Server. 受控執行個體是 SQL Server 資料庫引擎,但基礎結構和受控執行個體上的高可用性組態可能會造成一些差異。Managed Instance is a SQL Server database engine but infrastructure and High-availability configuration on Managed Instance may introduce some difference. 您可能預期某些查詢要快得多,而其他可能會變慢。You might expect that some queries would be faster while some other might be slower. 比較的目標是要驗證的受控執行個體中的工作負載效能是否符合 SQL Server 上的效能 (在平均值中),並找出是否有任何重大效能的查詢不符合您的原始效能。The goal of comparison is to verify that workload performance in Managed Instance matches the performance on SQL Server (in average), and identify are there any critical queries with the performance that don’t match your original performance.

可能的效能比較結果:The outcome of the performance comparison might be:

  • 受控執行個體上的工作負載效能對齊或更高的 SQL Server 上的工作負載效能。Workload performance on Managed Instance is aligned or better that the workload performance on SQL Server. 在此情況下您已經成功確認移轉能順利。In this case you have successfully confirmed that migration is successful.
  • 大部分的效能參數和中工作負載會正常執行,但有些例外狀況與效能降低的查詢。Majority of the performance parameters and the queries in the workload work fine, with some exceptions with degraded performance. 在此情況下,您必須找出差異和它們的重要性。In this case, you would need to identify the differences and their importance. 如果有一些重要的查詢,以降低效能,您應該調查為基礎的 SQL 計劃變更或查詢只達到某些資源限制。If there are some important queries with degraded performance, you should investigate are the underlying SQL plans changed or the queries are hitting some resource limits. 風險降低在此情況下可以套用一些提示上的重要查詢 (例如已變更的相容性層級,舊版基數估計工具) 是直接或使用計畫指南 rebuild 或 create statistics 和可能會影響計劃的索引。Mitigation in this case could be to apply some hints on the critical queries (for example changed compatibility level, legacy cardinality estimator) either directly or using plan guides, rebuild or create statistics and indexes that might affect the plans.
  • 大多數的查詢會變慢的相較於您的來源 SQL Server 受控執行個體。Most of the queries are slower on Managed Instance compared to your source SQL Server. 在此情況下嘗試識別根本原因的差異,例如達到某些資源限制喜歡 IO 限制、 記憶體限制、 執行個體記錄的速率限制等等。如果不有任何可能會導致不同的資源限制,請嘗試變更資料庫相容性層級,或變更資料庫設定喜歡舊版基數估計,然後重新啟動測試。In this case try to identify the root causes of the difference such as reaching some resource limit like IO limits, memory limit, instance log rate limit, etc. If there are no resource limits that can cause the difference, try to change compatibility level of the database or change database settings like legacy cardinality estimation and re-start the test. 檢閱受管理的執行個體或查詢存放區檢視所提供的建議,以找出迴歸效能的查詢。Review the recommendations provided by Managed Instance or Query Store views to identify the queries that regressed performance.


受控執行個體已預設啟用的內建的自動計畫更正功能。Managed Instance has built-in automatic plan correction feature that is enabled by default. 這項功能可確保運作正常中貼上作業的查詢就不會在未來的降低。This feature ensures that queries that worked fine in the paste would not degrade in the future. 請確定已啟用這項功能,而且您有夠長的工作負載執行與舊的設定之前您若要讓受控執行個體若要了解的基準效能和計劃變更新的設定。Make sure that this feature is enabled and that you have executed the workload long enough with the old settings before you change new settings in order to enable Managed Instance to learn about the baseline performance and plans.

進行變更的參數,或升級服務層,直到您取得符合您需求的工作負載效能,同時回歸到最適合的設定。Make the change of the parameters or upgrade service tiers to converge to the optimal configuration until you get the workload performance that fits your needs.

監視效能Monitor performance

受控執行個體提供許多進階的工具,可監視和疑難排解,以及您應該使用它們來監視您的執行個體的效能。Managed Instance provides a lot of advanced tools for monitoring and troubleshooting, and you should use them to monitor performance on your instance. 某些參數,您想要監視是:Some of the parameters that your would need to monitor are:

  • 若要判斷執行個體上的 CPU 使用量會佈建的虛擬核心的數目是您的工作負載的正確相符項目。CPU usage on the instance to determine does the number of vCores that you provisioned is the right match for your workload.
  • 若要判斷您受控執行個體上的頁面存留時間期望值您需要額外的記憶體Page-life expectancy on your Managed Instance to determine do you need additional memory.
  • 等候統計資料,像是INSTANCE_LOG_GOVERNORPAGEIOLATCH,會告訴您有儲存體 IO 問題,特別是在您可能需要預先配置檔案,以便取得更佳的 IO 效能的一般用途層上。Wait statistics like INSTANCE_LOG_GOVERNOR or PAGEIOLATCH that will tell do you have storage IO issues, especially on General Purpose tier where you might need to pre-allocate files to get better IO performance.

利用進階的 PaaS 功能Leverage advanced PaaS features

一旦您是在完全受控的平台上,而且您已驗證的工作負載效能會比 SQL Server 工作負載效能,需要 SQL Database 服務的一部分自動提供的優點。Once you are on a fully managed platform and you have verified that workload performances are matching you SQL Server workload performance, take advantages that are provided automatically as part of the SQL Database service.

即使您不在受管理的執行個體中進行一些變更,在移轉期間,可能會有高,您會將一些新功能時操作您的執行個體,以利用最新的資料庫引擎增強功能開啟。Even if you don't make some changes in managed instance during the migration, there are high chances that you would turn on some of the new features while you are operating your instance to take an advantage of the latest database engine improvements. 某些變更才會啟用一次已變更資料庫相容性層級Some changes are only enabled once the database compatibility level has been changed.

比方說,您不需要在受控執行個體上建立備份 (服務會自動為您執行備份)。For instance, you don’t have to create backups on managed instance - the service performs backups for you automatically. 您無法再擔心如何排程、使用及管理備份。You no longer must worry about scheduling, taking, and managing backups. 受控執行個體使用時間點復原 (PITR),讓您能夠還原到此保留期限內的任何時間點。Managed instance provides you the ability to restore to any point in time within this retention period using Point in Time Recovery (PITR). 此外,您不需要擔心如何設定為高可用性,因為高可用性已內建。Additionally, you do not need to worry about setting up high availability as high availability is built in.

若要加強安全性,請考慮使用Azure Active Directory 驗證稽核威脅偵測資料列層級安全性,並動態資料遮罩)。To strengthen security, consider using Azure Active Directory Authentication, Auditing, threat detection, row-level security, and dynamic data masking ).

除了進階的管理和安全性功能,受控執行個體提供進階的工具,可協助您將一組監視和調整您的工作負載In addition to advanced management and security features, Managed Instance provides a set of advanced tools that can help you to monitor and tune your workload. Azure SQL 分析可讓您監視大量的受管理的執行個體,並集中管理大量的執行個體與資料庫的監視。Azure SQL analytics enables you to monitor a large set of Managed Instances and centralize monitoring of a large number of instances and databases. 自動調整受控執行個體中持續監視您的 SQL 計劃執行統計資料的效能,並自動修正所識別之的效能問題。Automatic tuning in Managed Instance continuously monitor performance of your SQL plan execution statistics and automatically fix the identified performance issues.

後續步驟Next steps