SQL Server 2008 Service Broker 基礎應用實戰 (上)：異步訊息隊列完整建構指南

Service Broker 為 SQL Server 內建異步訊息隊列引擎，提供保證順序、不重複、可恢復、可備份的交易性訊息傳遞。SQL 2008 新增 CREATE BROKER PRIORITY 支援會話優先級。本文以「書店訂單 → 配送中心」跨資料庫異步處理為例，完整演示從啟用到對話結束的標準化建構流程。

架構設計：雙資料庫異步協作

• BookStore (啟動端)：接收訂單、發送訂單訊息、接收確認
• BookDistribution (目標端)：接收訂單、處理入庫出貨、回覆確認
• 同一實例內通訊，無需憑證/端點/路由配置

標準建構 10 步驟

1. 啟用資料庫 Service Broker

ALTER DATABASE BookStore SET ENABLE_BROKER;
ALTER DATABASE BookStore SET TRUSTWORTHY ON;
ALTER DATABASE BookDistribution SET ENABLE_BROKER;
ALTER DATABASE BookDistribution SET TRUSTWORTHY ON;

2. 建立資料庫主金鑰 (加密基礎)

USE BookStore; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'I5Q7w1d3';
USE BookDistribution; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D1J3q5z8X6y4';

3. 定義訊息類型 (雙向同步定義)

-- 兩個資料庫完全相同定義
CREATE MESSAGE TYPE [//SackConsulting/SendBookOrder] VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//SackConsulting/BookOrderReceived] VALIDATION = WELL_FORMED_XML;

4. 建立合約：規範雙向訊息流向

CREATE CONTRACT [//SackConsulting/BookOrderContract]
(
    [//SackConsulting/SendBookOrder] SENT BY INITIATOR,
    [//SackConsulting/BookOrderReceived] SENT BY TARGET
);

5. 建立佇列：訊息持久化容器

USE BookStore; CREATE QUEUE BookStoreQueue WITH STATUS=ON;
USE BookDistribution; CREATE QUEUE BookDistributionQueue WITH STATUS=ON;

6. 建立服務：綁定佇列與合約

USE BookStore;
CREATE SERVICE [//SackConsulting/BookOrderService]
ON QUEUE dbo.BookStoreQueue
([//SackConsulting/BookOrderContract]);

USE BookDistribution;
CREATE SERVICE [//SackConsulting/BookDistributionService]
ON QUEUE dbo.BookDistributionQueue
([//SackConsulting/BookOrderContract]);

7. 啟動對話並發送訊息 (啟動端)

USE BookStore;
DECLARE @Conv_Handler UNIQUEIDENTIFIER, @OrderMsg XML;
BEGIN DIALOG CONVERSATION @Conv_Handler
FROM SERVICE [//SackConsulting/BookOrderService]
TO SERVICE '//SackConsulting/BookDistributionService'
ON CONTRACT [//SackConsulting/BookOrderContract];

SET @OrderMsg = '
  
';

SEND ON CONVERSATION @Conv_Handler
MESSAGE TYPE [//SackConsulting/SendBookOrder] (@OrderMsg);

8. 查詢目標端佇列收到的訊息

USE BookDistribution;
SELECT message_type_name, CAST(message_body AS XML) AS message,
       queuing_order, conversation_handle, conversation_group_id
FROM dbo.BookDistributionQueue;

9. 接收處理並回覆 (目標端)

USE BookDistribution;
CREATE TABLE dbo.BookOrderReceived (...);
DECLARE @Conv_Handler UNIQUEIDENTIFIER, @Conv_Group UNIQUEIDENTIFIER,
        @OrderMsg XML, @TextResponseMsg VARCHAR(8000), @ResponseMsg XML, @OrderID INT;

RECEIVE TOP(1) 
    @OrderMsg = message_body,
    @Conv_Handler = conversation_handle,
    @Conv_Group = conversation_group_id
FROM dbo.BookDistributionQueue;

INSERT dbo.BookOrderReceived (...) VALUES (@Conv_Handler, @Conv_Group, @OrderMsg);

-- XQuery 擷取訂單 ID 並構建回覆
SELECT @OrderID = @OrderMsg.value('(/order/@id)[1]', 'int');
SET @TextResponseMsg = '';
SET @ResponseMsg = CAST(@TextResponseMsg AS XML);

SEND ON CONVERSATION @Conv_Handler
MESSAGE TYPE [//SackConsulting/BookOrderReceived] (@ResponseMsg);

10. 雙向結束對話 (必要步驟)

-- 啟動端接收確認並結束
USE BookStore;
RECEIVE TOP(1) @Conv_Handler = conversation_handle, @OrderMsg = message_body
FROM dbo.BookStoreQueue;
INSERT dbo.BookOrderConfirmation (...) VALUES (@Conv_Handler, @OrderMsg);
END CONVERSATION @Conv_Handler;

-- 目標端接收 EndDialog 訊息並結束
USE BookDistribution;
RECEIVE TOP(1) @Conv_Handler = conversation_handle, @message_type_name = message_type_name
FROM dbo.BookDistributionQueue;
IF @message_type_name = 'https://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    END CONVERSATION @Conv_Handler;

驗證會話狀態

SELECT state_desc, conversation_handle FROM sys.conversation_endpoints;

核心優勢總結

• 異步解耦：訂單系統不阻塞等待配送處理
• 交易保障：訊息隨資料庫備份/恢復，不丟失不重複
• 順序保證：同一會話訊息嚴格 FIFO
• 優先級控制 (2008 新增)：關鍵業務會話優先處理
• 可擴展性：訊息類型/合約/服務/佇列靈活組合應對複雜場景

善用 Service Broker 異步特性，可有效消除業務系統因同步等待產生的效能瓶頸，構建彈性、可擴展的企業級應用架構。