All posts by : Write Ahead Blog

1 of 40 | Reliably making a web-request from the database - One of the prime use cases of the CLR integration in SQL Server 2005 is to make outbound HTTP requests to web applications or web services. But how do you do that reliably given the large number of failures that need to be handled ranging from network unavailability, server timeouts, 404 errors, 500 errors, etc. While some of these errors may be transient and we could retry the requests, some may necessitate giving up altogether. This seems like a lot of effort for a user, who just wants to perform a simple web-request. One solution is to use Service Broker to add reliability. The user can simply begin a dialog and send the request over to a proxy service that handles the task of actually ......

2 of 40 | App Partitioning: Data Dependent Routing and Forwarding - One strategy to achieve scale-out is replicating data. Another is partitioning. Each has its own advantages and limitations; and you’d use one over the other depending on the nature of the data itself as well as the access pattern. While Service Broker naturally provides a mechanism for achieving scale-out when the data is replicated, it is also possible to build highly scalable apps that use partitioned data using Service Broker. The key to achieving this lies in data dependent routing, which is the subject of this post. But before we go into that topic, let us look at what Service Broker offers for talking to replicated services. The main purpose of routing provided by Service Broker is t......

3 of 40 | [PDC 2005] DAT303 SQL Server 2005: Building Distributed, Asynchronous Database Applications with the Service Broker - Here are the demos used in Gerald and Roger`s PDC talk on the Service Broker. The demos include: Basics - Creating metadata, beginning a dialog, sending a message and receiving a message. CLR Services - Writing a CLR stored proc and activating it internally, writing a CLR application and activating it externally (using the External Activator). CG Locking - Maintaining consistency when concurrent queue readers receive messages from the queue. WCF Channel - Layering the Windows Communication Foundation ("Indigo") on top of Service Broker by implementing  Request/Reply custom transport channels. [This is work in progress and hence we are not releasing this yet]. Remote - Setting up endpoints ......

4 of 40 | [PDC 2005] DAT303 SQL Server 2005: Building Distributed, Asynchronous Database Applications with the Service Broker - You can now download the demos used in Gerald and Roger`s PDC talk on the Service Broker from SSB CodeGallery. The demos include: Basics - Creating metadata, beginning a dialog, sending a message and receiving a message. CLR Services - Writing a CLR stored proc and activating it internally, writing a CLR application and activating it externally (using the External Activator). CG Locking - Maintaining consistency when concurrent queue readers receive messages from the queue. WCF Channel - Layering the Windows Communication Foundation ("Indigo") on top of Service Broker by implementing  Request/Reply custom transport channels. [This is work in progress and hence we are not releasing this ......

5 of 40 | [PDC 2005] DAT303 SQL Server 2005: Building Distributed, Asynchronous Database Applications with the Service Broker - You can now download the demos used in Gerald and Roger's PDC talk on the Service Broker from SSB CodeGallery. The demos include: Basics - Creating metadata, beginning a dialog, sending a message and receiving a message. CLR Services - Writing a CLR stored proc and activating it internally, writing a CLR application and activating it externally (using the External Activator). CG Locking - Maintaining consistency when concurrent queue readers receive messages from the queue. WCF Channel - Layering the Windows Communication Foundation ("Indigo") on top of Service Broker by implementing  Request/Reply custom transport channels. [This is work in progress and hence we are not releasing ......

6 of 40 | Exporting and importing service listings into a database - You implemented a Service Broker service and now want others to be able to talk to it. But they will have to first create your message types and contracts to be able to begin a dialog and send messages to your target service. If their service does not reside in the same SQL Server instance, they will have to setup a broker endpoint and setup adjacent layer security to talk to your endpoint. Next they have to setup routing and dialog level security on their side and hand you the public key certificates and reverse route information so that you can setup routing and dialog level security on your side. All this can be quite tedious and prone to errors as some of you may have experienced with B......

7 of 40 | Exporting and importing service listings into a database - You implemented a Service Broker service and now want others to be able to talk to it. But they will have to first create your message types and contracts to be able to begin a dialog and send messages to your target service. If their service does not reside in the same SQL Server instance, they will have to setup a broker endpoint and setup adjacent layer security to talk to your endpoint. Next they have to setup routing and dialog level security on their side and hand you the public key certificates and reverse route information so that you can setup routing and dialog level security on your side. All this can be quite tedious and prone to errors as some of you may have experienced with B......

8 of 40 | Exporting and importing service listings into a database - You implemented a Service Broker service and now want others to be able to talk to it. But they will have to first create your message types and contracts to be able to begin a dialog and send messages to your target service. If their service does not reside in the same SQL Server instance, they will have to setup a broker endpoint and setup adjacent layer security to talk to your endpoint. Next they have to setup routing and dialog level security on their side and hand you the public key certificates and reverse route information so that you can setup routing and dialog level security on your side. All this can be quite tedious and prone to errors as some of you may have experienced with B......

9 of 40 | Solution to BrokerChallenge 0 - It has been a week since I posted BrokerChallenge 0 and I am quite happy to see several people successfully cracked it. Still others gave it a shot but were not completely successful. So I decided to post the solution to the challenge. You can get the sample T-SQL script from here and modify it to suit your hostname, port and database name. With that you are all set to BEGIN a dialog and SEND me a message. My CommentService is still accepting messages although you will see your name in the consolation list of the Hall of Fame. Note: Several people who attempted to setup the broker stumbled on firewall issues. While you might be tempted to think you don`t need to puncture your firewall sinc......

10 of 40 | Solution to BrokerChallenge 0 - It has been a week since I posted BrokerChallenge 0 and I am quite happy to see several people successfully cracked it. Still others gave it a shot but were not completely successful. So I decided to post the solution to the challenge. You can get the sample T-SQL script from here and modify it to suit your hostname, port and database name. With that you are all set to BEGIN a dialog and SEND me a message. My CommentService is still accepting messages although you will see your name in the consolation list of the Hall of Fame. Note: Several people who attempted to setup the broker stumbled on firewall issues. While you might be tempted to think you don`t need to puncture your firewall sinc......

11 of 40 | Solution to BrokerChallenge 0 - It has been a week since I posted BrokerChallenge 0 and I am quite happy to see several people successfully cracked it. Still others gave it a shot but were not completely successful. So I decided to post the solution to the challenge. You can get the sample T-SQL script from here and modify it to suit your hostname, port and database name. With that you are all set to BEGIN a dialog and SEND me a message. My CommentService is still accepting messages although you will see your name in the consolation list of the Hall of Fame. Note: Several people who attempted to setup the broker stumbled on firewall issues. While you might be tempted to think you don't need to puncture your firewall sinc......

12 of 40 | [BrokerChallenge 0] SEND me a message - Over the past week I have been planning to launch the first open to public Service Broker. At the same time, my dev team was having a discussion regarding how we can do something cool and interesting to get the community involved in our product. So I decided to setup simple services on my public Service Broker to let people tinker around with it. This is the zeroth in a series of BrokerChalleges; zero since it requires hardly any skill, just the motivation to play with cool technology. My service broker endpoint is hosted at the location tcp://rushi.desai.name:4022 and uses certificate based authentication with this certificate identity (public key) and supports AES encryption. In this......

13 of 40 | [BrokerChallenge 0] SEND me a message - Over the past week I have been planning to launch the first open to public Service Broker. At the same time, my dev team was having a discussion regarding how we can do something cool and interesting to get the community involved in our product. So I decided to setup simple services on my public Service Broker to let people tinker around with it. This is the zeroth in a series of BrokerChalleges; zero since it requires hardly any skill, just the motivation to play with cool technology. My service broker endpoint is hosted at the location tcp://rushi.desai.name:4022 and uses certificate based authentication with this certificate identity (public key) and supports AES encryption. In this chal......

14 of 40 | [BrokerChallenge 0] SEND me a message - Over the past week I have been planning to launch the first open to public Service Broker. At the same time, my dev team was having a discussion regarding how we can do something cool and interesting to get the community involved in our product. So I decided to setup simple services on my public Service Broker to let people tinker around with it. This is the zeroth in a series of BrokerChalleges; zero since it requires hardly any skill, just the motivation to play with cool technology. My service broker endpoint is hosted at the location tcp://rushi.desai.name:4022 and uses certificate based authentication with this certificate identity (public key) and supports AES encryption. In this chal......

15 of 40 | SODA (Service-Oriented Data Architecture) at TechEd - Where do relational databases systems fit to provide reliability, scalability and resilience to loosely-coupled world of Service-Oriented Architecture (SOA) Fueled by XML, CLR hosting, integration with Visual Studio 2005, native web-services, asynchronous execution, reliable messaging and query notifications (to mention just a few key technologies), Service-Oriented Data Architecture (SODA) has been an important theme of the upcoming SQL Server 2005 release. Be sure to attend Paul Flessner`s keynote tomorrow morning. (There will be a fun demo you`ll get to see there). Also on Wednesday join David Campbell, the general manager of the SQL Server Engine group and Gerald Hinson who leads the ......

16 of 40 | SODA (Service-Oriented Data Architecture) at TechEd - Where do relational databases systems fit to provide reliability, scalability and resilience to loosely-coupled world of Service-Oriented Architecture (SOA) Fueled by XML, CLR hosting, integration with Visual Studio 2005, native web-services, asynchronous execution, reliable messaging and query notifications (to mention just a few key technologies), Service-Oriented Data Architecture (SODA) has been an important theme of the upcoming SQL Server 2005 release. Be sure to attend Paul Flessner`s keynote tomorrow morning. (There will be a fun demo you`ll get to see there). Also on Wednesday join David Campbell, the general manager of the SQL Server Engine group and Gerald Hinson who leads the ......

17 of 40 | SODA (Service-Oriented Data Architecture) at TechEd - Where do relational databases systems fit to provide reliability, scalability and resilience to loosely-coupled world of Service-Oriented Architecture (SOA)? Fueled by XML, CLR hosting, integration with Visual Studio 2005, native web-services, asynchronous execution, reliable messaging and query notifications (to mention just a few key technologies), Service-Oriented Data Architecture (SODA) has been an important theme of the upcoming SQL Server 2005 release. Be sure to attend Paul Flessner's keynote tomorrow morning. (There will be a fun demo you'll get to see there). Also on Wednesday join David Campbell, the general manager of the SQL Server Engine group and Gerald Hinson who leads the......

18 of 40 | Shipping rowsets across databases using Service Broker - [Attachment: ShippingRowsets.zip] While there are technologies such as log shipping, SQL Server Replication Service and database mirroring available for various scenarios that require maintaining a copy of the data in a secondary source, several customers have asked how they could use Service Broker for securely and reliably sending tables or rowsets from one database to another. Since there were no examples available for demonstrating how this task can be performed I decided to cook up a very simple sample. In this sample, we will use the FOR XML clause to convert a rowset into an XML document, send it from one database to another (which could be possibly residing on a different SQL Server......

19 of 40 | Shipping rowsets across databases using Service Broker - [Attachment: ShippingRowsets.zip] While there are technologies such as log shipping, SQL Server Replication Service and database mirroring available for various scenarios that require maintaining a copy of the data in a secondary source, several customers have asked how they could use Service Broker for securely and reliably sending tables or rowsets from one database to another. Since there were no examples available for demonstrating how this task can be performed I decided to cook up a very simple sample. In this sample, we will use the FOR XML clause to convert a rowset into an XML document, send it from one database to another (which could be possibly residing on a different SQL Server......

20 of 40 | Shipping rowsets across databases using Service Broker - [Attachment: ShippingRowsets.zip] While there are technologies such as log shipping, SQL Server Replication Service and database mirroring available for various scenarios that require maintaining a copy of the data in a secondary source, several customers have asked how they could use Service Broker for securely and reliably sending tables or rowsets from one database to another. Since there were no examples available for demonstrating how this task can be performed I decided to cook up a very simple sample. In this sample, we will use the FOR XML clause to convert a rowset into an XML document, send it from one database to another (which could be possibly residing on a different SQL S......

21 of 40 | Managing scheduled async SQL Server tasks - DBAs often struggle with setting up automated tasks for managing databases such as taking backups, maintaining warehouses, re-building indexes and generating scheduled reports just to mention a few. While Service Broker is seen mostly as a framework for developers to build reliable messaging applications, it can be used very well by DBAs for doing routine maintenance jobs asychronously. Roger Wolter's recent article on Microsoft TechNet Magazine (Spring 2005) talks about a simple batch scheduling system built using Service Broker. You can access it here: Async Lifestyle: Manage Your Tasks With Service Broker...

22 of 40 | Managing scheduled async SQL Server tasks - DBAs often struggle with setting up automated tasks for managing databases such as taking backups, maintaining warehouses, re-building indexes and generating scheduled reports just to mention a few. While Service Broker is seen mostly as a framework for developers to build reliable messaging applications, it can be used very well by DBAs for doing routine maintenance jobs asychronously. Roger Wolter`s recent article on Microsoft TechNet Magazine (Spring 2005) talks about a simple batch scheduling system built using Service Broker. You can access it here: Async Lifestyle: Manage Your Tasks With Service Broker...

23 of 40 | Managing scheduled async SQL Server tasks - DBAs often struggle with setting up automated tasks for managing databases such as taking backups, maintaining warehouses, re-building indexes and generating scheduled reports just to mention a few. While Service Broker is seen mostly as a framework for developers to build reliable messaging applications, it can be used very well by DBAs for doing routine maintenance jobs asychronously. Roger Wolter`s recent article on Microsoft TechNet Magazine (Spring 2005) talks about a simple batch scheduling system built using Service Broker. You can access it here: Async Lifestyle: Manage Your Tasks With Service Broker...

24 of 40 | Running auctions on SQL Server 2005 - [Attachment: CommodityExchange.zip] We studied auctions in our e-commerce and AI classes at U of M. In one of our assignments we were asked to configure an agent that played the Trading Agent Competition (TAC) which involved a variety of auctions for buying or trading airline tickets, hotel reservations and entertainment tickets. The agents participating in the competition talked to the auction server using a binary TCP protocol. In the real world, the protocol would require to have reliability and security and the app would require to scale to thousands of agents trading at any given time. Of particular interest to me were the entertainment ticket auctions. These were traded using continuo......

25 of 40 | Running auctions on SQL Server 2005 - [Attachment: CommodityExchange.zip] We studied auctions in our e-commerce and AI classes at U of M. In one of our assignments we were asked to configure an agent that played the Trading Agent Competition (TAC) which involved a variety of auctions for buying or trading airline tickets, hotel reservations and entertainment tickets. The agents participating in the competition talked to the auction server using a binary TCP protocol. In the real world, the protocol would require to have reliability and security and the app would require to scale to thousands of agents trading at any given time. Of particular interest to me were the entertainment ticket auctions. These were traded using continuo......

26 of 40 | Running auctions on SQL Server 2005 - [Attachment: CommodityExchange.zip] We studied auctions in our e-commerce and AI classes at U of M. In one of our assignments we were asked to configure an agent that played the Trading Agent Competition (TAC) which involved a variety of auctions for buying or trading airline tickets, hotel reservations and entertainment tickets. The agents participating in the competition talked to the auction server using a binary TCP protocol. In the real world, the protocol would require to have reliability and security and the app would require to scale to thousands of agents trading at any given time. Of particular interest to me were the entertainment ticket auctions. These were traded......

27 of 40 | Replacing an MSMQ queue with a SQL Server 2005 queue - Today, we did a short presentation on Service Broker for a customer who is interested in using the Service Broker technology. Their current application uses a large backend SQL Server database that is accessed from several mid-tier servers. In one of their scenarios, requests are queued to an MSMQ message queue and an NT service periodically reads these requests from the queue and performs some database operations on the backend. Switching from MSMQ to SQL Server 2005 Service Broker would give them the immediate advantages of persisting messages along with the data in the database and thus provide a single backup/restore solution. And they also get the exactly once in-order semantics of the......

28 of 40 | Replacing an MSMQ queue with a SQL Server 2005 queue - Today, we did a short presentation on Service Broker for a customer who is interested in using the Service Broker technology. Their current application uses a large backend SQL Server database that is accessed from several mid-tier servers. In one of their scenarios, requests are queued to an MSMQ message queue and an NT service periodically reads these requests from the queue and performs some database operations on the backend. Switching from MSMQ to SQL Server 2005 Service Broker would give them the immediate advantages of persisting messages along with the data in the database and thus provide a single backup/restore solution. And they also get the exactly once in-order semantics of the......

29 of 40 | Invalidating cached result sets using SQL Server Query Notification - Anyone who has written a web application has probably experienced the cost of doing a database round trip for fetching a result set everytime a web user loads a page. In fact, handling a page request may involve more than one queries (imagine 20 drop-down boxes, each of which must be populated). A fix to this problem is caching frequently requested result sets in memory to avoid the database round trip. The cache can be invalidated periodically get a  more recent version of the actual data. This technique seems to work fine if the data is not updated frequently and the application does not really need the result sets to be fresh. What would be ideal in this case is for the database to noti......

30 of 40 | Invalidating cached result sets using SQL Server Query Notification - Anyone who has written a web application has probably experienced the cost of doing a database round trip for fetching a result set everytime a web user loads a page. In fact, handling a page request may involve more than one queries (imagine 20 drop-down boxes, each of which must be populated). A fix to this problem is caching frequently requested result sets in memory to avoid the database round trip. The cache can be invalidated periodically get a  more recent version of the actual data. This technique seems to work fine if the data is not updated frequently and the application does not really need the result sets to be fresh. What would be ideal in this case is for the database to noti......

31 of 40 | WSE 2.0 Transport for Service Broker - Having seen implementations of MSMQ, SMTP and UDP transport providers for WSE, I was sure someone would come up with one for Service Broker as well. I played around with this for a bit but only got to implement the sender side. Mike Taulty recently released a Service Broker sample transport provider. I haven`t tried it myself yet but I will post my comments on this soon. Going forward, it will be possible to overlay Indigo on top of Service Broker in a similar fashion, using the Service Broker technology as a full duplex, transacted, secure and reliable channel. Anyone up for building a Service Broker Channel for the Indigo CTP bits...

32 of 40 | Subscribing a broker service to SQL events - One of the cool uses of SQL Service Broker is event notification. SQL Server 2005 introduces a mechanism for notifying events such as execution of a DDL statement or occurence of a SQL Trace event to a Service Broker service. Notifications are delivered as messages to the subscribing service via dialogs using a simple contract. Multiple services can subscribe to the same event and a service can get notifications from multiple events. Event notifications can be used both for monitoring some activity (eg> logging server memory changes) as well as taking action asynchronously (eg> sending a message to a remote service whenever table is created). In a previous post, I explained how to use the Q......

33 of 40 | C? programming language and database/XML applications - I found this article on MSDN about a new programming language called C? (C-omega) that was quite interesting. It is a language created by Microsoft Research that looks a lot like C# but has new constructs that help programmers to access and process XML and relational data. Some of the cool features that grabbed my attention were special streams type, XPath and SQL like querying capability and anonymous structures for semi-structured data. You can find out more about this cool new language on the research group homepage. They also have a shared-source preview compiler which I`m going to experiment with soon. Will post more about this later....

34 of 40 | Routing Service Broker conversations (Part 2) - In a previous post, I described the basic routing architecture of Service Broker. What I did not describe was a special facility that allows you to exploit external mechanisms for finding routes. For example, an enterprise may use Active Directory or LDAP for mapping service names to network locations; or an industrial consortium may host web-services that do the mapping. Service Broker allows you to define special exit routes to find services using external mechanism. You can create this exit route in a broker by defining and implementing a special service called he Broker Configuration Notification or BCN service. When the router cannot match the target service with a specified named rout......

35 of 40 | Download and try the InfoPathBroker sample - Following up on my previous article, I decided to provide a sample that people could try for building InfoPath interfaces to SQL Service Broker applications. The sample is a simple expense report form being submitted to backend HR broker service. You can download the code from here: InfoPathBroker sample Backported Microsoft.SqlServer.Broker library for .NET 1.1 (The service broker sample library had to be hacked to make it compatible to .NET 1.1 since the InfoPath Toolkit for Visual Studio is only available with VS.NET 2003 and not with Whidbey beta. This library is not 100% compatible to the original library since I`ve converted generic collections to object collections and SqlBytes to b......

36 of 40 | Activating Service Broker programs externally - In a previous post, I talked about internal activation. But it may not always be feasible to write Service Broker programs as stored procedures. For example, the service program may take a long time to process a message or might require to make a call to a web-service in which case you don`t want to eat up a thread from the SQL Server process. Or the program might need special permissions or be able to run as a different user. Or the program may be deployed on a separate machine. But how do you control the instantiation and termination of a stand-alone service programs One way to achieve this is through event notification. Service Broker provides an external activation mechanism that deliv......

37 of 40 | Integrating InfoPath with SQL Server 2005 - I discovered InfoPath only a few months ago and started wondering how it could be used in enterprise offices. InfoPath does a great job of building forms for publishing XML data quickly. It not only makes the task of building CRUD (create/retrieve/update/delete) applications for Web-services really easy but with the introduction of the .NET extensions for InfoPath, it is possible to build really diverse InfoPath application. So how do you build desktop productivity applications in InfoPath that offer asychronous execution and reliability that traditional web-services over HTTP do not offer Consider an enterprise that processes forms at the client and ships XML data to a server for backgroun......

38 of 40 | Using Microsoft.SqlServer.Broker class library for writing .NET service programs - Service Broker programs may be written as stand-alone applications that establish a connection with SQL Server or as stored procs that are activated internally. You can leverage the .NET platform in both scenarios since SQL Server 2005 integrates CLR and provides the ability of writing stored procs in CLR-based languages like C# or Visual Basic. In order to help you to write Service Broker applications on the .NET platform, the Service Broker team has provided a class library called Microsoft.SqlServer.Broker as a sample. The library provides an object model for abstracting Service Broker concepts as a layer on top of ADO.NET. It also provides a framework for writing common event-driven ser......

39 of 40 | Comparing Service Broker with other messaging technologies - An often asked question about SQL Service Broker is how it compares with other products offered by Microsoft such as MSMQ, BizTalk server, WSE and its successor Indigo. So I thought Ill take a shot at answering this politically provocative question. [These are my personal opinions].   First, Service Broker is not just about messaging. Rather it is a solution for using SQL Server as a platform for building asynchronous database applications. Several large applications like SAP and PeopleSoft leverage database tables for building queues for asynchronous processing. Service Broker implements queues as first-class database objects to provide high-performance and ready-to-use features such as or......

40 of 40 | What is Service Broker - A number of large database applications require asynchronous and distributed execution. Most loosely-coupled distributed systems provide scale out and resilience by queuing operations. Queues are used extensively in applications such as order entry systems, stock trading, warehouse querying and travel reservation systems. Writing queued distributed applications is a challenging task since it involves reliable network communication, integrity and ordering of messages, tolerance to process and system failuresa and coordination of transactions with the database. While most of these tasks can be achieved by using specialized messaging systems such as MSMQ and MQSeries, the close association bet......