Working with SQL in BizTalk and Mule
Databases are very frequently at the heart of an enterprise integration. EAI tasks frequently involve polling databases, calling stored procedures in databases, as well as ETL and basic CRUD work on databases. The database backing a particular application is often a natural source to integrate with – and if no on premise database exists for a particular source, creating one for local OLTP purposes can help increase insight and decrease chattiness between on premises and off premises applications.
It’s no wonder then that both BizTalk and Mulesoft ESB offer database connectivity out of the box. In this post, I’ll compare Mule and BizTalk’s SQL capabilities, primarily focusing on Oracle and SQL Server. Why these two? They’re the most popular enterprise grade database engines. MySQL is also very popular, but lacking in some enterprise features that this blog will be examining. PostgresSQL offers a fuller implementation and may fit well with Mule’s open source nature, but it is not widely used enough in the enterprise integration context to be fully relevant here.
BizTalk introduced the WCF sqlBinding and OracleDBBinding in the 2010 version. It has since replaced the legacy SQL adapter as the standard, and offers many powerful features, such as generating schemas to match stored procedure calls, call multiple stored procedures/table operations in a single message, MSDTC (distributed transaction management), and the ability to perform several types of polling operations. If you’re working with a procedure that will handle all of its own transactions, it’s also possible to have the adapter let the procedure manage the transactions (turning UseAmbientTransaction to false).
When BizTalk generates a schema for a SQL procedure, it will gather metadata about the types (including user defined types and table types) for that procedure. This functionality is a bit easier to use with SQL Server than with Oracle (which makes sense – SQL Server and BizTalk are both Microsoft products). It will use ADO.NET or ODP.NET as an underlying framework to handle connection pooling and make the basic connections with SQL.
These points are important because this functionality allows BizTalk to be very efficient in pulling or pushing larger amounts of data to and from SQL. Even more importantly, it can easily convert data from the BizTalk-native XML formatting to a SQL-native type (such as a table type), and translating hierarchical relationships from the XML message to the SQL tables can be more fluidly handled (see Tom Babiec’s post on that here). It’s much more natural to work with table types in a SQL procedure than needing to shred the XML into some sort of tabular format, and much easier to do through BizTalk than manually creating types or datatables in ADO.NET or ODP.NET.
It’s equally possible to execute simple or custom SQL statements (whether using SQLExecute and/or TableOps for Oracle or TableOps for SQL Server) if that’s all that is needed. However, caution must be used here – SQLExecute is probably preferable for Oracle as it allows for query parameterization (see Matt Mitchell’s excellent blog on this topic). For SQL Server, strongly typed calls will allow BizTalk to properly parameterize queries.
SQL Polling with the WCF adapter is a very powerful method. My personal preference is for XmlPolling, which allows for greater control over the XML document produced from SQL (with some additional cost in creating the schema). That method is outlined by here. It’s also possible to have this kind of control and debatching using strongly typed polling – however, the node names and message structure may be more difficult to work with in complicated relational hierarchies (outlined in Richard Seroter here).
So what are the downsides? Sending to a WCF-SQL port in an orchestration will cause a persistence point. Making lots of little calls to SQL in an orchestration can become very expensive. Some of this can be mitigated by using CompositeOperation calls (documented for SQL server here), allowing you to perform multiple operations in a single request. Still, sometimes an entire persistence point just to update a status column for a single row seems expensive. In these cases, ADO.NET (or ODP.NET) calls can be made directly in a C# helper library, but crucial calls should not be made this way. If a host instance were to crash, the orchestration’s state would be saved – an ADO.NET call happening from a helper library may or may not survive that crash. There are ways to avoid this – using atomic scopes when possible or avoiding orchestrations altogether when not necessary – but both these approaches are not always feasible. Another downside for BizTalk is lack of native support for JMS messaging, which may factor heavily into an Oracle based integration (although there is a third party adapter available from JNBridge.
Configuring ODP.NET for BizTalk can be painful. Getting the drivers isn’t too difficult, but the BizTalk adapter framework has hard coded certain assembly mappings to assemblies for Oracle 11g – using 12c is possible, but, as Sandro Pereira points out, it requires editing machine.config. SQL Server is fairly well supported out of the box.
Mulesoft ESB and JDBC
Mule does provide some custom connectors for various database providers, and they’re ultimately backed by JDBC. SQL Server and Oracle both have JDBC drivers available; setting up SQL Server drivers for Mule is slightly less involved than setting up ODP.NET for BizTalk as of this writing – get the JDBC driver, add it to the class references in AnyPoint (described here), and you’re good to go.
The Mule Database Connector allows for multiple SQL statements to be executed in a single call (like BizTalk’s hybrid operations), and allows for dynamic generation of queries. It has a clearer interface for building parameterized queries when there is a need to do so. And Mule has no built in automatic persistence points! You can make as many queries as your database server can handle, and Mule won’t require as many system resources as BizTalk to do it. This means that developers can spend less time worrying about how important a particular call is and whether it’s worth a persistence point. Mule, being built on a Java platform, also can natively support JMS messaging, giving it an automatic boost for Oracle based integrations which require this (it also supports other aspects that are more readily available in Oracle’s JDBC Java API than in its ODP.NET C# API).
A major downside for Mule is a lack of support for User Defined Types (there’s an open feature request related to this). It is possible to use User Defined Types with Mule, but there’s no automated way to do so – so they have to be dynamically or programatically generated. For example, Mule can execute a query like this for SQL Server (or its equivalent in PL/SQL):
DECLARE @p1 dbo.MyTableType; INSERT @p1 VALUES(....); EXEC usp_MyProcedure TableTypeParam = @p1;
Effectively, this is what the BizTalk adapter does behind the scenes. However, this is not a trivial task to handle, and until the Mule connector catches up here it’s a significant deficit. What about NoSQL? Well, Mule certainly has more in-built support for that out of the box, and a NoSQL model would solve some of these problems (just pass the whole object into the database for storage). However, NoSQL is not always appropriate for enterprise SQL needs, which typically will have to do significant relational joining of data for reporting and analysis purposes (especially as OLTP data has to be inserted into an OLAP source). There are certainly use cases where NoSQL can handle this, but caution must be taken here: inserting and retrieving BLOBs is something NoSQL excels at, but there will be increased cost and complexity when trying to shred those BLOBs relationally.
So who wins? Here’s how I see the breakdown.
BizTalk will work better if you’re:
- A .NET/Microsoft/SQL Server shop
- Integrating with Microsoft products
- Want to maintain a relational OLTP as part of your integration
- Want message durability and persistence out of the box
Mulesoft will be better if you’re:
- A Java/Oracle based shop
- Integrating with Java based products
- Are interested in pursuing NoSQL technologies in your integration model
- Value (potential) faster message processing for higher volume of messages compared to BizTalk
What happens if you have to integrate both Microsoft and Oracle products, and you have a team that’s equally proficient in Java and .NET technologies? I’d give BizTalk the edge here, mainly for its ability to support user defined types in both database engines. However, if you only have a single SQL Server based integration point but multiple Oracle/JMS integration points, Mule could win out.
Over all, I’d give a slight edge to BizTalk for enterprise application integration; but as the Mulesoft platform matures it will surely catch up (and perhaps surpass BizTalk) in some of these areas.