Configure Service Broker in SQL Server 2012.
Initiator Script
Step1 : Create Database, Master Key and Initiator Certificate
USE MASTER
GO
CREATE DATABASE InitiatorDB
GO
ALTER AUTHORIZATION ON DATABASE::InitiatorDB TO SA
Go
ALTER DATABASE InitiatorDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
Go
ALTER DATABASE InitiatorDB SET TRUSTWORTHY ON
GO
--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@1234'
GO
--Create Certificate
CREATE CERTIFICATE InitiatorBrokerCertificate
WITH SUBJECT = 'broker.InitiatorCertificate',
START_DATE = '2012-12-01',
EXPIRY_DATE = '2099-12-31'
ACTIVE FOR BEGIN_DIALOG = ON;
GO
--Take Backup of Certificate
BACKUP CERTIFICATE InitiatorBrokerCertificate
TO FILE = 'D:\Certificates\InitiatorBrokerCertificate.cer';
GO
Step2 : Create Target Certificate, Login and Endpoint
--Create Target Certificate from backup
CREATE CERTIFICATE TargetBrokerCertificate
FROM FILE = 'D:\Certificates\TargetBrokerCertificate.cer';
GO
CREATE LOGIN [TargetBrokerLogin]
FROM CERTIFICATE TargetBrokerCertificate
GO
CREATE ENDPOINT ServiceBrokerEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE InitiatorBrokerCertificate,
ENCRYPTION = SUPPORTED);
GO
GRANT CONNECT ON ENDPOINT :: ServiceBrokerEndPoint TO [TargetBrokerLogin]
GO
Step3 : Create User, Master Key, Initiator and Target Certificate
USE [InitiatorDB]
GO
--Create Broker User to Access Target Service
CREATE USER [TargetBrokerUser] WITHOUT LOGIN
GO
--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@1234'
GO
--Create Certificate
CREATE Certificate InitiatorUserCertificate
WITH Subject = 'user.InitiatorCertificate',
START_DATE = '2012-12-01',
EXPIRY_DATE = '2099-12-31'
ACTIVE FOR BEGIN_DIALOG = ON
GO
BACKUP CERTIFICATE InitiatorUserCertificate
TO FILE = 'D:\Certificates\InitiatorUserCertificate.cer'
GO
--Create Target Certificate From Backup
CREATE Certificate TargetUserCertificate
AUTHORIZATION TargetBrokerUser
FROM FILE = 'D:\Certificates\TargetUserCertificate.cer';
GO
GRANT CONNECT TO [TargetBrokerUser]
Step4 : Create Message Type, Contract, Queue, Service, Route and Binding
CREATE MESSAGE TYPE [MSG://Sample/Request] VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [MSG://Sample/Response] VALIDATION = WELL_FORMED_XML
GO
CREATE CONTRACT [CON://Sample/Contract] ([MSG://Sample/Request] SENT BY INITIATOR,
[MSG://Sample/Response] SENT BY TARGET)
GO
CREATE QUEUE [dbo].[InitiatorQueue] WITH STATUS = ON , RETENTION = OFF ,
POISON_MESSAGE_HANDLING (STATUS = ON) ON [PRIMARY]
GO
CREATE SERVICE [Service://Sample/Initiator]
ON QUEUE [dbo].[InitiatorQueue] ([CON://Sample/Contract])
GO
GRANT SEND ON SERVICE::[Service://Sample/Initiator] To [TargetBrokerUser]
CREATE ROUTE [TargetRequestRoute]
WITH SERVICE_NAME = N'Service://Sample/Target' ,
BROKER_INSTANCE = N'106775EA-80AC-48ED-AEFB-92AE1F9A0736' , --Broker Instance of Target Databases
ADDRESS = N'TCP://User9.excellence.local:4022'
GO
CREATE REMOTE SERVICE BINDING [InitiatorRemoteServiceBinding]
TO SERVICE N'Service://Sample/Target'
WITH USER = [TargetBrokerUser] , ANONYMOUS = OFF
GO
Target Script
Step1 : Create Database, Master Key and Target Certificate
USE MASTER
GO
CREATE DATABASE TargetDB
GO
ALTER DATABASE TargetDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE TargetDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE TargetDB SET TRUSTWORTHY ON
GO
--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@1234'
GO
--Create Certificate
CREATE CERTIFICATE TargetBrokerCertificate
WITH SUBJECT = 'broker.TargetCertificate',
START_DATE = '2012-12-01',
EXPIRY_DATE = '2099-12-31'
ACTIVE FOR BEGIN_DIALOG = ON;
GO
--Take Backup of Certificate
BACKUP CERTIFICATE TargetBrokerCertificate
TO FILE = 'D:\Certificates\TargetBrokerCertificate.cer';
GO
Step2 : Create Initiator Certificate, Login and Endpoint
--Create Target Certificate from backup
CREATE CERTIFICATE InitiatorBrokerCertificate
FROM FILE = 'D:\Certificates\InitiatorBrokerCertificate.cer';
GO
CREATE LOGIN [InitiatorBrokerLogin]
FROM CERTIFICATE InitiatorBrokerCertificate
GO
CREATE ENDPOINT ServiceBrokerEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE TargetBrokerCertificate,
ENCRYPTION = SUPPORTED);
GO
GRANT CONNECT ON ENDPOINT :: ServiceBrokerEndPoint TO [InitiatorBrokerLogin]
GO
Step3 : Create User, Master Key, Initiator and Target Certificate
USE [TargetDB]
GO
--Create Broker User to Access Target Service
CREATE USER [InitiatorBrokerUser] WITHOUT LOGIN
GO
--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@1234'
GO
--Create Certificate
CREATE CERTIFICATE TargetUserCertificate
WITH SUBJECT = 'user.TargetCertificate',
START_DATE = '2012-12-01',
EXPIRY_DATE = '2099-12-31'
ACTIVE FOR BEGIN_DIALOG = ON
GO
Backup Certificate TargetUserCertificate
TO File = 'd:\certificates\TargetUserCertificate.cer'
GO
--Create Target Certificate From Backup
CREATE Certificate InitiatorUserCertificate
AUTHORIZATION InitiatorBrokerUser
FROM FILE = 'd:\certificates\InitiatorUserCertificate.cer';
GO
GRANT CONNECT TO [InitiatorBrokerUser]
Step4 : Create Message Type, Contract, Queue, Service, Route and Binding
CREATE MESSAGE TYPE [MSG://Sample/Request] VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [MSG://Sample/Response] VALIDATION = WELL_FORMED_XML
GO
CREATE CONTRACT [CON://Sample/Contract] ([MSG://Sample/Request] SENT BY INITIATOR,
[MSG://Sample/Response] SENT BY TARGET)
GO
CREATE QUEUE [dbo].[TargetQueue] WITH STATUS = ON , RETENTION = OFF ,
POISON_MESSAGE_HANDLING (STATUS = ON) ON [PRIMARY]
GO
CREATE SERVICE [Service://Sample/Target]
ON QUEUE [dbo].[TargetQueue] ([CON://Sample/Contract])
GO
GRANT SEND ON SERVICE::[Service://Sample/Target] To [InitiatorBrokerUser]
CREATE ROUTE [InitiatorResponseRoute]
WITH SERVICE_NAME = N'Service://Sample/Initiator' ,
BROKER_INSTANCE = N'533AE34F-FD12-4C27-9545-1EEAD080404C' , --Broker Instance of Initiator Databases
ADDRESS = N'TCP://User8.excellence.local:4022'
GO
CREATE REMOTE SERVICE BINDING [TargetRemoteServiceBinding]
TO SERVICE N'Service://Sample/Initiator'
WITH USER = [InitiatorBrokerUser] , ANONYMOUS = OFF
GO
GRANT RECEIVE ON [TargetQueue] TO [InitiatorBrokerUser]
Send Message From Initiator Instance
Use InitiatorDB
Go
--Send Message to TargetQueue.
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg XML;
BEGIN TRANSACTION;
BEGIN DIALOG @InitDlgHandle
FROM SERVICE [Service://Sample/Initiator]
TO SERVICE N'Service://Sample/Target'
ON CONTRACT [CON://Sample/Contract]
WITH
ENCRYPTION = OFF;
SELECT @RequestMsg = N'Message for initiator.';
SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE [MSG://Sample/Request]
(@RequestMsg);
SELECT @RequestMsg AS SentRequestMsg;
COMMIT TRANSACTION
Receive Message at Target Instance
Use TargetDB
Go
--Get Message From Initiator
SELECT CAST(MESSAGE_BODY AS XML),* FROM [TARGETQUEUE]
Regards,
Nirav Gajjar