Sunday, May 19, 2013

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.


21 comments:

  1. Hi,
    Thanks for great article.
    I still don't get how you access the very data that has been changed. That console.writeln line is executed outside of dependency_OnChange. I understand that notification mechanism does not provide data itself, only change related info. But introduction of your article talks about DATA transfer options. So, how you access data with this Option 4? It would be lovely to use this option, but I just don't get how you get the data itself. Could you please give an explanation? Thank you very much!

    ReplyDelete
    Replies
    1. Hi,

      The SqlDependency does not give you the latest data, it only notify you that there are some data changes in your observing query. You have to manually load the latest data.

      The reason you find that I have the data displayed outside of dependency_OnChange is because I re-register SqlDependency inside the dependency_OnChange event. During the registration, I display all the data, note that I call RegisterDependency() method inside the event.

      Delete
  2. Hey,
    Great article, keep up man!!
    I'm using SqlDependency with custom queue and service. However after one succeed triggering in the OnChange even the following events ends up with following details - SqlNotificationEventArgs parameter : Info = Error; Source = client.
    I can't understand why. Only restart of my application helps, but also only for one time (i.e. first OnChange trigger.)

    Any Ideas?

    Thanks in advance.

    ReplyDelete
    Replies
    1. Hi,

      With the limited information provided, I cannot help you much. However, the SqlNotificationInfo.Error mean "internal server error occurred", then the SqlNotificationSource.Client mean "A client-initiated notification occurred, such as a client-side time-out or as a result of attempting to add a command to a dependency that has already fired.".

      I hope these info can help you in finding the root cause.

      More info:
      http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo(v=vs.110).aspx
      http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationsource(v=vs.110).aspx

      Delete
    2. thanks for quick replay.
      Could you please see if there are any problems:

      public bool RegisterSqlDependency()
      {
      using (SqlConnection connection = new SqlConnection(_connectionString))
      {
      using (SqlCommand commandDependency = GetCommandDependencyForSqlNotifications())
      {
      commandDependency.Connection = connection;
      //Remove old dependency object
      commandDependency.Notification = null;

      //associate sqlDependency w/ sqlCommand
      SqlDependency dependency = new SqlDependency(commandDependency, _sqlDependencyOptions, 0);

      dependency.OnChange -= Dependency_OnChange;
      dependency.OnChange += Dependency_OnChange;

      try
      {
      connection.Open();

      // set required OPTION flag for query notification.
      using (SqlCommand setCommand = new SqlCommand("SET ARITHABORT ON", connection))
      {
      setCommand.ExecuteNonQuery();
      }

      // run the sql query to be monitored
      int rowsAffectedCount = commandDependency.ExecuteNonQuery();

      return true;
      }
      catch (Exception ex)
      {
      Debug.Assert(false, ex.ToString());
      }
      finally
      {
      if (connection != null)
      {
      connection.Close();
      }
      }
      }
      }

      return false;
      }

      public SqlCommand GetCommandDependencyForSqlNotifications()
      {
      // select only unprocessed items
      SqlCommand command = new SqlCommand("SELECT ID FROM [dbo].[test] WHERE IsProcessed=0");
      command.CommandTimeout = 60;

      return command;
      }


      private void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
      {
      /* unsubsribe from event */
      SqlDependency dependency = sender as SqlDependency;

      if (dependency != null)
      {
      dependency.OnChange -= Dependency_OnChange;
      }

      // we handle only INSERT statements
      if (e.Info == SqlNotificationInfo.Insert)
      {
      OnNewSqlNotification(e);
      }

      //re-subscribe again as Sql Query Notifications fires only ONCE
      RegisterSqlDependency();
      }

      Delete
    3. Jus to let you know, after appllicatoin is UP the Dependency_OnChange event handler handle the SqlNotificationInfo.Insert well, but afterwards i got only SqlNotificationInfo.Error

      Delete
    4. Hi,

      I have copied your code and run it with my machine without any problem, but I do not have your OnNewSqlNotification(e) method implementation, so I have commented it.

      I guess in the OnNewSqlNotification(e) method internally, you actually re-register the SqlDepedency again without realizing it?

      Delete
    5. Thanks Seng,.
      I've commented out the event invocation, same behavior.
      Actually in this event captured in other module (has no relation to the SqlDependecy object at all ) and it's querying the database (in separate thread). So i can't understand the root cause of the problem.
      Strange but if i modify the monitored table quickly after the app is up i get the Insert notifications, but after one minute o so, the notifications arrived with ERROR.
      I also run SQL profiler with apporpriate events for broker and QueryNotifications but can't see anything.

      In SQL server i use code identical to your except that i have special sql user with appropriate permissions and i didn't created a routw (i.e. CREATE ROUTE).


      What do You Say?
      Thanks in advance.

      Delete
    6. The route is not mandatory for your case. SQL user privilege should be sufficient otherwise it would not work for the first time. And then, I see the OnNewSqlNotification method is accepting parameter with SqlNotificationEventArgs type, may be you can share the method code?

      I think it is better to communicate via email instead of flooding this comment section with source code. Feel free to drop me an email at silvestre.lee@gmail.com.

      Delete
    7. Thanks for your assistance Seng.
      I dropped you a email with clarifications and source code.

      Thanks in advance.

      Delete
  3. Hi Seng,

    I am interested in your source code for this but the link is not working. Can you repair the link?

    Thanks,

    ReplyDelete
    Replies
    1. Hi Ben,

      Hmm, the link was broken could be cause by the skydrive was changed to onedrive. Anyway, I have updated the link, please try it again.

      Thanks.

      Delete
    2. Still doesn't work for me but I managed to get my code working anyway - thank you for this post - it helped me a lot.

      Delete
  4. Kindly share me Same Code Sqldependency notificatin in asp.net, i m trying a lot technichs but desired result not found. plzzzz and thanks in advance

    ReplyDelete
  5. hi,
    how can i change internal polling interval of the SqlDependency. sqldependency each 10 ms exec a query to get change, this is very cpu sensitive how can i change timer interval for example from 10ms to 1000ms tnx

    ReplyDelete
  6. Sneg / evgeny,
    I am also facing thev same issue. Sql dependency works first time but after 60 seconds I get an error timeout and source is client. Can you guys please share what changes you did to fix this issue.

    Thanks

    ReplyDelete
  7. Hey, what should i do if i want to listen(read) only queue after certain time of interval
    please reply as soon as possible

    ReplyDelete
  8. Great article, is there any way to get updated Record we can just get notification that there is something change but in which column or row ? that would not identify how to do this ?

    ReplyDelete
  9. Hey, It is great article, but how can i notify changes on multi machine. Plz advice.

    ReplyDelete
  10. Thank you for this great article and tutorial. I have managed to set up query notifications. I have DataGridView control with BindingSource attached with it. Every Time I change the specific table in database, I receive the notification and right after I see DataGridView IndexOutOfRangeException. I just need to update my DataGridView whenever I receive the notification about changing the table in database. Looks like DataGridView.DataSource becomes null at some point during receiving notification.

    ReplyDelete

Send Transactional SMS with API

This post cover how to send transactional SMS using the Alibaba Cloud Short Message Service API. Transactional SMS usually come with One Tim...