How to Synchronize Distributed Cache with Database with CLR Stored Procedures

Distributed caching has become a very important part of any high transaction application in order to ensure that the database does not become a scalability bottleneck. But, since a distributed cache keeps a copy of your application data, you must always ensure that it is kept synchronized with your database. Without this, the distributed cache has older stale data that causes data integrity problems.

SQL Server provides an event notification mechanism where the distributed cache like NCache can register itself for change notification through SqlCacheDependency and then receive notifications from SQL Server when underlying data changes in the database. This allows NCache to immediately invalidate or reload the corresponding cached item and this keeps the cache always synchronized with the database.

However, SqlCacheDependency can become a very resource intensive way of synchronizing the cache with the database. First of all, you have to create a separate SqlCacheDependency for each cached item and this could easily go into tens of thousands if not hundreds of thousands. And, SQL Server uses data structures to maintain each SqlCachDependency separately so it can monitor any data changes related to it. And, this consumes a lot of extra resources and can easily choke the database server.

Secondly, SQL Server fires separate .NET events for each data change and NCache catches these events. And, these .NET events can be quite heavy and could easily overwhelm the network traffic and overall performance of NCache and your application.

There is a better alternative. This involves you writing a CLR stored procedure that connects with NCache from within SQL Server and directly updates or invalidates the corresponding cached item. And, then you can call this CLR stored procedure from an update or delete trigger of your table. You can do this either with SQL Server 2005 or 2008 and also from Oracle 10g or later but only if it is running on Windows.

A CLR stored procedure is more resource efficient because it is not creating data structures related to SqlCacheDependency. And, it also does not fire .NET events to NCache. Instead, it open up an NCache client connection and directly tells NCache whether to invalidate a cached item or reload it. And, this connection with NCache is highly optimized and much faster and lighter than .NET events.

Below is an example of how to use a CLR stored procedure.

1. Copy log4net and protobuf-net from Windows GAC to NCache/bin/assembly/2.0 folder (choose 4.0 if the target platform is .NET 4.0).

2.   Register NCache assembly Alachisoft.NCache.Web in SQL server

CREATE ASSEMBLY [Alachisoft.NCache.Web] from N'C:\Program Files\NCache\bin\assembly\2.0\Alachisoft.NCache.Web.dll' with permission_set = unsafe

3.  Enable CLR integration on database

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

4.  Enable trustworthy option for database

alter database Northwind set trustworthy on;

5.  Deploy the stored procedure from Visual Studio and test it.

CLR Stored Procedure

using Microsoft.SqlServer.Server;
using Alachisoft.NCache.Web.Caching;

public partial class StoredProcedures{
    public static Cache objCache;
    public static void InitializeCache() {
        if (objCache != null) return;

        objCache = NCache.InitializeCache("mycache");
        sp.Send("Cache Initialized");
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void RemoveCacheItemSProc(string key){
        try{
            InitializeCache();
            objCache.Remove(key);
        }
        catch (Exception e){
            sp.Send(e.Message + Environment.NewLine);
        }
    }
};

Database Trigger

ALTER TRIGGER [dbo].[myTrigger]
  ON [dbo].[customer]

  FOR DELETE, UPDATE
  AS

DECLARE @cacheKey varchar;
select @cacheKey ='Customer:CustomerID:' + (Select old.CustomerID Name from DELETED old );
exec RemoveCacheItemSProc @cacheKey;

CLR based stored procedures or triggers can greatly improve the application performance as compared to the SqlCacheDependency that is relatively slower and can be overwhelming for large datasets.

Download NCache Trial | NCache Details