Synchronize Distributed Object Cache with SQL Server

Author: Iqbal Khan

NCache lets you cache data closer to your application in the middle-tier so you can reduce expensive trips to the database. This, in turn, significantly speeds up your application. It also makes it more scalable as the load on your database is reduced, allowing it to handle a larger number of clients without any hardware upgrades.

However, when you cache data, you're creating a copy of it outside of your database. This introduces the risk of cached data becoming stale if the database is updated. The good news is that this situation occurs only when you have multiple applications modifying the same data in the database, and not all of them are updating the cache.

When other applications modify the database without updating the cache, a mechanism is needed for the cache to synchronize with the database. This is possible in multiple ways. If your database is SQL Server 2005 or later, NCache uses .NET SQL query notifications to synchronize the cache via SqlCacheDependency. Similarly, for Oracle 11g or later, NCache supports Oracle change notifications using OracleCacheDependency. Both are notification-based dependencies, where the database itself informs NCache of changes.

For databases without change notification support (e.g., older versions, NoSQL, or OLEDB), NCache offers a polling-based dependency to periodically check for changes. While this approach ensures compatibility, it is generally less efficient for high-scale environments due to the overhead of frequent polling.

In this article, I will discuss how NCache synchronizes the cache with SQL Server 2005 and later using .NET SQL query notifications.

Specifying SqlCacheDependency in code

Here is how you can use SqlCacheDependency in your .NET application to keep your cache synchronized with SQL Server 2005 and later. This allows NCache to automatically remove cache items when the corresponding data in the database is updated.

// Precondition: Cache is already connected

// Creating connection string to get connected with database
string connectionString = "your_connection_string_here";

// Getting products from database
List<Product> products = FetchProductFromDB();

foreach (Product product in products)
{
    string productKey = $"Product: {product.ProductID}";

    // Creating an SQL dependency on the UnitPrice of product. Whenever the UnitPrice changes, the product is removed from the cache
    string query = $"SELECT UnitPrice FROM dbo.Products WHERE ProductID = {product.ProductID}";

    // Creating dependency
    SqlCacheDependency dependency = new SqlCacheDependency(connectionString, query);

    CacheItem productItem = new CacheItem(product);

    // Adding Dependency to product item
    productItem.Dependency = dependency;

    // Adding CacheItem in cache
    _cache.Add(productKey, productItem);
}

In the above example, each cache item is added with an SqlCacheDependency, which allows NCache to monitor the SQL Server for changes to the underlying data. The SQL query used to create the dependency must exactly match the query used to fetch the data from the database. This ensures that SQL Server can accurately detect changes.

Additionally, the query must explicitly specify the column names being monitored (e.g., SELECT UnitPrice) instead of using SELECT *, as SQL Server does not support change notifications for wildcard column selections. When any monitored data is modified in the database, SQL Server triggers a notification, and NCache responds by removing the affected item from the cache to maintain consistency.

Enable Service Broker in SQL Server

Before using SqlCacheDependency with NCache, you must ensure that the Service Broker is enabled in the SQL Server database. This is essential because it allows SQL Server to send change notifications that NCache listens to in order to keep the cache synchronized with the database.

ALTER DATABASE Northwind SET ENABLE_BROKER;
GO

Please note that NCache automatically calls SqlCacheDependency.Start() method from within its own code. You never directly interact with SqlCacheDependency and therefore do not need to call "Start()" method on it.

Run Application

You are now ready to run your application that makes NCache calls to cache data. Now, whenever you cache anything in NCache, it notifies SQL Server about the cached data. When that data changes in SQL Server, a .NET event notification is sent back to NCache. NCache then removes this data from the cache because it has changed in the database. This way, the next time your application needs the data, it won’t find it in the cache. It then fetches the data from the database and stores it back in the cache.

Conclusion

As you have seen, NCache makes it very simple to synchronize your distributed cache with your SQL Server 2005 onwards database. Now, you can have the peace of mind knowing that your cache is never out of sync with your database.

---

Author: Iqbal Khan works for Alachisoft, a leading software company providing .NET and Java distributed caching, O/R Mapping, and SharePoint Storage Optimization solutions. You can reach him at iqbal@alachisoft.com.