Integration, Integration, Integration

  • Article

Microsoft is all about choice, so instead of having one way of shifting data between two different platforms we have three.  I get asked about two of these a lot but I also wanted to discuss the latest tool in this space to make sense of all them and understand when to use what:

  • Integration Services included in standard edition and up of SQL Server since SQL Server 2005
  • BizTalk Server, a product in its own right
  • StreamInsight in Enterprise (single threaded) and DataCenter editions of SQL Server 2008R2

So what’s different about these three?

Biztalkis a workflow service that enables integration of disparate systems, but also allows human intervention and business rules to be included.  It works at the transaction level rather than being used to bulk move lots of data.  If your a developer reading this you’ll be familiar with Windows Workflow foundation and Biztalk is where you can scale and run workflows you’ve written.  So Biztalk is for thing like Enterprise application integration (EAI), fore example synchronizing or exchanging data between a company's warehouse and enterprise resource planning (ERP) systems.

Integration Services is about lifting and shifting bulk data between two systems. Although it is part of SQL Server neither the begin or end points needs to be a SQL Server database. It also has complex transformation rules and IMO can pretty much do everything Informatica can do as fast as it can.  I know this because I used Informatica in production before joining Microsoft. Typically Integration Services is used to populate data warehouses and run associated tasks (building cubes, running reports, sending alerts etc.).

StreamInsight is the new shiny thing and is about working with data as it appears in a feed in near real time.  I have already done a few posts on that and the StreamInsight blog has some good samples but to précis those, it’s a services that you write rules in using LINQ (language integrated query) in visual studio.  It is being used for telemetry monitoring in manufacturing, and trading & fraud analysis in the financial sector.

So three different integration tools for three very different use cases.