教學課程:使用 DMS 從 SQL Server 離線移轉至 Azure SQL Database 受控執行個體Tutorial: Migrate SQL Server to an Azure SQL Database managed instance offline using DMS

您可以使用 Azure 資料庫移轉服務,將內部部署 SQL Server 執行個體的資料庫遷移至 Azure SQL Database 受控執行個體You can use the Azure Database Migration Service to migrate the databases from an on-premises SQL Server instance to an Azure SQL Database managed instance. 如需其他可能需要手動操作的方法,請參閱將 SQL Server 執行個體遷移至 Azure SQL Database 受控執行個體一文。For additional methods that may require some manual effort, see the article SQL Server instance migration to Azure SQL Database managed instance.

在本教學課程中,您要使用 Azure 資料庫移轉服務,將 Adventureworks2012 資料庫從內部部署 SQL Server 執行個體遷移至 Azure SQL Database 受控執行個體。In this tutorial, you migrate the Adventureworks2012 database from an on-premises instance of SQL Server to an Azure SQL Database managed instance by using the Azure Database Migration Service.

在本教學課程中,您了解如何:In this tutorial, you learn how to:

  • 建立 Azure 資料庫移轉服務的執行個體。Create an instance of the Azure Database Migration Service.
  • 使用 Azure 資料庫移轉服務來建立移轉專案。Create a migration project by using the Azure Database Migration Service.
  • 執行移轉。Run the migration.
  • 監視移轉。Monitor the migration.
  • 下載移轉報告。Download a migration report.

提示

當您使用 Azure 資料庫移轉服務將資料庫遷移至 Azure 時,您可以進行「離線」 或「線上」 移轉。When you migrate databases to Azure by using Azure Database Migration Service, you can do an offline or an online migration. 若使用離線移轉,當移轉開始時,應用程式也會開始停機。With an offline migration, application downtime starts when the migration starts. 若使用線上移轉,則只會在移轉結束時於完全移轉期間停機。With an online migration, downtime is limited to the time to cut over at the end of migration. 建議您先測試離線移轉來決定停機時間是否在容忍範圍內;如果無法容忍,則請進行線上移轉。We suggest that you test an offline migration to determine whether the downtime is acceptable; if not, do an online migration.

本文說明如何從 SQL Server 離線移轉至 Azure SQL Database 受控執行個體。This article describes an offline migration from SQL Server to an Azure SQL Database managed instance. 若要進行線上移轉,請參閱使用 DMS 從 SQL Server 線上移轉至 Azure SQL Database 受控執行個體For an online migration, see Migrate SQL Server to an Azure SQL Database managed instance online using DMS.

必要條件Prerequisites

若要完成本教學課程,您需要:To complete this tutorial, you need to:

  • 使用 Azure Resource Manager 部署模型建立 Azure 資料庫移轉服務的 Azure 虛擬網路 (VNet),以使用 ExpressRouteVPN 為您的內部部署來源伺服器提供站對站連線能力。Create an Azure Virtual Network (VNet) for the Azure Database Migration Service by using the Azure Resource Manager deployment model, which provides site-to-site connectivity to your on-premises source servers by using either ExpressRoute or VPN. 了解使用 Azure 資料庫移轉服務進行 Azure SQL Database 受控執行個體移轉的網路拓樸Learn network topologies for Azure SQL Database managed instance migrations using the Azure Database Migration Service. 如需建立 VNet 的詳細資訊,請參閱虛擬網路文件,特別是快速入門文章,裡面會提供逐步操作詳細資料。For more information about creating a VNet, see the Virtual Network Documentation, and especially the quickstart articles with step-by-step details.

    注意

    在 VNet 設定期間,如果您搭配與 Microsoft 對等互連的網路使用 ExpressRoute,請將下列服務端點新增至將佈建服務的子網路:During VNet setup, if you use ExpressRoute with network peering to Microsoft, add the following service endpoints to the subnet in which the service will be provisioned:

    • 目標資料庫端點 (例如,SQL 端點、Cosmos DB 端點等)Target database endpoint (for example, SQL endpoint, Cosmos DB endpoint, and so on)
    • 儲存體端點Storage endpoint
    • 服務匯流排端點Service bus endpoint

    此為必要設定,因為 Azure 資料庫移轉服務沒有網際網路連線。This configuration is necessary because the Azure Database Migration Service lacks internet connectivity.

  • 確定您的 VNet 網路安全性群組規則不會對 Azure 資料庫移轉服務封鎖下列輸入通訊埠:443、53、9354、445、12000。Ensure that your VNet Network Security Group rules don't block the following inbound communication ports to Azure Database Migration Service: 443, 53, 9354, 445, 12000. 如需 Azure VNet NSG 流量篩選的詳細資訊,請參閱使用網路安全性群組來篩選網路流量For more detail on Azure VNet NSG traffic filtering, see the article Filter network traffic with network security groups.

  • 設定用於來源資料庫引擎存取的 Windows 防火牆Configure your Windows Firewall for source database engine access.

  • 開啟您的 Windows 防火牆以允許 Azure 資料庫移轉服務存取來源 SQL Server,其預設會通過 TCP 連接埠 1433。Open your Windows Firewall to allow the Azure Database Migration Service to access the source SQL Server, which by default is TCP port 1433.

  • 如果您使用動態連接埠執行多個具名 SQL Server 執行個體,您可以啟用 SQL Browser 服務並允許通過防火牆存取 UDP 連接埠 1434,讓 Azure 資料庫移轉服務連線來源伺服器上的具名執行個體。If you're running multiple named SQL Server instances using dynamic ports, you may wish to enable the SQL Browser Service and allow access to UDP port 1434 through your firewalls so that the Azure Database Migration Service can connect to a named instance on your source server.

  • 如果您是在來源資料庫前面使用防火牆設備,您可能必須新增防火牆規則,才能讓 Azure 資料庫移轉服務存取用於移轉的來源資料庫,以及透過 SMB 連接埠 445 存取檔案。If you're using a firewall appliance in front of your source databases, you may need to add firewall rules to allow the Azure Database Migration Service to access the source database(s) for migration, as well as files via SMB port 445.

  • 依照在 Azure 入口網站中建立 Azure SQL Database 受控執行個體一文中的詳細資料,建立 Azure SQL Database 受控執行個體。Create an Azure SQL Database managed instance by following the detail in the article Create an Azure SQL Database managed instance in the Azure portal.

  • 確定用來連線來源 SQL Server 和目標受控執行個體的登入是 sysadmin 伺服器角色的成員。Ensure that the logins used to connect the source SQL Server and target managed instance are members of the sysadmin server role.

  • 建立 Azure 資料庫移轉服務可用來備份來源資料庫的網路共用。Create a network share that the Azure Database Migration Service can use to back up the source database.

  • 確認執行來源 SQL Server 執行個體的服務帳戶在您所建立的網路共用上具有寫入權限,而且來源伺服器的電腦帳戶對相同的共用具備讀取/寫入存取權。Ensure that the service account running the source SQL Server instance has write privileges on the network share that you created and that the computer account for the source server has read/write access to the same share.

  • 記下在您先前建立的網路共用上具有完整控制權限的 Windows 使用者 (和密碼)。Make a note of a Windows user (and password) that has full control privilege on the network share that you previously created. Azure 資料庫移轉服務會模擬該使用者認證,以便將備份檔案上傳至 Azure 儲存體容器以進行還原作業。The Azure Database Migration Service impersonates the user credential to upload the backup files to Azure storage container for restore operation.

  • 使用使用儲存體總管來管理 Azure Blob 儲存體資源 一文中的步驟來建立 blob 容器及取出其 SAS URI;在建立 SAS URI 時,請務必選取原則視窗上的所有權限 (讀取、寫入、刪除、列出)。Create a blob container and retrieve its SAS URI by using the steps in the article Manage Azure Blob Storage resources with Storage Explorer, be sure to select all permissions (Read, Write, Delete, List) on the policy window while creating the SAS URI. 此詳細資料會向 Azure 資料庫移轉服務提供您儲存體帳戶容器的存取權,以供上傳用於將資料庫遷移至 Azure SQL Database 受控執行個體的備份檔案。This detail provides the Azure Database Migration Service with access to your storage account container for uploading the backup files used for migrating databases to Azure SQL Database managed instance.

註冊 Microsoft.DataMigration 資源提供者Register the Microsoft.DataMigration resource provider

  1. 登入 Azure 入口網站,選取 [所有服務],然後選取 [訂用帳戶]。Sign in to the Azure portal, select All services, and then select Subscriptions.

    顯示入口網站訂用帳戶

  2. 選取您要在其中建立 Azure 資料庫移轉服務執行個體的訂用帳戶,然後選取 [資源提供者]。Select the subscription in which you want to create the instance of the Azure Database Migration Service, and then select Resource providers.

    顯示資源提供者

  3. 搜尋移轉,然後在 [Microsoft.DataMigration] 的右邊,選取 [註冊]。Search for migration, and then to the right of Microsoft.DataMigration, select Register.

    註冊資源提供者

建立 Azure 資料庫移轉服務執行個體Create an Azure Database Migration Service instance

  1. 在 Azure 入口網站中,選取 [+ 建立資源],搜尋 [Azure 資料庫移轉服務],然後從下拉式清單選取 [Azure 資料庫移轉服務]。In the Azure portal, select + Create a resource, search for Azure Database Migration Service, and then select Azure Database Migration Service from the drop-down list.

    Azure Marketplace

  2. 在 [Azure 資料庫移轉服務] 畫面上,選取 [建立]。On the Azure Database Migration Service screen, select Create.

    建立 Azure 資料庫移轉服務執行個體

  3. 在 [建立移轉服務] 畫面上,指定服務的名稱、訂用帳戶,以及新的或現有的資源群組。On the Create Migration Service screen, specify a name for the service, the subscription, and a new or existing resource group.

  4. 選取您要建立 DMS 執行個體的位置。Select the location in which you want to create the instance of DMS.

  5. 選取現有 VNet 或建立一個。Select an existing VNet or create one.

    VNet 會為 Azure 資料庫移轉服務提供來源 SQL Server 和目標 Azure SQL Database 受控執行個體的存取權。The VNet provides the Azure Database Migration Service with access to the source SQL Server and target Azure SQL Database managed instance.

    如需有關如何在 Azure 入口網站中建立 VNet 的詳細資訊,請參閱使用 Azure 入口網站建立虛擬網路一文。For more information on how to create a VNet in Azure portal, see the article Create a virtual network using the Azure portal.

    如需其他詳細資訊,請參閱了解使用 Azure 資料庫移轉服務進行 Azure SQL DB 受控執行個體移轉的網路拓樸一文。For additional detail, see the article Network topologies for Azure SQL DB managed instance migrations using the Azure Database Migration Service.

  6. 選取定價層。Select a pricing tier.

    如需成本和定價層的詳細資訊,請參閱定價分頁For more information on costs and pricing tiers, see the pricing page.

    建立 DMS 服務

  7. 選取 [建立] 以建立服務。Select Create to create the service.

建立移轉專案Create a migration project

建立服務執行個體之後,請在 Azure 入口網站中找出該服務,然後建立新的移轉專案。After an instance of the service is created, locate it within the Azure portal, open it, and then create a new migration project.

  1. 在 Azure 入口網站中,選取 [所有服務],搜尋 Azure 資料庫移轉服務,然後選取 [Azure 資料庫移轉服務]。In the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.

    找出 Azure 資料庫移轉服務的所有執行個體

  2. 在 [Azure 資料庫移轉服務] 畫面上,搜尋您建立的執行個體名稱,然後選取該執行個體。On the Azure Database Migration Service screen, search for the name of the instance that you created, and then select the instance.

  3. 選取 [+ 新增移轉專案]。Select + New Migration Project.

  4. 在 [新增移轉專案] 畫面上指定專案名稱、在 [來源伺服器類型] 文字方塊中選取 [SQL Server]、在 [目標伺服器類型] 文字方塊中選取 [Azure SQL Database 受控執行個體],然後針對 [選擇活動類型],選取 [離線資料移轉]。On the New migration project screen, specify a name for the project, in the Source server type text box, select SQL Server, in the Target server type text box, select Azure SQL Database Managed Instance, and then for Choose type of activity, select Offline data migration.

    建立 DMS 專案

  5. 選取 [Create] (建立) 以建立專案。Select Create to create the project.

指定來源詳細資料Specify source details

  1. 在 [移轉來源詳細資料] 畫面上,指定來源 SQL Server 的連線詳細資料。On the Migration source detail screen, specify the connection details for the source SQL Server.

  2. 如果您尚未在伺服器上安裝信任的憑證,請選取 [信任伺服器憑證] 核取方塊。If you haven't installed a trusted certificate on your server, select the Trust server certificate check box.

    未安裝信任的憑證時,SQL Server 會在執行個體啟動時產生自我簽署憑證。When a trusted certificate isn't installed, SQL Server generates a self-signed certificate when the instance is started. 此憑證用來加密用戶端連線的認證。This certificate is used to encrypt the credentials for client connections.

    警告

    使用自我簽署憑證加密的 SSL 連線不會提供增強式安全性。SSL connections that are encrypted using a self-signed certificate does not provide strong security. 這種連線容易受到攔截式攻擊。They are susceptible to man-in-the-middle attacks. 在生產環境中,或在連線到網際網路的伺服器上,您不應該仰賴使用自我簽署憑證的 SSL。You should not rely on SSL using self-signed certificates in a production environment or on servers that are connected to the internet.

    來源詳細資料

  3. 選取 [ 儲存]。Select Save.

  4. 在 [選取來源資料庫] 畫面上,選取 [Adventureworks2012] 資料庫進行移轉。On the Select source databases screen, select the Adventureworks2012 database for migration.

    選取來源資料庫

    重要

    如果您使用 SQL Server Integration Services (SSIS),DMS 目前不支援將 SSIS 專案/套件 (SSISDB) 的目錄資料庫從 SQL Server 遷移至 Azure SQL Database 受控執行個體。If you use SQL Server Integration Services (SSIS), DMS does not currently support migrating the catalog database for your SSIS projects/packages (SSISDB) from SQL Server to Azure SQL Database managed instance. 不過,您可以在 Azure Data Factory (ADF) 中佈建 SSIS,並將 SSIS 專案/套件重新部署到 Azure SQL Database 受控執行個體所裝載的目的地 SSISDB。However, you can provision SSIS in Azure Data Factory (ADF) and redeploy your SSIS projects/packages to the destination SSISDB hosted by Azure SQL Database managed instance. 如需有關遷移 SSIS 套件的詳細資訊,請參閱將 SQL Server Integration Services 套件遷移到 Azure 一文。For more information about migrating SSIS packages, see the article Migrate SQL Server Integration Services packages to Azure.

  5. 選取 [ 儲存]。Select Save.

指定目標詳細資料Specify target details

  1. 在 [移轉目標詳細資料] 畫面上指定目標的連線詳細資料;此目標是 AdventureWorks2012 資料庫所要移轉到的預先佈建 Azure SQL Database 受控執行個體。On the Migration target details screen, specify the connection details for the target, which is the pre-provisioned Azure SQL Database managed instance to which you're migrating the AdventureWorks2012 database.

    如果您尚未佈建 Azure SQL Database 受控執行個體,請選取連結來協助您佈建執行個體。If you haven't already provisioned the Azure SQL Database managed instance, select the link to help you provision the instance. 您仍然可以繼續建立專案,然後在 Azure SQL Database 受控執行個體準備就緒時,返回此特定專案來執行移轉。You can still continue with project creation and then, when the Azure SQL Database managed instance is ready, return to this specific project to execute the migration.

    選取目標

  2. 選取 [ 儲存]。Select Save.

選取來源資料庫Select source databases

  1. 在 [選取來源資料庫] 畫面上,選取您要遷移的來源資料庫。On the Select source databases screen, select the source database that you want to migrate.

    選取來源資料庫

  2. 選取 [ 儲存]。Select Save.

選取登入Select logins

  1. 在 [選取登入] 畫面上,選取您要移轉的登入。On the Select logins screen, select the logins that you want to migrate.

    注意

    此版本僅支援移轉 SQL 登入。This release only supports migrating the SQL logins.

    選取登入

  2. 選取 [ 儲存]。Select Save.

設定移轉設定Configure migration settings

  1. 在 [設定移轉設定] 畫面上,提供下列詳細資料:On the Configure migration settings screen, provide the following detail:

    選擇來源備份選項Choose source backup option 當您已有可供 DMS 用於資料庫移轉的完整備份檔案時,請選擇 [我會提供最新的備份檔案] 選項。Choose the option I will provide latest backup files when you already have full backup files available for DMS to use for database migration. 如果您想要讓 DMS 先建立來源資料庫完整備份,並將其用於移轉,請選擇 [我允許 Azure 資料庫移轉服務建立備份檔案] 選項。Choose the option I will let Azure Database Migration Service create backup files when you want DMS to take the source database full backup at first and use it for migration.
    網路位置共用Network location share Azure 資料庫移轉服務可將來源資料庫備份移入的本機 SMB 網路共用。The local SMB network share that the Azure Database Migration Service can take the source database backups to. 執行來源 SQL Server 執行個體的服務帳戶在此網路共用上必須具有寫入權限。The service account running source SQL Server instance must have write privileges on this network share. 在網路共用中提供伺服器的 FQDN 或 IP 位址,例如,'\\servername.domainname.com\backupfolder' 或 '\\IP address\backupfolder'。Provide an FQDN or IP addresses of the server in the network share, for example, '\\servername.domainname.com\backupfolder' or '\\IP address\backupfolder'.
    使用者名稱User name 請確定 Windows 使用者對於您先前提供的網路共用具有完整控制權限。Make sure that the Windows user has full control privilege on the network share that you provided above. Azure 資料庫移轉服務將會模擬該使用者認證,以便將備份檔案上傳至 Azure 儲存體容器以進行還原作業。The Azure Database Migration Service will impersonate the user credential to upload the backup files to Azure storage container for restore operation. 如果選取已啟用 TDE 的資料庫進行移轉,則上述的 Windows 使用者必須是內建的系統管理員帳戶,且必須停用 Azure 資料庫移轉服務的使用者帳戶控制,才能上傳及刪除憑證檔案。If TDE-enabled databases are selected for migration, the above windows user must be the built-in administrator account and User Account Control must be disabled for Azure Database Migration Service to upload and delete the certificates files.)
    密碼Password 使用者的密碼。Password for the user.
    儲存體帳戶設定Storage account settings 此 SAS URI 會向 Azure 資料庫移轉服務提供您儲存體帳戶容器的存取權,此容器會作為服務上傳備份檔案時的目的地,且會用於將資料庫遷移至 Azure SQL Database 受控執行個體。The SAS URI that provides the Azure Database Migration Service with access to your storage account container to which the service uploads the back-up files and that is used for migrating databases to Azure SQL Database managed instance. 了解如何取得 Blob 容器的 SAS URILearn how to get the SAS URI for blob container.
    TDE 設定TDE Settings 如果您要移轉已啟用透明資料加密 (TDE) 的來源資料庫,您必須具備目標 Azure SQL Database 受控執行個體的寫入權限。If you're migrating the source databases with Transparent Data Encryption (TDE) enabled, you need to have write privileges on the target Azure SQL Database managed instance. 請從下拉式功能表中選取用來佈建 Azure SQL DB 受控執行個體的訂用帳戶。Select the subscription in which the Azure SQL Database managed instance provisioned from the drop-down menu. 在下拉式功能表中選取目標 Azure SQL 資料庫受控執行個體Select the target Azure SQL Database Managed Instance in the drop-down menu.

    設定移轉設定

  2. 選取 [ 儲存]。Select Save.

檢閱移轉摘要Review the migration summary

  1. 在 [移轉摘要] 畫面上的 [活動名稱] 文字方塊中,指定移轉活動的名稱。On the Migration summary screen, in the Activity name text box, specify a name for the migration activity.

  2. 展開 [驗證選項] 區段以顯示 [選擇驗證選項] 畫面,指定是否驗證已遷移資料庫的查詢正確性,然後選取 [儲存]。Expand the Validation option section to display the Choose validation option screen, specify whether to validate the migrated database for query correctness, and then select Save.

  3. 檢閱並確認與移轉專案相關聯的詳細資料。Review and verify the details associated with the migration project.

    移轉專案摘要

  4. 選取 [ 儲存]。Select Save.

執行移轉Run the migration

  • 選取 [執行移轉]。Select Run migration.

    [移轉活動] 視窗隨即出現,而且活動的狀態為 [擱置]。The migration activity window appears, and the status of the activity is Pending.

監視移轉Monitor the migration

  1. 在移轉活動畫面中,選取 [重新整理] 以更新顯示。In the migration activity screen, select Refresh to update the display.

    移轉活動進行中

    您可以進一步展開資料庫和登入類別,以監視個別伺服器物件的移轉狀態。You can further expand the databases and logins categories to monitor the migration status of the respective server objects.

    移轉活動進行中

  2. 移轉完成之後,請選取 [下載報告] 以取得報告,其中會列出與移轉程序相關聯的詳細資料。After the migration completes, select Download report to get a report listing the details associated with the migration process.

  3. 確認目標 Azure SQL Database 受控執行個體環境上的目標資料庫。Verify that the target database on the target Azure SQL Database managed instance environment.

後續步驟Next steps