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:
- 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).
- Register NCache and following assemblies in SQL server. Example is given below. In this example we are using Northwind as a sample database.
- 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”.
- Write your stored procedure. Here is a sample code given:
- Enable CLR integration on database as given below:
- Deploy the stored procedure from Visual Studio and test it.
- 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.
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
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 ...");
}sp_configure 'clr enabled', 1
GO
RECONFIGURE
GOCLR 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.