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.

 

[SQL]

ALTER DATABASE Northwind SET ENABLE_BROKER;

GO

 

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 SUBSCRIBE QUERY NOTIFICATIONS TO <database_principal>

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]"

([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] ;

 

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();

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);

}

catch (OperationFailedException e)

{

    Console.WriteLine(e.ToString());

}

//Modifying Product record in database will automatically remove Product from cache

 

See Also

Cache Dependencies Overview | Using Oracle Dependency | Using Polling Based Dependency | Using CLR Procedures to Call NCache | Adding Data with File and Key Dependencies | Synchronizing Cache with Custom Data Sources | Synchronize Data across Multiple Caches  | Add Dependency to the Existing Cached Item | Aggregate Cache Dependency

---

Send comments on this topic.