SQLServer實戰(zhàn)經(jīng)驗分享

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

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

接下來我們就如何使用ServiceBroker來做一個場景描述。我們需要設(shè)計一個Web系統(tǒng)，系統(tǒng)由一個主Web系統(tǒng)和多個子Web系統(tǒng)組成。有一種情況下，在主模塊中會產(chǎn)生一類行為數(shù)據(jù)，這類行為數(shù)據(jù)需要傳播或者記錄到各個子模塊的數(shù)據(jù)庫中處理。要如何實現(xiàn)這樣的功能呢，當(dāng)然我們可以選用多種現(xiàn)有技術(shù)來實現(xiàn)，如(WebService，鏈接服務(wù)器技術(shù)，WCF等技術(shù))。在這里為了說明ServiceBroker的簡單易用性，我們?yōu)榇俗鲆缓唵问纠?/p>

前提：各個應(yīng)用數(shù)據(jù)庫要允許ServiceBroker和設(shè)置數(shù)據(jù)庫主密鑰

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

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

1.實現(xiàn)ServiceBroker消息、隊列和服務(wù)

公共部分

定義消息類型架構(gòu)集合　

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

定義初始服務(wù)

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;

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

END CONVERSATION @dialog;
GO

目標(biāo)方

定義隊列處理存儲過程

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
);

實現(xiàn)隊列處理存儲過程

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

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

--根據(jù)接收的消息類型執(zhí)行不同的消息處理邏輯
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)');

--此處可以處理自定義業(yè)務(wù)邏輯

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


END

COMMIT TRANSACTION
END
GO

實現(xiàn)目標(biāo)處理服務(wù)

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

2.實現(xiàn)ServiceBroker安全配置

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

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

初始方

創(chuàng)建擁有服務(wù)的用戶

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

創(chuàng)建與該用戶關(guān)聯(lián)的私鑰證書

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

將公鑰證書備份到文件以供目標(biāo)方服務(wù)使用

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

創(chuàng)建調(diào)用目標(biāo)服務(wù)的用戶

CREATE USER msgOperationProcessServiceUser WITHOUT LOGIN;

導(dǎo)入目標(biāo)服務(wù)的證書并把剛才創(chuàng)建的用戶設(shè)為所有者

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

建立目標(biāo)服務(wù)遠程服務(wù)綁定

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

目標(biāo)方

創(chuàng)建擁有服務(wù)的用戶

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

創(chuàng)建與該用戶關(guān)聯(lián)的私鑰證書

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

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

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

創(chuàng)建調(diào)用初始服務(wù)的用戶

CREATE USER msgOperationInitServiceUser WITHOUT LOGIN;

導(dǎo)入初始服務(wù)的證書并把剛才創(chuàng)建的用戶設(shè)為所有者

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

授予用戶發(fā)送服務(wù)的權(quán)限

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

3.實現(xiàn)ServiceBroker通訊設(shè)置(不同服務(wù)器之間通訊)

要把ServiceBroker部署到不同服務(wù)器的數(shù)據(jù)庫實例，需要在Master數(shù)據(jù)庫和應(yīng)用數(shù)據(jù)庫中同時做相應(yīng)的設(shè)置。

Master數(shù)據(jù)庫同樣要允許ServiceBroker和設(shè)置數(shù)據(jù)主密鑰。

初始方Master數(shù)據(jù)庫

創(chuàng)建初始服務(wù)器通訊證書

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

將公鑰證書備份到文件以供目標(biāo)服務(wù)器使用

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

創(chuàng)建初始服務(wù)器通訊終結(jié)點，這里我們假設(shè)使用33333端口監(jiān)聽

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

創(chuàng)建目標(biāo)服務(wù)器的用戶和登錄

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

導(dǎo)入由目標(biāo)服務(wù)器導(dǎo)出的證書

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

為表示目標(biāo)服務(wù)器用戶的登錄授予CONNECT權(quán)限

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

初始方應(yīng)用數(shù)據(jù)庫

服務(wù)路由設(shè)置

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

目標(biāo)方Master數(shù)據(jù)庫

創(chuàng)建目標(biāo)服務(wù)器通訊證書

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

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

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

創(chuàng)建目標(biāo)服務(wù)器通訊終結(jié)點，這里我們假設(shè)使用33333端口監(jiān)聽

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

創(chuàng)建初始服務(wù)器的用戶和登錄

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

導(dǎo)入由初始服務(wù)器導(dǎo)出的證書

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

為表示初始服務(wù)器的登錄授予CONNECT權(quán)限

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

目標(biāo)方應(yīng)用數(shù)據(jù)庫

服務(wù)路由設(shè)置

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

最后總結(jié)下SQLSVR2005 ServiceBroker部署在生產(chǎn)環(huán)境中的一些心得

1.安全策略，防火墻策略一定要配置正確
2.從備份還原的數(shù)據(jù)庫可能要重新開啟ServiceBroker開關(guān)
3.需要數(shù)據(jù)庫帶外訪問的一定要設(shè)置數(shù)據(jù)庫帶外訪問權(quán)限
4.每個服務(wù)器的主密鑰都是不同的，部署的新服務(wù)器上的每個要使用ServiceBroker的數(shù)據(jù)庫一定要重新創(chuàng)建數(shù)據(jù)庫主密鑰

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