Importance of Service Broker in SQL Server
Sooner or later every business process needs some asynchronous processes to cater the business needs and keep their application free from the heavy processes which can be tedious for the user if it directly processed from the application. It may decrease the performance of the application.
There are many asynchronous processes (like MSMQ, Web services, WCF and many more) but Service Broker is one of my favorite if I have setup of SQL Server for the application.
Service broker manages the queue as a database object and hence we get native support of SQL Server so that if we take a backup of SQL Server database, queue will be backed up with it.
Let me give you some situation where I have recently set up Service Broker.
I was working with one home health care management system of New York. Many agencies working with that SAAS (Software As A Service) product and we used to find thousands of concurrent user actively using that application in business hours. Every Wednesday each agency used to generate Invoice and billing for the insurance company as well as for staff because Friday is a payment day so invoice, pay slip etc. should be ready and calculated on Wednesday.
We used to get heavy database & network pressure at that time, out application keeps hanging and it shows time out error to many of the agencies too which is very annoying. We used to help them by creating invoices/bills manually directly from the backend but it is not good to approach client like this so we have finally decided to process all these calculations in form of service broker, as soon as client generates the request, we call Service broker and as soon as Service Broker completes process, it used to send an email to client so that they can get their bills/invoices from the application without making a pressure in application and getting timeout error.
This is really a power of asynchronous message processing.
I would like to share one more business requirement which forced me to set up service broker recently.
I was in involved in one project of one of the environmental laboratory in New Jeary, USA where they used to perform test for Air, Soil and Water. They have many different types of test used to perform on different types of samples. They define a special way to perform the test based on government norms as well as client’s demand. That special way (chemical to process the test) needs a combination of many different chemicals and other required stuff. Once they define this way, they named it “Recipe”. One Recipe may have many different chemicals as well as other Recipe too. While generating the report of Recipe, we needs to go infinite inside the content of Recipe as it may be single chemical or other Recipe and that other Recipe could be created from other Recipe. This Recipe report and calculation take too much time and hence intranet based .NET web application used to give time out error so many times and user gets frustrate so in this situation we have decided to use Service Broker so that user can put request for Recipe report from .NET application, Service Broker completes the process and send an email to user about completion.
This is how Services broker can be used. I will come up with one more article based on this topic very soon with the introduction of important part of Service Broker… stay tuned!!!
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.