How SQLCacheDependency Synchronizes Distributed Cache with Database?

Distributed Caching has become a popular way of improving .NET application performance and scalability. That is why developers are caching more and more data in distributed cache. However along with this come a few challenges. One important challenge is to ensure that data in the cache is always synchronized with the database. This is because the cache is keeping a copy of the data that already exists in the database.

If you have multiple applications updating the same data in the database but not all of them have access to the distributed cache, you’ll end up with a situation where data in the cache is older and different than its counterpart in the database. And, while this may be okay for some reference type of data, it is definitely not acceptable for transactional data. Reference data is one that you read a lot but don’t modify very frequently (e.g. product catalog) while transactional data is something you read and modify frequently (e.g. customer or account data).

So, how do you ensure that in these situations, the distributed cache stays synchronized with the database? The answer is SqlCacheDependency. SqlCacheDependency is part of ASP.NET Cache (System.Web.Caching) and allows you to specify a dataset in the database with an SQL statement and then receive .NET event notifications from SQL Server 2005/2008 whenever your dataset is modified in the database.

NCache has internally incorporated SqlCacheDependency for the purpose of synchronizing cache with SQL Server 2005/2008 or Oracle database. To you, NCache provides a similar interface called SqlDependency that allows you to specify an SQL statement representing one or more rows in a given table that make up your cached item. NCache then internally uses SqlCacheDependency to establish a link with the database against these rows.

So, if your data is updated in the database by one of your applications, SQL Server fires a .NET event notification which NCache catches and removes the corresponding item from the distributed cache. This resolves data inconsistency issue of having two different copies of the same data. This is because when your application wants the same data next time, it doesn’t find it in the cache and is forced to retrieve the latest copy from the database which it then caches as well. This way, NCache ensures that the data in the cache is always consistent with the data in the database.

Code Details: www.alachisoft.com/blogs/ncache/how-sqlcachedependency-synchronizes-distributed-cache-with-database/