BizTalk has always provided native methods to integrate SQL Server databases. Since the first version we are able to query the database using the BizTalk Adapter for SQL Server databases.
This native adapter is based on XMLSQL and uses updategrams to select, insert and update queries. This is tied to the limitation of always having to use the FOR XML statement in our queries or stored procedures. This is somehow a limitation because it ties the store procedure definition to the BizTalk implementation, not letting us reutilize the stored procedure already defined or using direct queries to the database with a simple T-SQL statement. Besides this adapter is still being used as the bases technology in some BizTalk components as the EDI Batching Functionality or in the ESB Toolkit, it is unsupported since BizTalk 2010.
What’s new in the BizTalk SQL server databases ecosystem
Microsoft has created the BizTalk WCF-SQL Adapter. This new adapter is based on ADO.NET technology and not only gives support for querying data in an easier way, it also gives support to many new SQL Server Databases features. Another difference is the underlying framework used to develop the adapters. The old SQL adapter was based on the BizTalk Adapter Framework and the new one is based on the WCF LOB SDK Adapter.
So, what can we do with the new adapter that we were not able to with the old one. Here’s a short list of improvements.
- No need to use the FOR XML statement in queries.
- Database push via SQL Query Notification
- Enhancements in the transaction behavior configuration
- Composite transactions for multiple queries
- Metadata browsing
- New data types support for SQL server 2008 and above
- Dynamic WSDL generation
- Azure SQL database support
If you already have a solution based on the SQL Adapter and you want to migrate it to a WCF-SQL based solution. You can use the Migration Tool for Microsoft BizTalk Adapters for Enterprise Applications (http://www.microsoft.com/en-us/download/details.aspx?id=7840 ).
This will create a new solution based on your solution with the new schemas and maps using the WCF-SQL adapter. This tool comes with a migration guide. Something you should be aware of is that the tool will fail if you have your schemas, maps or orchestrations defined in an external assembly. So you may have to tune up your solution before starting the migration.