In This post Let we see some important question and answers in Sql Server, which is a continuation of previous post.
35. What is Service Broker ?
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
No comments:
Post a Comment