I recently had a chance to investigate the new SQL Adapter that ships with BizTalk Server 2009 (as part of the BizTalk Adapter Pack) and wanted to highlight one of the most interesting features.
There are lots of things to love about the new adapter over the old one (now WCF-based, greater configuration, cross-table transactions, etc), but one of the coolest ones is support for SQL Server Query Notification. Instead of relying on a polling based solution to discover database changes, you can have SQL Server raise an event to your receive location when relevant changes are committed. Why is this good? For one scenario, consider a database that updates infrequently, but when changes DO occur, they need to be disseminated in a timely manner. You could use polling with a small interval, but that’s quite wasteful given the infrequency of change. However, using a 1-day polling interval is impractical if you need rapid communication of updates. This is where Query Notification is useful. Let’s walk through an example.
First, I created the following database table for “Employees.” I’ve highlighted the fact that when I installed the SQL Server 2008 database engine, I also installed Service Broker (which is required for Query Notification).
Once my table is in place, I want to next add the appropriate schema(s) to my BizTalk Server 2009 project. If you recall from using the BizTalk Adapter Pack in the past, you go to the “Add Generated Items” page and choose “Consume Adapter Service.”
The first thing you need to do is establish a connection string for accessing your database. The connection property window allows you to pick the server, instance, failover node, database name and more.
Once a valid connection is created, we can browse the database. Because I’ve chosen “Service” under the “Select Contract Type” drop down, I do not see the database artifacts in the “Select a Category” pane. Instead, I see operations that make BizTalk Server act as a service provider (e.g. polling) instead of a service consumer (e.g. select from table). I’ve chosen the “Notification” option.
The schema generated by this wizard is one of the most generic, non-contextual schema I’ve seen in a while. However, I’m not sure that I can fault BizTalk for that as it appears to be the standard Query Notification event schema provided by SQL Server.
Note that this schema DOES NOT have any information about which table changed, which row changed, or which data changed. All it tells you is that an event occurred in a database. During receive location configuration you can specify the type of data you are interested in, but that interest does not seep into this event message. The idea is that you take this notice and use an orchestration to then retrieve the data implied by this event. One big “gotcha” I see here is 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. So, I’d suggest generating this schema once, and throwing it into a BizTalk.Common.dll and having everyone else reference that in their projects.
Ok, let’s see what this event message actually looks like. After deploying the schema, we need to create a receive location that SQL Server publishes to. This adapter looks and feels like all the other WCF adapters, down to the URI request on the first tab.
The most important tab is the “Binding” one where we set our notification details. Specifically, I set the “Inbound Operation Type” to “Notification” (instead of polling), and set a notification statement. I’m looking for any changes to my table where the “IsChanged” column is set to “True.” Be aware that you have to specify a column name (instead of “select *”) and you must provide the database owner on the table reference (dbo.Employees).
After I built a send port that simply subscribed to this receive port, I changed a record in my table. The resulting Query Notification event message looked like this:
As you can see, by itself, this is a useless message. You require the knowledge of which receive port it came from, and what your notification statement was. I haven’t thought it through too much, but it would probably be nice to at least have a database or table reference in this message.
Now what if we want to do something with this event message? Let’s say that upon updates to the table, I want to select all changed records, update them so that they are no longer “changed”, and then publish that record set out. First, I walked through the “Consume Adapter Service” option again and chose a “Client” contract type and browsed to my “Employee” table and highlighted the “Select” operation.
From this wizard, I now get a schema with both a request message and a strongly typed response.
After distinguishing the fields of my Query Notification message, I created a new orchestration. I receive in the Query Notification message, and have a Decision to see if and update of the data has occurred.
If a “change” event is encountered, I want to query my database and pull back all records whose “IsChanged” value is “True”, and “Status” is equal to “Modified.” When using the classic SQL adapter, we had to constantly remember to flip some sort of bit so that the next polling action didn’t repeatedly pull the same records. This is still useful with the new adapter as I don’t want my “Select” query to yank messages that were previously read. So, I need to both select the records, and then update them. What’s great about the new adapter is that I can do this all at once, in a single transaction. Specifically, in my Select request message, I can embed an “Update” statement in it.
So above, I’m selecting “*” columns, and my “Query” node contains both my WHERE clause and UPDATE statement. In my orchestration, I send this Select request, get back all the records from the database, and publish that record set back out. I’ve highlighted the fact that my port operation name must match what’s expected by the adapter (in my case, “Select”).
Because the “Consume Adapter Service” wizard also generated a send port binding (which I’ve imported into my application), I can confirm that the expected operation name is “Select.”
So what does the result look like? I changed two records in my database table, which means a notification was instantly sent to my receive location, which in turn instantiated an orchestration which both selected the changed records and updated those same records. The final message distributed by my orchestration looked like this:
There you go. I’m a fan of this new capability as it truly makes our databases event driven participants in our environment instead of passive repositories that require explicit querying for changes of state. While I don’t think I’m a fan of the generic schema, I suspect the best pattern is to take that generated event message, and, using what we know about the notification statement, republish a new event message that contains enough context to be actionable by downstream consumers.
What do you think? Is this interesting or will you stick with straight polling as a database access method?
Technorati Tags: BizTalk
Good overview Richard! I’ve become very aware of the great capabilities of SQL Server Service Broker recently. Having query notifications integrate with BizTalk this easily will be very useful.
Another cool post around this is from the team building the adapter:
another fantastic post, can you pls let us know where can we download the WCF LOB adapters for biz 2009, the SQL adapter that you used in this article.
You should be able to grab both the BizTalk beta and Adapter Pack V2 beta from here … https://connect.microsoft.com/Downloads/Downloads.aspx?SiteID=218
Can this be used with BizTalk 2006 R2 & SQL Server 2005?
Pretty sure that query notification is part of SQl 2005, but I’m not sure if the adapter only works with the bits in SQL 2008.
Yes it can be used in 2006 R2/2005 tandem. Just make sure to grant service account that adapter is running under rights to create SSB queue (underneath notification uses SQL Service Broker)
Great post Richard! I have created a link off of my blog to this post as I can see this being ‘the way’ we scoop data from SQL Server in the future.
How would you use Inbound Notification in a WCF Service outside of BizTalk?
I have a WCF service that consumes an external web service. I was able to add the SQL Adapter Service with Notification. How can you consume the Notification, Get data from the Database and send the data to the external web service?
Can you help I keep getting this problem
The Messaging Engine failed to add a receive location “WcfReceiveLocation_SqlAdapterBinding_NotificationOperation_Custom” with URL “mssql://vm-dev01//BizTalkSOA?” to the adapter “WCF-Custom”. Reason: “System.Data.SqlClient.SqlException: The specified schema name “dbo” either does not exist or you do not have permission to use it.
when trying your SQL Notification example in your book.
It is happening when I try and enable the receive location, I have tried with/without the the DB Owner dbo in the TSQL statements. I have enabled service broker on the BizTalkSOA DB and granted various permissions e.g. CREATE QUEUE,CREATE CONTRACT etc gleamed from browsing articles via the web, but can’t get it to work. All the other SQL examples from you book work, just not this one.
I am using SQL2008 ON WS2008 and BTS009.
Watched your webcast on ESB 2.0 today excellent stuff.
Flattery will get you everywhere.
Seriously though, I’ve pulled up my virtual machine hosting all the book bits. I’ve got broker turned on for the database, and think I reviewed some of these steps (http://msdn.microsoft.com/en-us/library/ms172133.aspx) to ensure the right permissions. My receive locations runs under a host that has sysadmin permissions on my database. That may be why I don’t remember having to set explicit rights for my user in the DB …
An issue I’m having now is that it seems the WHERE clause in the notification statement is ignored. I’m getting two SQL notifications. One for the change event I want and a 2nd when I update the ischanged field to false when grabbing the data. Are you getting two notifications as well? Wondering if I need to code around this somehow by ignoring the query results that return no data because ischanged is false, or debug SSB query notification. Thanks!
I want to say that I saw that same behavior. Since you’re listening on the change of a particular scenario, you ALSO get a note when it changes from the WHERE to something else.
what am i doing wrong if i don’t see anything in the “Available categories and operations” box? mine is empty
Do you see anything for the “client” contract type, or is it just empty for the “server” one? Even if you click the root node in the client tree?
Great article, as well as your book “SOA Patterns with BTS 2009”!
I have a couple of questions:
1: (Since I’m not strong in the SQL Server side of things) What steps are there (if any) on the SQL Server side to use this?
2: What problems might I run into if I need Query notification from changes to different tables (like orders, payments, vendors, etc)?
Thanks much in advance!
I have been using WCF-SQL Notification for a while in our test servers and have noticed a thread & memory leak problem. I have 2 notification receive locations. Using the Win2008 Performance MMC & monitoring the BizTalk:Message Agent Thread count & Process Memory, I see both increment every 5 minutes even when absolutely nothing is going in/out of BizTalk. As soon as I stop the receive locations the threads & memory get released. This is leading to a “no threads in threadpool” error and BizTalk throttling itself due to lack of memory. It would be great to know if someone else can monitor their WCF-SQL Notification deployments and confirm this observation. So a warning to any persons deploying this in production – monitor your BizTalk carefully!
Interesting. Thanks for the heads up!
You need SQL Server Broker installed and running, and yes, as you get into more complicated, related tables, query notification may be a challenge. I haven’t honestly dug into too many of those cases, but, you could have a notification off of one table (e.g. orders), and when an orchestration receives that notification, it goes and pulls data from multiple tables that would have been impacted by that order being added.
For the tables I’m interested in, I have triggers that populate a common notification table with enough key data to refer back to the original table. Query notification is set up on that notification table, and my BTS notification handler orchestration then retrieves the row(s) from the notification table and dispatches the appropriate message(s) to the corresponding BTS application(s).
Not a bad idea. Good thinking.
I’m trying to discern the best way to get query notifications from SQL Server to discover database changes in the BizTalkMgmtDb, when administrators/IT-Pros makes changes to the various artifacts and their properties in the different biztalk applications via the admin console.
When changes occur to the BizTalkMgmtDb I need to be notified about the change (that an event occurred in a database) and then I need to make a query that identifies which application that was changed + which artifacts and properties that was changed within that application (which table changed, which row changed, or which data changed), by an admin person via the console.
I need to do this in order to update a configuration and change management system in our enterprise, but Im not sure how to do this in the best way, since all the different artifacts in an application has its own table in the BizTalkMgmtDb, which makes a query quite complex (and im not good at SQL queries).
Do you have any thoughts or clever ideas on how to go about this challenge?
MP, that’s an interesting scenario. Not sure if using an adapter on BizTalk configuration databases is the best idea. I take it that a business process or single change registry wouldn’t work? Thinking like a SharePoint list with approval workflow before making changes.
Without a key field in the notification I think query notification is worse than useless, its dangerous.
I did 28 inserts to a table, not in a transaction. I got 4 Notification messages. If these 4 notification messages would have kicked off 4 orchestrations, they all would have tried to grab the same records from the database. I would have gotten duplicate results.
SQL Server Notification Services (SSNS) are discontinued in SQL Server 2008. So implementing this scenario is not advisable. Microsoft is very quiet about this.
SQL Server Notification Services (SSNS) and Query Notifications are different technologies and Query Notifications does not going away.
So this scenario is not affected by SSNS deprecation in any way.
If you get an error like:
“Microsoft.ServiceModel.Channels.Common.XmlReaderGenerationException: The columns column1 and column2 are either duplicated or not in a sequence. Each column can only be selected one time, and columns must be selected in sequence.”
when you try to use the WCF SQL adapter to select stuff from a table, check the casing of the objects in your sql statement. This is because for some sql collations the column/table names are CASE SENSITIVE.
I’m trying to implement the same “SQL Query Notifications” in BT 2010 project with the notifications set up on table in SQL 2005 database. But, whenever I enable the Notification Receive port, I get numerous notifications that doesn’t even satisfies the “Notification Statement” configured within the WCF-SQL Receive Location.
Here is the sample of the Notification XML that I receive
I found the following post http://social.msdn.microsoft.com/Forums/en-US/biztalkgeneral/thread/034a0c7b-443d-4c64-bc0c-f5390885672e/, but the database in question is configured to be SQL 2005 compatible.
Any thoughts on what might be wrong?
The XML tags got stripped off in my previous post.. Here are the values for the Info, Source, Type nodes within the Notification
Info = Query
Source = Statement
Type = Subscribe
I should also add that the Notifications work fine if I use a 2008 R2 database.
Tough one. I was wondering if 2005 is supported, but it is. May want to try asking on the BizTalk forums http://social.msdn.microsoft.com/Forums/en-US/biztalkr2adapters/threads
We are implementing sql query notitifcation approach in few of our interfaces. It is giving expected results if there is single receive location implementing the notification. The issue started after i had multiple receive locations implenting this.
The notification query checks on a row basis. There are 3 rows in a custom table BiztalkNotification and 3 receive locations poll the table using appropriate filters so that they listen on any changes done to the particular row.
But if i enable all 3 receive locations, I get the error as below and the receive location gets disabled.
Messaging Engine failed to add a receive location “rloc_XXXXXXXX” with URL “mssql://xxxx/yyyyyy/zzzzz?InboundId=UpdateNotiifcation” to the adapter “WCF-Custom”. Reason: “System.InvalidOperationException: The ChannelDispatcher at mssql://xxxx/yyyyyy/zzzzz?InboundId=UpdateNotiifcation’ with contract(s) ‘”IOneWayAsync”‘ is unable to open its IChannelListener. —> System.InvalidOperationException: SqlDependency does not support calling Start() with different connection strings having the same server, user, and database in the same app domain.
at System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults)
This does not happen always. It comes intermittently. Sometimes, it allows all receive locations to be enabled and sometimes it doesnt.
Did you face anything like this ?
What i really do not like about the SQL Query notifications is no notification is received for records that have changed/inserted while the receive location is down once it is up. (http://msdn.microsoft.com/en-us/library/dd788506(v=BTS.10).aspx) Is there a workaround for this?
You could set the NotifyOnListenerStart = true to receive a notification informing that the receive location is available, as soon as the receive location recovers.
I configured the transport to use Ambient Transaction and this technique seems to work, but Biztalk sometimes trigger a warning on event viewer:
System.Transactions.TransactionException: The operation is not valid for the state of the transaction.
Should I explicitly use a transaction in the orchestration?
Can you please share the Code for the example above.
I am havign trouble understanding the Map part here.
Hi there. I don’t have the code available anymore. That said, the role of the mapping there was to query the database after the query notification has told us that a message (or messages) are ready for polling.
I have a Biztalk application configure to use SQL Notifications; the first time I tested it work nicely. I got a notification when a record was changed on the db. But now I moved the application to another BizTalk server and when I start the application I start to get SQL Query notifications even nothing has changed in the SQL DB. Any ideas?
Hey Richard, I’ve followed both this example and the one from your book SOA Capabilities, both great, many thanks but…………………. In both examples the notification ignores the where clause and returns a result for both the update that sets the status to ‘pending’vand the update that sets the status to ‘read’?
I’m pretty sure that I’ve followed both to the letter, any ideas?