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 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
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:
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
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:
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:
- 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:
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:
- 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:
The above scenario gives us the following schematic overview:
Variant 1: PollWhileDataFound = True
Setting the PollWhileDataFound property to True, gives us the following results (after resetting the table to it’s original state):
And via Profiler, this gives us the following:
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:
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:
However, the profiler run might not be as expected:
You can see, the PollingDataAvailableStatement is no longer executed!
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:
Variant 3: PollWhileDataFound = True, UseAmbientTransaction = False
This is an expected result, but still nice to have it confirmed:
And in Profiler:
As you can see, the PollingStatement is executed straight after each other, until no more data is found.
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!