Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Introduction
A service broker is a high availibity technique in SQL Server which was introduced from SQL Server 2005.
This is primarily to send the big files into smaller segments and assigning them a queue.
Normally it’s a good way of dealing with disaster recovery when their is point in time failure.
A service broker is most often used when the limitation is associated where we use a connection which is inferior from the rest of the world
The place where service broker is configured sends the data in queues, which can be treated as part of backup file and then is transmitted over a dedicated/leased
line to the server, and then then restored on the main database, which is fully functional.
Benefits
The benefit of using service broker is that it allow multiple queues from different locations to insert into a Single Server(which is
In case any of XML file from one of the server location gets corrupted which is part of queue, then it gets send to the last where the DBA can manually check it and try to get it sorted out,
or permanently remove it from the queue or get a fresh copy of the XML file.
Configuration
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | CREATE DATABASE ServiceBrokerTest GO USE ServiceBrokerTest GO -- Enable Service Broker ALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER GO -- Create Message Type CREATE MESSAGE TYPE SBMessage VALIDATION = NONE GO -- Create Contract CREATE CONTRACT SBContract (SBMessage SENT BY INITIATOR) GO -- Create Send Queue CREATE QUEUE SBSendQueue GO -- Create Receive Queue CREATE QUEUE SBReceiveQueue GO -- Create Send Service on Send Queue CREATE SERVICE SBSendService ON QUEUE SBSendQueue (SBContract) GO -- Create Receive Service on Recieve Queue CREATE SERVICE SBReceiveService ON QUEUE SBReceiveQueue (SBContract) GO -- Begin Dialog using service on contract DECLARE @SBDialog uniqueidentifier DECLARE @Message NVARCHAR(128) BEGIN DIALOG CONVERSATION @SBDialog FROM SERVICE SBSendService TO SERVICE 'SBReceiveService' ON CONTRACT SBContract WITH ENCRYPTION = OFF -- Send messages on Dialog SET @Message = N'Very First Message'; SEND ON CONVERSATION @SBDialog MESSAGE TYPE SBMessage (@Message) SET @Message = N'Second Message'; SEND ON CONVERSATION @SBDialog MESSAGE TYPE SBMessage (@Message) SET @Message = N'Third Message'; SEND ON CONVERSATION @SBDialog MESSAGE TYPE SBMessage (@Message) GO -- View messages from Receive Queue SELECT CONVERT(NVARCHAR(MAX), message_body) AS Message FROM SBReceiveQueue GO -- Receive messages from Receive Queue RECEIVE TOP(1) CONVERT(NVARCHAR(MAX), message_body) AS Message FROM SBReceiveQueue GO -- Receive messages from Receive Queue RECEIVE CONVERT(NVARCHAR(MAX), message_body) AS Message FROM SBReceiveQueue GO -- Clean Up USE master GO DROP DATABASE ServiceBrokerTest GO |
