Database Dependency Using SQL Server

NCache provides feature of SQL dependency for the purpose of synchronizing cache with Microsoft SQL Server 2005/2008. An item is added with SqlDependency (provided by .NET framework), specifying an SQL statement representing a query result set in the database. NCache then establishes a link with the database against these rows. If data is updated in the database by any application, SQL Server fires a .NET event notification which NCache catches and removes the corresponding item from the distributed cache.

1. Configuring Notification on SQL Server

NCache tracks changes in database using notifications received from SQL server about changes in database. Internally NCache registers for these data change notification and receives notification from database in case of any change in the registered query result set. On the basis of these received notification, cache invalidates the corresponding data and removes it from cache.

Notifications must be enabled in SQL server database for NCache. Unlike time based expirations where data is invalidated by cache clean up thread on a configurable interval, NCache removes the corresponding data as soon it receives notification from the database.

For this purpose, enable the service broker in SQL Server 2005 or above before NCache can use SqlCacheDependency. This allows SQL Server 2005 to send event notifications to NCache to let it know that data has changed in the database.

ALTER DATABASE Northwind SET ENABLE_BROKER;
GO

To verify that the broker is running, properties in the database server can be checked using SQL Management studio or run the following query:

SELECT is_broker_enabled FROM sys.databases WHERE name = '<dbname>'`

If the result is “1”, it means broker has been enabled.

For further details, refer to MSDN article on Enabling Event Notifications for SQL Server.

2. Database Permissions

The following permissions need to be enabled in order to use NCache SQL cache dependency if the user does not have database permissions. Database permissions are defined for two different modes: Default mode and Custom mode.

Default Mode

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <database_principal>
GRANT CREATE QUEUE TO <database_principal>
GRANT CREATE SERVICE TO <database_principal>
GRANT CREATE PROCEDURE TO <database_principal>

Custom Mode

CREATE QUEUE "NCacheSQLQueue-[IP-Address]";
CREATE SERVICE "NCacheSQLService-[IP-Address]"
ON
QUEUE."NCacheSQLQueue-[IP-Address]"([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO[User Name];
GRANT RECEIVE ON :: "NCacheSQLQueue-[IP-Address]"TO[User Name];
GRANT RECEIVE ON :: QueryNotificationErrorsQueue TO[User Name];
GRANT SEND ON SERVICE :: "NCacheSQLService-[IP-Address]"to[User Name];

Note

Replace [IP-Address] with node IP address and [User Name] with database user name.

Important

In a clustered environment, users are supposed to create SQL Service and Queue per NCache process by checking “Use custom SQL Notification Mode” checkbox in Options tab in NCache Manager.
You can also create required SQL Service and Queue using script NCacheServiceQueue.sql located at %NCHOME%/bin/resources.

3. Adding Data with SQL Dependency

To add data with SQL dependency, create SqlCacheDependency object, and use Add/Insert API to add item with SqlCacheDependency.

To utilize the API, include the following namespace in your application: Alachisoft.NCache.Runtime.Dependencies.


Product product = new Product();
product.ProductID = 1001;
product.ProductName = "Chai";

string key = "Product:" + product.ProductID;

try{
      //Creating SqlCacheDependency
      string connectionString = "Data Source=localhost;Initial  Catalog=Northwind;Integrated Security=SSPI;";
      string query = "SELECT ProductID FROM dbo.Products WHERE ProductID = 1001";

      SqlCacheDependency sqlDependency = new SqlCacheDependency(connectionString, query);

      //Adding cache item "Product:1001" with SqlCacheDependency
      cache.Insert(key, product, sqlDependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Normal);

      //Modify Product record in database while program waits...
      Thread.Sleep(5000);

      //... and then check for its existence
      Object item = cache.Get(key);
      if (item == null){
          // item removed successfully
      }
      else{
      // item not removed successfully
      }
}
catch (OperationFailedException e){
      //handle exception
}

4. Write Trigger to Call Stored Procedure

A database trigger needs to be written that will call stored procedure created in the previous steps whenever an update or delete is performed on database table. Following is a sample script to create trigger on Product table in Northwind database:

CREATE TRIGGER [dbo].[myTrigger]
ON [dbo].[Products]
FOR DELETE,UPDATE
AS
DECLARE @cacheName AS NVARCHAR(4000)
DECLARE @key AS VARCHAR(4000)

SELECT @key=Cast((Deleted.ProductID)asnVarChar)+':dbo.Products', @cacheName='mycache' FROM Deleted

EXEC dbo.RemoveOnUpdate@cacheName, @key

5. Adding Data in Cache

Following is a sample code that adds an item in cache and updates it in database to verify that item has been removed from cache:

To utilize the API, include the following namespace in your application: Alachisoft.NCache.Runtime.Dependencies.

  //Create object to be added with dependency
  Product product = new Product();
  product.ProductID = 1001;
  product.ProductName = "Chai";

  string key = "1:dbo.Products";

  try{
      //Cache key should be same as used in trigger
      cache.Insert(key, product);

      //Modify Product record in database while program waits...
      Thread.Sleep(5000);

      //... and then check for its existence
      Object item = cache.Get(key);
      if (item == null){
          // item removed successfully
      }
      else{
          // item not removed successfully
      }
  }
  catch (OperationFailedException e){
      // handle exception
  }