Using CLR Stored Procedures to Sync NCache with a Database

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.

A better alternative involves writing a CLR stored procedure that connects with NCache from within SQL Server and directly updates or invalidates the corresponding cached item. It 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 opens 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.

Here are the steps to use CLR Store Procedure with NCache:

  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.
  3. 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
  4. 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”.
  5. Using CLR Stored Procedures to Sync NCache with a Database
  6. Write your stored procedure. Here is a sample code given:
  7. 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 ...");
            }
  8. Enable CLR integration on database as given below:
  9. sp_configure 'clr enabled', 1
    GO
    RECONFIGURE
    GO
  10. Deploy the stored procedure from Visual Studio and test it.
  11. 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.


What to Do Next?

© Copyright Alachisoft 2002 - . All rights reserved. NCache is a registered trademark of Diyatech Corp.