NCache 4.4 - Online Documentation

Using SQL Dependency

NCache provides feature of SQL dependency for the purpose of synchronizing cache with Microsoft SQL Server 2005/2008. An item is added with SQL dependency, specifying SQL statement representing one or more rows (query result set) in database. Then NCache 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.
i. 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.
To verify that the broker is running, properties in the databaseserver 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.
ii. 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.
For Default Mode
GRANT CREATE QUEUE TO <database_principal>
GRANT CREATE SERVICE TO <database_principal>
GRANT CREATE PROCEDURE TO <database_principal>
For Custom Mode
CREATE QUEUE "NCacheSQLQueue-[IP-Address]";
CREATE SERVICE "NCacheSQLService-[IP-Address]"
ON QUEUE "NCacheSQLQueue-[IP-Address]"
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] ;
  • IReplace [IP-Address] with node IP address and [User Name] with database user name.
  • In a clustered environment, users are supposed to create SQL Service and Queue per NCache process.
  • You can also create required SQL Service and Queue using script "NCacheServiceQueue.sql" located at:
      "%Install Directory%/NCache/bin/resources"
iii. Adding Data with SQL Dependency
To add data with Sql dependency, create SqlCacheDependency object, and use Add/Insert API to add item with SqlCacheDependency.
//Create object to be added with dependency
Product product=new Product();
string key="Product:"+product.ProductID;
    //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);
catch (OperationFailedException e)
//Modifying Product record in database will automatically remove Product from cache
See Also