設定外部備用資源

MySQL   |  PostgreSQL   |  SQL Server

本頁面說明如何設定 Cloud SQL 執行個體，將資料發布至 Cloud SQL 外部或內部的訂閱者。SQL Server 適用的 Cloud SQL 外部複製作業會使用交易式複製，其中 Cloud SQL 會做為發布者，將資料發布給訂閱者。

如 Microsoft 文件所述，交易式複製支援發布多種類型的物件。此外，Cloud SQL 對這項功能的限制，與 Microsoft 說明文件中的限制類似。

支援的物件包括：

• Tables
• 預存程序
• 瀏覽次數
• 索引檢視
• 使用者定義函式

雖然本頁提供發布程序範例，但詳情請參閱 Microsoft 說明文件中的「發布資料和資料庫物件」。建議您在發布程序中使用 SQL Server Management Studio (SSMS)，因為 SSMS 可能會更清楚顯示可用選項。

另請參閱「關於 Cloud SQL 中的複製功能」。

注意：如需本頁範例提供的預存程序參考資訊，請參閱「Cloud SQL 預存程序」。

實作交易複製

Cloud SQL 執行個體可透過交易式複製，做為外部訂閱者的發布者和發布者。

如要設定交易式複製，您可以：

• 使用 Cloud SQL 提供的預存程序。這些前置字串包括： gcloudsql_transrepl_
• 使用 Microsoft 提供的預存程序調整複製作業

限制和必要條件

規劃交易複製時，請詳閱本節。

限制

• 使用高可用性 (HA) 的執行個體沒有一致的傳出 IP 位址。如果這類執行個體使用公開 IP 連線，就無法做為發布端。因此，如果執行個體使用高可用性，就必須使用私人 IP 連線。
• 如果使用 Private Service Connect，請務必在發布端執行個體設定傳出連線。
• 如要瞭解如何定義發行項，請參閱 Microsoft 說明文件中的「定義發行項」，包括限制和約束。
• Cloud SQL 提供的預存程序僅支援推送訂閱項目。
• 如果執行個體已設定複製功能，就無法設定外部複製。

必要條件

Cloud SQL 執行個體和訂閱者執行個體之間必須設定雙向網路連線。訂閱者可以是外部訂閱者 (例如地端部署訂閱者)，也可以是 Cloud SQL 內部的訂閱者。

如果 Cloud SQL 執行個體使用公開 IP，Cloud SQL 會在輸出和輸入路徑使用不同的 IP 位址。副本必須將主要執行個體的傳出 IP 位址加入許可清單，您可以使用 gcloud 指令擷取該位址：

gcloud sql instances describe [PRIMARY_NAME] --format="default(ipAddresses)"

如要搭配地端部署執行個體使用 Cloud SQL 私人 IP，必須設定私人服務存取權。這項作業需要透過必須公告的自訂 IP 範圍，在 Cloud SQL 虛擬私有雲和客戶的虛擬私有雲之間建立對接。

如要搭配使用 Cloud SQL 和 Private Service Connect，請參閱「設定 Private Service Connect」。使用 Private Service Connect 時，請務必在發布者執行個體上設定傳出連線，以便與訂閱者通訊。

從地端部署環境連線時，地端部署防火牆必須允許傳入和傳出連線。具體來說，地端部署防火牆必須允許這類連線透過通訊埠 1433，連至特定 Google Cloud 服務 (在本例中為 Cloud SQL) 使用的私人服務存取子網路範圍。建議您允許子網路範圍，而非為每個建立的執行個體指定特定 IP。

如需相關資訊，請參閱下列資源：

• 設定私人 IP (包含設定私人服務存取權的連結)
• 設定 Private Service Connect
• 私人服務存取權
• 設定私人服務連線

權限與角色

以下各節將說明權限和角色。

套裝權限

Cloud SQL 提供的預存程序包含交易式複製所需的權限。這些是包裝函式預存程序，有時會呼叫一或多個 Microsoft 預存程序。如要瞭解 Microsoft 預存程序，請參閱 Microsoft 說明文件。

必要角色

使用的帳戶 (包括記錄讀取器代理程式) 需要 db_owner 角色，就像 sqlserver 使用者一樣。如需必要資訊，請參閱複製代理程式安全性模型。

使用預存程序發布、發行及訂閱

本節提供設定交易式複製的建議步驟。

sqlserver 使用者有儲存程序，可將 Cloud SQL 執行個體設為發布者。如需參考資訊，請參閱「Cloud SQL 預存程序」。

使用測試資料庫準備交易式複製

為正式資料庫設定交易式複製前，您可以將執行個體設為測試資料庫物件的發布者。在本頁中，測試資料庫稱為 pub_demo 資料庫。

使用 sqlserver 使用者連線至 Cloud SQL 執行個體，並建立測試用資料庫。例如：

Create Database pub_demo;
GO
USE pub_demo;
CREATE TABLE Employee(employeeId INT primary key);
INSERT INTO Employee([employeeId]) VALUES (1);
INSERT INTO Employee([employeeId]) VALUES (2);

-- Add procedure
CREATE OR ALTER PROCEDURE dbo.p_GetDate
AS
SELECT getdate()

-- Add view
CREATE OR ALTER VIEW dbo.v_GetDbs
AS
SELECT name from sys.databases

-- Function
CREATE OR ALTER FUNCTION dbo.fn_ListDbFiles(@id int)
RETURNS TABLE
AS
RETURN
(
select * from sys.master_files where database_id = @id
)

設定發布資料庫

針對發布資料庫，您可以使用 msdb.dbo.gcloudsql_transrepl_setup_distribution，這是下列 Microsoft 預存程序的包裝函式預存程序：

• sp_adddistributor
• sp_adddistributiondb
• sp_adddistpublisher

例如：

EXEC msdb.dbo.gcloudsql_transrepl_setup_distribution @login='sqlserver', @password='<password>'

啟用資料庫發布功能

如要啟用或停用資料庫的發布選項，可以使用 msdb.dbo.gcloudsql_transrepl_replicationdboption。這個預存程序適用於使用 sp_replicationdboption 的發布者發布選項。

例如：

EXEC msdb.dbo.gcloudsql_transrepl_replicationdboption @db='pub_demo', @value='true'

新增記錄讀取器代理

您可以使用 sp_addlogreader_agent 為資料庫設定記錄讀取器代理程式。

例如：

EXEC msdb.dbo.gcloudsql_transrepl_addlogreader_agent @db='pub_demo', @login='sqlserver', @password='<password>'

建立資料庫的發布項目

您可以使用 msdb.dbo.gcloudsql_transrepl_addpublication 為指定的資料庫建立交易發布。這個預存程序會包裝 sp_addpublication。

例如：

EXEC msdb.dbo.gcloudsql_transrepl_addpublication @db='pub_demo', @publication='pub1'

為指定發布項目建立快照代理程式

如要為發布者資料庫建立快照代理程式，可以使用 msdb.dbo.gcloudsql_transrepl_addpublication_snapshot，其中包含 sp_addpublication_snapshot。

例如：

EXEC msdb.dbo.gcloudsql_transrepl_addpublication_snapshot @db='pub_demo',  @publication='pub1', @login='sqlserver', @password='<password>'

建立文章並新增至出版品

您可以在發布者資料庫中建立文章，並將文章新增至出版品。以 sqlserver 使用者身分，使用 sp_addarticle。

您也可以使用 SSMS 新增文章。詳情請參閱「在發布項目中新增及刪除文章」。

例如：

USE pub_demo;
GO

EXEC sp_addarticle @publication = 'pub1',
                   @article = 'csql_dbo.employee',
                   @source_owner = 'dbo',
                   @source_object = 'Employee',
                   @description = N'cloudsql_article_table',
                   @schema_option = 0x000000000903409D,
                   @identityrangemanagementoption = N'manual',
                   @destination_table = 'Employee',
                   @destination_owner = 'dbo';

-- add function
use [pub_demo]
exec sp_addarticle  @publication = N'pub1',
                   @article = N'fn_ListDbFiles',
                   @source_owner = N'dbo',
                   @source_object = N'fn_ListDbFiles',
                   @type = N'func schema only',
                   @description = N'',
                   @creation_script = N'',
                   @pre_creation_cmd = N'drop',
                   @schema_option = 0x0000000008000001,
                   @destination_table = N'fn_ListDbFiles',
                   @destination_owner = N'dbo',
                   @status = 16

-- add procedure
use [pub_demo]
exec sp_addarticle  @publication = N'pub1',
                   @article = N'p_GetDate',
                   @source_owner = N'dbo',
                   @source_object = N'p_GetDate',
                   @type = N'proc schema only',
                   @description = N'',
                   @creation_script = N'',
                   @pre_creation_cmd = N'drop',
                   @schema_option = 0x0000000008000001,
                   @destination_table = N'p_GetDate',
                   @destination_owner = N'dbo',
                   @status = 16

-- add view
use [pub_demo]
exec sp_addarticle  @publication = N'pub1',
                   @article = N'v_GetDbs',
                   @source_owner = N'dbo',
                   @source_object = N'v_GetDbs',
                   @type = N'view schema only',
                   @description = N'',
                   @creation_script = N'',
                   @pre_creation_cmd = N'drop',
                   @schema_option = 0x0000000008000001,
                   @destination_table = N'v_GetDbs',
                   @destination_owner = N'dbo',
                   @status = 16

將訂閱項目新增至出版品

您可以在資料庫中，將訂閱項目新增至發布項目。以 sqlserver 使用者身分，使用 sp_addsubscription 設定訂閱者狀態。

例如：

Use pub_demo;
GO
EXEC sp_addsubscription @publication ='pub1',
                        @subscriber = N'10.10.100.1,1433',
                        @destination_db = pub_demo,
                        @subscription_type = N'Push',
                        @sync_type = N'automatic',
                        @article = N'all',
                        @update_mode = N'read only',
                        @subscriber_type = 0

連線至訂閱者並建立訂閱資料庫

您可以連線至訂閱者，並建立訂閱資料庫，以便填入複製的資料。

例如：

 Create Database pub_demo

新增排程代理工作，同步處理推送訂閱項目

您可以新增排程代理工作，將推送訂閱項目同步處理至發布項目。例如，在發布者資料庫上，執行類似下方的指令。這個指令會使用 msdb.dbo.gcloudsql_transrepl_addpushsubscription_agent，這是 sp_addpushsubscription_agent 的包裝函式預存程序：

EXEC msdb.dbo.gcloudsql_transrepl_addpushsubscription_agent
@db='pub_demo',
@publication = 'pub1',
@subscriber_db= 'pub_demo',
@subscriber_login='sqlserver',
@subscriber_password='<password>',
@subscriber='11.11.111.1,1433'

啟動發布快照代理程式工作

您可以按照下列步驟，啟動發布快照代理程式工作：

USE pub_demo;
EXEC sp_startpublication_snapshot
@publication = 'pub1'

授予帳戶存取權，以便使用複製監控工具

msdb.dbo.gcloudsql_transrepl_addmonitoraccess 可用來：

• 提供 SSMS 中複製監視器的存取權
• 查詢發布資料庫中的資料表

因此，這個預存程序可讓您在發布資料庫的複製相關資料表 (例如 MSrepl_errors 資料表) 上使用 SELECT 陳述式：

EXEC msdb.dbo.gcloudsql_transrepl_addmonitoraccess
@login = 'sqlserver'

變更發行資料庫屬性

你可以變更heartbeat_interval。請使用 msdb.dbo.gcloudsql_transrepl_changedistributor_property 程序，該程序會包裝 sp_changedistributor_property。

詳情請參閱 sp_changedistributor_property 的說明文件。如要進一步瞭解 heartbeat_interval 值，也請參閱該說明文件。

例如：

EXEC msdb.dbo.gcloudsql_transrepl_changedistributor_property
@property = N'heartbeat_interval',
@value = 90

使用 Replication Monitor

在 SSMS 中，在複製節點上按一下滑鼠右鍵，然後選擇「Launch Replication Monitor」(啟動複製監視器)。

點選「代理」分頁標籤後，應該會看到類似下方的畫面：

使用預存程序移除複製作業

本節提供移除交易式複製的建議步驟。

捨棄訂閱項目

如要捨棄訂閱項目，請使用 sp_dropsubscription 預存程序。

以下是捨棄訂閱項目的指令範例：

USE  pub_demo;
GO
EXEC sp_dropsubscription
          @publication = 'csql_pub_pub_demo',
          @article     = N'all',
          @subscriber  = N'11.11.111.1,1433'

注意：在下列 SQL Server 版本中，使用類似範例的指令來捨棄訂閱項目時，請勿指定通訊埠：SQL Server 2019 Standard、SQL Server 2019 Enterprise、SQL Server 2019 Express、SQL Server 2019 Web、SQL Server 2022 Standard、SQL Server 2022 Enterprise、SQL Server 2022 Express 和 SQL Server 2022 Web。

捨棄訂閱端

如要捨棄訂閱者，請使用 msdb.dbo.gcloudsql_transrepl_dropsubscriber 預存程序：

EXEC msdb.dbo.gcloudsql_transrepl_dropsubscriber
 @subscriber  = N'11.11.111.1,1433'

捨棄發布項目

如要捨棄發布作業，請使用 msdb.dbo.gcloudsql_transrepl_droppublication 預存程序：

EXEC msdb.dbo.gcloudsql_transrepl_droppublication
  @db = 'pub_demo', @publication='pub1'

停用發布資料庫

如要停用發布資料庫，請使用 msdb.dbo.gcloudsql_transrepl_replicationdboption 預存程序：

EXEC msdb.dbo.gcloudsql_transrepl_replicationdboption
@db='pub_demo',
@value=N'false'

移除發布資料庫

如要移除發布資料庫，請使用 msdb.dbo.gcloudsql_transrepl_remove_distribution 預存程序：

EXEC msdb.dbo.gcloudsql_transrepl_remove_distribution

疑難排解提示和步驟

以下各節說明常見的疑難排解主題。

從正確的資料庫執行預存程序

執行 sp_addarticle、sp_addsubscription 或 sp_startpublication_snapshot 時，可能會發生下列錯誤：

Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation.

此外，執行 sp_dropsubscription 時，您可能會收到下列錯誤：

This database is not enabled for publication.

如果發生這些錯誤，請注意，您必須從適用的資料庫中執行這些預存程序。您可以使用下列指令，確認指令是從正確的資料庫執行：

USE <database_name>;
GO
<Run stored proc>

複製

SQL 錯誤記錄和其他位置會提供複製錯誤。您可以直接查詢發布資料庫中的某些資料表，找出複製錯誤。例如：

select * from [cloudsql_distribution]..msrepl_errors
select * from [cloudsql_distribution]..MSreplication_monitordata

Microsoft 提供更多範例，說明如何使用 Replication Monitor 查詢錯誤。例如，sqlserver 使用者可能無法存取指令。

複製代理程式工作

設定複製作業後，sqlserver 使用者在 SSMS 中看不到新的 SQL 代理程式工作。不過，您可以透過下列方式查看：

USE msdb
select * from dbo.sysjobs

複製監視器中缺少發布者

您可以使用複製監控工具查看複製狀態，並排解複製問題。

舉例來說，如果您設定了複製功能，且發布者 Cloud SQL 執行個體使用 IP 位址，SSMS 可能會找不到發布者。這是因為它不知道主機名稱和 IP 位址之間的對應關係。

「Replication Monitor」包含空白的「Publications」分頁：

如要解決這個問題，您可以在 SQL Server Configuration Manager 中建立別名，連結發布者的 SQL Server 主機名稱和用於從 SSMS 連線的 IP 位址：

1. 啟動 SQL Server 組態管理員。

   

2. 找到並選取「Aliases」節點。

   

3. 在「Alias name」下方的方塊上按一下滑鼠右鍵，建立新的別名。32 位元和 64 位元別名的程序相同：

   

4. 使用下列查詢，擷取發布端執行個體的實際主機名稱：

   

5. 在別名視窗中，輸入下列欄位，然後選取「OK」：

   別名：提供步驟 4 查詢中的伺服器名稱。

   Port No: 提供通訊埠 1433。

   通訊協定：保留預設值 TCP/IP。

   伺服器：提供發布端執行個體的 IP 位址。

   

6. 使用新別名連線，並啟動 Replication Monitor：

   

發布資訊應與下列內容類似：

如要進一步瞭解如何排解複製問題，請參閱「疑難排解工具：找出 SQL Server 交易複製錯誤」。

估算需要複製的文章大小

將 Cloud SQL 執行個體做為發布者時，必須先產生要發布項目的初始快照，才能開始複製。這個快照會儲存在本機。視文章數量、大小和資料類型而定，儲存空間需求可能會增加。sp_spaceused 預存程序只會提供文章所需磁碟空間的粗略估計值。

快照包含儲存結構定義和資料的檔案。

後續步驟

• 瞭解 Cloud SQL 中的複製功能。