SQLServer實戰經驗分享

在平常的項目設計中，我們經常會用到各種各樣的隊列來實現分布式系統的異步調用邏輯和數據消息的傳遞，從而來達到增強應用程序的性能和可伸縮性，通過事務性隊列的離線消息處理機制更可以大大提高系統的可靠性。SQLServer自2005以后版本便增加了一個新的內置

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

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

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

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

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

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

公共部分

定義消息類型架構集合　

CREATE XML SCHEMA COLLECTION
[http://Samples/SQL/ServiceBroker/msgOperationSchema]
AS N'









';

定義消息類型

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

定義消息契約　

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

初始方

定義隊列

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

定義初始服務

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

定義初始存儲過程

CREATE PROCEDURE dbo.usp_msgOperation_SET
@msgId int,
@msgContent nvarchar(2000)
AS
declare @message_body as xml([http://Samples/SQL/ServiceBroker/msgOperationSchema]);
declare @dialog as uniqueidentifier;

--填充消息體
SET @message_body ='
'+cast(@msgId as varchar)+'
'+@msgContent+'
';

BEGIN DIALOG @dialog
FROM SERVICE [http://Samples/SQL/ServiceBroker/msgOperationInitService]
TO SERVICE 'http://Samples/SQL/ServiceBroker/msgOperationProcessService'
ON CONTRACT [http://Samples/SQL/ServiceBroker/msgOperationContract];
--WITH ENCRYPTION = OFF , LIFETIME = 3600;

--發送消息
SEND ON CONVERSATION @dialog
MESSAGE TYPE [http://Samples/SQL/ServiceBroker/msgOperation] (@message_body);

END CONVERSATION @dialog;
GO

目標方

定義隊列處理存儲過程

CREATE PROCEDURE dbo.usp_msgOperation_CMD AS
RETURN 0
GO

定義隊列

CREATE QUEUE msgOperationProcessQueue
WITH
STATUS = ON,
RETENTION = OFF,
ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = dbo.usp_msgOperation_CMD,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF
);

實現隊列處理存儲過程

ALTER PROCEDURE dbo.usp_msgOperation_CMD
AS

declare @message_body as xml;
declare @message_type as sysname;
declare @dialog as 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

--如果沒收到任何消息則跳出循環
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK;
END

--根據接收的消息類型執行不同的消息處理邏輯
IF (@message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
END CONVERSATION @dialog;
END
ELSE IF(@message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
END CONVERSATION @dialog;
END
ELSE IF(@message_type = 'http://Samples/SQL/ServiceBroker/msgOperation')
BEGIN
declare @msgId int
declare @msgContent nvarchar(2000)

BEGIN TRY
SET @msgId = @message_body.value('data(//msgId)[1]', 'int');
SET @msgContent = @message_body.value('data(//msgContent)[1]', 'nvarchar(2000)');

--此處可以處理自定義業務邏輯

END CONVERSATION @dialog;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
CONTINUE
END CATCH


END

COMMIT TRANSACTION
END
GO

實現目標處理服務

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

2.實現ServiceBroker安全配置

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

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

初始方

創建擁有服務的用戶

CREATE USER msgOperationInitServiceUser WITHOUT LOGIN;
ALTER AUTHORIZATION ON
SERVICE::[http://Samples/SQL/ServiceBroker/msgOperationInitService]
TO
msgOperationInitServiceUser;

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

CREATE CERTIFICATE msgOperactionInitServiceCertPriv AUTHORIZATION msgOperationInitServiceUser
WITH SUBJECT = 'ForMsgOperactionInitService',
START_DATE = '01/01/2009',
EXPIRY_DATE = '01/01/2100';

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

BACKUP CERTIFICATE msgOperactionInitServiceCertPriv
TO FILE = 'X:\**\msgOperactionInitServiceCertPub.cer';

創建調用目標服務的用戶

CREATE USER msgOperationProcessServiceUser WITHOUT LOGIN;

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

CREATE CERTIFICATE msgOperactionProcessServiceCertPub AUTHORIZATION msgOperationProcessServiceUser
FROM FILE = 'X:\**\msgOperactionProcessServiceCertPub.cer';

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

CREATE REMOTE SERVICE BINDING ToMsgOperactionProcessService
TO SERVICE 'http://Samples/SQL/ServiceBroker/msgOperationProcessService'
WITH USER = msgOperationProcessServiceUser;

目標方

創建擁有服務的用戶

CREATE USER msgOperationProcessServiceUser WITHOUT LOGIN;
ALTER AUTHORIZATION ON SERVICE::[http://Samples/SQL/ServiceBroker/msgOperationProcessService] TO msgOperationProcessServiceUser;

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

CREATE CERTIFICATE msgOperactionProcessServiceCertPriv AUTHORIZATION msgOperationProcessServiceUser
WITH SUBJECT = 'ForMsgOperactionProcessService',
START_DATE = '01/01/2009',
EXPIRY_DATE = '01/01/2100';

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

BACKUP CERTIFICATE msgOperactionProcessServiceCertPriv
TO FILE = 'X:\**\msgOperactionProcessServiceCertPub.cer';

創建調用初始服務的用戶

CREATE USER msgOperationInitServiceUser WITHOUT LOGIN;

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

CREATE CERTIFICATE msgOperactionInitServiceCertPub AUTHORIZATION msgOperationInitServiceUser
FROM FILE = 'X:\**\msgOperactionInitServiceCertPub.cer';

授予用戶發送服務的權限

GRANT SEND ON SERVICE::[http://Samples/SQL/ServiceBroker/msgOperationInitService] TO msgOperationInitServiceUser;

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

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

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

初始方Master數據庫

創建初始服務器通訊證書

CREATE CERTIFICATE [Server1_CertPriv]
WITH SUBJECT = 'ForServer1Auth',
START_DATE = '01/01/2009',
EXPIRY_DATE = '01/01/2100'
ACTIVE FOR BEGIN_DIALOG = ON;

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

BACKUP CERTIFICATE [Server1_CertPriv]
TO FILE = 'X:\**\Server1_CertPub.cer';
GO

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

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

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

CREATE LOGIN [Server2_UserProxy] WITH PASSWORD = '123456';
CREATE USER [Server2_UserProxy];

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

CREATE CERTIFICATE [Server2_CertPub] AUTHORIZATION [Server2_UserProxy]
FROM FILE = 'X:\**\Server2_CertPub.cer';

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

GRANT CONNECT ON ENDPOINT::[Server1_Endpoint] TO [Server2_UserProxy];

初始方應用數據庫

服務路由設置

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

目標方Master數據庫

創建目標服務器通訊證書

CREATE CERTIFICATE [Server2_CertPriv]
WITH SUBJECT = 'ForServer2Auth',
START_DATE = '01/01/2009',
EXPIRY_DATE = '01/01/2100'
ACTIVE FOR BEGIN_DIALOG = ON;

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

BACKUP CERTIFICATE [Server2_CertPriv]
TO FILE = 'X:\**\Server2_CertPub.cer';
GO

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

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

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

CREATE LOGIN [Server1_UserProxy] WITH PASSWORD = '123456';
CREATE USER [Server1_UserProxy];

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

CREATE CERTIFICATE [Server1_CertPub] AUTHORIZATION [Server1_UserProxy]
FROM FILE = 'X:\**\Server1_CertPub.cer';

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

GRANT CONNECT ON ENDPOINT::[Server2_Endpoint] TO [Server1_UserProxy];

目標方應用數據庫

服務路由設置

CREATE ROUTE msgOperationInitServiceRoute WITH
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的數據庫一定要重新創建數據庫主密鑰

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com