What you need to know about the BizTalk WCF-SQL adapter

Introduction

As a BizTalk consultant, it is always important to know the product and its features inside out. Knowing the cards you have to play with when designing a solution, gives you an advantage as you know what you can expect in certain situations.

The WCF-SQL adapter is certainly one of the better known adapters out there, so why decide to write yet another piece on it? There are already plenty of blog posts out there, yet I often find myself double-checking certain behaviors and I wanted to write something so I could refer to it at a later stage and help some people out less experienced.

I know there already is an excellent article up on TechNet around this topic, called Typed Polling with WCF-SQL Adapter: Best Practices and Troubleshooting Tips, but the article here will try to add some extra to that.

The adapter

The WCF-SQL adapter allows to read and write data from/to Microsoft SQL Server databases. It comes in two varieties: the plain WCF-SQL adapter and the WCF-Custom adapter using the sqlBinding. Not much difference there, although I tend to prefer the WCF-Custom adapter because it offers more flexibility and configuration options. Therefore, in this article, I will always use the WCF-Custom one.

WCF-SQL Binding properties

sqlBinding

The above screenshot is from a WCF-Custom transport properties screen and lists all of the properties available.

The ones I will be discussing, are the following:

  • polledDataAvailableStatement
  • pollingStatement
  • pollWhileDataFound
  • useAmbientTransaction

They pretty much speak for themselves, but they tend to influence each other as well and that is where I’ll focus on in this article.

Laying the foundation

Let’s create us a test scenario to start with: a table, some stored procedures, and some data. I tried to keep this as simple as possible:

First up is the table:

CREATE TABLE [dbo].[PollingTable]
(
  [Id] [int] IDENTITY(1,1) NOT NULL, 
  [PollingData] [nvarchar](50) NOT NULL, 
  [Status] [int] NOT NULL, 
  [DateTimeModifiedUTC] [datetime] NULL, 
  [DateTimeCreatedUTC] [datetime] NOT NULL, 
  CONSTRAINT [PK_PollingTable] PRIMARY KEY CLUSTERED  ( [Id] ASC )
)
GO

ALTER TABLE [dbo].[PollingTable] ADD  CONSTRAINT [DF_PollingTable_Status]  DEFAULT ((0)) FOR [Status]
GO

ALTER TABLE [dbo].[PollingTable] ADD  CONSTRAINT [DF_PollingTable_DateTimeCreated]  DEFAULT (getdate()) FOR [DateTimeCreated]
GO

Next, the stored procedures to call, for the DataAvailable and PollingStatements:

CREATE PROCEDURE usp_WCFSQLAdapterDemo_DataAvailable
AS
BEGIN 
  ----------------------------------------
  SET NOCOUNT ON; 
  ----------------------------------------
  SELECT
    COUNT(1) 1
  FROM 
    [dbo].[PollingTable] 
  WHERE 
    [Status] = 0 
  ----------------------------------------
END
GO

and

CREATE PROCEDURE [dbo].[usp_WCFSQLAdapterDemo_Polling]
AS
BEGIN 
  --------------------------------------------------------
  SET NOCOUNT ON; 
  --------------------------------------------------------
  DECLARE @IdTable TABLE (Id INT) 

  UPDATE 
    [dbo].[PollingTable] 
  SET 
    [Status] = 1, 
    [DateTimeModifiedUTC] = GETUTCDATE() 
  OUTPUT  
    inserted.Id 
      INTO @IdTable 
  WHERE 
    Id IN 
    ( 
      SELECT
        TOP (1)
          Id 
      FROM 
        [dbo].[PollingTable] 
      WHERE 
        [Status] = 0 
      ORDER BY 
        [DateTimeCreatedUTC] ASC 
    ) 

  SELECT 
    [Id], 
    [PollingData], 
    [Status], 
    [DateTimeModifiedUTC], 
    [DateTimeCreatedUTC] 
  FROM 
    [dbo].[PollingTable] 
  WHERE 
    Id IN 
    (
      SELECT 
        Id 
      FROM 
        @IdTable
    ) 
  FOR
    XML AUTO, ELEMENTS
  ---------------------------------------------------------
END

And finally, some data to be added:

INSERT INTO 
  [dbo].[PollingTable] 
  ( 
    [PollingData]
  ) 
  VALUES 
  ( 
    'MyData'
  )
GO 10

Note: if you are unfamiliar with the syntax ‘GO 10’: the statement will execute the insert statement before it, but 10 times.

You should now have ended up with the following data:

pollingTable_result

Note: we are only doing a TOP (1) from the table at any given time, to simulate records coming in. I’m aware that a TOP 100 or something similar is much better and faster, but this exceeds the purpose of my demo.

Testing the foundation

Note: to make it easy for you, I supplied the binding file of the demo for download.

Make sure, you set up a Send Port, in order to catch any messages coming in from your receive locations that we are about to make. Then create a new receive port and receive location. The adapter of the new receive location should be the WCF-SQL adapter and point it to your local database, where you just created your table.

Fill in the new data for the PolledDataAvailableStatement and PollingStatement, like so:

foundation_settings

  • inboundOperationType is set to XmlPolling, which allows you to create strongly typed schemas from the output of your PollingStatement.
  • polledDataAvailableStatement is the name of the stored procedure we created earlier.
  • pollingIntervalInSeconds is the number of seconds between polling. As you will see later on in this articlle, this depends on the configuration.
  • pollingStatement is the name of the stored procedure we’ll use for the polling.
  • pollWhileDataFound specifies whether the SQL adapter ignores the polling interval and continuously executes the SQL statement specified for the PolledDataAvailableStatement binding property if data is available in the table being polled. If no data is available from the table, the adapter reverts to execute the SQL statement at the specified polling interval.
  • useAmbientTransaction specifies whether the SQL adapter performs the operations using the transactional context provided by the caller application.

Once you set this up, start your send port and enable your receive location.

You should end up with something similar in your table:

foundation_run

The DateTimeModifiedUTC column indicates in this case, that the polling stored procedure ran every 10 or so seconds.

If you go further and take a look with SQL Server Profiler, you’ll see the following result:

foundation_profiler

  • In the first seconds, no records were available.
    Then, I inserted the records by executing the insert statements.
  • Every 10 seconds the PollingDataAvailableStatement was executed.
    Since data was found, the PollingStatement was executed straight after that. Only to repeat later on, after the 10 second delay.
  • When no records are available anymore, the PollingStatement is no longer triggered.

You can setup Profiler for yourself, in the above case, by using the following settings:

foundation_profiler_trace

The above scenario gives us the following schematic overview:

foundation_schematic

Variant 1: PollWhileDataFound = True

Setting the PollWhileDataFound property to True, gives us the following results (after resetting the table to it’s original state):

pollwhiledatafound_results

And via Profiler, this gives us the following:

pollwhiledatafound_profiler

You can clearly see that:

  • When new data is found, the PollingStatement is executed immediately, just like normally.
  • However, straight after the PollingStatement, another PolledDataAvailableStatement is triggered immediately, to check if more data can be found. If that is the case, the PollingStatement is triggered immediately. This results in a much more responsive/performant system.

This concludes to the following schematic overview:

variant1

Variant 2: UseAmbientTransaction = false

This time, we are resetting the pollWhileDataFound property to false and setting useAmbientTransaction to false as well!

The UseAmbientTransaction property specifies whether the SQL adapter performs the operations using the transactional context provided by the caller application (BizTalk). The default is true, which means that the adapter always performs the operations in a transactional context.
If there are other resources participating in the transaction, and SQL Server also joins the transaction, the transaction gets “elevated” to an MSDTC transaction.

Important: This means that if you disable the transaction for your polling, any changes you are doing in your PollingStatement will not be rolled back in case there is an issue! This can definitely cause some issues if you’re not working in an atomic way!

The results might be expected:

useambiantfalse_results

However, the profiler run might not be as expected:

useambiantfalse_profiler

You can see, the PollingDataAvailableStatement is no longer executed!

Why?
By disabling the PollingDataAvailableStatement, Microsoft is making sure that any logic you might have put in there, is not executed. The PollingDataAvailableStatement might be (ab)used to perform some kind of updates or clean-up and when no longer being able to run in a transactional context, this would definitely pose issues. By disabling this option, Microsoft makes sure this cannot happen.
You might be tempted to perform the same in the PollingStatement however…

The above behavior can be seen in this schematic:

variant2

Variant 3: PollWhileDataFound = True, UseAmbientTransaction = False

This is an expected result, but still nice to have it confirmed:

variant3_results

And in Profiler:

variant3_profiler

As you can see, the PollingStatement is executed straight after each other, until no more data is found.

The schematic:

variant3

Conclusion

The WCF-SQL adapter covers a lot of potential scenario’s you might encounter. There are however some things you need to know before changing some of it’s sqlBinding properties.

I hope this was useful, let me know in the comments if you have any feedback.

Have a nice day!
Pieter

1 thought on “What you need to know about the BizTalk WCF-SQL adapter

  1. This is a great article. It provided me with the info I was looking for.Thank you for such a detailed explanation on this topic ! =)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s