SQL Server Query Notification with BizTalk Server 2009 WCF SQL Adapter

Introduction

SQL Server 2005 introduced query notifications which allow applications to subscribe to the database and receive notifications from the database based on the changes in the result set of the query on which the application is subscribed. This can change the behavior and performance of the application as the application does not have to query the database in order to get changes. For e.g. if a service/application has cached the data, it can refresh its cache whenever there is a change in the results of the cache data. In this way, efficiently and in real time, the data can be refreshed.

With regard to BizTalk, previously we only used polling (using the SQL Server adapter) in order to get the results from the database. Polling would be a heavy operation depending on the polling interval and the results being returned which would affect the overall BizTalk server performance. But now by utilizing the Query notification feature of SQL Server 2005/2008, BizTalk server can receive notifications whenever there are changes in the result set of the query. For further reading, you can read Using Query Notification on MSDN. Before planning to use the SQL Query notifications using the WCF adapter, please go through Considerations for Receiving Query Notifications Using the Adapter on MSDN.

Generating Schemas from Consume Adapter Service Wizard

For using Query notifications in BizTalk, the first step is to use the Consume Adapter Service Wizard to generate the schemas. You can start the wizard by Right click your project->Add Generated Items -> Consume Adapter service. In order to use the SQL WCF service, you have to select sqlBinding from the wizard and supply the SQL URI. Please refer to SQL Server Connection URI on MSDN. Click the configure button and configure the mssql URI.

1. In the Security tab, choose the credential type (windows/username) and supply the username/password if using SQL Authentication.
2. In the URI tab, supply the URI properties. Supply the database name in the InitialCatalog property, SQL Server Instance Name and the SQL server name/IP in the Server property. The inbound id is used for typed polling and it makes the URI unique.
3. In the Binding properties, go to the Inbound property group and set the InboundOperationType property to Notification. For complete binding properties, read Working with BizTalk Adapter for SQL Server Binding Properties on MSDN.

   

4. Since you are using polling and you have set the Inbound operation type to Notification, you will set the Notification properties in which Notification Statement is specified. Notification statement is the query based on which notifications will be received by the adapter. Whenever there will be changes in the result set returned by the query, SQL notifications will be sent. For complete reference for creating Query notification, read Creating a Query for Notification on MSDN. In my case, the query was ”Select [columns] from MAR_SP_INFO_V2”.

   
   
   In the end, two files will be generated. One is the simple schema with three fields as shown below and the other is a binding file XML.

   

Setting Up the Orchestration for Query Notification and Processing the Results

When the notification is received in the orchestration, the orchestration has to determine the type of notification as the WCF adapter will return two types of notifications:

1. Notifications based on the changes on the result set
2. Notification when receive location was enabled after a failure

The adapter will send notification whenever the receive location is backed up again when NotifyOnListenerStart is set to true in the binding properties. But beware that the adapter does not perform any activity when the receive location is down and there are changes in the database. The adapter will start notification after the receive location is up again. For e.g. when the receive location was down and a few records were inserted and updated, when it will come up again, it will not notify what had happened. The orchestration must have an implementation to determine the changes. For this, you can read Receiving Query Notifications After a Receive Location Breakdown on MSDN.

The schema that was generated from the wizard will have three fields Info, Source and Type. In the orchestration, the first step would be to have a decide shape to decide which type of notification was received by the orchestration. I decided to distinguish all the three fields so that I could use them in my orchestration. If you do not, you can use xpaths to extract the value of the fields.

In the decide shape, first check the Info field and Source. If the Info is “ListenerStarted” and Source is “SqlBinding” and Type is “Startup”, you can proceed to the logic to detect changes to the database while the receive location was down. If the Info is “Insert/Update or Delete” operation, the Source would be “Data” and Type would be “Change”.

In my orchestration, I am doing nothing for receiving Listener start notifications or for Updates and Deletes, and I am only interested in taking actions against the insert operation in the table. Therefore, I am checking this in my decide shape. I will devise some mechanism to check if my receive location went up after going down what shall I do.

For now, I need to get the new records which are inserted and process them. I am using the WCF SQL Adapter and selecting all the records whose StatusRecord field is set to NEW. NEW is the default value for a new record that is inserted for me to identify the records. I will write in detail in my next post how I am using the WCF SQL Adapter for selecting the records. When I select the records and finish processing them, I update the StatusRecord column to READ.

I pass the whole select message response to my helper class where all the processing is done and if the operation is successful, I log the results.

Configuring the WCF Custom Adapter Properties for Notification

There are again two ways to configure the adapter properties, first you can redefine the properties here or you can directly import the properties from the binding file which was generated by the WCF adapter service wizard. For that, you can refer to Configuring a Physical Port Binding Using a Port Binding File on MSDN. I haven’t looked into it but will use when needed.

I will have manual bindings for the WCF Custom adapter. When finished with the orchestration, you have to build and deploy the BizTalk application. Then from the BizTalk administration console, open the receive port node and create a new receive port. Then create a new receive location. Select the type as WCF-Custom and use the default XML Receive pipeline. Click the configure button to configure the adapter properties.

In the general tab, specify the address URI. You can copy paste that from the Binding file generated by the Consume Adapter Service wizard.

In the Other tab, specify the username and password for the database. Otherwise, you will get user credential error.

Now for the Binding properties, go to the Binding Tab and select sqlBinding as the Binding Type. You will see all the binding properties below. We will be interested only in the notification binding properties. Set inboundOperationType as Notification, set the notificationStatement property to the SQL Query. On the basis of this query result set, a notification will be sent to the Orchestration. And notifyOnListenerStart property to True if you want to receive the notification when the receive location is enabled. In my case, it is false.

History

• 13^th April, 2010: Initial post