Service Broker between different instances by certificate.
Initiator Instance:
use master
go
/*
1. create user, login, 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.syslogins as s where name = 'initiator_user')
drop login initiator_user
go
create login initiator_user with password = 'Password#123';
go
if exists(select * from sys.sysusers as s where s.name = 'initiator_user')
drop user initiator_user;
go
create user initiator_user;
go
if exists(select * from sys.certificates where name = 'initiator_transport_cert')
drop certificate initiator_transport_cert
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 = 'f:\initiator_transport.cert'
go
if exists(select * from sys.endpoints as e where e.name = 'service_broker_endpoint')
drop endpoint service_broker_endpoint
go
create endpoint service_broker_endpoint
state = started
as tcp(listener_port = 4033)
for service_broker(authentication = certificate initiator_transport_cert )
go
------------------------------------------------------------------------------------------------------
/*
1. import certificate form target service
2. grant permission to user
*/
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
if exists(select * from sys.certificates as c where name = 'target_transport_cert')
drop certificate target_transport_cert
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 = 'f:\target_transport.cert'
go
grant connect on endpoint::service_broker_endpoint to target_user;
go
if exists(select * from sys.databases as d where name = 'Initiator_Database')
drop database Initiator_Database;
go
create database Initiator_Database;
go
use Initiator_Database
go
/*
create queue, service and route
*/
create message type ReceiveMessage validation = well_formed_xml;
create message type ReplyMessage validation = well_formed_xml;
go
create contract SampleContract
(ReceiveMessage sent by initiator,ReplyMessage sent by target);
go
create queue initiator_queue;
go
create service initiator_service
on queue initiator_queue(SampleContract);
go
grant send on service::initiator_service to public;
go
create route target_route
with service_name = 'target_service',
address = 'tcp://dw-new:4033';
go
use msdb
go
create route initiator_route
with service_name = 'initiator_route',
address = 'local';
go
-----------------------------------------------------------------------------------------
use Initiator_Database
go
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);
--BEGIN TRANSACTION;
begin try
begin dialog conversation @InitDlgHandle
from service initiator_service
to service 'target_service'
on contract SampleContract
with encryption = off;
select @RequestMsg = '<RequestMsg>queue test</RequestMsg>';
send on conversation @InitDlgHandle message type ReceiveMessage(@RequestMsg)
end try
begin catch
select error_message();
--end conversation @InitDlgHandle
end catch
--commit
go
select * from sys.transmission_queue as tq where tq.to_service_name = 'target_service'
select * from sys.routes as r
select d.service_broker_guid from sys.databases as d where d.database_id = db_id()
-----------------------------------------------------------------------------------------
DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;
--BEGIN TRANSACTION;
WAITFOR
( RECEIVE TOP(1) @RecvReplyDlgHandle = conversation_handle,
@RecvReplyMsg = message_body
from initiator_queue
),timeout 1000;
end conversation @RecvReplyDlgHandle;
select @RecvReplyMsg as ReceivedReplyMessage
--commit transaction
go
Target Instance:
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.syslogins where name = 'target_user')
drop login target_user
go
create login target_user with password = 'Password#123';
go
if exists(select * from sys.sysusers as s where name = 'target_user')
drop user target_user;
go
create user target_user;
go
if exists(select * from sys.certificates as c where name = 'target_transport_cert')
drop certificate target_transport_cert;
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:\cer\target_transport.cert'
go
if exists(select * from sys.endpoints as e where e.name = N'service_broker_endpoint')
drop endpoint service_broker_endpoint
go
create endpoint service_broker_endpoint
state = started
as tcp(listener_port = 4033)
for service_broker(authentication = certificate target_transport_cert)
go
select * from sys.endpoints as e
select * from sys.routes as r
---------------------------------------------------------------------------------------------------------
/*
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:\cer\initiator_transport.cert';
go
grant connect on endpoint::service_broker_endpoint to initiator_user;
go
if exists(select * from sys.databases as d where d.name = 'Target_Database')
drop database Target_Database;
go
create database Target_Database
go
use Target_Database
go
/*
create queue, service and routes
*/
create message type ReceiveMessage validation = well_formed_xml;
create message type ReplyMessage validation = well_formed_xml;
go
create contract SampleContract
(ReceiveMessage sent by initiator,ReplyMessage sent by target);
go
create queue target_queue;
go
create service target_service
on queue target_queue(SampleContract);
go
grant send on service::target_service to public;
go
--use msdb
--go
create route initiator_route
with service_name = 'initiator_service',address = 'tcp://db1:4033';
go
use msdb
go
create route target_route
with service_name = 'target_service',address = 'local';
go
use Target_Database
go
use Target_Database
go
----------------------------------------------------------------
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;
declare @replmsg varchar(200)
--BEGIN TRANSACTION;
waitfor (receive top(1) @RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
from target_queue
),timeout 1000;
select @RecvReqMsg as ReceivedRequestMessage;
if @RecvReqMsg = '<RequestMsg>queue test</RequestMsg>'
begin
set @replmsg = '<ReplyMsg>reply message</ReplyMsg>';
send on conversation @RecvReqDlgHandle(@replmsg);
end conversation @RecvReqDlgHandle;
end
--commit transaction
select * from sys.transmission_queue as tq
select * from sys.dm_broker_queue_monitors as dbqm
