SQL Server's Forgotten Technology

Microsoft has released so many different technologies that it is hard to keep up with them all, but living day to day in Visual Studio developing applications it is smart to know what is available to make the right decisions. Recently my company had a need for integrating data to and from Microsoft's Dynamics and our LOB systems through a batch process(so they thought). Many solutions were thrown on the table... Custom SQL workflow schemas using triggers and SP's. Writing code to manage a staging table between the two and SSIS. After thinking about the possibilities, I mentioned to our DBA, "What about Notification Services?"... What was I thinking? I could not even get the name right...I mean, "Service Broker". You could have heard a pen drop because not too many of us in the group really knew what it did. However, I recalled being at a PASS conference back in 2005, yeah that long ago and seeing a demo on the technology. I remembered it could do what we needed. Knowing that Service Broker was much like MSMQ, I mentioned that we could have reliability through message queues between two different databases, even on different servers. We could cut back on the amount of code we needed to write and it was straight SQL that was being used, so as long as we had SQL Server, anyone could see what was going on with the queues. After talking to a couple of SQL Server experts who did not have much experience with the technology I decided to give it a go by building a proof of concept. Without knowing anything really about the technology, I was able to build something to demonstrate it's capabilities that we needed in about two days. I was amszed at how easy it was to work with even though I was not someone who wrote script in SQL Server Management Studio all the time. After building the POC, I started thinking of how SB could have saved me in the past with data integration with previous solutions. I also started sharing with my SQL expert friends and one started paying more attention to it, playing around with it's capabilities as well. I mean we are talking about a technology that has been around for about 3 years. I was not sure if it was going to be continued in Sql Server 2008, but luckily it was there, demonstrating that it was something MS thought was still valuable to developers. I think the reason why it did not receive more adoption was because it is more of a app developement tool, that most DBAs and BI guys feel it is not as important... similar to SQLCLR.

Here are some links that helped me get things going.

http://msdn.microsoft.com/en-us/library/ms166063.aspx

http://msdn.microsoft.com/en-us/library/ms166071.aspx

http://www.codeproject.com/KB/database/IntroServiceBroker.aspx

http://technet.microsoft.com/en-us/library/ms186742.aspx

http://download.microsoft.com/download/3/3/f/33fb3703-6e6e-4715-9318-c52853bd55bf/FirstLookatMicrosoftSQLServer_Ch15.pdf

http://www.sqlserverandxml.com/2008/07/service-broker-how-to-clean-up-all-open.html

http://technet.microsoft.com/en-us/magazine/cc160787.aspx#Managing

http://www.simple-talk.com/content/print.aspx?article=407

http://articles.techrepublic.com.com/5100-10878_11-6156264.html#

Feel free to ping me if you have questions about my experience with Service Broker. I think I will be using it more often.

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Posted by: BayerWhite
Posted on: 10/27/2008 at 1:00 AM
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (49) | Post RSSRSS comment feed

Comments

Comments are closed