Practical Applications of BizTalk Server, Windows Workflow and SQL Server Integration Services

In an effort to maintain control over the proliferation of application servers and data, many organizations have undertaken projects to consolidate and integrate systems. One of the first hurdles in undertaking an integration project is determining the most efficient integration technology. Unfortunately, just within the Microsoft stack, there are technologies that consistently cause confusion and are often misused. For example, the lines are continuing to blur when comparing BizTalk Server to Windows Workflow especially now that hosting Workflows using Windows Server AppFabric can provide functionality that appears similar to BizTalk. However, there are still quite a few giveaways when you need one versus the other. Underneath most applications, we commonly find a data platform and for many organizations, their platform of choice is SQL Server. The intersection of just these 3 technologies leads many to ask the question, “if I want to integrate System A with System B, which technology should I use?” Many times there are no hard and fast rules, but with some careful thought and consideration of organization demands and technological capabilities, the fog will lift and a clear answer will emerge. This post will by no means provide the definitive answer, but I will provide a few common scenarios that might help illuminate the path.

BizTalk Server

If your company does not have a BizTalk installation, it is best to thoroughly review the real issues that you are trying to solve with a careful assessment. Matching the requirements or the pain points against BizTalk’s features and the licensing costs can be a quick way to determine if you are trying to use a much bigger tool than the problem you are trying to solve or if the cost to build a custom workflow application far outweighs the cost of implementing BizTalk from the ground up. One must always factor in the experience level of the development and network administration staff. BizTalk has an extremely large learning curve, so, do not expect to drop the disk in the tray and be up and running in a few weeks. If you are starting from scratch, plan for at least a 6 to 12 month learning curve with some real hands on training for anyone involved with any task related to BizTalk including developers, database administrators, network security, etc. There are some very real costs that need to be factored in and the assessment of whether to use BizTalk is typically a full engagement unto itself.

When reviewing the platform’s capabilities, it is plain to see that out of the box, BizTalk Server has many strengths and core features that would be time consuming and costly to implement with custom code, regardless of technology stack:

  • Loosely Coupled Messaging
  • Message Transformation
  • Protocol Bridging
  • Enterprise Single Sign-On
  • Health and Activity Tracking
  • Orchestration
  • Long Running Transactions, Persistence and Compensation
  • Business Activity Monitoring (BAM)
  • Robust, high performance Business Rule Engine
  • BizTalk ESB Toolkit
  • Accelerators for specific industry message formats such as SWIFT, RosettaNet, HL7 and HIPAA
  • Scalability
  • Redundancy, Fail over and Disaster Recovery

It is important to keep in mind that at the core, BizTalk is a messaging engine. The primary unit of work is the message. I find BizTalk to be much better at handling discreet messages rather than large batches of data that need to be disassembled before they can be transformed and processed. Consider the fact that BizTalk operates on the Publish-Subscribe model where messages are typically received by an adapter, written to the SQL Server Message Box and subsequently subscribed to and processed by another process. The bigger the message, the bigger the batch, the slower that process becomes.

Windows Workflow

Windows Workflow has features that are similar to those offered by BizTalk although many of the features require custom coding or in-depth configuration in order to enable them. Windows Server AppFabric is helping to close the gap and offer an alternative to standing up clusters of BizTalk servers, but the two technologies are very different. I view Windows Workflow as a platform or a framework from which workflows can be created across the Microsoft .NET stack. In general, I suggest using Windows Workflow for:

  • Embedding Workflow within.NET applications, particularly client-side (WPF, ASP.NET, SharePoint)
  • Exposing Workflows as Web Services (if no other messaging or Application Integration requirements exist); This is particularly true if you need to create custom services that have a procedural or sequential flow that will change frequently. WF is a good way of self-documenting the process and Microsoft has improved the integration between WCF and WF.
  • Building workflows with a lower cost of ownership when compared to BizTalk. In order to provide a robust environment, Windows Server AppFabric can be used as the hosting environment. However, like BizTalk, there may be a learning curve before the development staff can effectively architect a solution. There is also a possibility of software changes since the technology is a new addition to the Microsoft stack. When the features of BizTalk are considered overkill, I believe this will become Microsoft’s de facto recommendation for building and exposing workflows in the enterprise.

SQL Server Integration Services (SSIS)

SSIS is a technology that many of my clients do not consider when evaluating a system integration project. Several of my “SOA” projects over the past couple of years could safely be categorized as data integration projects. Since databases are ubiquitous throughout the enterprise, I am intrigued when organizations start trying to jam a BizTalk messaging solution in the place where SSIS clearly fits. When we start talking about database to database data movement, BizTalk and WF should generally drop off the list of top options if we are dealing with more than just a handful of records. Take into account that BizTalk is really about messaging. Anyone who has spent any time programming with BizTalk will agree that dealing with large batches of messages begins to be a challenge to say the least. Typically, when dealing with batches, one must keep track of each individual message and a reference to the overall batch to which it belonged. When individual messages inside of the batch begin to fail for whatever reason, the requirements and exception handling begin to get murky and virtually every scenario will be different. If you have ever sat in a requirements meeting or a design session, you would typically hear questions like: “If we have 100 messages in a batch and five fail, what do we do? Fail the whole batch? Fail just the five and continue processing? Fail after the first error or after a certain percentage of the total batch size?” These discussions can last for minutes, hours, days – you name it. It is a difficult topic and the giveaway that you are trying to force a technology into a situation it should not be used is when you start modifying your business process because the tool cannot perform some action without a major overhaul that you *think* might work. Tell that to the developer that needs to make an adjustment to the code 2 years from now. BizTalk does have specific adapters to connect to a wide variety of databases, but I really see their primary use as a way to persist or retrieve discreet pieces of data needed in the course of orchestration processing or message exchange. I do not recommend their use as a way of enabling high volume data extraction, transformation and loading between databases.

With that said, I suggest using SSIS (or your favorite ETL tool) for the following:

  • Bulk Data Transfers
  • Database to Database Transfer
  • Batching / De-batching

Summary

At a high-level, here are my general recommendations:

What is BizTalk good for?

Exchanging high volumes of messages between systems especially with varying protocols and where long running orchestration is required.

What is Windows Workflow good for?

Providing a programming framework to implement light weight workflows with a much lower dependency on enterprise infrastructure and software costs.

What are SQL Server and Integration Services good for?

Storing and moving data between databases.

It is important to let the tools and the software work from their strengths. Before undertaking a development effort or recommending software for a system architecture, I urge my team members to start from the position of what the software can do well rather than trying to make it do things it was never built to do. Following this guideline will help organizations build more stable applications that can be supported by development staff and the software vendors alike.

This entry was posted in System Integration and tagged , , , , . Bookmark the permalink.