SQL Server Service Broker "At a Glance"

SQL Server Service Broker at a Glance

"Introduction

"SQL Server Service Broker provides the SQL Server Database Engine native support for messaging and queuing applications. This makes it easier for developers to create sophisticated applications that use the Database Engine components to communicate between disparate databases. Developers can use Service Broker to easily build distributed and reliable applications." This is according to the Technet reference found here.

SQL Server Service broker was introduced in SQL Server 2005. After more than 7 years of its existence SQL Server Service broker remains a gray area to most SQL Server developers. Below are cases where service broker can be use.

Table of Contents

• Introduction
• Real Life Scenario.
• Service Broker to the rescue.
• Why not SSIS?
• Why not Replication then?
• Service Broker Step By Step
  • 1. Know Where Service Broker is in SQL Server Management Studio?
  • 2. How to Enable Service broker in the Database?
  • 3. How to create a message type?
  • 4. Create a Contract
  • 5. Create Sender and Receiver Queue
  • 6. Create Sender and Receiver Services
  • 7. Send a message to the Queue.
  • 7. View the message without removing it from the Queue

Real Life Scenario.

You are an IT professional in a department store industry named “My-mall”. “My-mall” have 100 point of sale(POS) terminals sitting on top of a SQL Server “sales” database. Sales database is owned by “sales Department”. Sales department critical task is accommodating sales from customer either by payment through credit card, cash, debit card or gift certificates . “My-mall” also have an “inventory” database owned by the “inventory department”. Inventory department critical task is replenishing stocks in the store in a timely manner as well as maintaining the correct inventory of the product. My-mall also has an accounting database owned by the accounting department.

It is possible to build an a single database solution to accommodate all the three department needs like this in a small scale enterprise but my-mall is a huge enterprise accommodating more than 100000 customers per day. There is also a strong division between the departments although they share the same data in a way.

Service Broker to the rescue.

Service broker is ideal for this scenario because it will allow the sales department to process sales without any delay. Amidst processing sales, the sales database can send a message to the inventory database and the accounting database that a sales transaction is being made. The inventory database can also accommodate inventory operation and can send a message back to the sales department if a new product becomes available in the store.

...

..."

I thought this TechNet Wiki article a nice, quick and easy overview (funny that given its title) of SQL Server Service Broker. We're going to start using Service Broker in production this year, so I wanted to capture this as I know we're going to get questions like, "What is this Service Broker thing and what does it do..." and this way I can do that thing I know everyone hates, "Did you real my blog post, XX months ago..." lol :P