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