Service Broker 应用示例

Service Broker的一些理论请查看上一篇随笔,更多多的信息请查看MSDN.

示例,不多说其他的,直接上代码:

--创建master库中的密钥,后面的证书将会以此密钥加密,所以需要保证source instance及target instance的master key是一致的.
if not exists(select * from sys.symmetric_keys as sk where name = '##MS_DatabaseMasterKey##')
    create master key encryption by password = 'Password#123'
go

在将target instance的账号target_user及证书还原后,需要设置权限,使这些账户可以访问我们的Endpoint,当然source instance的账号source:

grant connect on endpoint::service_broker_endpoint to target_user;
go

以下是完整的示例:

/*
master数据库的maser key的密码需要加强.请重新设置.
initiator_user的登陆密码也需要加强,请重新设置.
endpoint的端口需要进行重新设置,并且需要防火墙允许访问.
*/

/*
 * Description:  instance1: source instance 数据主要从该实例中发出
 * Created:      Grant.wu
 * CreateDate:     
 * History:
 * =============================================================================
 * Author      DateTime        Alter Description
 * =============================================================================
 */
use master
go
/*
1. create user, login, certificate and endpoint
2. export the certificate
*/

if exists(select * from sys.endpoints as e where e.name = 'service_broker_endpoint')
    drop endpoint service_broker_endpoint
go

if exists(select * from sys.certificates where name = 'initiator_transport_cert')
    drop certificate initiator_transport_cert
go

if exists(select * from sys.sysusers as s where s.name = 'initiator_user')
    drop user initiator_user;
go

if exists(select * from sys.syslogins as s where name = 'initiator_user')
    drop login initiator_user
go

if not exists(select * from sys.symmetric_keys as sk where name = '##MS_DatabaseMasterKey##')
    create master key encryption by password = 'Password#123'
go

create login initiator_user with password = 'Password#123';
go

create user initiator_user;
go

create certificate initiator_transport_cert
authorization initiator_user
with subject = 'initiator transport certificate',start_date = '2011-01-01',expiry_date='2100-01-01';
go

backup certificate initiator_transport_cert
to file = 'd:\data\initiator_transport.cert'
go

--DROP ENDPOINT service_broker_endpoint
create endpoint service_broker_endpoint
state = started
as tcp(listener_port = 5501)
for service_broker(authentication = certificate initiator_transport_cert )
GO

/*
route的端口设置需要与target端的端口设置一致
trustworthy选项确保SSBInitiator数据库可以操作其他数据库
请更改route中的主机名！！
*/
use master
go
------------------------------------------------------------------------------------------------------
/*
1. import certificate form target service
2. grant permission to user
*/

if exists(select * from sys.certificates as c where name = 'target_transport_cert')
    drop certificate target_transport_cert
go
if exists(select * from sys.syslogins where name = 'target_user')
    drop login target_user
go

if exists(select * from sys.sysusers as s where name = 'target_user')
    drop user target_user;
go


create login target_user with password = 'Password#123';
go

create user target_user for login target_user;
go

create certificate target_transport_cert
authorization target_user
from file = 'D:\Data\target_transport.cert'
go

grant connect on endpoint::service_broker_endpoint to target_user;
go

if not exists(select * from sys.databases as d where name = 'SSBInitiator')
    create database SSBInitiator;
go
--create database Initiator_Database;
--go
if not exists(select * from sys.databases as d where d.is_broker_enabled = 1 and d.database_id = db_id('SSBInitiator'))
    alter database SSBInitiator set enable_broker with rollback immediate;
go
alter database SSBInitiator set trustworthy on
go
use SSBInitiator
go
/*
create queue, service and route
*/

if exists(select * from sys.services as s where name = 'initiator_service')
    drop service initiator_service;
go

if exists(select * from sys.service_queues as sq where name = 'initiator_queue')
    drop queue initiator_queue
go

if exists(select * from sys.service_contracts as sc where name = 'wwwDBContract')
    drop contract wwwDBContract
go

if exists(select * from sys.service_message_types as smt where name ='ReceiveMessage')
    drop message type ReceiveMessage;
go

if exists(select * from sys.service_message_types as smt where name ='ReplyMessage')
    drop message type ReplyMessage;
go

if not exists(select * from sys.service_message_types as smt where name ='EndOfStream')
    create message type EndOfStream;
go

if not exists(select * from sys.service_message_types as smt where name ='FraudEndOfStream')
    create message type FraudEndOfStream;
go

IF EXISTS(SELECT 1 FROM sys.routes AS r WHERE r.name='target_route')
    DROP ROUTE target_route;
go

create message type ReceiveMessage validation = none;
create message type ReplyMessage validation = none;
go
create contract wwwDBContract
(ReceiveMessage sent by initiator,EndOfStream sent by initiator, ReplyMessage sent by target);
go

create queue initiator_queue with retention = off;
go

create service initiator_service
on queue initiator_queue(wwwDBContract);
go

grant send on service::initiator_service to public;
go

--NOTE:please change hostname to your webdb's name !!!!
create route target_route
with service_name = 'target_service',
address = 'tcp://targetserver:5502';
go

use msdb
go
IF EXISTS(SELECT 1 FROM sys.routes AS r WHERE r.name='initiator_route')
    DROP ROUTE initiator_route;
go

create route initiator_route
with service_name = 'initiator_route',
address = 'local';
go

/*
master数据库的maser key的密码需要加强.请重新设置.
target_user的登陆密码也需要加强,请重新设置.
endpoint的端口需要进行重新设置,并且需要防火墙允许访问.
*/

/*
 * Description:  instance2: target instance 目标实例,负责接收数据的实例,此示例中为只读实例
 * Created:      Grant.wu
 * CreateDate:     
 * History:
 * =============================================================================
 * Author      DateTime        Alter Description
 * =============================================================================
 */
use master
go
/*
1. create target user, certificate and endpoint
2. export the certificate
*/
if not exists(select * from sys.symmetric_keys as sk where name = '##MS_DatabaseMasterKey##')
    create master key encryption by password = 'Password#123';
go

if exists(select * from sys.endpoints as e where e.name = N'service_broker_endpoint')
    drop endpoint service_broker_endpoint
go

if exists(select * from sys.certificates as c where name = 'target_transport_cert')
    drop certificate target_transport_cert;
go

if exists(select * from sys.sysusers as s where name = 'target_user')
    drop user target_user;
go

if exists(select * from sys.syslogins where name = 'target_user')
    drop login target_user
go

create login target_user with password = 'Password#123';
go

create user target_user;
go

create certificate target_transport_cert
authorization target_user
with subject = 'target transport certificate',start_date = '2011-01-01',expiry_date='2100-01-01';
go

backup certificate target_transport_cert
to file = 'd:\data\target_transport.cert'
go

--DROP ENDPOINT service_broker_endpoint
create endpoint service_broker_endpoint
state = started
as tcp(listener_port = 5502)
for service_broker(authentication = certificate target_transport_cert)
go

/*
route的端口设置需要与initiator端的端口设置一致
trustworthy选项确保SSBTarget数据库可以操作其他数据库
请更改route中的主机名
*/
use master
go
---------------------------------------------------------------------------------------------------------

/*
import certificate from initiator service
grant the permission
*/
if exists(select * from sys.syslogins as s where name = 'initiator_user')
    drop login initiator_user
go

if exists(select * from sys.certificates as c where name = 'initiator_transport_cert')
    drop certificate initiator_transport_cert
go

if exists(select * from sys.sysusers as s where s.name = 'initiator_user')
    drop user initiator_user;
go

create login initiator_user with password = 'Password#123';
go

create user initiator_user for login initiator_user;
go

create certificate initiator_transport_cert
authorization initiator_user
from file = 'd:\data\initiator_transport.cert';
go

grant connect on endpoint::service_broker_endpoint to initiator_user;
go

if not exists(select * from sys.databases as d where d.name = 'SSBTarget')
    create database SSBTarget;
go

if not exists(select * from sys.databases as d where d.database_id = db_id('SSBTarget') and d.is_broker_enabled = 1)
    alter database SSBTarget set enable_broker with rollback immediate;
go
alter database SSBTarget set trustworthy on
go

use SSBTarget
go

if exists(select * from sys.services as s where name = 'target_service')
    drop service target_service;
go

if exists(select * from sys.service_queues as sq where name = 'target_queue')
    drop queue target_queue
go

if exists(select * from sys.service_contracts as sc where name = 'wwwDBContract')
    drop contract wwwDBContract
go

if exists(select * from sys.service_message_types as smt where name ='ReceiveMessage')
    drop message type ReceiveMessage;
go

if exists(select * from sys.service_message_types as smt where name ='ReplyMessage')
    drop message type ReplyMessage;
go

if exists(select * from sys.service_message_types as smt where name ='EndOfStream')
    drop message type EndOfStream;
go
if exists(select * from sys.service_message_types as smt where name ='FraudEndOfStream')
    drop message type FraudEndOfStream;
go
if exists(select 1 from sys.routes as r where name = 'initiator_route')
    drop route initiator_route
go
/*
create queue, service and routes
*/
create message type ReceiveMessage validation = none;
create message type ReplyMessage validation = none;
create message type EndOfStream;
create message type FraudEndOfStream;
go
create contract wwwDBContract
(ReceiveMessage sent by initiator,EndOfStream sent by initiator, ReplyMessage sent by target);
go

create queue target_queue with retention=off;
go

create service target_service
on queue target_queue(wwwDBContract);
go

grant send on service::target_service to public;
go

--please change the hostname to your maindb's hostname
create route initiator_route
with service_name = 'initiator_service',address = 'tcp://source server:5501';
go
use msdb
go
if exists(select 1 from sys.routes as r where name = 'target_route')
    drop route target_route;
go

create route target_route
with service_name = 'target_service',address = 'local';
go