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

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

建立 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 執行個體」

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

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

5. 在「使用者名稱」和「密碼」欄位中，輸入您為 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 指令列下載 AdventureWorksLT2017.bak 備份檔案至 C 槽：

   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 中，建立 SQL Server 2017 Enterprise 適用的 Cloud SQL 執行個體：

   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
   

   超級使用者為 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