SQLServer實戰經驗分享--ServiceBroker安全配置和使用示例

　　在平常的項目設計中，我們經常會用到各種各樣的隊列來實現分布式系統的異步調用邏輯和數據消息的傳遞，從而來達到增強應用程序的性能和可伸縮性，通過事務性隊列的離線消息處理機制更可以大大提高系統的可靠性。SQLServer自2005以後版本便增加了一個新的內置隊列處理應用模塊ServiceBroker，此功能模塊大大簡化了隊列的使用操作，更方便的是能和原先的SQLServer系統在事務處理上完美的結合在一起。可是唯一的缺陷是增加了設計的耦合性。

　　接下來我們就如何使用ServiceBroker來做一個場景描述。我們需要設計一個Web系統，系統由一個主Web系統和多個子Web系統組成。有一種情況下，在主模塊中會產生一類行為數據，這類行為數據需要傳播或者記錄到各個子模塊的數據庫中處理。要如何實現這樣的功能呢，當然我們可以選用多種現有技術來實現，如(WebService，鏈接服務器技術，WCF等技術)。在這裡為了說明ServiceBroker的簡單易用性，我們為此做一簡單示例。

　　前提：各個應用數據庫要允許ServiceBroker和設置數據庫主密鑰

　　代碼部署劃分：1公共部分(初始方和目標方共同使用)，2.初始方，3.目標方

　　示例具體實現步驟主要分為(具體參數詳細配置請參考MSDN文檔)：

　　1.實現ServiceBroker消息、隊列和服務

　　公共部分

　　定義消息類型架構集合　

CREATEXMLSCHEMacOLLECTION
[http://Samples/SQL/ServiceBroker/msgOperationSchema]
ASN'<?XMLversion="1.0"?>
<xs:schemaxmlns:xs="http://www.w3.org/2001/XMLSchema">
　<xs:elementname="msgOperation">
　　<xs:complexType>
　　　<xs:sequence>
　　　　<xs:elementname="msgId"type="xs:int"/>
　　　　<xs:elementname="msgContent"type="xs:string"/>
　　　</xs:sequence>
　　</xs:complexType>
　</xs:element>
</xs:schema>';

　　定義消息類型

CREATEMESSAGETYPE[http://Samples/SQL/ServiceBroker/msgOperation]
VALIDATION=VALID_XMLWITHSCHEMacOLLECTION
[http://Samples/SQL/ServiceBroker/msgOperationSchema];

　　定義消息契約　

CREATECONTRACT[http://Samples/SQL/ServiceBroker/msgOperationContract]
(
　　[http://Samples/SQL/ServiceBroker/msgOperation]
　　SENTBYINITIATOR
);

　　初始方

　　定義隊列

CREATEQUEUEmsgOperationInitQueue
WITH
STATUS=ON,
RETENTION=OFF
GO

---

　　定義初始服務

CREATESERVICE[http://Samples/SQL/ServiceBroker/msgOperationInitService]
ONQUEUEmsgOperationInitQueue
([http://Samples/SQL/ServiceBroker/msgOperationContract]);
GO

　　定義初始存儲過程

CREATEPROCEDUREdbo.usp_msgOperation_SET
　　@msgIdint,
　　@msgContentnvarchar(2000)
AS
　　declare@message_bodyasXML([http://Samples/SQL/ServiceBroker/msgOperationSchema]);
　　declare@dialogasuniqueidentifIEr;
　　
　　--填充消息體
　　SET@message_body='<msgOperation>
　　<msgId>'+cast(@msgIdasvarchar)+'</msgId>
　　<msgContent>'+@msgContent+'</msgContent>
　　</msgOperation>';
　　
　　BEGINDIALOG@dialog
　　　　FROMSERVICE[http://Samples/SQL/ServiceBroker/msgOperationInitService]
　　　　TOSERVICE'http://Samples/SQL/ServiceBroker/msgOperationProcessService'
　　　　ONCONTRACT[http://Samples/SQL/ServiceBroker/msgOperationContract];
　　　　--WITHENCRYPTION=OFF,LIFETIME=3600;
　　--發送消息
　　SENDONCONVERSATION@dialog
　　　　MESSAGETYPE[http://Samples/SQL/ServiceBroker/msgOperation](@message_body);
　　ENDCONVERSATION@dialog;
GO

　　目標方

　　定義隊列處理存儲過程

CREATEPROCEDUREdbo.usp_msgOperation_CMDAS
　　RETURN0
GO

　　定義隊列

CREATEQUEUEmsgOperationProcessQueue
WITH
STATUS=ON,
RETENTION=OFF,
ACTIVATION
(
　　STATUS=ON,
　　PROCEDURE_NAME=dbo.usp_msgOperation_CMD,
　　MAX_QUEUE_READERS=1,
　　EXECUTEASSELF
);

---

　　實現隊列處理存儲過程

ALTERPROCEDUREdbo.usp_msgOperation_CMD
AS
declare@message_bodyasXML;
declare@message_typeassysname;
declare@dialogasuniqueidentifIEr;
WHILE(1=1)
　　BEGIN
　　　　BEGINTRANSACTION
　　　　--接收下一條可用的消息
　　　　WAITFOR(
　　　　　　RECEIVETOP(1)--一次只處理一條消息
　　　　　　@message_type=message_type_name,
　　　　　　@message_body=message_body,
　　　　　　@dialog=[conversation_handle]
　　　　　　FROMdbo.msgOperationProcessQueue
　　　　),TIMEOUT2000
　　　　--如果沒收到任何消息則跳出循環
　　　　IF(@@ROWCOUNT=0)
　　　　BEGIN
　　　　　　ROLLBACKTRANSACTION
　　　　　　BREAK;
　　　　END　　
　　　　--根據接收的消息類型執行不同的消息處理邏輯
　　　　IF(@message_type='http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
　　　　BEGIN
　　　　　　ENDCONVERSATION@dialog;
　　　　END
　　　　ELSEIF(@message_type='http://schemas.microsoft.com/SQL/ServiceBroker/Error')
　　　　BEGIN
　　　　　　ENDCONVERSATION@dialog;
　　　　END
　　　　ELSEIF(@message_type='http://Samples/SQL/ServiceBroker/msgOperation')
　　　　BEGIN
　　　　　　declare@msgIdint
　　　　　　declare@msgContentnvarchar(2000)
　　　　　　BEGINTRY
　　　　　　　　SET@msgId=@message_body.value('data(//msgId)[1]','int');
　　　　　　　　SET@msgContent=@message_body.value('data(//msgContent)[1]','nvarchar(2000)');
　　　　　　　　--此處可以處理自定義業務邏輯
　　　　　　　　
　　　　　　　　ENDCONVERSATION@dialog;
　　　　　　ENDTRY
　　　　　　BEGINCATCH
　　　　　　　　ROLLBACKTRANSACTION
　　　　　　　　CONTINUE
　　　　　　ENDCATCH
　　　　　　
　　　　END
　　　　COMMITTRANSACTION
　　END
GO

---

　　實現目標處理服務

CREATESERVICE[http://Samples/SQL/ServiceBroker/msgOperationProcessService]
ONQUEUEdbo.msgOperationProcessQueue
([http://Samples/SQL/ServiceBroker/msgOperationContract])
GO

　　2.實現ServiceBroker安全配置

　　在一台數據庫服務器上的不同數據庫之間的安全配置比較簡單，默認情況下數據庫之間是沒有外部訪問權限的，要實現你就需要在本地服務器上開啟模擬上下文的數據庫模塊，即在數據庫中設置 ALTER DATABASE database_name SET TRUSTWORTHY ON 來實現互相訪問的目的。

　　這裡我們需要實現一種更靈活，更安全的配置方式，那就是基於證書的安全配置。

　　初始方

　　創建擁有服務的用戶

CREATEUSERmsgOperationInitServiceUserWITHOUTLOGIN;
ALTERAUTHORIZATIONON
SERVICE::[http://Samples/SQL/ServiceBroker/msgOperationInitService]
TO
msgOperationInitServiceUser;

　　創建與該用戶關聯的私鑰證書

CREATECERTIFICATEmsgOperactionInitServiceCertPrivAUTHORIZATIONmsgOperationInitServiceUser
WITHSUBJECT='ForMsgOperactionInitService',
START_DATE='01/01/2009',
EXPIRY_DATE='01/01/2100';

　　將公鑰證書備份到文件以供目標方服務使用

BACKUPCERTIFICATEmsgOperactionInitServiceCertPriv
　　TOFILE='X:**msgOperactionInitServiceCertPub.cer';

　　創建調用目標服務的用戶

CREATEUSERmsgOperationProcessServiceUserWITHOUTLOGIN;

　　導入目標服務的證書並把剛才創建的用戶設為所有者

---

CREATECERTIFICATEmsgOperactionProcessServiceCertPubAUTHORIZATIONmsgOperationProcessServiceUser
FROMFILE='X:**msgOperactionProcessServiceCertPub.cer';

　　建立目標服務遠程服務綁定

CREATEREMOTESERVICEBINDINGToMsgOperactionProcessService
TOSERVICE'http://Samples/SQL/ServiceBroker/msgOperationProcessService'
WITHUSER=msgOperationProcessServiceUser;

　　目標方

　　創建擁有服務的用戶

CREATEUSERmsgOperationProcessServiceUserWITHOUTLOGIN;
ALTERAUTHORIZATIONONSERVICE::[http://Samples/SQL/ServiceBroker/msgOperationProcessService]TOmsgOperationProcessServiceUser;

　　創建與該用戶關聯的私鑰證書

CREATECERTIFICATEmsgOperactionProcessServiceCertPrivAUTHORIZATIONmsgOperationProcessServiceUser
WITHSUBJECT='ForMsgOperactionProcessService',
START_DATE='01/01/2009',
EXPIRY_DATE='01/01/2100';

　　將公鑰證書備份到文件以供初始方服務使用

BACKUPCERTIFICATEmsgOperactionProcessServiceCertPriv
　　TOFILE='X:**msgOperactionProcessServiceCertPub.cer';

　　創建調用初始服務的用戶

CREATEUSERmsgOperationInitServiceUserWITHOUTLOGIN;

　　導入初始服務的證書並把剛才創建的用戶設為所有者

CREATECERTIFICATEmsgOperactionInitServiceCertPubAUTHORIZATIONmsgOperationInitServiceUser
FROMFILE='X:**msgOperactionInitServiceCertPub.cer';

---

　　授予用戶發送服務的權限

GRANTSENDONSERVICE::[http://Samples/SQL/ServiceBroker/msgOperationInitService]TOmsgOperationInitServiceUser;

　　3.實現ServiceBroker通訊設置(不同服務器之間通訊)

　　要把ServiceBroker部署到不同服務器的數據庫實例，需要在Master數據庫和應用數據庫中同時做相應的設置。

　　Master數據庫同樣要允許ServiceBroker和設置數據主密鑰。

　　初始方Master數據庫

　　創建初始服務器通訊證書

CREATECERTIFICATE[Server1_CertPriv]
　WITHSUBJECT='ForServer1Auth',
　START_DATE='01/01/2009',
　EXPIRY_DATE='01/01/2100'
ACTIVEFORBEGIN_DIALOG=ON;

　　將公鑰證書備份到文件以供目標服務器使用

BACKUPCERTIFICATE[Server1_CertPriv]
　TOFILE='X:**Server1_CertPub.cer';
GO

　　創建初始服務器通訊終結點，這裡我們假設使用33333端口監聽

CREATEENDPOINT[Server1_Endpoint]
STATE=STARTED
ASTCP(LISTENER_PORT= 33333)
FORSERVICE_BROKER
(
　　AUTHENTICATION=CERTIFICATE[Server1_CertPriv]　
);

　　創建目標服務器的用戶和登錄

CREATELOGIN[Server2_UserProxy]WITHPASSWord='123456';
CREATEUSER[Server2_UserProxy];

　　導入由目標服務器導出的證書

CREATECERTIFICATE[Server2_CertPub]AUTHORIZATION[Server2_UserProxy]
FROMFILE='X:**Server2_CertPub.cer';

　　為表示目標服務器用戶的登錄授予CONNECT權限

GRANTCONNECTONENDPOINT::[Server1_Endpoint]TO[Server2_UserProxy];

---

　　初始方應用數據庫

　　服務路由設置

CREATEROUTEmsgOperationProcessServiceRouteWITH
SERVICE_NAME='http://Samples/SQL/ServiceBroker/msgOperationProcessService',
--BROKER_INSTANCE='CFDF4485-FAEF-47F9-B1F6-40DFD65685B7',
ADDRESS='TCP://[IP]:33333';
GO

　　目標方Master數據庫

　　創建目標服務器通訊證書

CREATECERTIFICATE[Server2_CertPriv]
　WITHSUBJECT='ForServer2Auth',
　START_DATE='01/01/2009',
　EXPIRY_DATE='01/01/2100'
ACTIVEFORBEGIN_DIALOG=ON;

　　將公鑰證書備份到文件以供初始服務器使用

BACKUPCERTIFICATE[Server2_CertPriv]
　TOFILE='X:**Server2_CertPub.cer';
GO

　　創建目標服務器通訊終結點，這裡我們假設使用33333端口監聽

CREATEENDPOINT[Server2_Endpoint]
STATE=STARTED
ASTCP(LISTENER_PORT=33333)
FORSERVICE_BROKER
(
　　AUTHENTICATION=CERTIFICATE[Server2_CertPriv]　
);

　　創建初始服務器的用戶和登錄

CREATELOGIN[Server1_UserProxy]WITHPASSWord='123456';
CREATEUSER[Server1_UserProxy];

　　導入由初始服務器導出的證書

CREATECERTIFICATE[Server1_CertPub]AUTHORIZATION[Server1_UserProxy]
FROMFILE='X:**Server1_CertPub.cer';

　　為表示初始服務器的登錄授予CONNECT權限

GRANTCONNECTONENDPOINT::[Server2_Endpoint]TO[Server1_UserProxy];

　　目標方應用數據庫

　　服務路由設置

CREATEROUTEmsgOperationInitServiceRouteWITH
SERVICE_NAME='http://Samples/SQL/ServiceBroker/msgOperationInitService',
--BROKER_INSTANCE='52CAD803-6951-4FD3-A16A-6995C50024B1',
ADDRESS='TCP://[IP]:33333';
GO

　　最後總結下SQLSVR2005 ServiceBroker部署在生產環境中的一些心得

　　1.安全策略，防火牆策略一定要配置正確

　　2.從備份還原的數據庫可能要重新開啟ServiceBroker開關

　　3.需要數據庫帶外訪問的一定要設置數據庫帶外訪問權限

　　4.每個服務器的主密鑰都是不同的，部署的新服務器上的每個要使用ServiceBroker的數據庫一定要重新創建數據庫主密鑰