Share via

Store and Forward Pattern - BizTalk and SQL Azure

  • Article

Introduction

An important aspect of messaging is that a message sends from one system or application can be received by another system or application. However, a 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 previously 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 capabilities 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 guaranteed 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 scenarios 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 on where your messages come from SQL Azure can be an 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 the internet.

Scenario

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 insurance. The request is stored in SQL Azure. BizTalk polls in a controlled manner SQL Azure to receive 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 too many requests at the same time or maybe not be online because of maintenance. In the latter case, you only have to stop polling SQL Azure table.

Connectivity with SQL Azure

BizTalk Adapter Pack 2010 offers connectivity to the 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 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] 

See Also

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.