Problems with the BizTalk SQL Adapter......

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.

Schema's
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.
 

 

Published 12-21-2004 2:44 PM by Patrick Wellink
Filed under:

Comments

# re: Problems with the BizTalk SQL Adapter......

Thursday, December 23, 2004 12:59 PM by Patrick Wellink
Hi Patrik,

I agree with you on the performance issue, the SQL adapter is not too quick when it's doing lots of updates. I think this is more due to the request and response messages passing through the message box than the isolation level (I could be wrong though).

To reduce deaslocks with multiple calls, one option is to override the serailazation level in the stored procedure (if you are using SPs). This has helped me a couple of times.

As for the performace, adapters are usually used to communicate with external systems. They use the tracking, and the configurable ports, and retry on failure, which is great if the SqlServer is an another location and the connection is unrelyable. I have started building a C# data access layer when I need quick database access to a local server. It beats the adapter when it comes to performance and error handling.

Regards,

Alan

# re: Problems with the BizTalk SQL Adapter......

Thursday, December 23, 2004 1:20 PM by Patrick Wellink

I tried overriding the isolation level but it didn't really work for me.

I created an Adapter that handles Send calls to SQL and it greatly outperforms the SQL Adapter in all ways, and almost everything is supported.

I am working on a receive adapter now.

# re: Problems with the BizTalk SQL Adapter......

Saturday, February 03, 2007 2:34 PM by Hebron

Could you share your ideas on how you wrote a custom SQL Adapter for both Send and receive operation?

I have a legacy application which I'm porting to use BizTalk 2006. I am researching now on what performance and scalabiltiy gains I can expect. I read on several newsgroups that SQL Adapter is slow. Does this mean if I were to use a SQL Adapter to read in data from a databuffer (SQL Table) to my business process (BizTalk), I would face performance issues?

# re: Problems with the BizTalk SQL Adapter......

Monday, February 05, 2007 7:21 AM by Patrick Wellink

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