在 Compute Engine 上複製 Microsoft SQL Server 資料庫

透過集合功能整理內容 你可以依據偏好儲存及分類內容。

設定環境

如要完成本教學課程，您需要設定運算環境，並具備下列項目：

• Compute Engine 上的 SQL Server 執行個體 (名為 sql-server-prod)，用於代表您的正式資料庫伺服器。
• 附加至實際工作環境伺服器的其他磁碟 (名稱為 sql-server-prod-data)，用於儲存實際工作環境資料庫。
• Wide World Importers SQL Server 範例資料庫的副本，用於模擬要複製的正式版資料庫。
• 在 Compute Engine 上建立名為 sql-server-test 的 SQL Server 執行個體，用來代表測試資料庫伺服器。將資料庫複製到這個伺服器。

下圖說明這個架構。

建立實際工作環境 VM 執行個體

如要模擬實際工作環境，請設定在 Windows Server 上執行 SQL Server 的 Compute Engine VM 執行個體。

本教學課程的 VM 執行個體會使用兩個磁碟：一個 50 GB 磁碟用於作業系統和使用者帳戶，另一個 100 GB 磁碟用於資料庫儲存空間。

在 Compute Engine 中，使用不同的磁碟不會帶來任何效能優勢。磁碟效能取決於連接至執行個體的所有磁碟總儲存空間容量，以及 VM 執行個體的 vCPU 總數。因此，資料庫和記錄檔案可以位於相同的磁碟。

連線至 VM 執行個體

1. 前往 Google Cloud 控制台的「VM instances」(VM 執行個體) 頁面。

   前往 VM 執行個體頁面

2. 等待約 5 分鐘，讓 VM 執行個體準備就緒。

   如要監控 VM 的初始化程序，請在 Cloud Shell 中查看其序列埠輸出內容：

   gcloud compute instances tail-serial-port-output sql-server-prod
   

   當您看到下列訊息時，表示初始化已完成。

   Instance setup finished. sql-server-prod is ready to use.
   

   按下 Control+C 即可停止監控序列埠。

3. 按一下執行個體名稱 sql-server-prod，開啟「VM 執行個體詳細資料」頁面。

4. 在「遠端存取」下方，按一下「設定 Windows 密碼」，然後按一下「設定」，在遠端機器上建立帳戶。

   系統會在這個步驟產生密碼，記下密碼或將密碼複製到安全的暫時性檔案中。

5. 在 Google Cloud 控制台的「Compute Engine」部分，按一下「RDP」下拉式選單，然後選取「Download the RDP file」選項，即可下載執行個體的 RDP 檔案。

   使用此檔案即可透過遠端桌面協定用戶端連線至執行個體。詳情請參閱 Microsoft 遠端桌面用戶端。

6. 出現提示時，請輸入剛才產生的密碼，然後按一下「OK」。

7. 如要接受伺服器憑證並登入遠端 Windows 執行個體，請按一下「Continue」。

8. 當系統詢問您是否要讓網路上的其他電腦和裝置偵測到您的電腦時，請按一下「No」。

設定其他磁碟

連接至實際工作環境執行個體的第二個磁碟則用於儲存實際工作環境資料庫。這個磁碟是空白的，因此您需要分割、格式化及掛接磁碟。

1. 在已連線至 sql-server-prod 執行個體的 RDP 工作階段中，按一下 Windows 工作列上的「Start」按鈕，輸入 diskpart，然後按一下「diskpart」開啟 DiskPart。
2. 當系統提示您允許應用程式進行變更時，請按一下「是」。

3. 顯示已連接至執行個體的磁碟清單：

   list disk
   

   輸出內容如下：

   Disk ###  Status         Size     Free     Dyn  Gpt
   --------  -------------  -------  -------  ---  ---
   Disk 0    Online           50 GB      0 B
   Disk 1    Online          100 GB   100 GB
   

   磁碟 1 (100 GB) 是資料磁碟。

4. 選取資料磁碟：

   select disk 1
   

5. 初始化磁碟：

   clean
   

6. 建立 GUID 分區表：

   convert gpt
   

7. 使用整個磁碟建立資料分割區：

   create partition primary
   

8. 列出可用的磁碟區：

   list volume
   

   輸出內容如下：

   Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
   ----------  ---  -----------  -----  ----------  -------  ---------  --------
   Volume 0     C                NTFS   Partition     49 GB  Healthy    Boot
   Volume 1                      FAT32  Partition    100 MB  Healthy    System
   Volume 2                      RAW    Partition     99 GB  Healthy
   

   第 2 個磁碟區 (99 GB) 是資料磁碟。

9. 選取磁碟區：

   select volume 2
   

10. 請使用 NTFS 檔案系統格式化分區，並將其標示為 data：

    format quick fs=ntfs label=data
    

11. 將磁碟掛接為磁碟機 D：

    assign letter=d
    

12. 退出 DiskPart：

    exit
    

下載範例資料庫

如要為本練習設定環境，您需要執行下列操作：

• 在磁碟 D (data) 上建立目錄結構，用於儲存資料庫。
• 下載 Wide World Importers SQL Server 範例資料庫 的完整備份檔案。這個資料庫會模擬您要複製的實際工作環境資料庫。

如要建立目錄並下載備份檔案，請按照下列步驟操作：

1. 在 RDP 工作階段中，按一下 Windows 工作列的「Start」按鈕，輸入 PowerShell，然後選取 Windows PowerShell 應用程式。

2. 在 PowerShell 提示中，建立資料庫儲存空間的目錄結構：

   mkdir D:\sql-server-data\wideworldimporters
   

3. 將備份檔案下載至 D 碟：

   bitsadmin /transfer sampledb /dynamic /download /priority FOREGROUND `
     https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak `
     D:\sql-server-data\WideWorldImporters-Full.bak
   

還原範例資料庫

您需要使用 Microsoft SQL Server Management Studio (SSMS) 互動式精靈，或直接執行 Transact-SQL 指令，將範例資料庫還原至磁碟 D (data)。

如要驗證範例資料庫是否正常運作，您可以執行查詢。

1. 在 Microsoft SQL Server Management Studio 中，依序選取「File」 >「New」 >「Query with Current Connection」，開啟新的查詢視窗，然後複製下列程式碼：

   SELECT top(100)
     i.InvoiceDate, i.InvoiceID, i.CustomerID, c.CustomerName,
     i.ConfirmedDeliveryTime, i.ConfirmedReceivedBy
   FROM
     WideWorldImporters.Sales.Invoices i
     JOIN WideWorldImporters.Sales.Customers c
     ON i.CustomerID=c.CustomerID
   WHERE i.ConfirmedDeliveryTime IS NOT NULL
   ORDER BY i.InvoiceDate desc;
   

   這項查詢會從最近 100 筆已提交的月結單中擷取摘要資訊。

2. 在查詢視窗中按一下滑鼠右鍵，然後點選「執行」。

   「Results」窗格會顯示摘要資訊。

   

建立測試 VM 執行個體

在本節中，您將建立名為 sql-server-test 的 SQL Server 執行個體，做為複製資料庫的目的地。這個執行個體的設定與實際工作環境執行個體相同。不過，您不需要建立第二個資料磁碟，而是在本教學課程稍後的部分連接資料磁碟。

連線至 VM 執行個體

1. 前往 Google Cloud 控制台的「VM instances」(VM 執行個體) 頁面。

   前往 VM 執行個體頁面

2. 等待約 5 分鐘，讓 VM 執行個體準備就緒。

   如要監控 VM 的初始化程序，請在 Cloud Shell 中查看其序列埠輸出內容：

   gcloud compute instances tail-serial-port-output sql-server-prod
   

   當您看到下列訊息時，表示初始化已完成。

   Instance setup finished. sql-server-test is ready to use.
   

   按下 Control+C 即可停止監控序列埠。

3. 按一下執行個體名稱 sql-server-test，即可顯示「VM 執行個體詳細資料」頁面。

4. 在「遠端存取」下方，按一下「設定 Windows 密碼」，然後按一下「設定」，在遠端機器上建立帳戶。

   系統會在這個步驟產生密碼，記下密碼或將密碼複製到安全的暫存檔案中。

5. 在 Google Cloud 控制台的「Compute Engine」部分，按一下「RDP」下拉式選單，然後選取「Download the RDP file」選項，即可下載執行個體的 RDP 檔案。

   使用此檔案即可透過遠端桌面協定用戶端連線至執行個體。詳情請參閱 Microsoft 遠端桌面用戶端。

6. 出現提示時，請輸入剛才產生的密碼，然後按一下「確定」。

7. 如要接受伺服器憑證並登入遠端 Windows 執行個體，請按一下「Continue」。

8. 當系統詢問您是否要讓電腦可供偵測時，請按一下「否」。

使用 Compute Engine 磁碟快照複製資料庫

在 Compute Engine 上執行的 SQL Server 資料庫，可透過以下方式複製：將資料庫儲存在其他資料磁碟上，然後使用永久磁碟快照建立該磁碟的複本。

永久磁碟快照可讓您取得磁碟上資料的時間點副本。排定磁碟快照作業時間，是自動備份資料的一種方式。

在本教學課程的這個部分，您將執行以下操作：

• 為正式版伺服器的資料磁碟建立快照。
• 使用快照建立新磁碟。
• 將新磁碟掛接至測試伺服器。
• 將此磁碟上的資料庫連結至測試執行個體上的 SQL Server。

下圖說明如何使用磁碟快照複製資料庫。

建立磁碟快照

將磁碟快照附加至測試執行個體

您必須從建立的快照建立新的資料磁碟，然後將其附加至 sql-server-test 執行個體。

在 Windows 中掛接新的資料磁碟

您建立的磁碟已連接至 VM 執行個體，但處於離線狀態，而且已將磁區設為唯讀。如要將磁碟區設為可讀寫及可掛載，請執行下列步驟：

1. 在與 sql-server-test 執行個體連線的 RDP 用戶端視窗中，按一下 Windows 工作列上的「開始」按鈕，輸入 diskpart，然後按一下「diskpart」開啟 DiskPart。
2. 當系統提示您允許應用程式進行變更時，請按一下「是」。

3. 顯示已連接至執行個體的磁碟清單：

   list disk
   

   輸出內容如下：

   
   Disk ###  Status         Size     Free     Dyn  Gpt
   --------  -------------  -------  -------  ---  ---
   Disk 0    Online           50 GB      0 B
   Disk 1    Offline         100 GB      0 B       *
    

   資料磁碟 (磁碟 1，100 GB) 處於離線狀態。

4. 選取資料磁碟：

   select disk 1
   

5. 將磁碟重新連線：

   online disk
   

6. 列出可用的磁碟區：

   list volume
   

   輸出內容如下：

   Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
   ----------  ---  -----------  -----  ----------  -------  ---------  --------
   Volume 0     C                NTFS   Partition     49 GB  Healthy    Boot
   Volume 1                      FAT32  Partition    100 MB  Healthy    System
   Volume 2                      RAW    Partition     99 GB  Healthy
   

   資料量 2 (99 GB) 是您的資料量。系統會將其列為「隱藏」，且未指派磁碟機代號。

7. 選取磁碟區：

   select volume 2
   

8. 清除您在依據快照建立磁碟區時設定的屬性：

   attr volume clear readonly hidden nodefaultdriveletter shadowcopy
   

   這個指令會讓磁碟區可供掛接。

9. 將磁碟區掛接為磁碟機 D：

   assign letter=d
   

10. 退出 DiskPart：

    exit
    

重設檔案擁有權和權限

由於您是透過 sql-server-prod 執行個體的快照建立資料磁碟，因此檔案擁有權和權限的使用者 ID 與 sql-server-test 執行個體的不同。您需要將檔案的擁有權變更為 sql-server-test 執行個體上的使用者，並更新權限，讓本機使用者和 MSSQLSERVER 使用者都能讀取檔案。

1. 在連線至 sql-server-test 執行個體的 RDP 用戶端視窗中，按一下 Windows 工作列上的「開始」按鈕，然後輸入 cmd。
2. 以系統管理員身分開啟「命令提示字元」應用程式。

3. 將 sql-server-data 資料夾中的檔案擁有者設為 MSSQLSERVER 服務使用者：

   icacls d:\sql-server-data /setowner "nt service\mssqlserver" /t
   

4. 重設頂層 sql-server-data 資料夾中所有檔案的所有權限：

   icacls d:\sql-server-data /reset /t
   

5. 更新 sql-server-data 資料夾的存取控制清單 (ACL)：

   icacls d:\sql-server-data /grant Administrators:(oi)(ci)f "nt service\mssqlserver":(oi)(ci)f "owner rights":(oi)(ci)f  %USERNAME%:(oi)(ci)f
   

6. 停用雲端硬碟根目錄的繼承功能，這樣系統就只會套用上述權限：

   icacls d:\sql-server-data /inheritancelevel:r
   

7. 退出命令提示字元應用程式：

   exit
   

sql-server-test 執行個體上的 SQL Server 執行個體和本機使用者現在可以存取資料庫檔案。

附加複製的資料庫

您現在可以將 D 磁碟機 (data) 上的複製資料庫附加至測試 SQL Server 例項。您可以使用 Microsoft SQL Server Management Studio 精靈以互動方式附加資料庫，也可以直接執行 Transact-SQL 指令。

如要驗證範例資料庫是否正常運作，您可以執行查詢。

1. 在 SQL Server Management Studio 中，依序選取「File」 >「New」 >「Query with Current Connection」，開啟新的查詢視窗，然後複製下列程式碼：

   SELECT top(100)
     i.InvoiceDate, i.InvoiceID, i.CustomerID, c.CustomerName,
     i.ConfirmedDeliveryTime, i.ConfirmedReceivedBy
   FROM
     WideWorldImporters.Sales.Invoices i
     JOIN WideWorldImporters.Sales.Customers c
     ON i.CustomerID=c.CustomerID
   WHERE i.ConfirmedDeliveryTime IS NOT NULL
   ORDER BY i.InvoiceDate desc;
   

   這項查詢會從最近 100 筆已提交的月結單中擷取摘要資訊。

2. 如要執行查詢，請在查詢視窗上按一下滑鼠右鍵，然後選取「執行」。

   「Results」窗格會顯示摘要資訊。

   

您現在已瞭解如何使用永久磁碟快照複製資料庫，接下來可以嘗試使用備份和還原功能複製資料庫。如要完成這第二種方法的教學課程，您必須刪除從 sql-server-test 執行個體複製的資料庫。

刪除複製的資料庫

如要刪除使用磁碟快照建立的複製資料庫，請執行下列步驟。

使用備份與還原功能進行複製

另一種複製在 Compute Engine 上執行的 SQL Server 資料庫的方法，是使用原生 SQL Server 備份和還原功能。使用這種方法時，您會使用 Cloud Storage 傳輸備份。

本教學課程的這一節會使用您在「使用 Compute Engine 磁碟快照複製資料庫」一節中建立的資源。如果您尚未完成該部分，請先完成再繼續操作。

在本教學課程的這個部分，您將執行以下操作：

• 建立 Cloud Storage bucket。
• 在實際工作環境伺服器上備份資料庫。
• 將備份檔案從實際工作伺服器複製到 Cloud Storage。
• 將備份檔案從 Cloud Storage 複製到測試伺服器。
• 在測試執行個體上還原備份。

下圖顯示如何使用 Cloud Storage 傳輸備份，藉此複製資料庫。

由於 Google Cloud 以外的系統可以存取 Cloud Storage，因此您可以使用這種方法從外部 SQL Server 執行個體複製資料庫。

建立 Cloud Storage 值區

您需要建立 Cloud Storage 值區，用於儲存從 sql-server-prod 執行個體轉移至 sql-server-test 執行個體的備份檔案。

對資料庫進行完整的時間點備份

在實際工作環境中，您可能已建立備份。您可以使用這些備份做為複製資料庫的基礎。在本教學課程中，您會建立僅副本備份，這樣不會影響任何現有的完整或增量備份排程。

將備份檔案複製到 Cloud Storage

1. 在連線至 sql-server-prod 執行個體的 RDP 工作階段中，開啟 Windows PowerShell 視窗。

2. 將備份檔案複製到先前建立的 Cloud Storage 值區：

   gcloud storage cp d:\sql-server-data\WideWorldImporters-copy.bak "gs://$(gcloud config get-value project)-bucket/" --no-clobber
   

3. 退出 PowerShell。

   exit
   

將備份檔案從 Cloud Storage 複製到 sql-server-test

1. 在連線至 sql-server-test 執行個體的 RDP 工作階段中，開啟 Windows PowerShell 視窗。

2. 將備份檔案複製到先前建立的 Cloud Storage 值區：

   gcloud storage cp "gs://$(gcloud config get-value project)-bucket/WideWorldImporters-copy.bak" d:\sql-server-data\
   

3. 退出 PowerShell。

   exit
   

您的測試執行個體現在已在本機磁碟上備份資料庫的完整副本。

還原備份

您現在可以將完整備份還原至 sql-server-test 執行個體的磁碟機 D (data)。

如要證明資料庫可正常運作，您可以執行查詢。

1. 在 Microsoft SQL Management Studio 中，依序選取「File」 >「New」 >「Query with Current Connection」，開啟新的查詢視窗，然後複製下列程式碼：

   SELECT top(100)
     i.InvoiceDate, i.InvoiceID, i.CustomerID, c.CustomerName,
     i.ConfirmedDeliveryTime, i.ConfirmedReceivedBy
   FROM
     WideWorldImporters.Sales.Invoices i
     JOIN WideWorldImporters.Sales.Customers c
     ON i.CustomerID=c.CustomerID
   WHERE i.ConfirmedDeliveryTime IS NOT NULL
   ORDER BY i.InvoiceDate desc;
   

   這項查詢會從最近 100 筆已提交的月結單中擷取摘要資訊。

2. 在查詢視窗上按一下滑鼠右鍵，然後點選「執行」。

   「Results」窗格會顯示摘要資訊。

   

使用 Cloud SQL 做為複製目的地

如果目標資料庫託管在 Cloud SQL，而來源資料庫則位於 Compute Engine，則唯一支援的複製機制是將資料庫備份至 Cloud Storage，然後將資料庫還原至 Cloud SQL。

在本教學課程中，您將重複使用先前建立的備份。

建立 SQL Server 適用的 Cloud SQL 執行個體

1. 開啟 Cloud Shell。

   開啟 Cloud Shell

2. 建立 Cloud SQL for SQL Server 執行個體，並執行與 sql-server-prod 執行個體相同的資料庫版本：

   gcloud sql instances create sqlserver-cloudsql  \
     --database-version=SQLSERVER_2022_STANDARD \
     --cpu=2 \
     --memory=5GB \
     --root-password=sqlserver12@ \
     --region=${REGION}
   

   這會建立一個執行個體，其中的 root 使用者為 sqlserver，密碼為 sqlserver12@。

更新物件權限

您必須在 Cloud Storage 值區和備份物件上設定正確的權限，才能讓 Cloud SQL 服務帳戶讀取這些項目。使用 Google Cloud 主控台匯入物件時，系統會自動設定這些權限，您也可以使用 gcloud 指令設定這些權限。

1. 開啟 Cloud Shell。

   開啟 Cloud Shell

2. 設定含有 Cloud SQL 執行個體服務帳戶位址的環境變數：

   CLOUDSQL_SA="$(gcloud sql instances describe sqlserver-cloudsql --format='get(serviceAccountEmailAddress)')"
   

3. 將服務帳戶新增至 bucket 的 IAM 政策，做為讀取者和寫入者：

   gcloud storage buckets add-iam-policy-binding "gs://$(gcloud config get-value project)-bucket/" \
       --member=user:"${CLOUDSQL_SA}" --role=roles/storage.objectUser
   

匯入匯出的資料庫

1. 開啟 Cloud Shell。

   開啟 Cloud Shell

2. 將匯出的檔案匯入 Cloud SQL 執行個體：

   gcloud sql import bak sqlserver-cloudsql \
       "gs://$(gcloud config get-value project)-bucket/WideWorldImporters-copy.bak" \
       --database WideWorldImporters
   

   系統顯示提示訊息時，請輸入 y。

3. 安裝 SQL Server 工具套件：

   sudo apt install -y mssql-tools
   

   如果您接受授權條款，請在系統提示時輸入 yes。

   您可以使用這些工具，從 Cloud Shell 連線至 Cloud SQL，以便在 Cloud SQL 執行個體上執行查詢。

4. 將 Cloud SQL Proxy 連線至 SQL Server 執行個體：

   CONNECTION_NAME=$(gcloud sql instances describe sqlserver-cloudsql --format='value(connectionName)')
   cloud_sql_proxy -instances=${CONNECTION_NAME}=tcp:1433 &
   

5. 如要確認複製的資料庫是否正常運作，請執行查詢：

   /opt/mssql-tools/bin/sqlcmd -U sqlserver -S 127.0.0.1 -Q \
     'SELECT top(100)
       i.InvoiceDate, i.InvoiceID, i.CustomerID, LEFT(c.CustomerName,20) CustomerName,
       i.ConfirmedDeliveryTime, LEFT(i.ConfirmedReceivedBy,20) ConfirmedReceivedBy
     FROM
       WideWorldImporters.Sales.Invoices i
       JOIN WideWorldImporters.Sales.Customers c
       ON i.CustomerID=c.CustomerID
     WHERE i.ConfirmedDeliveryTime IS NOT NULL
     ORDER BY i.InvoiceDate desc;'
   

   系統提示時，請輸入 sqlserver-cloudsql 資料庫伺服器 (sqlserver12@) 的 sqlserver 使用者密碼。

   這項查詢會從最近 100 筆已提交的月結單中擷取摘要資訊。

   輸出內容如下：

   InvoiceDate      InvoiceID   CustomerID  CustomerName         ConfirmedDeliveryTime                  ConfirmedReceivedBy
   ---------------- ----------- ----------- -------------------- -------------------------------------- --------------------
         2016-05-30       70349         581 Wingtip Toys (Munich            2016-05-31 07:05:00.0000000 Youssef Eriksson
         2016-05-30       70350         123 Tailspin Toys (Roe P            2016-05-31 07:10:00.0000000 Ella Zvirbule
         2016-05-30       70351         175 Tailspin Toys (San A            2016-05-31 07:15:00.0000000 Julio Correa
         2016-05-30       70352        1029 Veronika Necesana               2016-05-31 07:20:00.0000000 Veronika Necesana
         2016-05-30       70353        1014 Narendra Tickoo                 2016-05-31 07:25:00.0000000 Narendra Tickoo
         2016-05-30       70354         930 Shantanu Huq                    2016-05-31 07:30:00.0000000 Shantanu Huq
         2016-05-30       70355         963 Be Trang                        2016-05-31 07:35:00.0000000 Be Trang
         2016-05-30       70356         567 Wingtip Toys (Jerome            2016-05-31 07:40:00.0000000 Severins Polis
         2016-05-30       70357         510 Wingtip Toys (Grabil            2016-05-31 07:45:00.0000000 Manish Ghosh
   ...