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.

Download NCache free trial - Extremely fast and scalable in-memory distributed cache

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 and following assemblies in SQL server. Example is given below. In this example we are using Northwind as a sample database.

use Northwind

alter database Northwind
set trustworthy on;
go

drop assembly SMdiagnostics
drop assembly [System.Web]
drop assembly [System.Messaging]
drop assembly [System.ServiceModel]
drop assembly [System.Management]

CREATE ASSEMBLY SMdiagnostics AUTHORIZATION dbo
FROM N'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll'
WITH permission_set = unsafe

CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo
FROM N'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'
WITH permission_set = unsafe

CREATE ASSEMBLY [System.Management] AUTHORIZATION dbo
FROM N'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.management.dll'
WITH permission_set = unsafe

CREATE ASSEMBLY [System.Messaging] AUTHORIZATION dbo
FROM N'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH permission_set = unsafe

CREATE ASSEMBLY [System.ServiceModel] AUTHORIZATION dbo
FROM N'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll'
WITH permission_set = unsafe

CREATE ASSEMBLY NCache
FROM N'C:\Program Files\NCache\bin\assembly\2.0\Alachisoft.NCache.Web.dll'
WITH permission_set = unsafe

3. Open visual studio to write a stored procedure against NCache And create a SQL CLR Database project as mentioned below. Add a reference to the NCache assembly that you created in the last step. The assembly that you need to refer is highlighted above. It will appear under SQL Server with the same name as “NCache”.

CLR_VS_Studio

4.  Write your stored procedure. Here is a sample code given:

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void TestSProc(string cacheName)
    {
        //--- Put your code here
        SqlPipe sp = SqlContext.Pipe;

        try
        {
            sp.Send("Starting .....");

            if (string.IsNullOrEmpty(cacheName))
                cacheName = "mycache";

            Cache _cache = NCache.InitializeCache(cacheName);
            _cache.Insert("key", DateTime.Now.ToString());
            sp.Send("Test is completed ...");
        }

5.  Enable CLR integration on database as given below:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

6. Deploy the stored procedure from Visual Studio and test it.
7. After deploying the stored procedure, you need to place your stored procedure assembly in (C:\Program Files\NCache\bin\assembly\2.0) folder as it does not resolve assembly references directly from windows GAC folder and needs them locally.

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

This entry was posted in CLR procedures, Database synchronize, Distributed caching and tagged , , . Bookmark the permalink.

3 Responses to How to Synchronize Distributed Cache with Database with CLR Stored Procedures

  1. sergey says:

    Hello Iqbal,

    I created the CLR stored proc as you described with same code as in paragraph 5. When I try to exec, it throws the following exception:

    Alachisoft.NCache.Runtime.Exceptions.ConfigurationException: An error occured while reading client.ncconf.

    Seems that NCache cannot find config file, but the file exists in the installation directory C:\Program Files\NCache\config

    Please help.

    Thanks
    Sergey

    • Iqbal Khan says:

      Hi Sergey,

      Please download the fix that will resolve this issue in your environment. This implementation was provided on top of NCache 4.1 version and will be compatible only after applying below fix.

      http://www.alachisoft.com/downloads/support/NCache4.1_.NET_CLR_SPROC_Fix.zip

      Please refer to the “Readme.txt” file in the patch to apply it in your environment.

      Currently this fix is provided for Cache Server installation and contains .Net 2.0 assemblies only, you can test and verify this in your environment, we can provide you complete patch specific to .NET and NCache installation on demand.

      Moreover, you need to ensure that after deploying the stored procedure, you need to place your stored procedure assembly in (C:\Program Files\NCache\bin\assembly\2.0) folder as it does not resolve assembly references directly from windows GAC folder and needs them locally.

      Please let me know how it goes

  2. Iqbal Khan says:

    This post has been updated. Now all the steps work with NCache 4.1

Leave a Reply

Your email address will not be published. Required fields are marked *


five + = 14

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>