BizTalk Adapter Pack v2 + SQL Query Notifications

I ran into a great post by Richard Seroter that discusses the new capabilities of Query Notifications in BizTalk 2009 when using the BizTalk Adapter Pack.

Query Notifications allow BizTalk to listen for changes that occur on the SQL Server that you are interested in receiving updates from. In previous versions of BizTalk you would usually attack this scenario with a Polling Receive location and then update the records that you have just processed or store a timestamp of the last time that you polled. This would allow you to only process new records.

Polling is nothing new to BizTalk scenarios. Many adapters support polling including POP3, FTP, File and Database adapters. Other adapters will listen for events, or messages to be raised and then BizTalk will process the message. These adapters include SAP, SOAP and HTTP.

In many Database scenarios you may poll every minute and only retreive information periodically. No data may have changed since the last polling attempt. This is wasting resources as you are constantly connecting to check if there is any work for BizTalk. By using this Event driven model, BizTalk will receive a notification that there is work for it, but the initial notification will not contain the data that is changed. BizTalk will then need to make a connection to see what records have changed.

BizTalk will still need to manage which records have been processed. However, BizTalk is able to execute a Select statement to find the updated records and update them all in the same transaction so that they will not get processed again. See Richard's post for more details.

So if you have not had a chance to walk through Richard's blog post I highly reccommend that you do. Not having used the SQL Service broker before I did have a few problems when I started up my sample.

The Messaging Engine failed to add a receive location "WcfReceiveLocation_SqlAdapterBinding_Notification_Custom" with URL "mssql://btsserver//BizTalkSQLNotify?" to the adapter "WCF-Custom". Reason: "System.InvalidOperationException: The ChannelDispatcher at 'mssql://btsserver//BizTalkSQLNotify?' with contract(s) '"IOneWayAsyncTxn"' is unable to open its IChannelListener. ---> System.InvalidOperationException: The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications.

If you receive the preceding error you can follow these steps to enable your Service Broker for the database that you want to listen for notifications. Run the following commands (at your own risk) in SQL Server Managment Studio

SELECT is_broker_enabled FROM sys.databases WHERE name = 'your_database_name'

If the value returned is 0 then your broker is currently disabled. Continue with the following steps:

ALTER DATABASE your_database_name SET NEW_BROKER WITH ROLLBACK IMMEDIATE

ALTER DATABASE your_database_name SET ENABLE_BROKER

You should now be able to run this SQL Statment and have the result be '1'. This will indicate that the broker is in fact enabled.

SELECT is_broker_enabled FROM sys.databases WHERE name = 'your_database_name'

The next error that I received was this one:

Service Broker needs to access the master key in the database ''. Error code:26. The master key has to exist and the service master key encryption is required.

To resolve this issue I rant the following command:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your_password'

This password can be anything, you it is not your SQL Server password.