We have compiled most frequently asked SQL Server Interview Questions which will help you with different expertise levels.
SQL Server Interview Questions on Service Broker
Question 1.
Why do we need Queues?
Answer:
There are instances when we expect that the other application with which we are interacting is not available. For example, when you chat on messaging systems like yahoo, MSN, ICQ, etc, you do not expect that the other users will be guaranteed online. So there is where we need queues. So during chatting, if the user is not online all the messages are sent to a queue. Later when the user comes online he can read all messages from the queue.
Question 2.
What is “Asynchronous” communication?
Answer:
Once a client has sent messages to the other end application, he can continue with some other task without waiting for any notifications from the end client. For instance, take an example of any online email system. Once you have sent a mail to the end-user, you do not have to wait for notification from the ends user. The user just sends the message to the queue which is later picked up by the mailing system and sent to the desired end-user.
Note MSMQ does the messaging and queuing, but now the queuing functionality is leveraged in
SQL Server 2005, due to its practical needs.
Question 3.
What is SQL Server Service broker?
Answer:
SQL Server Service broker provides asynchronous queuing functionality to SQL Server. So now the end client will not have to wait. He can just say add these 1000 records and then come back after one hour or so to see has the work been done or not.
Question 4.
What are the essential components of SQL Server Service broker?
Answer:
Following are the essential components of SQL Server:-
1. End-Points
The endpoints can be two applications running on different servers or instances, or they can be two applications running on the same server.
2. Message
A message is an entity that is exchanged between Server Brokers. A message must have a name and data type. Optionally, a message can have validation on that type of data. A message is part of a conversation and it has a unique identifier as well as a unique sequence number to enforce message ordering.
3. Dialog
Dialog ensures messages to be read in the same order as they were put into queue between endpoints. In short, it ensures a proper ordered sequence of events at both ends for a message.
4. Conversation Group
Conversation Group is a logical grouping of Dialog. To complete a task you can need one or more dialog. For instance, an online payment gateway can have two Dialog’s first is the “Address Check” and second is the “Credit Card Number” validation, these both dialog form your complete “Payment process”. So you can group both the dialogs in one Conversation Group.
5. Message Transport
Message transport defines how the messages will be sent across networks. Message transport is based on TCP/IP and FTP. There are two basic protocols “Binary Adjacent Broker Protocol” which is like TCP/IP and “Dialog Protocol” which like FTP. ‘
Question 5.
What is the main purpose of having a Conversation Group?
Answer:
There two main purposes of having a conversation group:-
1. You can lock a conversation group during reading so that no other process can read ^
those queue entries.
2. The most difficult thing in an asynchronous message system is to maintain states. ‘There is a huge delay between arrivals of two messages. So conversation groups maintain state using state table. It uses instance ID to identify messages in a group.
Question 6.
How to implement Service Broker?
Answer:
Below are the steps for practical implementation:-
1. Create a Messagetype that describes how the message is formed. If the message type is XML you can also associate a schema with it.
2. Further you have to assign this Messagetype to Contract. The message type is grouped in Contracts. The contract is an entity that describes messages for a particular Dialog.
So a contract can have multiple message types.
3. Contracts are further grouped in service. Service has all the dialogs needed to complete one process.
4. Service can further be attached to multiple queues. Service is the basic object from the SQL Server Service broker’s point of view.
5. So when any client wants to communicate with a queue he opens a dialog with the service.
The above figure shows how SQL Server Service broker works. A client who wants to use the queues does not have to understand the complexity of queues. They only communicate with the logical view of SQL Server Service broker objects (Messages, Contracts, and Services). In turn, these objects interact with the queues below and shield the client from any physical complexities of queues.
Below is a simple practical implementation of how this works. Try running the below statements from a T-SQL and see the output.
— Create a Message type and do not do any data type validation for this
CREATE MESSAGE TYPE MessageType
VALIDATION = NONE
GO
-- Create Message contract what type of users can send these messages
at this moment we are defining current as an initiator
CREATE CONTRACT MessageContract
(MessageType SENT BY INITIATOR)
GO
-- Declare the two end points that's sender and receive queues
CREATE QUEUE SenderQ
CREATE QUEUE ReceiverQ
GO
-- Create service and bind them to the queues
CREATE SERVICE Sender
ON QUEUE SenderQ
CREATE SERVICE Receiver
ON QUEUE ReceiverQ (MessageContract)
GO
-- Send message to the queue
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message NVARCHAR(IOO)
BEGIN
BEGIN TRANSACTION;
BEGIN DIALOG @conversationHandle
FROM SERVICE Sender
TO SERVICE ’Receiver'
ON CONTRACT MessageContract
-- Sending message
SET @message = N1 SQL Server Interview Questions by Shivprasad Koirala ’;
SEND ON CONVERSATION QconversationHandle
MESSAGE TYPE MessageType (@message)
COMMIT TRANSACTION
END
GO
-- Receive a message from the queue
RECEIVE CONVERT (NVARCHAR (max), message_body) AS message
FROM ReceiverQ
-- Just dropping all the object so that this sample can run successfully
DROP SERVICE Sender
DROP SERVICE Receiver
DROP QUEUE SenderQ
DROP QUEUE ReceiverQ
DROP CONTRACT MessageContract
DROP MESSAGE TYPE MessageType
GO
After executing the above T-SQL command you can see the output below.
Note In case your SQL Server service broker is not active you will get the following error as shown below.
In order to remove that error you have to enable the service broker by using
Alter Database [DatabaseName] set Enable_broker
At this moment I have created all these samples in the sample database “AdventureWorks”.
Question 7.
How do we encrypt data between Dialogs?
Answer:
If you create a dialog using the “WITH ENCRYPTION” clause a session key is created that’s used to encrypt the messages sent between dialog,