Problems with the BizTalk SQL Adapter……

By patrickwellink
December 21, 2004

I have some experience withe the Biztalk SQL Adapter and I am not too happy about my findings. Here are some of my findings:

  • The SQL Adapter is SLOW cause the Transaction level is Serializable

  • You CANNOT modify the SQL Schema’s

  • Schema Generation is cumbersome

Transaction level Serializable
This is a slow transaction level in SQL server. Causing a lot of unneccesary locks in the database. With one message everything works very nice. But when you submit about a 1000 messages at once each message causing some queries and some inserts you will see plenty of deadlocks and things will start to go wrong.

Well let’s say we have the SQL Receive function pickup some data from a SQL database… And let’s suppose we want to correlate on let’s say an order number. Ok we start the schema generation wizard…. ( don’t forget to modify the XML clause of the sproc ) and we end up with a nice schema. Let’s test the schema once ( don’t forget to change the for XML clause in the sproc again). After a while everything should be running smoothly ( although rather slow).
Then we decide we need to promote a field, or add a promoted property….. Everything falls apart. By adding the properties we also change generated SQL Adapter Schema. If the Schema is changed the SQL adapter won’t recognize the schema anymore…….So if you have to use the SQL adapter and correllate on that message as well you always have to use a map in the pipeline to map from the SQL Adapter format to a Internal format where you can define your promoted properties and correlation fields.

So if you have to do a lot of SQL stuff in your orchestrations write your own adapter.


Comments: 1

  1. Patrick Wellink says:

    You could face performance issues. the transaction level is serializable for everything the adapter does, so updating and inserting into the same table can lead to issues

Comments are closed.

  • Recent Posts
  • Recent Comments
  • Archives
  • Categories
  • Meta