Store and Forward Pattern - BizTalk and SQL Azure

Store and Forward Pattern - BizTalk and SQL Azure


Important aspect of messaging is that a message send from one system or application can be received by another system or application. However an system or application may not be able to receive the message, because of the format, encoding or protocol (connectivity). A messaging system or product like BizTalk Server can provide capabilities to enable messaging between applications and/or systems and solving the previous mentioned issues. 

At the core of BizTalk Server you will find a messaging engine, 
which provides the underlying architecture for integrating and exchanging messages between various services, both within and outside your organization. This engine is capable of receiving and sending messages, parse incoming messages to identify their format, and so on. 

BizTalk has capabilties to support asynchronous messaging, orchestration, correlation and long running transactions.  However, the focus in this article will be on messaging albeit one of the messaging patterns. There are many forms of messaging that are best described through patterns. Many of the messaging patterns can be implemented using BizTalk. One of the patterns is garanteed delivery. You can implement this pattern to make messages persistent so that they are not lost even if the messaging system crashes. 

Store and Forward Messaging Pattern

Store and forward messaging pattern (also qualified as garanteed delivery) can play an important role when it comes to asynchronous messaging. It can be used to integrate with systems that accept different message traffic, handle fail-over scenario's or priority mediation of messages. One of the main advantages of using this pattern is that consumers don't need to be online at the same time. While the network is unavailable, the messaging system can use a store and forward mechanism to ensure message durability. Messages can be stored in memory until they can be successfully forwarded to the next contract point. This mechanism works well when the messaging system is running reliably, but if the messaging system crashes, all of the stored messages are lost. As a preventative measure, applications use persistent media like files and databases to ensure recovery from system crashes. For a database you can use SQL Server on premise or you could use SQL Azure. Depending where your messages comes from SQL Azure can be option to use as a store.

SQL Azure

SQL Azure is a cloud-based service from Microsoft offering data-storage capabilities (similar to those of Amazon S3 and Amazon Relational Database Service) as a part of the Azure Services Platform. Unlike similar cloud-based databases, SQL Azure allows users to make relational queries against stored data, which can either be structured or semi-structured, or even unstructured documents.

SQL Azure uses a special version of Microsoft SQL Server as its backend. It provides high availability by storing multiple copies of databases, elastic scale and rapid provisioning. It exposes a subset of the full SQL Server functionality, including only a subset of the data types — including string, numeric, date and boolean. It uses an XML-based format for data transfer. Like Microsoft SQL Server, SQL Azure uses T-SQL as the query language and Tabular Data Stream (TDS) as the protocol to access the service over internet.


Store and forward mechanism can be implemented using BizTalk and SQL Azure. Imaging an application, service or web site offers a service to request for a quote on an insurance. The request is stored in SQL Azure. BizTalk polls in a controlled manner SQL Azure to recieve the request and routes it to a backend system that will process the request. SQL Azure will act as a store to persist the requests. BizTalk will poll a SQL Azure table to get requests out. This can be done in a controlled manner. It may be possible that the backend system cannot process to much requests at the same time or maybe not be online because of maintenance. In the latter case you only have stop polling SQL Azure table.

Connectivity with SQL Azure

BizTalk Adapter Pack 2010 offers connectivity to line of business systems like Oracle eBusiness Suite, SAP, Siebel eBusiness Suite, Oracle and SQL Server database. This pack offers WCF-SQL adapter (i.e. binding) that supports connectivity with 2005, 2008, and 2008 R2. It also provides connectivity with SQL Azure, the cloud version of SQL Server. SQL Azure has not fully implemented every capability that on-premise SQL Server has, but it does offer capabilities that can be used through BizTalk Server Adapter Pack 2010 like polling a table or calling a stored procedures.

[More Content to be added]

Creating the database (Store)

[More Content to be added] 

Setting up Polling from BizTalk (Forward)

[More Content to be added] 

Leave a Comment
  • Please add 4 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Page 1 of 2 (14 items) 12
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
  • Steef-Jan Wiggers edited Original. Comment: Added text

  • Steef-Jan Wiggers edited Revision 1. Comment: Added text

  • Steef-Jan Wiggers edited Revision 2. Comment: Added text

  • Steef-Jan Wiggers edited Revision 3. Comment: Edit text

  • Fernando Lugão Veltem edited Revision 4. Comment: alter font style from title

  • Steef-Jan Wiggers edited Revision 5. Comment: Added text

  • Steef-Jan Wiggers edited Revision 6. Comment: Added text

  • Steef-Jan Wiggers edited Revision 7. Comment: Added Text

  • Steef-Jan Wiggers edited Revision 8. Comment: Added Text

  • Steef-Jan Wiggers edited Revision 9. Comment: Added Text

  • Steef-Jan Wiggers edited Revision 10. Comment: Added text

  • Steef-Jan Wiggers edited Revision 11. Comment: Added text

  • Steef-Jan Wiggers edited Revision 12. Comment: Added text

  • Sandro Pereira edited Revision 15. Comment: Fixing text format

Page 1 of 1 (14 items)