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的数据库一定要重新创建数据库主密钥