|
本文和大家分享一下SQL2008基于SSB消息的示例，本地与远程的配置，已应用实践环境。
------------------------------------------------------------------------------------
---------------------- 基本证书的Service Broker 跨服务器发送消息----------------------
------------------------------------------------------------------------------------
-- 本地服务器配置第一步：创建交换证书和SSB端点
-- 请先在C盘根目录新建ssb文件夹，并设定Everyone用户完全控制权限
USE master;
GO
-- A master key is required to use certificates.
BEGIN TRANSACTION;
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='xueitKeythy@eendpo'
COMMIT;
GO
-- Create a certificate to authenticate the endpoint.
IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'LocalServiceCert')
DROP CERTIFICATE LocalServiceCert;
GO
CREATE CERTIFICATE LocalServiceCert
WITH SUBJECT = 'Service broker transport authentication for LocalServiceCert';
GO
-- Backup to a file to allow the certificate to be given to the initiator.
BACKUP CERTIFICATE LocalServiceCert
TO FILE = 'c:\ssb\LocalServiceCert.cert';
GO
-- Create the broker endpoint using the certificate for authentication.
IF EXISTS (SELECT * FROM sys.endpoints WHERE name = 'LocalServiceEndpoint')
DROP ENDPOINT LocalServiceEndpoint;
GO
CREATE ENDPOINT LocalServiceEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE LocalServiceCert);
GO
-- 远程服务器配置第一步：创建交换证书和SSB端点
-- 请先在C盘根目录新建ssb文件夹，并设定Everyone用户完全控制权限
USE master;
GO
-- A master key is required to use certificates.
BEGIN TRANSACTION;
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='xueitKeythy@eendpo'
COMMIT;
GO
-- Create a certificate to authenticate the endpoint.
IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'RemotServiceCert')
DROP CERTIFICATE RemotServiceCert;
GO
CREATE CERTIFICATE RemotServiceCert
WITH SUBJECT = 'Service broker transport authentication for RemotServiceCert';
GO
-- Backup to a file to allow the certificate to be given to the initiator.
BACKUP CERTIFICATE RemotServiceCert
TO FILE = 'c:\ssb\RemotServiceCert.cert';
GO
-- Create the broker endpoint using the certificate for authentication.
IF EXISTS (SELECT * FROM sys.endpoints WHERE name = 'RemotServiceEndpoint')
DROP ENDPOINT RemotServiceEndpoint;
GO
CREATE ENDPOINT RemotServiceEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE RemotServiceCert);
GO
----------EXCHANGE CERTIFICATES BEFORE PROCEEDING---------------
-- The initiator and target certificates must be exchanged in order for them to
-- authenticate each other. In a production system, this "out of band" exchange
-- should be done with a high level of trust, since a certificate bearer will be
-- able to begin dialogs and send messages to service broker services in the
-- authenticating server. However, assuming the sample is being used on a development
-- system, the exchange may be simple remote copies.
-- 本地服务器配置第二步：创建用户并授权连接
-- 将远程ssb文件夹下的证书文件复制到本地ssb文件夹
USE master;
GO
IF EXISTS (SELECT * FROM sys.syslogins WHERE name = 'SSBRemotService')
DROP LOGIN SSBRemotService;
GO
CREATE LOGIN SSBRemotService WITH PASSWORD = 'xueitKeythy@eendpo';
GO
IF EXISTS (SELECT * FROM sys.sysusers WHERE name = 'SSBRemotService')
DROP USER SSBRemotService;
GO
CREATE USER SSBRemotService;
GO
IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'RemotServiceCert')
DROP CERTIFICATE RemotServiceCert;
GO
CREATE CERTIFICATE RemotServiceCert
AUTHORIZATION SSBRemotService
FROM FILE = 'c:\ssb\RemotServiceCert.cert';
GO
GRANT CONNECT ON ENDPOINT::LocalServiceEndpoint TO SSBRemotService;
GO
-- 远程服务器配置第二步：创建用户并授权连接
-- 将本地ssb文件夹下的证书文件复制到远程ssb文件夹
USE master;
GO
IF EXISTS (SELECT * FROM sys.syslogins WHERE name = 'SSBLocalService')
DROP LOGIN SSBLocalService;
GO
CREATE LOGIN SSBLocalService WITH PASSWORD = 'xueitKeythy@eendpo';
GO
IF EXISTS (SELECT * FROM sys.sysusers WHERE name = 'SSBLocalService')
DROP USER SSBLocalService;
GO
CREATE USER SSBLocalService;
GO
IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'LocalServiceCert')
DROP CERTIFICATE LocalServiceCert;
GO
CREATE CERTIFICATE LocalServiceCert
AUTHORIZATION SSBLocalService
FROM FILE = 'c:\ssb\LocalServiceCert.cert';
GO
GRANT CONNECT ON ENDPOINT::RemotServiceEndpoint TO SSBLocalService;
GO
-- The target creates an initiator user certified by the initiator certificate
-- and grants it connection access to the service broker endpoint.
-- Modify the location of the certificate in script to suit configuration.
-- 本地服务器配置第三步：创建队列，服务，对话和路由给远程服务。
USE TestDB;
GO
-- 启用Service Broker
ALTER DATABASE TestDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE TestDB set ENABLE_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE TestDB SET TRUSTWORTHY ON
GO
-- 创建消息接收处理过程
CREATE PROCEDURE PRO_ReceiveMessage
AS
BEGIN
SET NOCOUNT ON;
DECLARE @message_body varbinary(MAX)
DECLARE @message_type int
DECLARE @dialog uniqueidentifier
while (1 = 1)
begin
begin transaction
-- Wait for the message.
WAITFOR (
RECEIVE top(1) -- just handle one message at a time
@message_type=message_type_id, --the type of message received
@message_body=message_body, -- the message contents
@dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM LocalServiceQueue
), TIMEOUT 1000 -- if the queue is empty for one second, give UPDATE and go away
-- If we didn't get anything, bail out
if (@@ROWCOUNT = 0)
BEGIN
Rollback Transaction
BREAK
END
-- Check for the End Dialog message.
If (@message_type <> 2) -- End dialog message
BEGIN
--PRINT '接收的消息为: ' + @message_body
--SELECT [dbo].[SendRtxNotify] ('Title', cast(@message_body as varchar), '周宏艳', 0)
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = Object_id(N'[dbo].[SSBTest]') AND TYPE IN ( N'U' ))
DROP TABLE SSBTest
SELECT @dialog dialog,@message_body message_body,@message_type message_type INTO SSBTest
END
-- Commit the transaction. At any point before this, we could roll
-- back - the received message would be back on the queue and the response
-- wouldn't be sent.
commit transaction
end
END
GO
-- Create a message queue.
CREATE QUEUE LocalServiceQueue WITH STATUS = ON , RETENTION = OFF ,ACTIVATION (
STATUS = ON
,PROCEDURE_NAME = dbo.PRO_ReceiveMessage
,MAX_QUEUE_READERS = 10
,EXECUTE AS SELF
);
GO
-- Create a service with a default contract.
CREATE SERVICE LocalServiceService ON QUEUE LocalServiceQueue ([DEFAULT]);
GO
-- Any user can send on the service.
GRANT SEND ON SERVICE::LocalServiceService TO PUBLIC;
GO
-- Create a route to the initiator service.
CREATE ROUTE RemotServiceRoute
WITH SERVICE_NAME = 'RemotServiceService',
ADDRESS = 'tcp://211.154.133.19:4022';
GO
-- In msdb, create an incoming route to the target service.
USE msdb;
GO
CREATE ROUTE LocalServiceRoute
WITH SERVICE_NAME = 'LocalServiceService',
ADDRESS = 'local';
GO
|
