Monday, 6 June 2011

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.

1 comment:

  1. This article has given me a lot of insights, thanks for making this in reality simple to take in, keep a good work!Anyhow, I’m definitely pleased I found it and I’ll be bookmarking it and checking back often! Bus back panel advertising in Indore moreover has the flexibility to offer an extent of courses of action for an extent of spending arrangements, so depending upon what you have to finish you will find an answer that enables you to gather business and get more customers. On the off chance that you're in business and use advertising as a segment of your advancing activity, then you should banter with this service ,the advertising assistants.

    ReplyDelete