遷移總覽: SQL Server 至 SQL 受控執行個體Migration overview: SQL Server to SQL Managed Instance

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

瞭解將您的 SQL Server 遷移至 Azure SQL 受控執行個體的不同遷移選項和考慮。Learn about different migration options and considerations to migrate your SQL Server to Azure SQL Managed Instance.

您可以遷移在內部部署或上執行的 SQL Server:You can migrate SQL Server running on-premises or on:

  • 虛擬機器上的 SQL ServerSQL Server on Virtual Machines
  • Amazon Web Services (AWS) EC2Amazon Web Services (AWS) EC2
  • Amazon 關係資料庫服務 (AWS RDS) Amazon Relational Database Service (AWS RDS)
  • 計算引擎 (Google Cloud Platform GCP) Compute Engine (Google Cloud Platform - GCP)
  • 適用于 SQL Server (Google Cloud Platform 的雲端 SQL-GCP) Cloud SQL for SQL Server (Google Cloud Platform – GCP)

如需其他案例,請參閱 資料庫移轉指南For other scenarios, see the Database Migration Guide.

概觀Overview

如果您需要完全受控的服務,而不需要管理虛擬機器或其作業系統,則建議使用AZURE SQL 受控執行個體作為 SQL Server 工作負載的目標選項。Azure SQL Managed Instance is a recommended target option for SQL Server workloads that require a fully managed service without having to manage virtual machines or their operating systems. SQL 受控執行個體可讓您將內部部署應用程式隨即轉移至 Azure,只需進行應用程式或資料庫的變更,同時將您的實例與原生虛擬網路 (VNet) 支援完全隔離。SQL Managed Instance enables you to lift-and-shift your on-premises applications to Azure with minimal application or database changes while having complete isolation of your instances with native virtual network (VNet) support.

考量Considerations

評估遷移選項時要考慮的重要因素取決於:The key factors to consider when evaluating migration options depend on:

  • 伺服器和資料庫的數目Number of servers and databases
  • 資料庫的大小Size of databases
  • 在遷移過程中可接受的商務停機時間Acceptable business downtime during the migration process

將 SQL Server 遷移至 SQL 受控執行個體的主要優點之一,就是您可以選擇遷移整個實例,或只是個別資料庫的子集。One of the key benefits of migrating your SQL Servers to SQL Managed Instance is that you can choose to migrate the entire instance, or just a subset of individual databases. 請仔細規劃在您的遷移程式中包含下列各項:Carefully plan to include the following in your migration process:

  • 所有需要共存于相同實例的資料庫All databases that need to be colocated to the same instance
  • 應用程式所需的實例層級物件,包括登入、認證、SQL Agent 作業和操作員,以及伺服器層級的觸發程式。Instance-level objects required for your application, including logins, credentials, SQL Agent jobs and operators, and server-level triggers.

注意

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

選擇適當的目標Choose appropriate target

一些一般指導方針,可協助您選擇適當的 SQL 受控執行個體服務層級和特性,以協助符合您的 效能基準Some general guidelines to help you choose the right service tier and characteristics of SQL Managed Instance to help match your performance baseline:

  • 使用 [CPU 使用量] 基準來布建受控實例,以符合您 SQL Server 的實例所使用的核心數目。Use the CPU usage baseline to provision a managed instance that matches the number of cores your instance of SQL Server uses. 可能需要調整資源以符合 硬體產生特性It may be necessary to scale resources to match the hardware generation characteristics.
  • 使用 [記憶體使用量] 基準來選擇適當符合記憶體配置的 vCore 選項Use the memory usage baseline to choose a vCore option that appropriately matches your memory allocation.
  • 您可以使用檔案子系統的基準 IO 延遲,在一般用途 (延遲超過5毫秒) 和業務關鍵 (延遲低於 3 ms) 服務層級。Use the baseline IO latency of the file subsystem to choose between General Purpose (latency greater than 5 ms) and Business Critical (latency less than 3 ms) service tiers.
  • 使用基準輸送量來預先配置資料和記錄檔的大小,以達到預期的 IO 效能。Use the baseline throughput to preallocate the size of the data and log files to achieve expected IO performance.

您可以在部署期間選擇計算和儲存體資源,然後在使用 Azure 入口網站 之後變更它們,而不會導致您的應用程式停機。You can choose compute and storage resources during deployment and then change them after using the Azure portal without incurring downtime for your application.

重要

受控實例虛擬網路需求中的任何差異都可能會讓您無法建立新的實例,或使用現有的實例。Any discrepancy in the managed instance virtual network requirements can prevent you from creating new instances or using existing ones. 深入瞭解如何 建立新   的網路和設定 現有的   網路。Learn more about creating new and configuring existing networks.

SQL Server VM 替代方案SQL Server VM alternative

您的企業可能會有需求,讓 Azure Vm 上的 SQL Server 比 Azure SQL 受控執行個體更適合的目標。Your business may have requirements that make SQL Server on Azure VMs a more suitable target than Azure SQL Managed Instance.

如果您的企業適用下列情況,請考慮改為移至 SQL Server VM:If the following apply to your business, consider moving to a SQL Server VM instead:

  • 如果您需要直接存取作業系統或檔案系統,例如使用 SQL Server 在相同的虛擬機器上安裝協力廠商或自訂代理程式。If you require direct access to the operating system or file system, such as 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.

移轉工具Migration tools

建議的遷移工具是 Data Migration Assistant 和 Azure 資料庫的遷移服務。The recommended tools for migration are the Data Migration Assistant and the Azure Database Migration Service. 還有其他可用的替代方法。There are other alternative migration options available as well.

下表列出建議的遷移工具:The following table lists the recommended migration tools:

技術Technology 描述Description
Azure 資料庫移轉服務 (DMS) (部分機器翻譯)Azure Database Migration Service (DMS) 第一方 Azure 服務支援在離線模式下針對可在遷移過程中承受停機的應用程式,進行遷移。First party Azure service that supports migration in the offline mode for applications that can afford downtime during the migration process. 不同于線上模式的連續遷移,離線模式遷移會執行從來源到目標的完整資料庫備份的一次性還原。Unlike the continuous migration in online mode, offline mode migration runs a one-time restore of a full database backup from the source to the target.
原生備份和還原Native backup and restore SQL 受控執行個體支援 () .bak 檔案還原原生 SQL Server 資料庫備份,讓可將完整資料庫備份提供給 Azure 儲存體的客戶成為最簡單的遷移選項。SQL Managed Instance supports RESTORE of native SQL Server database backups (.bak files), making it the easiest migration option for customers who can provide full database backups to Azure storage. 在本文稍後的「 遷移資產」一節 中,也支援並記載完整和差異備份。Full and differential backups are also supported and documented in the migration assets section later in this article.

替代工具Alternative tools

下表列出替代的遷移工具:The following table lists alternative migration tools:

技術Technology 描述Description
異動複寫Transactional replication 提供發行者-訂閱者類型遷移選項,同時維持交易一致性,以將資料從來源 SQL Server 資料庫資料表 (s) 複寫至 SQL 受控執行個體。Replicate data from source SQL Server database table(s) to SQL Managed Instance by providing a publisher-subscriber type migration option while maintaining transactional consistency.
大量複製Bulk copy 大量複製程式 (bcp) 公用程式會將 SQL Server 實例的資料複製到資料檔案中。The bulk copy program (bcp) utility copies data from an instance of SQL Server into a data file. 使用 BCP 公用程式從來源匯出資料,並將資料檔案匯入目標 SQL 受控執行個體。Use the BCP utility to export the data from your source and import the data file into the target SQL Managed Instance.

針對將資料移至 Azure SQL Database 的高速大量複製作業, 智慧型大量複製工具 可用來利用平行複製工作,將傳送速率最大化。For high-speed bulk copy operations to move data to Azure SQL Database, Smart Bulk Copy tool can be used to maximize transfer speeds by leveraging parallel copy tasks.
匯入匯出 Wizard/BACPACImport Export Wizard / BACPAC BACPAC 是一個 Windows 檔案,其 .bacpac 副檔名會封裝資料庫的架構和資料。BACPAC is a Windows file with a .bacpac extension that encapsulates a database's schema and data. BACPAC 可以用來從來源 SQL Server 匯出資料,以及將檔案匯回 Azure SQL 受控執行個體。BACPAC can be used to both export data from a source SQL Server and to import the file back into Azure SQL Managed Instance.
Azure Data Factory (ADF)Azure Data Factory (ADF) Azure Data Factory 中的 複製活動 會使用內建的連接器和 受控執行個體,將資料從來源 SQL Server 資料庫 (s) 遷移至 SQL Integration Runtime。The Copy activity in Azure Data Factory migrates data from source SQL Server database(s) to SQL Managed Instance using built-in connectors and an Integration Runtime.

ADF 支援各種不同的 連接器 ,可將資料從 SQL Server 來源移至 SQL 受控執行個體。ADF supports a wide range of connectors to move data from SQL Server sources to SQL Managed Instance.

比較遷移選項Compare migration options

比較遷移選項來選擇適合您商務需求的路徑。Compare migration options to choose the path appropriate to your business needs.

下表比較建議的遷移選項:The following table compares the recommended migration options:

移轉選項Migration option 使用時機When to use 考量Considerations
Azure 資料庫移轉服務 (DMS) (部分機器翻譯)Azure Database Migration Service (DMS) -大規模遷移單一資料庫或多個資料庫。- Migrate single databases or multiple databases at scale.
-可在遷移過程中容納停機時間。- Can accommodate downtime during migration process.

支援的來源:Supported sources:
-SQL Server (2005-2019) 內部部署或 Azure VM- SQL Server (2005 - 2019) on-premises or Azure VM
-AWS EC2- AWS EC2
-AWS RDS- AWS RDS
-GCP Compute SQL Server VM- GCP Compute SQL Server VM
-大規模的大規模遷移可透過 PowerShell自動進行。- Migrations at scale can be automated via PowerShell.
-完成遷移的時間取決於資料庫大小,並且受備份和還原時間的影響。- Time to complete migration is dependent on database size and impacted by backup and restore time.
-可能需要足夠的停機時間。- Sufficient downtime may be required.
原生備份和還原Native backup and restore -遷移個別的企業營運應用程式資料庫 (s) 。- Migrate individual line-of-business application database(s).
-快速且輕鬆地進行遷移,而不需要個別的遷移服務或工具。- Quick and easy migration without a separate migration service or tool.

支援的來源:Supported sources:
-SQL Server (2005-2019) 內部部署或 Azure VM- SQL Server (2005 - 2019) on-premises or Azure VM
-AWS EC2- AWS EC2
-AWS RDS- AWS RDS
-GCP Compute SQL Server VM- GCP Compute SQL Server VM
-資料庫備份會使用多個執行緒來優化資料傳輸至 Azure Blob 儲存體,但 ISV 頻寬和資料庫大小可能會影響傳送速率。- Database backup uses multiple threads to optimize data transfer to Azure Blob storage but ISV bandwidth and database size can impact transfer rate.
-停機時間應能容納執行完整備份和還原 (所需的時間,這是) 資料作業的大小。- Downtime should accommodate the time required to perform a full backup and restore (which is a size of data operation).

替代選項Alternative options

下表將比較替代的遷移選項:The following table compares the alternative migration options:

方法/技術Method / technology 使用時機When to use 考量Considerations
異動複寫Transactional replication -持續將源資料庫資料表的變更發佈至目標 SQL 受控執行個體資料庫資料表,以進行遷移。- Migrate by continuously publishing changes from source database tables to target SQL Managed Instance database tables.
-所選資料表的完整或部分資料庫移轉 (資料庫) 的子集。- Full or partial database migrations of selected tables (subset of database).

支援的來源:Supported sources:
-SQL Server (2012-2019) 但有一些限制- SQL Server (2012 - 2019) with some limitations
-AWS EC2- AWS EC2
-GCP Compute SQL Server VM- GCP Compute SQL Server VM

-相較于其他遷移選項,安裝程式相當複雜。- Setup is relatively complex compared to other migration options.
-提供連續複寫選項來遷移資料 (而不需要讓資料庫離線) 。- Provides a continuous replication option to migrate data (without taking the databases offline).
-在來源 SQL Server 上設定「發行者」時,異動複寫有一些要考慮的限制。- Transactional replication has a number of limitations to consider when setting up the Publisher on the source SQL Server. 若要深入瞭解,請參閱 發佈物件的限制See Limitations on Publishing Objects to learn more.
-可以使用 監視複寫活動 的功能。- Capability to monitor replication activity is available.
大量複製Bulk copy -遷移完整或部分資料移轉。- Migrating full or partial data migrations.
-可以容納停機時間。- Can accommodate downtime.

支援的來源:Supported sources:
-SQL Server (2005-2019) 內部部署或 Azure VM- SQL Server (2005 - 2019) on-premises or Azure VM
-AWS EC2- AWS EC2
-AWS RDS- AWS RDS
-GCP Compute SQL Server VM- GCP Compute SQL Server VM
-需要從來源匯出資料並匯入目標的停機時間。- Requires downtime for exporting data from source and importing into target.
-匯出/匯入中所使用的檔案格式和資料類型必須與資料表架構一致。- The file formats and data types used in the export / import need to be consistent with table schemas.
匯入匯出 Wizard/BACPACImport Export Wizard / BACPAC -遷移個別的企業營運應用程式資料庫 (s) 。- Migrate individual Line-of-business application database(s).
-適用于較小的資料庫。- Suited for smaller databases.
不需要個別的遷移服務或工具。Does not require a separate migration service or tool.

支援的來源:Supported sources:
-SQL Server (2005-2019) 內部部署或 Azure VM- SQL Server (2005 - 2019) on-premises or Azure VM
-AWS EC2- AWS EC2
-AWS RDS- AWS RDS
-GCP Compute SQL Server VM- GCP Compute SQL Server VM

-需要停機,因為資料需要在來源匯出並匯入目的地。- Requires downtime as data needs to be exported at the source and imported at the destination.
-匯出/匯入中所使用的檔案格式和資料類型必須與資料表架構一致,以避免截斷/資料類型不符的錯誤。- The file formats and data types used in the export / import need to be consistent with table schemas to avoid truncation / data type mismatch errors.
-匯出具有大量物件的資料庫所花費的時間,可能會大幅增加。- Time taken to export a database with a large number of objects can be significantly higher.
Azure Data Factory (ADF)Azure Data Factory (ADF) -從 SQL Server 資料庫 (s) 的來源遷移和/或轉換資料。- Migrating and/or transforming data from source SQL Server database(s).
-將資料從多個資料來源合併到 Azure SQL 受控執行個體通常適用于商業智慧 (BI) 工作負載。- Merging data from multiple sources of data to Azure SQL Managed Instance typically for Business Intelligence (BI) workloads.
-需要在 ADF 中建立資料移動管線,以將資料從來源移至目的地。- Requires creating data movement pipelines in ADF to move data from source to destination.
- 成本 是很重要的考慮,而且是以管線觸發程式、活動執行、資料移動的持續時間等為基礎。- Cost is an important consideration and is based on the pipeline triggers, activity runs, duration of data movement, etc.

功能互通性Feature interoperability

當您遷移依賴其他 SQL Server 功能的工作負載時,還有其他考慮。There are additional considerations when migrating workloads that rely on other SQL Server features.

SQL Server Integration ServicesSQL Server Integration Services

使用 Azure 資料庫移轉服務 (DMS) ,將 SSISDB 中的 SQL SERVER INTEGRATION SERVICES (SSIS) 套件和專案遷移至 azure SQL 受控執行個體。Migrate SQL Server Integration Services (SSIS) packages and projects in SSISDB to Azure SQL Managed Instance using Azure Database Migration Service (DMS).

僅支援從 SQL Server 2012 開始的 SSISDB 中的 SSIS 套件來進行遷移。Only SSIS packages in SSISDB starting with SQL Server 2012 are supported for migration. 在遷移之前轉換舊版 SSIS 套件。Convert legacy SSIS packages before migration. 若要深入瞭解,請參閱 專案轉換教學 課程。See the project conversion tutorial to learn more.

SQL Server Reporting ServicesSQL Server Reporting Services

SQL Server Reporting Services (SSRS) 報表可以遷移至 Power BI 中的分頁報表。SQL Server Reporting Services (SSRS) reports can be migrated to paginated reports in Power BI. 使用 RDL 遷移工具 來協助準備和遷移您的報表。Use the RDL Migration Tool to help prepare, and migrate your reports. 此工具是由 Microsoft 所開發,可協助客戶將 RDL 報表從其 SSRS 伺服器遷移至 Power BI。This tool was developed by Microsoft to help customers migrate RDL reports from their SSRS servers to Power BI. 它可在 GitHub 上取得,並記載遷移案例的端對端逐步解說。It is available on GitHub, and it documents an end-to-end walkthrough of the migration scenario.

SQL Server Analysis ServicesSQL Server Analysis Services

從 SQL Server 2012 和更新版本 SQL Server Analysis Services 表格式模型可以遷移至 Azure Analysis Services,這是在 Azure 中 Analysis Services 表格式模型的 PaaS 部署模型。SQL Server Analysis Services Tabular models from SQL Server 2012 and above can be migrated to Azure Analysis Services, which is a PaaS deployment model for Analysis Services Tabular model in Azure. 您可以在這段 影片教學課程中深入瞭解如何將內部內部部署模型遷移至 Azure Analysis Services。You can learn more about migrating on-prem models to Azure Analysis Services in this video tutorial.

或者,您也可以考慮 使用新的 XMLA 讀取/寫入端點,將內部部署 Analysis Services 表格式模型遷移至 Power BI Premium。Alternatively, you can also consider migrating your on-premises Analysis Services Tabular models to Power BI Premium using the new XMLA read/write endpoints.

注意

Power BI XMLA 讀取/寫入端點功能目前處於公開預覽狀態,在功能正式推出之前,不應考慮生產工作負載。Power BI XMLA read/write endpoints functionality is currently in Public Preview and should not be considered for Production workloads until the functionality becomes Generally Available.

高可用性High availability

SQL Server 高可用性功能 Always On 容錯移轉叢集實例和 Always On 可用性群組會在目標 Azure SQL 受控執行個體上淘汰,因為高可用性架構已內建于 一般用途 (標準可用性模型) 業務關鍵 (premium 可用性模型) SQL 受控執行個體。The SQL Server high availability features Always On failover cluster instances and Always On availability groups become obsolete on the target Azure SQL Managed Instance as high availability architecture is already built into both General Purpose (standard availability model) and Business Critical (premium availability model) SQL Managed Instance. 高階可用性模型也提供讀取向外延展功能,可讓您連接到其中一個次要節點,以進行唯讀用途。The premium availability model also provides read scale-out that allows connecting into one of the secondary nodes for read-only purposes.

除了 SQL 受控執行個體中包含的高可用性架構之外,還有 自動容錯移轉群組 功能,可讓您管理將受控實例中的資料庫複寫和容錯移轉至另一個區域的功能。Beyond the high availability architecture that is included in SQL Managed Instance, there is also the auto-failover groups feature that allows you to manage the replication and failover of databases in a managed instance to another region.

SQL Agent 作業SQL Agent jobs

使用離線 Azure 資料庫移轉服務 (DMS) 選項來遷移 SQL Agent 工作Use the offline Azure Database Migration Service (DMS) option to migrate SQL Agent jobs. 否則,請使用 SQL Server Management Studio 編寫 Transact-sql (T-sql) 中的作業腳本,然後在目標 SQL 受控執行個體上手動重新建立它們。Otherwise, script the jobs in Transact-SQL (T-SQL) using SQL Server Management Studio and then manually recreate them on the target SQL Managed Instance.

重要

Azure DMS 目前僅支援具有 T-sql 子系統步驟的作業。Currently, Azure DMS only supports jobs with T-SQL subsystem steps. 具有 SSIS 封裝步驟的作業必須以手動方式遷移。Jobs with SSIS package steps will have to be manually migrated.

登入和群組Logins and groups

來源 SQL Server 的 SQL 登入可以使用資料庫移轉服務,在離線模式中 (DMS) 移至 Azure SQL 受控執行個體。SQL logins from the source SQL Server can be moved to Azure SQL Managed Instance using Database Migration Service (DMS) in offline mode. 使用 [遷移嚮導] 中的 [選取 登入] 分頁,將登入遷移至目標 SQL 受控執行個體。Use the Select logins blade in the Migration Wizard to migrate logins to your target SQL Managed Instance.

根據預設,Azure 資料庫移轉服務僅支援遷移 SQL 登入。By default, Azure Database Migration Service only supports migrating SQL logins. 不過,您可以藉由下列方式啟用遷移 Windows 登入的功能:However, you can enable the ability to migrate Windows logins by:

確定目標 SQL 受控執行個體具有 Azure AD 讀取存取權,可由具有 全域管理員 角色的使用者透過 Azure 入口網站進行設定。Ensuring that the target SQL Managed Instance has Azure AD read access, which can be configured via the Azure portal by a user with the Global Administrator role. 設定您的 Azure 資料庫移轉服務執行個體,以啟用 Windows 使用者/群組登入移轉,此功能是透過 Azure 入口網站在 [設定] 頁面上設定。Configuring your Azure Database Migration Service instance to enable Windows user/group login migrations, which is set up via the Azure portal, on the Configuration page. 啟用此設定之後,請重新啟動服務,變更才會生效。After enabling this setting, restart the service for the changes to take effect.

重新啟動服務之後,Windows 使用者/群組登入就會出現在可供移轉的登入清單中。After restarting the service, Windows user/group logins appear in the list of logins available for migration. 對於您遷移的任何 Windows 使用者/群組登入,系統會提示您提供相關聯的網域名稱。For any Windows user/group logins you migrate, you are prompted to provide the associated domain name. 不支援服務使用者帳戶 (具有 NT AUTHORITY 網域名稱的帳戶) 和虛擬使用者帳戶 (具有 NT SERVICE 網域名稱的帳戶)。Service user accounts (account with domain name NT AUTHORITY) and virtual user accounts (account name with domain name NT SERVICE) are not supported.

若要深入瞭解,請參閱 如何使用 t-sql 將 SQL Server 實例中的 windows 使用者和群組遷移至 AZURE SQL 受控執行個體To learn more, see how to migrate windows users and groups in a SQL Server instance to Azure SQL Managed Instance using T-SQL.

或者,您可以使用由 Microsoft 資料移轉架構設計人員特別設計的 PowerShell 公用程式工具Alternatively, you can use the PowerShell utility tool specially designed by the Microsoft Data Migration Architects. 此公用程式會使用 PowerShell 來建立 T-sql 腳本,以重新建立登入,並從來源選取資料庫使用者至目標。The utility uses PowerShell to create a T-SQL script to recreate logins and select database users from the source to the target. 此工具會自動將 Windows AD 帳戶對應到 Azure AD 帳戶,並且可以針對來源 Active Directory 的每個登入進行 UPN 查閱。The tool automatically maps Windows AD accounts to Azure AD accounts, and can do a UPN lookup for each login against the source Active Directory. 此工具會編寫自訂伺服器和資料庫角色的腳本,以及角色成員資格、資料庫角色和使用者權限。The tool scripts custom server and database roles, as well as role membership, database role, and user permissions. 目前不支援自主資料庫,而且只會編寫腳本的部分可能 SQL Server 許可權。Contained databases are not currently supported and only a subset of possible SQL Server permissions are scripted.

加密Encryption

使用原生還原選項將受  透明資料加密保護的資料庫移轉   到受控實例時,請先將 對應的憑證從來源 SQL Server 遷移至目標 SQL 受控執行個體, 進行資料庫還原。When migrating databases protected by  Transparent Data Encryption to a managed instance using native restore option, migrate the corresponding certificate from the source SQL Server to the target SQL Managed Instance before database restore.

系統資料庫System databases

不支援系統資料庫還原。Restore of system databases is not supported. 若要遷移 (儲存在 master 或 msdb 資料庫) 的實例層級物件,請使用 Transact-sql (T-sql) 編寫腳本,然後在目標受控實例上重新建立這些物件。To migrate instance-level objects (stored in master or msdb databases), script them using Transact-SQL (T-SQL) and then recreate them on the target managed instance.

利用先進的功能Leverage advanced features

請務必利用 SQL 受控執行個體所提供的先進雲端式功能。Be sure to take advantage of the advanced cloud-based features offered by SQL Managed Instance. 例如,您不再需要擔心管理備份的方式,因為服務會為您執行此工作。For example, you no longer need to worry about managing backups as the service does it for you. 您可以還原至 保留期限內的任何時間點You can restore to any point in time within the retention period. 此外,您不需要擔心如何設定高可用性,因為 內建高可用性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.

除了先進的管理和安全性功能之外,SQL 受控執行個體還提供一組可協助您 監視和微調工作負載的 advanced tools。In addition to advanced management and security features, SQL Managed Instance provides a set of advanced tools that can help you monitor and tune your workload. Azure SQL 分析可讓您以集中方式監視大型的受控實例集。 自動調整  在受控實例中,會持續監視 SQL 計畫執行統計資料的效能,並自動修正已識別的效能問題。Azure SQL Analytics allows you to monitor a large set of managed instances in a centralized manner. Automatic tuning in managed instances continuously monitors performance of your SQL plan execution statistics and automatically fixes the identified performance issues.

只有當 資料庫相容性層級 變更為最新相容性層級 (150) 時,才可使用某些功能。Some features are only available once the database compatibility level is changed to the latest compatibility level (150).

移轉資產Migration assets

如需其他協助,請參閱下列針對真實世界遷移專案所開發的資源。For additional assistance, see the following resources that were developed for real world migration projects.

資產Asset 說明Description
資料工作負載評定模型及工具Data workload assessment model and tool 此工具會針對指定的工作負載,提供建議的「最適合」目標平台、雲端整備,以及應用程式/資料庫補救等級。This tool provides suggested "best fit" target platforms, cloud readiness, and application/database remediation level for a given workload. 此工具提供簡單的按一下即計算與報告產生功能,其可透過提供和自動化的統一目標平台決策程序來協助加速大型資產評估。It offers simple, one-click calculation and report generation that helps to accelerate large estate assessments by providing and automated and uniform target platform decision process.
DBLoader 公用程式DBLoader Utility DBLoader 可以用來將資料從分隔的文字檔載入 SQL Server 中。The DBLoader can be used to load data from delimited text files into SQL Server. 此 Windows 主控台公用程式會使用 SQL Server native client 大量載入介面,此介面適用于所有 SQL Server 版本,包括 Azure SQL MI。This Windows console utility uses the SQL Server native client bulkload interface, which works on all versions of SQL Server, including Azure SQL MI.
將內部部署 SQL Server 登入移至 Azure SQL 受控執行個體的公用程式Utility to move On-Premises SQL Server Logins to Azure SQL Managed Instance 此 PowerShell 腳本會建立 T-sql 命令腳本來重新建立登入,並從內部部署 SQL Server 選取 Azure SQL 受控執行個體的資料庫使用者。A PowerShell script that creates a T-SQL command script to re-create logins and select database users from on-premises SQL Server to Azure SQL Managed Instance. 此工具可讓您將 Windows AD 帳戶自動對應到 Azure AD 帳戶,以及選擇性地遷移 SQL Server 的原生登入。The tool allows automatic mapping of Windows AD accounts to Azure AD accounts as well as optionally migrating SQL Server native logins.
使用 Logman 進行 Perfmon 資料收集自動化Perfmon data collection automation using Logman 此工具會收集執行資料以瞭解有助於遷移目標建議的基準效能。A tool that collects Perform data to understand baseline performance that assists in the migration target recommendation. 此工具會使用 logman.exe 建立命令,該命令會建立、啟動、停止及刪除遠端 SQL Server 上設定的效能計數器。This tool that uses logman.exe to create the command that will create, start, stop, and delete performance counters set on a remote SQL Server.
白皮書-還原完整和差異備份來將資料庫移轉至 Azure SQL 受控執行個體Whitepaper - Database migration to Azure SQL Managed Instance by restoring full and differential backups 本白皮書提供的指引和步驟可協助您加快從 SQL Server 到 Azure SQL 的遷移受控執行個體如果您只 (完整備份和差異備份,且沒有) 的記錄備份功能。This whitepaper provides guidance and steps to help accelerate migrations from SQL Server to Azure SQL Managed Instance if you only have full and differential backups (and no log backup capability).

這些資源是在資料 SQL Ninja 計畫中開發,由 Azure 資料群組工程小組贊助。These resources were developed as part of the Data SQL Ninja Program, which is sponsored by the Azure Data Group engineering team. 資料 SQL Ninja 計畫其核心宗旨是要為複雜的現代化步驟除去障礙並加速其過程,並將資料平台移轉機會與 Microsoft 的 Azure 資料平台相比較。The core charter of the Data SQL Ninja program is to unblock and accelerate complex modernization and compete data platform migration opportunities to Microsoft's Azure Data platform. 如果您認為組織想參與資料 SQL Ninja 計畫,請連絡帳戶小組並要求其提交提名。If you think your organization would be interested in participating in the Data SQL Ninja program, please contact your account team and ask them to submit a nomination.

下一步Next steps

若要開始將您的 SQL Server 遷移至 Azure SQL 受控執行個體,請參閱 SQL Server 至 SQL 受控執行個體遷移指南To start migrating your SQL Server to Azure SQL Managed Instance, see the SQL Server to SQL Managed Instance migration guide.