SQL Server 2005 - Service Broker

Service Broker is a new feature of SQL Server 2005. Even though I have been playing on and off with SQL 2005 since it was “Yukon Beta 1”, I had not heard much about this feature before going to the Melbourne SQL Server SIG session last night. I was not sure if it would have many applications in a Data Warehousing application, which is the area I normally work in. But due to the fact that it allows you to asynchronously kick off a long running process, using standard T-SQL code I can see a number of uses. We have had to do numerous hacks in the past to provide users with a way of firing off a long running process and this looks like a much better solution. Some of the methods we have

Of course there are many other uses for this product, so if you have not looked into it yet, do yourself a favour and check it out. There is an on demand web cast available that should give you a reasonable overview.

The Microsoft® SQL Server™ 2005 Service Broker allows developers to build reliable, scalable, asynchronous, queued database applications. When combined with the CLR and XML features, this makes SQL Server 2005 a great platform for building the next generation of business applications. The major features of the Service Broker include: queues as database objects; data integrity for queued messages; activation of processing logic triggered by message arrival; and reliable, transactional delivery of messages between SQL Server instances.

One other interesting thing that came up at last nights meeting was the use of semi-colons in TSQL. In the past TSQL has not required a semi-colon as an end of statement delimiter and even in SQL 2005 that is still the case. However, semi-colons are allowed in TSQL and are part of the ANSI-99 standard. It was recommended as a best practise, that you start using semi-colon in all your TSQL code from now on. As SQL Server moves closer to standards compliance it is likely that at some point in the future the requirement to have semi-colons will become the default (although I imagine that there will be wizards to upgrade legacy code or optional switches to revert to the old behaviour, it is always nicer to be prepared ahead of time)

Update: SQL Server Central has posted a good overview of the SQL Service Broker here

Print | posted on Wednesday, June 22, 2005 5:21 PM

Comments on this post

# re: SQL Server 2005 - Service Broker

Requesting Gravatar...
Thanks for the link to SQL Server Central. Article is excellent to give an introduction.
Left by Agson Chellkudam on Sep 11, 2006 5:16 PM

# re: SQL Server 2005 - Service Broker

Requesting Gravatar...
very nice artical about Sql server 2005 - Service Broker
Left by sudhakar on Jan 08, 2008 6:26 PM

# re: SQL Server 2005 - Service Broker

Requesting Gravatar...
GOOD ARTICLE ...'SERVICE BROKER ' THANKS
Left by JAGDISH on Feb 21, 2008 8:34 AM

# re: SQL Server 2005 - Service Broker

Requesting Gravatar...
Nice explaination


Thanks,
http://paresh-sqldba.blogspot.com
Left by Paresh Prajapati on Dec 23, 2009 10:04 PM

# re: SQL Server 2005 - Service Broker

Requesting Gravatar...
GOOD ARTICLE
Left by Bishnu on Feb 12, 2011 9:55 PM

Your comment:

 (will show your gravatar)