WCF polling adapter

This post shows how to Consume a BizTalk WCF-Custom adapter with sqlbindings to create a polling mechanism from a SQL table.

A bit about polling.

In general I am a big fan of polling from SQL, compare to having a receive port within a listen shape and a delay, which will cause dehydration. In cases of a short time correlation when the response is expected in a fairly short time a listen shape is a good solution especially if we might not be notified of an exception happening later on or when implementing scatter gather patterns, but if waiting time can exceed minutes then “waiting in the DB”, to my opinion, is the correct approach.

I did come upon solutions with messages that could wait for a correlation response from an external system using a delay shape for month, which resulted in thousands of dehydrated messages, that stuffs up the message box, it’s hard to keep track of the messages and resolve failure scenarios, not to mention the overhead on the entire system.

The benefit of waiting in the DB and polling when a state was reached reduces message box overhead and hands a lot of control over the message in the sense of knowing what state a process is in, having the ability to store information from the message in additional columns (assuming for example the message is serialized to an XML column and key fields are saved in additional columns) and the obvious ability to query those fields, being able to initiate polling only when several conditions are met(for example values or rows form other tables), and easy to amend rules having the polling logic in a stored procedure.

There are several polling techniques available in the 2009 WCF-custom Adapter with SQL bindings as Polling, TypedPolling, XMLPolling and notification can be found here.

In this post I will show polling using the XMLPolling type.

A look in to the stored procedure:

      --how many messages in each batch
      declare @BatchSize int = 5;
      declare @tblIDs table (BatchID int)
      declare @BatchCount int = 0;

    insert into @tblIDs
     select top(@BatchSize)  BatchID from Batch WITH(NOLOCK) where Operation = @Operation and Processed = 0

      IF (select count(BatchID) from @tblIDs) > 0
      BEGIN
            update Batch set Processed = 1 where BatchID in(select BatchID from @tblIDs)
            set @BatchCount = (select count(BatchID) from @tblIDs)
      END

      declare @xVar XML
      SET @xVar =
      (
            select [xml] from Batch where BatchID in(select BatchID from @tblIDs)
            FOR XML AUTO
      );

      WITH XMLNAMESPACES ('http://BizTalk/BatchsMessages' as ns1)
       Select @Operation as Operation, @BatchCount as BatchCount ,@xVar as 'ns1:xmlMessage'
      FOR XML RAW('ns1:BatchsMessages')

The schema:

Use the xmlschema command to generate an XSD, use this XSD schema in the BizTalk project as the polling schema.

You need to change the import schemaLocation=”sqltypes.xsd” in the generated XSD to point to a local XSD schema, you can get it here.

Create a schema Envelope point the body XPath to the root element as shown below:

clip_image001

The properties of the schema and the root element should look something like this.

clip_image001[7] clip_image001[9]

PipeLine:

Create a receive pipeline and add a dissembler component, add the pooling schema to the Document schemas (collection) and the envelope schema to the Envelope schema (collection).

This should strap the wrapper element the adapter will add to the message.

Orchestration:

In the orchestration create an activate receive port specified to receive a messages of the pooling schema type.

clip_image001[12]

The port message type should be set to the polling schema type.

clip_image001[14]

Physical port binding:

Create a WCF-Custom receive location with sqlbinding as shown bellow.

clip_image001[16]

The root node and namespace must match the ones on the envelope schema, the pipeline will strip off that root element passing in to the orchestration the polling schema.

Attach the pipeline to the receive location.

Note you must have some select statement or a SP call in the polledDataAvailableStatement property, should be some sort of count, if the result from that statement is >0 a call is made to the next statement ”pollingStatement” (tip: you can initiate polling by specifying “select 1”).

You are good to go .

A walk through how to create XMLPolling WCF custom with SQL binding can be found here.

Advertisements

Monitoring BizTalk Performance Counters for throttling

In occasions when a BizTalk server has a big load to process often we will notice every thing slows down and it seems BizTalk is not processing any more messages.

This happens when a throttling state was achieved, and BizTalk will stop processing messages, In my case the Message delivery throttling state had the value of 3 meaning “Throttling due to high in-process message count” we had too many messages going in, allowing the host to use more memory(see below) solved the problem for the amount of messages that where input to BizTalk.

To monitor BizTalk throttling states we can use some performance counters, to add the counters:

go to:  Performance monitor -> BizTalk:MessageAgent

The description for each of the performance counters can be found here msdn.

Tip: on 64 bit machines we can allow the host to use up to 50% of the memory on the server that will allow BizTalk the ability to have a higher throughput.

On the host configuration click advanced -> throttling trash hold -> Process memory usage (on 64 bit can increase to 50%)