Synchronize Distributed Object Cache with SQL Server 2008/2012

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 turns significantly speeds up your application. It also makes it more scalable because 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 actually creating a copy of it outside of your database. This introduces the risk that data in your database would be modified but your cached copy would remain unchanged and become stale. The good news is that this situation occurs only when you have multiple applications modifying the same data in the database and when not all of them are updating the cache.

When you have the situation where other applications are modifying data in the database and not updating the cache, then you need a mechanism for the cache to be able to synchronize itself with the database directly. This is possible in multiple different ways. If your database is SQL Server 2008/2012 then the cache can use .NET event notifications for synchronization, which is quite efficient. But, if your database is SQL Server 2000, Oracle, or any other OLEDB compliant database, then the only way to synchronize is by polling the database looking for any updates. This method is of course not as efficient because the cache ends up making a lot of unnecessary trips to the database.

In this article, I will discuss how NCache synchronizes the cache with SQL Server 2008/2012 database by using .NET event notifications.

Specifying SqlCacheDependency in code

Here is what you need to specify in your .NET application code in order to use this feature. NCache provides an interface very similar to the Microsoft's SqlCacheDependency interface because internally NCache is using SqlCacheDependency object to synchronize with SQL Server 2008/2012.

public class Program {

public static void Main(string[] args)
    {
        NCache.InitializeCache("myCache");
        Customer cust = new Customer();
        cust.CustomerID = "ALFKI";
        LoadCustomer(cust);

        List<Customer> custList = FindCustomers("San Francisco");

        // Do your regular work here...

        NCache.Cache.Dispose();
    }

// A standard Load method that loads a single row from database
public Customer LoadCustomer(Customer cust)
    {
        String key = "Customer:CustomerID:" + cust.CustomerID;
        Customer cust2 = (Customer)NCache.Cache.Get(key);
        if (cust2 != null)
        return cust2;

        CustomerFactory custFactory = new CustomerFactory();

        // Load a single customer from the database
        // SELECT * FROM Customers WHERE CustomerID = 'ALFKI'
        custFactory.Load(cust);

        // Create a SqlCacheDependency for this item
        CacheItem item = new CacheItem(cust);
        item.Dependency = SqlDependencyFactory.CreateSqlYukonDependency(connectionString,
        "SELECT CustomerID FROM Customers WHERE CustomerID = '" + cust.CustomerID + "'");

        // Store item in the cache along with SqlCacheDependency
        NCache.Cache.Insert(key, item);
        return cust;
    }

    // A query method
    public List<Customer> FindCustomers(String city)
        {
            String key = "List<Customer>:City:" + city;
            List<Customer> custList = (List<Customer>)NCache.Cache.Get(key);
            if (custList != null)
            return custList;

        CustomerFactory custFactory = new CustomerFactory();

        // Load a list of customers from database based on a criteria
        // SELECT * FROM Customers WHERE City = 'San Francisco'
        custList = custFactory.FindByCity(city);

        // Create a SqlCacheDependency for this list of customers
        CacheItem item = new CacheItem(custList);
        item.Dependency = SqlDependencyFactory.CreateSqlYukonDependency(connectionString,
        "SELECT CustomerID FROM Customers WHERE City = '" + city + "'");

        // Store list of customers in the cache along with SqlCacheDependency
        NCache.Cache.Insert (key, item);
        return custList;
    }
}

The above code shows two different situations where you can specify SqlCacheDependency. First is when you're loading a single row from the database, converting it into an object, and storing it in the cache. In this case, please note that you have to specify an equivalent SQL statement that you used in your code to fetch this single row. This SQL statement is then used by SqlCacheDependency object to establish a connection with the database and register the rows for which SQL Server 2008/2012 should send .NET event notifications.

The second situation is where you're fetching a collection of rows from the database as collection of objects and then storing the entire collection as an individual object in the cache. In this case, you need to create an SqlCacheDependency that uses the same SQL statement to fetch the same rows that you just did from the database. This way, NCache notifies SQL Server 2008/2012 that when any of these rows change a .NET event notification should be sent to NCache.

The above code example covers most of the situations where you would need to specify a SqlCacheDependency in your code . Another situation is when you are trying to fetch a collection of related objects to Customer (e.g. 1-n relationship). In that situation, you'll do exactly what you did for fetching a collection of Customer objects. The only thing that would change is the SQL statement that is being used to fetch related objects.

Please note that the SQL statement you give to CreateSqlYukonDependency() method must not use "SELECT * ..." format. You must specify one or more column names as part of the SELECT statement. Otherwise, SqlCacheDependency won't work.

Enable Service Broker in SQL Server 2008/2012

You must enable the service broker in SQL Server 2008/2012 before NCache can use SqlCacheDependency. This allows SQL Server 2008/2012 to send event notifications to NCache to let it know that data has changed in 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, NCache notifies SQL Server 2008/2012 about it and then when this data changes in SQL Server 2008/2012, it sends a .NET event notification 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 this data, it does not find it in the cache and goes to the database to fetch it and then stores in the cache.

Conclusion

As you have seen, NCache makes it very simple to synchronize your distributed cache with your SQL Server 2008/2012 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.