January 2014 - Nirav's DiaryNirav's Diary

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

Purge records of Sys.Transmission_Queue of Service Broker in SQL Server.

Today i got one issue on service broker for sys.transmission_queue.
Service broker configured well and working fine, suddenly service broker tranmission_queue goes high and high.
I have checked Transfer_Status to check the error if there is any, but i can’t see any error in Transfer_Status.

So, the question is what to do if SYS.Transmission_Queue stucks.
I had searched on web and read many bolg’s and forums but can’t find proper way to subsidize the transmission queue.

Finally, i have started to send the message manually from sys.transmission_queue and clean the conversation.

The steps that i followed is as below.

Step1 : Insert all Sys.Transmission_Queue data into temp table.

SELECT *
INTO MY_TRANSMISSION_QUEUE
FROM SYS.TRANSMISSION_QUEUE

Step2 : Now purge records of Sys.Transmission_Queue.

BEGIN
BEGIN TRY
DECLARE @Convhandle UNIQUEIDENTIFIER

DECLARE CONV CURSOR FOR

SELECT S2TRANSQUEUE.CONVERSATION_HANDLE
FROM SYS.CONVERSATION_ENDPOINTS AS S2CONVEND WITH(NOLOCK)
 INNER JOIN SYS.TRANSMISSION_QUEUE S2transqueue WITH(NOLOCK)
 ON S2TRANSQUEUE.CONVERSATION_HANDLE = S2CONVEND.CONVERSATION_HANDLE

OPEN CONV
FETCH NEXT FROM CONV INTO @Convhandle

WHILE @@FETCH_STATUS = 0
 BEGIN
 END CONVERSATION @Convhandle WITH CLEANUP
 FETCH NEXT FROM CONV INTO @Convhandle
 PRINT @Convhandle
 END

CLOSE CONV
DEALLOCATE CONV
END TRY

BEGIN CATCH
PRINT Error_message()
END CATCH

Step3 : Alter table and add one new column TransferStatus to check manually transfer status.


ALTER TABLE MY_TRANSMISSION_QUEUE
ADD TRANSFERSTATUS BIT

UPDATE MY_TRANSMISSION_QUEUE
SET TRANSFERSTATUS = 0

Step4 : Now send data manually from temp table and change the TransferStatus column.

DECLARE @Cnt INT
SELECT @Cnt = Count(*)
FROM My_transmission_queue(NOLOCK)
WHERE TRANSFERSTATUS = 0

WHILE( @Cnt > 0 )
 BEGIN
 DECLARE @Con_hand UNIQUEIDENTIFIER
 DECLARE @Xml XML
 DECLARE @Seq INT
 SELECT TOP 1 @Con_hand = CONVERSATION_HANDLE,
 @Xml = Cast(MESSAGE_BODY AS XML),
 @Seq = MESSAGE_SEQUENCE_NUMBER
 FROM My_transmission_queue(NOLOCK)
 WHERE TRANSFERSTATUS = 0
 ---Process Queue
 DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
 DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION

BEGIN DIALOG @InitDlgHandle
 FROM SERVICE [SB://Service/Audit/Initiator]
 TO SERVICE N'SB://Service/Audit/Target'
 ON CONTRACT [SB://Contract/Audit]
 WITH ENCRYPTION = ON;

 SEND ON CONVERSATION @InitDlgHandle
 MESSAGE TYPE [SB://Message/Audit/Request]
 (@Xml);

COMMIT TRANSACTION
 --End of Process Queue.

UPDATE MY_TRANSMISSION_QUEUE
 SET TRANSFERSTATUS = 1
 WHERE TRANSFERSTATUS = 0
 AND CONVERSATION_HANDLE = @Con_hand
 AND MESSAGE_SEQUENCE_NUMBER = @Seq
 SET @Cnt = @Cnt - 1
 END

Note: This script is useful if you have only one type of service. If you have more than one service in sys.transmission_queue you have to change the code as your requirement.

Thanks & Regards,
Nirav Gajjar

Nirav's Diary

All about Microsoft Technology

Monthly Archives: January 2014

Configure Service Broker in SQL Server 2012

Purge records of Sys.Transmission_Queue of Service Broker in SQL Server