NCache 4.3 - Online Documentation

SQL Yukon Dependency

 
NOTE: This feature is not available in NCache Express and Professional edition.
 
While working with databases in a multiuser environment, updates in the databases are very frequent. When cache is involved, you need to keep cached items synchronized with the database. One possible solution is that, on every update, database itself notifies the cache and cache expires the out of synch items. Sql Yukon Dependency is provided by NCache for this purpose. Item expires if result of the command (based on command text) changes. SQLYukonDependency is only available in NCache CLR 2.0 and requires SQL Server 2005 or above.
 
Following is the step-by-step guide for using SQLCacheDependency.
 
Enabling Database Notification:
 
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 you can check properties in the databaseserver using SQL Management studio or run the following query:
 
select is_broker_enabled from sys.databases where name = '<dbname>'
 
Make sure that you get "1" for the database you intend to use.
 
Database Permissions :
 
If you are not a database owner (dbo) then please enable following permissions in order to use NCache SQL cache dependency. Database permissions are defined for two different modes: Default mode and Custom mode. In Default mode SQLDependency will create the SQL Service and Queue at run time. Custom mode is used by those clients who want to use SQLDependency without Service and Queue creation rights. In this case SQLDependency will not create the SQL Service and Queue at run time and clients are supposed to create it for each NCache process.
 
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] ;
 
Note:
  • Replace [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".
 
Using Dependency:
 
  1. Create a .Net test application.
  2. Add the following namespaces in your project:
     
    using Alachisoft.NCache.Web.Caching;
    using Alachisoft.NCache.Runtime.Dependencies;
    using Alachisoft.NCache.Runtime;
     
  3. Declare a variable "_cache" in the code and initialize it with "sqlDependencyCache", and clear it if this cache was used previously.
     
    Cache _cache = NCache.InitializeCache("sqlDependencyCache");
    _cache.Clear();
     
  4. Create a String "connectionString" containing information to connect with SQL database e.g.
     
    String connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;";
     
  5. Create a dependency and add it to the cache.
     
    CacheDependency sqlSync = new SqlCacheDependency(connectionString, "SELECT ProductID FROM dbo.Products WHERE ProductID = 006");
    _cache.Insert("ProductID:006", "my Item", sqlSync, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Normal);
     
  6. Now update the key "6" of the Database table using the following SQL command.
     
    [SQL]
    UPDATE Products SET ProductName = 'Modified Product' WHERE ProductID =6
     
  7. This invalidates cacheKey Dependant-ProductID6 " and is removed by NCache.
     
     
     
    Note : If SQL Server goes down/restarts, all dependencies become invalid and are removed from the cache .
 
 
 
See Also
 
© Copyright  2014 Alachisoft. All rights reserved.