Saturday, 11 June 2016

Steps to Create and Execute a Service Broker in SQL SERVER


In this post we are going to see how to create a service broker in SQL SERVER, and there usages in DB

     Service broker is used to execute a instance in async process , between the two different servers , two different database and in a same database using the communication channel, TCP/IP .

      Components uses are Service, Message Type, Contract , Queue. Each sender and receiver can create the all the components

      To Create Service Broker , Let we take an example that i am inserting a record from the front end which pass the data to a stored procedure and i dont need to wait for insertion because this table consists huge million records so now i can use Fire and forget method here otherwise UI get non - responsiveness


CREATE DATABASE SBDB
GO

ALTER DATABASE SBDB SET ENABLE_BROKER
GO
ALTER DATABASE SBDB SET TRUSTWORTHY ON
GO



CREATE TABLE EMPLOYEE ( ID INT IDENTITY(1,1),NAME VARCHAR(40),AGE INT,SALARY INT)



/* Drop script */
IF EXISTS(SELECT '1' FROM sys.services WHERE name = N'//SBMessage/TargetService')
BEGIN
      DROP SERVICE [//SBMessage/TargetService]
END


IF EXISTS(SELECT '1' FROM sys.services WHERE name = '//SBMessage/InitService')
BEGIN
      DROP SERVICE [//SBMessage/InitService]   
END

IF EXISTS(SELECT '1' FROM sys.service_queues WHERE name=N'receiveQueue')
BEGIN
      DROP QUEUE receiveQueue
END

IF EXISTS(SELECT '1' FROM sys.service_contracts WHERE name = N'//SBMessage/Contract')
BEGIN
      DROP CONTRACT [//SBMessage/Contract]
END

IF EXISTS(SELECT '1' FROM sys.service_message_types WHERE name = N'//SBMessage/Reply')
BEGIN
      DROP MESSAGE TYPE [//SBMessage/Reply];
END

IF EXISTS(SELECT '1' FROM sys.service_queues WHERE name=N'senderQueue')
BEGIN
      DROP QUEUE senderQueue
END

IF EXISTS(SELECT '1' FROM sys.service_message_types WHERE name = N'//SBMessage/Request')
BEGIN
      DROP MESSAGE TYPE [//SBMessage/Request];
END



/* Sender script */
/* Message Type */
CREATE MESSAGE TYPE [//SBMessage/Request] AUTHORIZATION [dbo] VALIDATION = WELL_FORMED_XML
Go

/* Create a Queue for sender */
CREATE QUEUE senderQueue
GO

/* Receiver Script */
/* Message Type */
CREATE MESSAGE TYPE [//SBMessage/Reply] AUTHORIZATION [dbo] VALIDATION = WELL_FORMED_XML
GO


/* Create a Contract */
CREATE CONTRACT [//SBMessage/Contract]
(
[//SBMessage/Request] SENT BY INITIATOR,
[//SBMessage/Reply] SENT BY TARGET
);
GO





/* Create a Queue for Reciever */
CREATE QUEUE receiveQueue
WITH STATUS = ON, RETENTION = OFF, ACTIVATION
(
PROCEDURE_NAME = dbo.insertemployee,
MAX_QUEUE_READERS = 10,
STATUS = ON,
EXECUTE AS SELF
)
GO

/* Create a Service script for sender */
CREATE SERVICE [//SBMessage/InitService] ON QUEUE senderQueue([//SBMessage/Contract]);
Go


/* Create a Service Script for Reciever */
CREATE SERVICE [//SBMessage/TargetService] ON QUEUE receiveQueue([//SBMessage/Contract]);
GO



IF EXISTS(SELECT '1' FROM sys.objects WHERE name = N'SendEmployeeRecord')
BEGIN
      DROP PROCEDURE SendEmployeeRecord;
END
Go




CREATE PROCEDURE SendEmployeeRecord(@name VARCHAR(30),@age INT,@salary INT)
AS
BEGIN

DECLARE @empxml XML
DECLARE @handle UNIQUEIDENTIFIER;

SELECT @empxml = '<employee><name>'+@name+'</name><age>'+CAST(@age AS VARCHAR(3))+'</age><salary>'+CAST(@salary AS VARCHAR(100))+'</salary></employee>'

-- Begin the handle
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE [//SBMessage/InitService]
TO SERVICE N'//SBMessage/TargetService'
ON CONTRACT [//SBMessage/Contract]
WITH ENCRYPTION = OFF ;

--Begin the conversation
SEND ON CONVERSATION @handle MESSAGE TYPE [//SBMessage/Request] (@empxml);

END

IF EXISTS(SELECT '1' FROM sys.objects WHERE name = N'insertemployee')
BEGIN
  DROP PROCEDURE insertemployee
END
Go


ALTER PROC dbo.insertemployee
AS
BEGIN
           
      DECLARE @contentxml XML
      DECLARE @conversationhandle UNIQUEIDENTIFIER
     
      WAITFOR (RECEIVE TOP(1) @contentxml = message_body,
                                                @conversationhandle = conversation_handle
                                           FROM receivequeue),TIMEOUT 5000
      IF @contentxml IS NOT NULL
      BEGIN
            INSERT INTO employee(name,age,salary)
            SELECT
               @contentxml.value('(employee/name)[1]','varchar(40)'),
               @contentxml.value('(employee/age)[1]','int'),
               @contentxml.value('(employee/salary)[1]','int')
        
      END              
     
      END CONVERSATION @conversationhandle
      WITH CLEANUP
     
END




---- Testing the Record
EXEC SendEmployeeRecord ‘jgh’,23,4500000
SELECT * FROM employee



From this post you can learn how to create a service broker in Sql Server.



No comments:

Post a Comment