SQL Server Service Broker Interview Preparation Guide

Catalog views serve as a repository to store static metadara like server side and database specific objects. This includes logins, tables, stored procedures etc. It is always better to use catalogue views to view system data rather than using system tables.

Using Dialog Security the dialogues can be encrypted at the initiator and then decrypted at the target. Dialogue security can be implemented by implemented as full dialogue security or anonymous dialog security. In full dialogue, both the database that initiates the service and database that calls the service to be implemented needs to have two users. Each service in the conversation is bound by a remotes service. A certificate is associated with each user, so that messages can be encrypted using the recipient's public key and decrypted using the corresponding private key. If anonymous dialog security is used, target service need not explicitly permit access to a remote user. A guest account can be used to generate a session key and encrypt the message.

Using Transact SQL statements service broker can be used. An application can be implemented as a program running outside of SQL Server or as a stored procedure. Service broker uses a number of components to perform a task. A program that starts a conversation creates and sends a message to a service that may or may not respond. Any application that intends to use Service broker does not need any special object model or library. Transact-SQL commands to SQL Server can be sent for processing.

Any application using service broker communicates by exchanging conversations that is nothing but asynchronous and reliable messages. Conversation group is a group of conversations used to accomplish the same task. These groups help in concurrency. They can also be used to manage the states.

The components in SQL server Service broker are represented by Server objects used in the messaging. Queue is an object that holds the messages for processing. Dialogues exchanged between two endpoints are grouped by conversation groups. There are some components used at design time called as service definition components used to specify the basic design of the application. Routing and security components are used to define the communications.

SQL service broker provides a mechanism to queue provide reliable messaging for SQL server. Using SQL server, the service broker assists in passing messages between applications. It also rejects messages with invalid format and performs retries. Service broker helps to make scalable and secure database applications.

In Asynchronous communication, messages can be queued until the service which was down is up and running. Asynchronous communication is loosely coupled. This means that there can be another component waiting to acknowledge the message. In this communication, the sender need not wait for a response but can continue to work.

A queue holds the incoming message before they are processed for a service.
One queue per service is created.

Below are the Service Broker infrastructure components:

Endpoint
Message Type
Contract
Route
Queue
Service
Remote Binding Service