Query notification and the BizTalk WCF SQL Adapter: Comments and unresolved questions

I work with a architect who has an obsession about not allowing BizTalk to poll SQL servers. We have tried many times to cure their disorder.

One of the solutions that we looked at was SQL query notification as described below;

During my investigation I set up a simple example with a table called Q.

After setting up service broker and the WCF SQL adapter schemas my example was working in quick order and we were not polling the database. I used the following notification statement;

SELECT DataId FROM dbo.Q WHERE Status = ‘Pending’

The first thing my architect friend did not like was the two new objects that where created in the database namely SqlQueryNotificationStoredProcedure-e3acc292-4c92-4d02-8aad-58ce8a4db0ab and the SqlQueryNoficationService. Why do have have to have these horrible names in my database they said? You really can’t please some people. His comment was interesting because each time you stop the SQL notification receive location these objects disappear from the database. Once you restart the SQL notification receive location they are created with different GUIDs. This might be a problem for some databases because the BizTalk host user requires permissions on the target database to be able to create(and delete) stored procedures on the database. I used a sledgehammer and granted my BizTalk host user db_owner role on the TestQ database.

The next gripe was that if you delete a row from table Q you don’t get a notification message. Indeed you only get a delete message if you delete a row that is in status = ‘Pending”. If I removed the WHERE clause then i get all deletes as well.

Richard Seroter highlighted that the notification message “DOES NOT have any information about which table changed, which row changed, or which data changed. …” and furthermore “…that the target namespace is not related to the target database. This means you can you can only have one instance of this schema in your environment in order to avoid encountering collisions between matching namespace+root schemas. “. This means that you have to have some mechanism to get all the rows that have changed, do something with the data and then make sure that you don’t pick up the changes again the next time you get a notification message. I decided to explore using change tracking to work out what has changed.

SQL Server 2008 introduced change tracking, a lightweight solution that provides an efficient change tracking mechanism. To enable change tracking I ran the following scripts;

ALTER DATABASE TestQ SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

ALTER TABLE TestQ.Q ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

Now to get all the changes when a notification message is received I created the following stored procedure;

CREATE PROCEDURE getDataThatHasChanged

@last_sync_version bigint
AS
BEGIN

SET NOCOUNT ON;

SELECT q.Data,q.Status, c.DataId,c.SYS_CHANGE_OPERATION

FROM CHANGETABLE (CHANGES Q, @last_sync_version) AS c

LEFT OUTER JOIN Q AS q

ON q.DataId = c.DataId;

–get the maximum version to be passed in as last_sync_version next time

SELECT CHANGE_TRACKING_CURRENT_VERSION()AS next_sync_version

END
GO

I ran the WCF SQL adapter wizard to create following schema that will contain all the changes that have occurred since the last notification message was received.

I created a singleton orchestration that is started with last_sync_version = 0. This orchestration subscribes SQL query notification messages from the Q table.