SQL Server Service Broker 實戰：憑證式安全配置、跨伺服器路由與完整部署範例

前言：為何選擇 Service Broker？

分散式系統需要可靠的非同步訊息傳遞。Service Broker (SB) 內建於 SQL Server 2005+，提供 **交易性佇列、保證順序、持久化、失效重試、安全傳輸**，且與資料庫交易完整整合 (同一交易內資料變更與訊息發送原子性)。本文以「主系統 → 多子系統行為資料同步」為場景，完整示範 **訊息/合約/佇列/服務建立、憑證式安全配置、跨伺服器端點/路由、啟動預存程序** 的端到端部署。

架構總覽

• Initiator (主系統)：產生行為資料 → 發送訊息至 Target Service。
• Target (子系統)：接收訊息 → 啟動預存程序處理業務邏輯 → 結束對話。
• 安全模式：**憑證驗證** (Certificate Authentication)，不依賴 TRUSTWORTHY，支援跨網域/工作群組。
• 網路拓撲：TCP 端點 (預設 4022，本文用 33333) + 路由 (ROUTE) 指向對方 IP:Port。

第一階段：核心物件建立 (雙邊共用定義)

1. 訊息類型與 XML Schema 集合

-- 雙邊資料庫均執行
CREATE XML SCHEMA COLLECTION [https://Samples/SQL/ServiceBroker/msgOperationSchema] AS
N'

  
    
      
        
        
      
    
  
';

CREATE MESSAGE TYPE [https://Samples/SQL/ServiceBroker/msgOperation]
    VALIDATION = VALID_XML WITH SCHEMA COLLECTION [https://Samples/SQL/ServiceBroker/msgOperationSchema];

CREATE CONTRACT [https://Samples/SQL/ServiceBroker/msgOperationContract]
( [https://Samples/SQL/ServiceBroker/msgOperation] SENT BY INITIATOR );

第二階段：Initiator 端 (主系統) 部署

1. 佇列與服務

CREATE QUEUE msgOperationInitQueue
WITH STATUS = ON, RETENTION = OFF;

CREATE SERVICE [https://Samples/SQL/ServiceBroker/msgOperationInitService]
ON QUEUE msgOperationInitQueue
([https://Samples/SQL/ServiceBroker/msgOperationContract]);

2. 發送預存程序

CREATE PROCEDURE dbo.usp_msgOperation_SET
    @msgId INT,
    @msgContent NVARCHAR(2000)
AS
BEGIN
    DECLARE @message_body XML = 
        N'' + CAST(@msgId AS NVARCHAR(20)) + N''
        + N'' + @msgContent + N'';
    
    DECLARE @dialog UNIQUEIDENTIFIER;
    
    BEGIN DIALOG @dialog
        FROM SERVICE [https://Samples/SQL/ServiceBroker/msgOperationInitService]
        TO SERVICE N'https://Samples/SQL/ServiceBroker/msgOperationProcessService'
        ON CONTRACT [https://Samples/SQL/ServiceBroker/msgOperationContract]
        WITH ENCRYPTION = OFF; -- 憑證模式下由傳輸層加密
    
    SEND ON CONVERSATION @dialog
        MESSAGE TYPE [https://Samples/SQL/ServiceBroker/msgOperation] (@message_body);
    
    END CONVERSATION @dialog;
END

第三階段：Target 端 (子系統) 部署

1. 啟動預存程序 (核心處理邏輯)

CREATE PROCEDURE dbo.usp_msgOperation_CMD
AS
BEGIN
    DECLARE @message_body XML, @message_type SYSNAME, @dialog UNIQUEIDENTIFIER;
    
    WHILE (1 = 1)
    BEGIN
        BEGIN TRANSACTION;
        
        WAITFOR (
            RECEIVE TOP(1)
                @message_type = message_type_name,
                @message_body = message_body,
                @dialog = conversation_handle
            FROM dbo.msgOperationProcessQueue
        ), TIMEOUT 2000; -- 2 秒無訊息則跳出
        
        IF (@@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION; BREAK;
        END
        
        -- 系統訊息處理
        IF (@message_type = N'https://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
            END CONVERSATION @dialog;
        ELSE IF (@message_type = N'https://schemas.microsoft.com/SQL/ServiceBroker/Error')
            END CONVERSATION @dialog;
        -- 業務訊息處理
        ELSE IF (@message_type = N'https://Samples/SQL/ServiceBroker/msgOperation')
        BEGIN
            DECLARE @msgId INT, @msgContent NVARCHAR(2000);
            BEGIN TRY
                SET @msgId = @message_body.value(N'(//msgId)[1]', 'INT');
                SET @msgContent = @message_body.value(N'(//msgContent)[1]', 'NVARCHAR(2000)');
                
                -- ★★★ 此處實作實際業務邏輯 (INSERT/UPDATE/呼叫其他 SP) ★★★
                -- 範例：INSERT INTO AuditLog (MsgId, Content, RecvTime) VALUES (@msgId, @msgContent, SYSUTCDATETIME());
                
                END CONVERSATION @dialog;
            END TRY
            BEGIN CATCH
                ROLLBACK TRANSACTION; -- 交易回滾，訊息留在佇列稍後重試
            END CATCH
        END
        
        COMMIT TRANSACTION;
    END
END

2. 佇列與啟動設定 (自動並行處理)

CREATE QUEUE msgOperationProcessQueue
WITH STATUS = ON, RETENTION = OFF,
ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = dbo.usp_msgOperation_CMD,
    MAX_QUEUE_READERS = 4, -- 最大並行讀取器數，依 CPU 核心調整
    EXECUTE AS SELF -- 或指定具體使用者 EXECUTE AS 'domain\svc_account'
);

CREATE SERVICE [https://Samples/SQL/ServiceBroker/msgOperationProcessService]
ON QUEUE dbo.msgOperationProcessQueue
([https://Samples/SQL/ServiceBroker/msgOperationContract]);

第四階段：憑證式安全配置 (核心亮點，替代 TRUSTWORTHY)

TRUSTWORTHY 簡單但風險高 (資料庫擁有者提權、跨資料庫所有權鏈)。憑證模式：**私鑰簽署發送端、公鑰驗證接收端**，細粒度授權 SEND 權限，無提權風險，支援跨網域。

Initiator 端配置

-- 1. 建立服務擁有者使用者 (無登入)
CREATE USER msgOperationInitServiceUser WITHOUT LOGIN;
ALTER AUTHORIZATION ON SERVICE::[https://Samples/SQL/ServiceBroker/msgOperationInitService] TO msgOperationInitServiceUser;

-- 2. 建立私鑰憑證 (簽署發送訊息)
CREATE CERTIFICATE msgOperactionInitServiceCertPriv
    AUTHORIZATION msgOperationInitServiceUser
    WITH SUBJECT = 'For MsgOperaction InitService', START_DATE = '2024-01-01', EXPIRY_DATE = '2099-12-31';

-- 3. 備份公鑰供 Target 匯入
BACKUP CERTIFICATE msgOperactionInitServiceCertPriv TO FILE = 'C:\Certs\msgOperactionInitServiceCertPub.cer';

-- 4. 建立代表 Target 服務的使用者
CREATE USER msgOperationProcessServiceUser WITHOUT LOGIN;

-- 5. 匯入 Target 公鑰憑證
CREATE CERTIFICATE msgOperactionProcessServiceCertPub
    AUTHORIZATION msgOperationProcessServiceUser
    FROM FILE = 'C:\Certs\msgOperactionProcessServiceCertPub.cer';

-- 6. 建立遠端服務繫結 (關鍵：指定使用 Target 公鑰驗證)
CREATE REMOTE SERVICE BINDING ToMsgOperactionProcessService
    TO SERVICE N'https://Samples/SQL/ServiceBroker/msgOperationProcessService'
    WITH USER = msgOperationProcessServiceUser;

Target 端配置

-- 1. 建立服務擁有者使用者
CREATE USER msgOperationProcessServiceUser WITHOUT LOGIN;
ALTER AUTHORIZATION ON SERVICE::[https://Samples/SQL/ServiceBroker/msgOperationProcessService] TO msgOperationProcessServiceUser;

-- 2. 建立私鑰憑證
CREATE CERTIFICATE msgOperactionProcessServiceCertPriv
    AUTHORIZATION msgOperationProcessServiceUser
    WITH SUBJECT = 'For MsgOperaction ProcessService', START_DATE = '2024-01-01', EXPIRY_DATE = '2099-12-31';

-- 3. 備份公鑰供 Initiator 匯入
BACKUP CERTIFICATE msgOperactionProcessServiceCertPriv TO FILE = 'C:\Certs\msgOperactionProcessServiceCertPub.cer';

-- 4. 建立代表 Initiator 服務的使用者
CREATE USER msgOperationInitServiceUser WITHOUT LOGIN;

-- 5. 匯入 Initiator 公鑰憑證
CREATE CERTIFICATE msgOperactionInitServiceCertPub
    AUTHORIZATION msgOperationInitServiceUser
    FROM FILE = 'C:\Certs\msgOperactionInitServiceCertPub.cer';

-- 6. 授予 Initiator 使用者發送權限
GRANT SEND ON SERVICE::[https://Samples/SQL/ServiceBroker/msgOperationInitService] TO msgOperationInitServiceUser;

第五階段：跨伺服器通訊配置 (Master DB + 端點 + 路由)

若 Initiator/Target 位於**不同 SQL Server 執行個體**，需在雙方 **master** 資料庫與應用資料庫配置：

Initiator 伺服器 (Server1)

-- Master DB
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongP@ssw0rd!'; -- 若不存在
CREATE CERTIFICATE [Server1_CertPriv]
    WITH SUBJECT = 'For Server1 Auth', START_DATE = '2024-01-01', EXPIRY_DATE = '2099-12-31',
    ACTIVE FOR BEGIN_DIALOG = ON;
BACKUP CERTIFICATE [Server1_CertPriv] TO FILE = 'C:\Certs\Server1_CertPub.cer';

CREATE ENDPOINT [Server1_Endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 33333)
    FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE [Server1_CertPriv]);

CREATE LOGIN [Server2_UserProxy] WITH PASSWORD = 'ComplexP@ss!';
CREATE USER [Server2_UserProxy];
CREATE CERTIFICATE [Server2_CertPub] AUTHORIZATION [Server2_UserProxy] FROM FILE = 'C:\Certs\Server2_CertPub.cer';
GRANT CONNECT ON ENDPOINT::[Server1_Endpoint] TO [Server2_UserProxy];

-- 應用資料庫
CREATE ROUTE msgOperationProcessServiceRoute
WITH SERVICE_NAME = N'https://Samples/SQL/ServiceBroker/msgOperationProcessService',
ADDRESS = N'TCP://10.1.10.20:33333'; -- Target 伺服器 IP:Port

Target 伺服器 (Server2)

-- Master DB (對稱配置)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongP@ssw0rd!';
CREATE CERTIFICATE [Server2_CertPriv] WITH SUBJECT = 'For Server2 Auth', ..., ACTIVE FOR BEGIN_DIALOG = ON;
BACKUP CERTIFICATE [Server2_CertPriv] TO FILE = 'C:\Certs\Server2_CertPub.cer';

CREATE ENDPOINT [Server2_Endpoint] STATE = STARTED AS TCP (LISTENER_PORT = 33333)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE [Server2_CertPriv]);

CREATE LOGIN [Server1_UserProxy] WITH PASSWORD = 'ComplexP@ss!';
CREATE USER [Server1_UserProxy];
CREATE CERTIFICATE [Server1_CertPub] AUTHORIZATION [Server1_UserProxy] FROM FILE = 'C:\Certs\Server1_CertPub.cer';
GRANT CONNECT ON ENDPOINT::[Server2_Endpoint] TO [Server1_UserProxy];

-- 應用資料庫
CREATE ROUTE msgOperationInitServiceRoute
WITH SERVICE_NAME = N'https://Samples/SQL/ServiceBroker/msgOperationInitService',
ADDRESS = N'TCP://10.1.10.10:33333'; -- Initiator 伺服器 IP:Port

生產環境部署心得與檢查清單

結語

Service Broker 提供資料庫原生的企業級訊息總線能力。掌握 **「核心物件 → 憑證安全 → 跨機端點/路由 → 啟動佇列 → 監控運維」** 五大模組，即可構建高可靠、高安全、可水平擴展的非同步整合架構。關鍵成功因素：拒絕 TRUSTWORTHY、嚴謹管理憑證生命週期、將路由配置納入部署自動化、建立傳輸佇列監控告警。將 SB 視為「資料庫內建的 RabbitMQ/Kafka」，善用其交易整合優勢，讓分散式系統更簡潔、更強健。