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.
