Query Notification & Service Broker

Do you ever encounter a system requirement which require you to periodically monitor a database table to check if there is any data change within it, then you need to perform some code work?

For example, the requirement is to call a web service when there is any data change in the database. What would you do in this scenario?

Option 1:

Are you going to use Trigger? When there is a record being inserted or updated, you write some code logic in the Trigger to perform some work and then call a web service? However, you do realize that code is restricted to the database area only. I mean, you are only able to write T-SQL to perform the work including calling a web service which is not possible.

Option 2:

However, there is an alternate way to do it by using SQL CLR to perform the work and calling a web service with C# code. Later, I realize that the web service client require System.ServiceModel.dll reference, and this assembly is not available in the SQL Server. Therefore, in order to make this work, I need to configure my SQL Server to grant permission to my SQL CLR Trigger to be able to call unsafe assembly and also making my SQL Server to trustworthy. However for this case, some companies rules do not allow us to change any database setting, we have to skip this idea.

Option 3:

When Trigger is out from the options, we have to do database table polling, which mean every second or every specific time interval, I am going to query my database to see any data change in my table. I can write a console application or a windows service to perform the polling job. Once my application detect a change, I will perform the required work and call a web service. This option is commonly used but not efficient because keep polling the database is going to stress the database.

Option 4:

In stead of using trigger and polling, we request the database to notify us when there is a change in the database by using Service Broker + Query Notification. And, this post topic is about how to setting it up and use it.

But before that, we need to know the difference between Query Notification and Polling. Is your problem really can be solved by using Query Notification?

Source from MSDN:

Query Notification	Polling
Query notification is initiated by SQL Server. The notification statement issued by the adapter just instructs the database to initiate notification in case there is a change in the result set of the statement.	Polling is initiated by the adapter. The adapter executes a statement to validate whether data is available for polling, and then initiates polling by executing the polling statement if some data is available for polling.
You can use the query notification statement to only read data in a SQL Server database table.	You can use the polling statement to read or update data in a SQL Server database table.
Query notification informs only about the type of change in the data such as Insert, Update, and Delete.	Polling informs you about the actual data that has changed.
The data-change notification is instantaneous.	The data-change notification depends on the polling interval, and the adapter clients are informed about the data changes at the end of every polling interval. Polling can give you better throughput in scenarios where the data changes are happening continuously, and you do not want to be notified of each change as and when it happens. Instead, you specify a polling interval after which you want to be notified of all the changes that have happened since the last change notification.

The concept is we provide a query for SQL Server to monitor. Then, we enable Service Broker in the SQL Server. We subscribe a Query Notification request, so that if there is any change to the provided query, a notification will be sent to a queue by the Service Broker.

Assume I have a database call NotificationDB. In the database, I have a table call dbo.Leaves, and I need to subscribe query notification to that table.

Note: The query notification subscription need to meet the requirement mentioned here.

So, the following are the steps to make the mentioned concept work:

Database side:

1. Enable Service Broker in the SQL Server by running the following script:
   
   
   USE NotificationDB
   ALTER DATABASE NotificationDB SET ENABLE_BROKER
   
   
2. Create a queue for Service Broker to drop notification message into it:
   
   
   CREATE QUEUE MyNotificationQueue
   
   
3. Create a service that drop message to the above queue.
   
   
   CREATE SERVICE MyNotificationService
     ON QUEUE MyNotificationQueue
     ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])
   
   
4. Create a route for the service to point back to my local database
   
   
   CREATE ROUTE
     NotificationMessagesRoute
     WITH SERVICE_NAME = 'MyNotificationService',
          ADDRESS = 'LOCAL'
   
   

Application side:

1. Start a listener to the queue by using SqlDependency :
   
   
   SqlDependency.Start(
                   ConfigurationManager.ConnectionStrings["default"].ConnectionString,
                   "MyNotificationQueue");
   
   
   
2. Create another method that use SqlDependency to poll the Service Broker:
   
   
   
   

   public void RegisterDependency()

   {

       using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))

       {

           try

           {

               SqlCommand command = new SqlCommand("Select [ID],[ApplicantName], [Status] from dbo.Leaves WHERE [Status] = 'Approved' AND HasScheduled = 0", connection);

   
   

               //Monitor the Service Broker, and get notified if there is change in the query result

               SqlDependency dependency = new SqlDependency(command, "Service=MyNotificationService;local database=NotificationDB", int.MaxValue);

   
   

               //Fire event when message is arrived

               dependency.OnChange += this.dependency_OnChange;

   
   

               connection.Open();

                   

               SqlDataReader reader = command.ExecuteReader();

   
   

               while (reader.Read())

               {

                   Leave leave = new Leave();

                   leave.ID = Convert.ToInt32(reader["ID"]);

                   leave.ApplicantName = reader["ApplicantName"].ToString();

                   leave.Status = reader["Status"].ToString();

   
   

                   Console.WriteLine(string.Format("{0}\t{1}\t{2}", leave.ID, leave.ApplicantName, leave.Status));

               }

   
   

               connection.Close();

           }

           catch (SqlException ex)

           {

               Console.WriteLine(string.Format("Error: {0}", ex.Message));

           }

           catch (Exception ex)

           {

               Console.WriteLine(string.Format("Error: {0}", ex.Message));

           }

       }

   }
   
   
3. Create an event handler to handle what to do when a message is arrived. It is required to re-register the dependency every time a notification message has been received:
   
   
   void dependency_OnChange(object sender, SqlNotificationEventArgs e)
   {
       var info = e.Info;
       var source = e.Source;
       var type = e.Type;
              
       SqlDependency dependency = sender as SqlDependency;
   
   
       dependency.OnChange -= new OnChangeEventHandler(dependency_OnChange);
       RegisterDependency(); //Re-register dependency is required after a notification is received everytime
   
   
       //Do whatever you like here after message arrive
       //Can be calling WCF service or anything supported in C#
   }

FYI: The notification messages are in XML format. The detail message format can be found here: http://schemas.microsoft.com/SQL/Notifications/QueryNotification/QueryNotification.xsd

The above SqlNotificationEventArgs event argument contain the content of the notification message. I am not able to sniff the message content, however, base on the above XML schema, the notification message should be look like this:

<QueryNotification Type="change" Source="data" Info="insert">
    <Message>bla bla bla…</Message>
</QueryNotification>

So, whenever there is a new record has been inserted to the table, then the monitored query will be affected with the new row, then the notification message will be look like above, and finally the SqlNotificationEventArgs.Type = insert. For any row of record has been updated, the Type will be "update".

Also, you can see how many active query subscriptions currently have in the database with the following statement:

SELECT * FROM sys.dm_qn_subscriptions

Then, you will see something like this:

In summary, instead of polling a table by keep sending query to the database frequently with a timely interval to look for any data change, we can ask the database to notify us. Theoretically, it sound like better performance, however I am not too sure about that, how SqlDedepency handle the database connection may be a concern because in order to get message notifications, it require to constantly open a connection. I have not have a chance to dig deep enough, probably will share more info in the future when I encounter any performance problem with SqlDependency.


If you are interested with my complete source code, feel free to download from HERE.