Configure Service Broker in SQL Server 2012

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