Polling your database queues less-often - using udp via sql server clr

We've all done it before: we have a database table somewhere that is being populated with records and we need to know when something new has arrived (let's forget for the moment that a real queuing infrastructure is probably more suited for this). The way it's usually implemented is:

You decide on the maximum age of the items in the queue before they need to be processed - this forms the basis of your polling interval. For example, if a change to the table must be processed within 5 minutes, your polling interval might be 4 minutes (to allow for the actual processing time). Of course, this is a sliding window, so most requests will not reach the maximum age (i.e. if one is inserted just before the table is polled).

There are obviously some inefficiencies here:

What happens if there are no requests? You are going to poll anyway.
What happens if a record is inserted directly after the polling interval? It will have to wait until the next interval.

It would be preferable if you were notified of a change to the table rather having to poll.

The solution (if you are using sql server 2005 or above) is to create a stored procedure that notifies the interested parties that there are new records to process. Think of it as "preempting the poll" - it's best to leave in the polling as a "worse-case" in case the udp broadcast is not received.

The c# code for the sql class lib may look similar to the following:

using System;
using System.Net;
using System.Net.Sockets;
using System.Security.Cryptography;
using System.Text;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace MyApp.Sql
{
    public class Utilities
    {
        [SqlProcedure]
        public static void Sync(string sourceServer, string sourceDatabase, string notificationIp, int notificationPort)
        {
            var ipAddress = !String.IsNullOrEmpty(notificationIp) ? IPAddress.Parse(notificationIp) : IPAddress.Broadcast;
            var remoteEp = new IPEndPoint(ipAddress, notificationPort == 0 ? 53101 : notificationPort);

            using (var udpClient = new UdpClient())
            {
                byte[] input = Encoding.ASCII.GetBytes(String.Format("{0}:{1}", sourceServer, sourceDatabase));
                udpClient.Send(input, input.Length, remoteEp);
            }
        }
    }
}

And to make this component available to your sql installation (if you have the necessary permissions):

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NotifyChanges]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[NotifyChanges]
GO

IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'MyApp_Notifier')
DROP ASSEMBLY [MyApp_Notifier]

CREATE ASSEMBLY MyApp_Notifier FROM 'MyApp.Sql.Dll'
WITH PERMISSION_SET = UNSAFE
GO
CREATE PROCEDURE [dbo].[NotifyChanges]
@sourceServer [nvarchar](255),
@sourceDatabase [nvarchar](255),
@notificationIp [nvarchar](255),
@notificationPort [int]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [MyApp_Notifier].[MyApp.Sql.Utilities].[Sync]
GO

The next step is to create a trigger on your polling table and call this new stored procedure when a new record is inserted. Note that the server name and database name are passed in the udp message in case you have multiple notification tables broadcasting to a single listener.

ALTER TRIGGER [dbo].[MyPollingTrigger] ON [dbo].[MyPollingTable]
AFTER INSERT
AS
DECLARE @DB_NAME VARCHAR ( 127 )
SET @DB_NAME = DB_NAME()
EXEC [NotifyChanges]
  @@SERVERNAME,
  @DB_NAME,
  NULL,
  0

In a future post I will show the udp server that you can use to respond to these events, but it's fairly trivial using the built-in .net socket libraries.