本教學課程將說明如何將資料從在 Compute Engine 上執行的 Microsoft SQL Server 2017 Enterprise 遷移至 SQL Server 2017 Enterprise 適用的 Cloud SQL。如果您是系統管理員、開發人員、工程師、資料庫管理員或 DevOps 工程師,本教學課程將會非常實用。本教學課程將說明如何設定 SQL Server 發布器、使用 SQL Server 代理程式將快照複製到 SQL Server 2017 適用的 Cloud SQL,並驗證資料是否已成功匯入。
在本教學課程中,您將使用快照複製功能,讓來源和目標保持同步。快照複製功能會向訂閱者傳送每篇文章的完整副本,也就是已發布的資料庫物件。Cloud SQL 也支援交易複製功能,只會傳送增量資料。交易複製功能的限制是資料表必須具備主鍵。如要進一步瞭解 SQL Server 複寫類型,請參閱 SQL Server 說明文件。
為求簡單起見,本教學課程會使用來源 SQL Server 來代管發布端。在實際工作情境中,如果資料是從 Google Cloud外部複製,您可能會選擇在 Compute Engine 執行個體的 Google Cloud 端代管發布作業。
本教學課程假設您熟悉下列項目:
- SQL Server
- Microsoft PowerShell
- Compute Engine
- SQL Server 適用的 Cloud SQL
目標
- 在 Compute Engine 上建立 SQL Server 虛擬機器 (VM) 執行個體,以便代管範例資料庫。
- 填入範例資料庫。
- 建立 Cloud SQL for SQL Server 執行個體。
- 建立發布商。
- 設定發布和訂閱。
- 啟動從 SQL Server 到 Cloud SQL 的複製作業。
- 驗證匯入的資料。
費用
在本文件中,您會使用 Google Cloud的下列計費元件:
- Compute Engine
- Cloud SQL
- Cloud Storage
- SQL Server (premium with Compute Engine)
您可以使用 Pricing Calculator 根據預測用量產生預估費用。
事前準備
- 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.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
Enable the Cloud SQL Admin and Compute Engine API APIs.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
Enable the Cloud SQL Admin and Compute Engine API APIs.
-
In the Google Cloud console, 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.
- 安裝所需的遠端桌面通訊協定 (RDP) 用戶端。詳情請參閱「Microsoft 遠端桌面用戶端」。如果您已安裝遠端桌面協定用戶端,可以略過這項工作。
完成本文件所述工作後,您可以刪除已建立的資源,避免繼續計費。詳情請參閱「清除所用資源」。
建立 SQL Server VM
第一步是��� Compute Engine 的 Windows 2016 VM 上建立 SQL Server 2017 執行個體,然後使用 RDP 連線至該執行個體。
在 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 據點」。產生 Windows 密碼:
gcloud compute reset-windows-password sqlserver --zone=us-central1-f
前往 Google Cloud 控制台的「VM instances」(VM 執行個體) 頁面。
在 Google Cloud 控制台的「Compute Engine」部分,按一下「RDP」下拉式選單,然後選取「Download the RDP file」選項,即可下載執行個體的 RDP 檔案。
使用此檔案即可透過遠端桌面協定用戶端連線至執行個體。詳情請參閱 Microsoft 遠端桌面用戶端。
在「使用者名稱」和「密碼」欄位中,輸入您為 SQL Server VM 執行個體建立的使用者名稱和密碼。將「Domain」(網域) 欄位留空,然後按一下「OK」即可連線至 SQL Server VM。
在系統提示時接受憑證。
如果您接受條款,請按一下 [Continue] (繼續)。
在執行個體中,將所有視窗最小化,按一下 Windows 工作列上的「開始」,輸入
PowerShell
,然後在 Windows PowerShell ���用程式上按一下���鼠右鍵,選取「以系統管理員身分執行」。在 PowerShell 提示中,為資料庫儲存空間建立目錄結構:
mkdir c:\sql-server-data\adventureworks
建立及填入範例資料庫
您現在可以下載 Microsoft AdventureWorks 資料庫備份檔案,並將其還原至 SQL Server 執行個體。這個資料庫會模擬您要遷移的正式資料庫。
從 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
還原資料庫:
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"
請查詢
Customer
資料表中的列數,驗證新復原的資料庫。輸出結果為 847 列。osql -E -Q "select count(*) from AdventureWorksLT2017.SalesLT.Customer"
準備 Cloud SQL 執行個體
在 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 位址。將來源 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
將 SQL Server VM IP 位址與 Cloud SQL 執行個體新增至許可清單 (白名單):
gcloud sql instances patch target-sqlserver --authorized-networks=$sql_source_ip
建立目的地資料庫:
gcloud sql databases create AdventureWorksTarget --instance=target-sqlserver
在以管理員身分啟動的 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 上執行發布器。
在以管理員身分啟動的 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 指令列。確認執行個體名稱現在為
"sqlserver"
:osql -E -Q "select @@servername;"
指定發布資料庫:
$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"
設定發布資料庫:
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' "
請以發布商的身分,向發布器註冊 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'"
設定發布
分發作業設定完成後,您可以設定要發布的資料表。
在 PowerShell 指令列中啟用並啟動 SQL Server 代理程式:
Set-Service -Name SQLServerAgent -StartupType Automatic Start-Service -Name SQLServerAgent
為
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"
設定發布作業,每小時傳送一次來源快照至 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"
在出版品中新增文章 (表格)。雖然
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' "
手動建立文章快照。如果不這樣做,排程器可能需要花費最多一小時的時間,才能建立初始快照。
osql -E -Q "use [AdventureWorksLT2017] exec sp_startpublication_snapshot @publication = N'advn-pub3'"
設定訂閱項目
您現在可以建立訂閱項目,將發布的資料傳送至 Cloud SQL。
透過 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' "
驗證 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
清除所用資源
完成教學課程後,您可以清除所建立的資源,這樣資源就不會占用配額並產生費用。下列各節將說明如何刪除或關閉這些資源。
刪除專案
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.
後續步驟
- 瞭���如何使用備份檔案,將資料從 SQL Server 2017 遷移至 SQL Server 適用的 Cloud SQL。
- 瞭解如何使用備份檔案,將資料從 SQL Server 2008 遷移至 SQL Server 適用的 Cloud SQL。
- 探索 Google Cloud 的參考架構、圖表和最佳做法。歡迎瀏覽我們的雲端架構中心。