使用快照複製功能，將資料從 SQL Server 2017 遷移至 SQL Server 適用的 Cloud SQL

本教學課程說明如何將資料從 Compute Engine 上執行的 Microsoft SQL Server 2017 Enterprise 遷移至 Cloud SQL for SQL Server 2017 Enterprise。如果您是系統管理員、開發人員、工程師、資料庫管理員或 DevOps 工程師，本教學課程將會非常實用。本教學課程說明如何設定 SQL Server Distributor、使用 SQL Server Agent 將快照複製到 SQL Server 2017 適用的 Cloud SQL，以及驗證資料是否已成功匯入。

在本教學課程中，您將使用快照複製功能，讓來源和目標保持同步。快照複寫會將每個文章 (也就是已發布的資料庫物件) 的完整副本傳送給訂閱者。Cloud SQL 也支援交易式複製，只會傳送增量資料。交易式複寫的限制是資料表必須有主鍵。如要進一步瞭解 SQL Server 複製類型，請參閱 SQL Server 說明文件。

為求簡單起見，本教學課程會使用來源 SQL Server 來代管散發者。在實際運作情境中，如果資料是從外部複製，您可能偏好在 Google Cloud 端以 Compute Engine 執行個體代管發布作業。 Google Cloud

本教學課程假設您熟悉下列項目：

目標

  • 在 Compute Engine 上建立 SQL Server 虛擬機器 (VM) 執行個體，以代管範例資料庫。
  • 填入範例資料庫。
  • 建立 SQL Server 適用的 Cloud SQL 執行個體。
  • 建立經銷商。
  • 設定發布和訂閱。
  • 從 SQL Server 啟動複製作業至 Cloud SQL。
  • 驗證匯入的資料。

費用

在本文件中，您會使用 Google Cloud的下列計費元件：

如要根據預測用量估算費用，請使用 Pricing Calculator。

初次使用 Google Cloud 的使用者可能符合免費試用資格。

事前準備

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Google Cloud project.

  4. Enable the Cloud SQL Admin and Compute Engine API APIs.

    Enable the APIs

  5. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  6. Make sure that billing is enabled for your Google Cloud project.

  7. Enable the Cloud SQL Admin and Compute Engine API APIs.

    Enable the APIs

    8.

  8. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

  9. 安裝您選擇的遠端桌面通訊協定 (RDP) 用戶端。詳情請參閱「Microsoft 遠端桌面用戶端」。 如果您已安裝遠端桌面協定用戶端，則可略過這項工作。

    10. 完成本文所述工作後，您可以刪除已建立的資源，避免繼續計費。詳情請參閱清除所用資源一節。

建立 SQL Server VM

第一步是在 Compute Engine 的 Windows 2016 VM 上建立 SQL Server 2017 執行個體，並使用 RDP 連線至該執行個體。

  1. 在 Cloud Shell 中，於 Windows Server 2016 上建立 SQL Server 2017 Standard 執行個體。來源資料庫必須是 SQL Server Standard 或 Enterprise 資料庫，因為較低層級的資料庫沒有資料庫發布者功能。

    gcloud compute instances create sqlserver --machine-type=n1-standard-4 \
    --boot-disk-size=100GB \
    --image-project=windows-sql-cloud \
    --image-family=sql-std-2017-win-2016 \
    --zone=us-central1-f  \
    --scopes=https://www.googleapis.com/auth/cloud-platform

    在本教學課程中，您會在 us-central1-f 地區建立執行個體，開機磁碟大小為 100 GB。詳情請參閱「Cloud 據點」一文。

  2. 產生 Windows 密碼：

    gcloud compute reset-windows-password sqlserver --zone=us-central1-f

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

    前往「VM instances」(VM 執行個體) 頁面

  4. 在 Google Cloud 控制台的「Compute Engine」部分，按一下「RDP」下拉式選單，然後選取「Download the RDP file」(下載遠端桌面協定檔案) 選項，下載執行個體的遠端桌面協定檔案。

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

  5. 在「username」(使用者名稱)和「password」(密碼) 欄位中，輸入您為 SQL Server VM 執行個體建立的使用者名稱和密碼。將「Domain」(網域) 欄位保留空白，然後按一下「OK」(確定)，即可連線至 SQL Server VM。

    螢幕截圖：輸入使用者名稱和密碼的對話方塊。

    系統提示時，請接受憑證。

  6. 如果您接受條款，請按一下 [Continue] (繼續)。

  7. 在執行個體中，將所有視窗最小化，按一下 Windows 工作列上的「開始」，輸入 PowerShell，然後在 Windows PowerShell 應用程式上按一下滑鼠右鍵，並選取「以系統管理員身分執行」。

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

    mkdir c:\sql-server-data\adventureworks

建立及填入範例資料庫

現在請下載 Microsoft AdventureWorks 資料庫備份檔案，並將其還原至 SQL Server 執行個體。這個資料庫會模擬您要遷移的實際工作環境資料庫。

  1. 從 PowerShell 命令列將備份檔案下載至 C 磁碟機：AdventureWorksLT2017.bak

    bitsadmin /transfer sampledb /dynamic /download /priority FOREGROUND "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksLT2017.bak " C:\sql-server-data\AdventureWorksLT2017.bak

  2. 還原資料庫：

    osql -E -Q "USE [master]
RESTORE DATABASE [AdventureWorksLT2017]
FROM  DISK = N'C:\sql-server-data\AdventureWorksLT2017.bak' WITH  FILE = 1,
MOVE N'AdventureWorksLT2012_Data' TO N'C:\sql-server-data\adventureworks\AdventureWorksLT2012.mdf',
MOVE N'AdventureWorksLT2012_Log' TO N'C:\sql-server-data\adventureworks\AdventureWorksLT2012_log.ldf',
NOUNLOAD,  STATS = 5"

  3. 查詢 Customer 資料表中的列數，驗證新還原的資料庫。輸出結果為 847 列。

    osql -E -Q "select count(*) from AdventureWorksLT2017.SalesLT.Customer"

準備 Cloud SQL 執行個體

  1. 在 Cloud Shell 中，建立 Cloud SQL for SQL Server 2017 Enterprise 執行個體：

    gcloud sql instances create target-sqlserver  \
    --database-version=SQLSERVER_2017_STANDARD \
    --cpu=4 \
    --memory=15 \
    --storage-size=100 \
    --root-password=sqlserver12@ \
    --zone=us-central1-f

    root 使用者為 sqlserver，密碼為 sqlserver12@。請記下 SQL Server IP 位址。

  2. 將來源 SQL Server VM 的 IP 位址儲存在環境變數中：

    sql_source_ip=$(gcloud compute instances describe sqlserver \
    --zone=us-central1-f | grep natIP | awk '{print $2}')
echo $sql_source_ip

  3. 將 SQL Server VM IP 位址和 Cloud SQL 執行個體新增至許可清單 (白名單)：

    gcloud sql instances patch target-sqlserver --authorized-networks=$sql_source_ip

  4. 建立目的地資料庫：

    gcloud sql databases create AdventureWorksTarget --instance=target-sqlserver

  5. 在以管理員身分啟動的 PowerShell 指令列中，檢查是否能連線至 Cloud SQL for SQL Server：

    $cloud_sql_server_ip=gcloud sql instances describe target-sqlserver --format='value(ipAddresses.ipAddress)'
osql -S $cloud_sql_server_ip -U sqlserver -P sqlserver12@ -Q "select 'test'"

    這個指令會輸出 'test'。

從 VM 啟動複製到 Cloud SQL 的作業

使用 SQL Server 快照複製功能，建立將資料庫快照複製到 Cloud SQL 的作業。

設定發布商

現在請將 SQL Server VM 設定為 SQL Server 發布者。實務上，發布者可以在獨立機器上執行，但本教學課程是在同一部 VM 上執行。

  1. 在以管理員身分啟動的 PowerShell 指令列中，更新 SQL Server 本機執行個體名稱，使其與 VM 主機名稱相符：

    $servername=hostname
osql -E -Q "sp_dropserver 'INST-INSTALL-SQ';"
osql -E -Q "sp_addserver '$servername', local;"
Restart-Service -F MSSQLServer
mkdir c:\sql-server-data\repldata

    如果收到 Cannot open MSSQLServer service on computer '.'. 錯誤訊息，可能是因為您未以管理員身分執行 PowerShell 指令列。

  2. 確認執行個體名稱現在為「"sqlserver"」：

    osql -E -Q "select @@servername;"

  3. 指定發布資料庫：

    $servername=hostname
osql -E -Q "use master; exec sp_adddistributor @distributor = N'$servername', @password = N'';"
osql -E -Q "exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1"

  4. 設定發布資料庫：

    osql -E -Q "
use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
    create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
    exec sp_updateextendedproperty N'SnapshotFolder', N'c:\sql-server-data\repldata', 'user', dbo, 'table', 'UIProperties'
else
    exec sp_addextendedproperty N'SnapshotFolder', N'c:\sql-server-data\repldata', 'user', dbo, 'table', 'UIProperties'
"

  5. 向發布者註冊 SQL Server VM 名稱：

    osql -E -Q "exec sp_adddistpublisher @publisher = N'$servername',
@distribution_db = N'distribution', @security_mode = 1, @working_directory
= N'c:\sql-server-data\repldata', @trusted = N'false', @thirdparty_flag =
0, @publisher_type = N'MSSQLSERVER'"

設定發布

設定完發布作業後，請設定要發布的資料表。

  1. 在 PowerShell 指令列中，啟用並啟動 SQL Server 代理程式：

    Set-Service -Name SQLServerAgent -StartupType Automatic
Start-Service -Name SQLServerAgent

  2. 為 AdventureWorksLT2017 資料庫建立發布作業：

    $servername=hostname
osql -E -Q "
use [AdventureWorksLT2017]
exec sp_replicationdboption @dbname = N'AdventureWorksLT2017', @optname = N'publish', @value = N'true'"

osql -E -Q "use [AdventureWorksLT2017]
exec sp_addpublication @publication = N'advn-pub3', @description = N'Snapshot publication of database ''AdventureWorksLT2017'' from Publisher ''$servername''.', @sync_method = N'native', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'snapshot', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1"

  3. 設定發布項目，每小時將來源的快照傳送至 Cloud SQL：

    osql -E -Q "
use [AdventureWorksLT2017]
exec sp_addpublication_snapshot @publication = N'advn-pub3', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1"

  4. 將文章 (表格) 新增至出版品。雖然 AdventureWorksLT2017 資料庫包含許多表格，但為了簡化本教學課程，您會複製三個表格：Address、Customer 和 CustomerAddress。

    osql -E -Q "
use [AdventureWorksLT2017]
exec sp_addarticle @publication = N'advn-pub3', @article = N'Address', @source_owner = N'SalesLT', @source_object = N'Address', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'Address', @destination_owner = N'SalesLT', @vertical_partition = N'false'
"

osql -E -Q "
use [AdventureWorksLT2017]
exec sp_addarticle @publication = N'advn-pub3', @article = N'Customer', @source_owner = N'SalesLT', @source_object = N'Customer', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'Customer', @destination_owner = N'SalesLT', @vertical_partition = N'false'
"

osql -E -Q "
use [AdventureWorksLT2017]
exec sp_addarticle @publication = N'advn-pub3', @article = N'CustomerAddress', @source_owner = N'SalesLT', @source_object = N'CustomerAddress', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'CustomerAddress', @destination_owner = N'SalesLT', @vertical_partition = N'false'
"

  5. 手動建立文章的快照。否則排程器可能需要一小時，才會建立初始快照。

    osql -E -Q "use [AdventureWorksLT2017]
exec sp_startpublication_snapshot @publication = N'advn-pub3'"

設定訂閱方案

現在請建立訂閱項目，將發布的資料傳送至 Cloud SQL。

  1. 在 PowerShell 命令列中建立訂閱項目，將發布快照傳送至 Cloud SQL：

    $cloud_sql_server_ip=gcloud sql instances describe target-sqlserver --format='value(ipAddresses.ipAddress)'
$cloud_sql_user="sqlserver"
$cloud_sql_password="sqlserver12@"
$target_db_name="AdventureWorksTarget"
osql -E -Q " use [AdventureWorksLT2017] exec sp_addsubscription @publication = N'advn-pub3', @subscriber = N'$cloud_sql_server_ip', @destination_db = N'$target_db_name', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

exec sp_addpushsubscription_agent @publication = N'advn-pub3', @subscriber = N'$cloud_sql_server_ip', @subscriber_db = N'$target_db_name', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'$cloud_sql_user', @subscriber_password = N'$cloud_sql_password', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20200408, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor' "

  1. 確認資料是否已匯入 Cloud SQL 執行個體：

    osql -S $cloud_sql_server_ip -U $cloud_sql_user -P $cloud_sql_password -Q "select count(*) from [AdventureWorksTarget].[SalesLT].[CustomerAddress] UNION
select count(*) from [AdventureWorksTarget].[SalesLT].[Customer]
UNION
Select count(*) from [AdventureWorksTarget].[SalesLT].[Address]"

    這個指令會輸出 417、450、847

清除所用資源

完成教學課程後，您可以清除所建立的資源，這樣資源就不會繼續使用配額，也不會產生費用。下列各節將說明如何刪除或關閉這些資源。

刪除專案

  1. In the Google Cloud console, go to the Manage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then click Delete.
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

後續步驟