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.
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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:WindowsMicrosoft.NETFrameworkv3.0Windows Communication FoundationSMdiagnostics.dll' WITH permission_set = unsafe CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo FROM N'C:WindowsMicrosoft.NETFramework64v2.0.50727System.Web.dll' WITH permission_set = unsafe CREATE ASSEMBLY [System.Management] AUTHORIZATION dbo FROM N'C:WindowsMicrosoft.NETFramework64v2.0.50727System.management.dll' WITH permission_set = unsafe CREATE ASSEMBLY [System.Messaging] AUTHORIZATION dbo FROM N'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Messaging.dll' WITH permission_set = unsafe CREATE ASSEMBLY [System.ServiceModel] AUTHORIZATION dbo FROM N'C:Program Files (x86)Reference AssembliesMicrosoftFrameworkv3.0System.ServiceModel.dll' WITH permission_set = unsafe CREATE ASSEMBLY NCache FROM N'C:Program FilesNCachebinassembly2.0Alachisoft.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”.
4. Write your stored procedure. Here is a sample code given:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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:
|
1 2 3 4 5 |
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 FilesNCachebinassembly2.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.
SQLCacheDependency doesn’t have problems in itself. If you have your application committing changes to the database then the best practice is to add a distributed cache in between to handle the dependency and consequently commits to the database. Only in two scenarios you look for alternative solutions.
One is when some other application is making the changes to the database and you don’t have any control over it or you can’t direct it to use the cache to make DB commits.
Second reason could be when you want to have many Dependencies against each table or you have a high DB writes/updates environment then you can use Polling Dependency. It will poll the database to detect any change and the invalidate items in the cache.
For more information refer to this document: https://www.alachisoft.com/resources/docs/ncache/help/polling-based-dependency.html
Hello Iqbal,
I am trying to follow this article however I am getting the following error when running the SQL script.
Msg 6544, Level 16, State 1, Line 26
CREATE ASSEMBLY for assembly ‘System.ServiceModel’ failed because assembly ‘microsoft.visualbasic.activities.compiler’ is malformed or not a pure .NET assembly.
If using WCF from inside a SQLCLR procedure is no longer supported what is the recommended method of cache synchronization while avoiding the performance problems with SqlCacheDependency.
Thanks, Ryan.
This post has been updated. Now all the steps work with NCache 4.1
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.
https://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 FilesNCachebinassembly2.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
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 FilesNCacheconfig
Please help.
Thanks
Sergey