SQL Server 實例遷移至 Azure SQL 受控執行個體SQL Server instance migration to Azure SQL Managed Instance

適用於: Azure SQL 受控執行個體

在本文中,您將瞭解將 SQL Server 2005 或更新版本實例遷移至 AZURE SQL 受控執行個體的方法。In this article, you learn about the methods for migrating a SQL Server 2005 or later version instance to Azure SQL Managed Instance. 如需遷移至單一資料庫或彈性集區的相關資訊,請參閱 遷移總覽: SQL Server 至 SQL DatabaseFor information on migrating to a single database or elastic pool, see Migration overview: SQL Server to SQL Database. 如需有關從其他平臺遷移的遷移資訊,以及工具和選項的指引,請參閱 遷移至 AZURE SQLFor migration information about migrating from other platforms and guidance on tools and options, see Migrate to Azure SQL.

注意

如果您想要快速啟動並試用 Azure SQL 受控執行個體,您可能會想要移至 快速入門手冊 ,而不是此頁面。If you want to quickly start and try Azure SQL Managed Instance, you might want to go to the quickstart 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 an elastic pool, see Migrate a SQL Server database to Azure SQL Database.

評估 SQL 受控執行個體相容性Assess SQL Managed Instance compatibility

首先,判斷 SQL 受控執行個體是否與您應用程式的資料庫需求相容。First, determine whether SQL Managed Instance is compatible with the database requirements of your application. SQL 受控執行個體的設計目的是要針對大部分使用 SQL Server 的現有應用程式,提供簡單的隨即轉移。SQL Managed Instance is designed to provide easy lift and shift migration for the majority of existing applications that use SQL Server. 不過,您有時可能需要的功能不受支援,而且執行因應措施的成本太高。However, you may sometimes require features or capabilities that are not yet supported and the cost of implementing a workaround is too high.

使用 Data Migration Assistant 來偵測 Azure SQL Database 上影響資料庫功能的潛在相容性問題。Use Data Migration Assistant to detect potential compatibility issues impacting database functionality on Azure SQL Database. 如果有一些回報的封鎖問題,您可能需要考慮替代選項,例如 AZURE VM 上的 SQL ServerIf there are some reported blocking issues, you might need to consider an alternative option, such as SQL Server on Azure VM. 以下是一些範例: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 you absolutely need to stay at a specific version of SQL Server (2012, for instance).
  • 如果您的計算需求遠低於受控實例供應專案 (一個 vCore,例如) ,而且資料庫匯總不是可接受的選項。If your compute requirements are much lower than managed instance offers (one vCore, for instance), and database consolidation is not an acceptable option.

如果您已解決所有已識別的遷移封鎖程式,並且繼續遷移至 SQL 受控執行個體,請注意,某些變更可能會影響工作負載的效能:If you have resolved all identified migration blockers and are continuing the migration to SQL 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.

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

建立效能基準Create a performance baseline

如果您需要將受控實例上的工作負載效能與在 SQL Server 上執行的原始工作負載進行比較,您必須建立將用於比較的效能基準。If you need to compare the performance of your workload on a 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, and 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 and CPU usage for the selected queries. 這些值可讓您比較在受控實例上執行的工作負載與來源 SQL Server 實例上的原始值的效能。These values would enable you to compare performance of workload running on the managed instance to the original values on your source SQL Server instance.

您需要在 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 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 the Page Life Expectancy memory performance counter.
  • 使用 sys.dm_io_virtual_file_stats view 或 效能計數器,監視來源 SQL Server 實例上的磁片 IO 使用量。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 a 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, or tempdb or parameterization issues, you should try to resolve them on your source SQL Server instance before taking the baseline and migration. 將已知問題遷移至任何新系統可能會導致非預期的結果,並使任何效能比較失效。Migrating known issues to any new system might 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 a managed instance with the baseline performance of the workload on the source SQL Server instance.

部署到最佳大小的受控實例Deploy to an optimally sized managed instance

SQL 受控執行個體是針對規劃移至雲端的內部部署工作負載量身打造的。SQL 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:

  • 根據基準 CPU 使用量,您可以布建符合您在 SQL Server 上使用之核心數目的受控實例,並記住,可能需要調整 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 the managed instance is installed.
  • 根據基準記憶體使用量,選擇 具有相符記憶體的服務層Based on the baseline memory usage, choose the service tier that has matching memory. 無法直接選擇記憶體數量,因此您必須選取具有相符記憶體的虛擬核心數量的受控實例 (例如,第5代) 中的 5.1 GB/vCore。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 the General Purpose (latency greater than 5 ms) and Business Critical (latency less than 3 ms) service tiers.
  • 根據基準輸送量,預先配置資料或記錄檔的大小,以取得預期的 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 afterward 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 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 a migration method and migrate

SQL 受控執行個體的目標是需要從內部部署或 Azure VM 資料庫執行大量資料庫移轉的使用者案例。SQL Managed Instance targets user scenarios requiring mass database migration from on-premises or Azure VM database implementations. 當您需要轉移定期使用實例層級和/或跨資料庫功能的應用程式後端時,它們是最佳選擇。They are the 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.

SQL 受控執行個體是一項受控服務,可讓您將一些一般 DBA 活動委派至平臺,因為這些活動是內建的。SQL 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. 因此,某些實例層級的資料並不需要遷移,例如定期備份或 Always On 設定的維護工作,因為這是內建的 高可用性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.

SQL 受控執行個體支援下列資料庫移轉選項 (目前這些是唯一支援的遷移方法) :SQL 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 資料庫移轉服務 是一個完全受控的服務,其設計目的是要讓多個資料庫來源順暢地從多個資料庫來源遷移到 Azure 資料平臺。Azure Database Migration Service 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. 針對您的企業工作負載,建議使用資料庫移轉服務遷移方法。Database Migration Service is the recommended method of migration for your enterprise workloads.

如果您在內部部署 SQL Server 使用 SQL Server Integration Services (SSIS) ,資料庫移轉服務還不支援將 ssis 目錄 (儲存 SSIS 套件的 SSISDB) 遷移,但您可以在 Azure-SSIS Integration Runtime 中布建 () IR Azure Data Factory,以在受控實例中建立新的 SSISDB,讓您可以將套件重新部署到其中。If you use SQL Server Integration Services (SSIS) on SQL Server on premises, Database Migration Service does not yet support migrating the SSIS catalog (SSISDB) that stores SSIS packages, but you can provision Azure-SSIS Integration Runtime (IR) in Azure Data Factory, which will create a new SSISDB in a managed instance so you can redeploy your packages to it. 請參閱 Azure Data Factory 中的建立 Azure-SSIS IRSee Create Azure-SSIS IR in Azure Data Factory.

若要深入瞭解此案例和資料庫移轉服務的設定步驟,請參閱 使用資料庫移轉服務,將您的內部部署資料庫移轉至受控實例To learn more about this scenario and configuration steps for Database Migration Service, see Migrate your on-premises database to managed instance using Database Migration Service.

從 URL 原生還原Native RESTORE from URL

將原生備份還原 ( .bak 檔案) 取自 Azure 儲存體所提供的 SQL Server 實例,這是 SQL 受控執行個體的主要功能之一,可讓您快速輕鬆地進行離線資料庫遷移。RESTORE of native backups (.bak files) taken from a SQL Server instance, available on Azure Storage, is one of the key capabilities of SQL Managed Instance that enables quick and easy offline database migration.

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

圖表顯示 SQL Server 有一個標示為 [備份]/[上傳至 URL] Azure 儲存體的箭號,以及另一個標示為 [從 Azure 儲存體至 SQL 受控執行個體的 URL 還原] 的第二個箭號。

下表詳述根據您執行的來源 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 2012 SP1 之前的 CU2Prior to 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 a managed instance 使用 SAS 認證從 URL 還原RESTORE FROM URL with SAS CREDENTIAL

重要

  • 當您使用原生還原選項將受 透明資料加密 保護的資料庫移轉到受控實例時,來自內部部署或 Azure VM SQL Server 的對應憑證必須在資料庫還原之前進行遷移。When you're migrating a database protected by Transparent Data Encryption to a managed instance using native restore option, the corresponding certificate from the on-premises or Azure VM SQL Server needs to be migrated before database restore. 如需詳細步驟,請參閱將 TDE 憑證移轉至受控執行個體For detailed steps, see Migrate a TDE cert to a 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 a 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 a 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 the risk of some performance degradations compared to your source SQL Server instance. 如果使用者資料庫在移轉之前的相容性層級為 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 a managed instance. 系統資料庫的相容性層級是 140。Compatibility level of system databases is 140. 由於遷移至受控實例的工作實際上是遷移至最新版的 SQL Server database engine,因此您應該注意,您需要重新測試工作負載的效能,以避免某些令人驚訝的效能問題。Since migration to a managed instance is actually migrating to the latest version of the 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:

  • 藉由調查不同的實例、資料庫、tempdb 設定和設定,將受控實例上的設定與來源 SQL Server 實例的設定對齊。Align your settings on the managed instance with the settings from the source SQL Server instance by investigating various instance, database, tempdb 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 better performance.
  • 深入瞭解 可能會造成受控實例與 SQL Server 之間效能差異的主要環境差異,並找出可能影響效能的風險。Learn about the key environment differences that might cause the performance differences between a 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 a 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 created baseline performance of your workload measures on the source SQL Server instance. 因此,您應該將效能參數與基準進行比較,並找出重大的差異。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 the managed instance and SQL Server. Azure SQL 受控執行個體是 SQL Server 的資料庫引擎,但受控實例上的基礎結構和高可用性設定可能會造成一些差異。Azure SQL Managed Instance is a SQL Server database engine, but infrastructure and high-availability configuration on a managed instance may introduce some differences. 您可能會預期某些查詢的速度較快,有些則可能會較慢。You might expect that some queries would be faster while some others might be slower. 比較的目標是要確認受控實例中的工作負載效能符合平均) 上 SQL Server (的效能,並找出效能不符合原始效能的任何重要查詢。The goal of comparison is to verify that workload performance in the managed instance matches the performance on SQL Server (on average), and identify 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 the managed instance is aligned or better than the workload performance on SQL Server. 在此情況下,您已成功確認遷移成功。In this case, you have successfully confirmed that migration is successful.
  • 大部分的效能參數和工作負載中的查詢都能正常運作,但有些例外狀況會降低效能。The 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 whether the underlying SQL plans changed or the queries are hitting some resource limits. 在此情況下,緩和措施可能是在重要查詢上套用一些提示 (例如,變更的相容性層級、舊版基數估算器) 直接或使用計劃指南、重建或建立可能會影響計畫的統計資料和索引。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 a managed instance compared to your source SQL Server instance. 在此情況下,請嘗試找出差異的根本原因,例如 達到某些資源限制 ,例如 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 the compatibility level of the database or change database settings like legacy cardinality estimation and re-start the test. 請查看受控實例或查詢存放區 views 提供的建議,以找出回歸效能的查詢。Review the recommendations provided by the managed instance or Query Store views to identify the queries that regressed performance.

重要

Azure SQL 受控執行個體有預設啟用的內建自動方案修正功能。Azure SQL Managed Instance has a 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 the 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

SQL 受控執行個體提供許多用於監視和疑難排解的 advanced tools,而且您應該使用它們來監視實例的效能。SQL 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 you would need to monitor are:

  • 實例上的 CPU 使用量,以判斷您布建的虛擬核心數目是否符合您的工作負載。CPU usage on the instance to determine if the number of vCores that you provisioned is the right match for your workload.
  • 您受控實例上的頁面壽命預期,以判斷 您是否需要額外的記憶體Page-life expectancy on your managed instance to determine if you need additional memory.
  • 或之類 INSTANCE_LOG_GOVERNOR 的統計資料 PAGEIOLATCH 會指出您是否有儲存體 IO 問題(尤其是在一般用途層上),您可能需要預先配置檔案,以取得更好的 IO 效能。Statistics like INSTANCE_LOG_GOVERNOR or PAGEIOLATCH that will tell if you have storage IO issues, especially on the General Purpose tier, where you might need to pre-allocate files to get better IO performance.

利用 advanced PaaS 功能Leverage advanced PaaS features

當您在完全受控的平臺上,並確認工作負載效能符合 SQL Server 工作負載效能時,請使用在服務中自動提供的優點。Once you are on a fully managed platform and you have verified that workload performances are matching your SQL Server workload performance, use advantages that are provided automatically as part of the 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 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. SQL 受控執行個體可讓您使用 時間點復原 (PITR) 來還原到此保留期間內的任何時間點。SQL 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.

除了先進的管理和安全性功能之外,受控實例還提供一組可協助您 監視和調整工作負載的 advanced tools。In addition to advanced management and security features, a 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 instances continuously monitors performance of your SQL plan execution statistics and automatically fixes the identified performance issues.

後續步驟Next steps