Setup SQL Server Environment

In order to set SQL dependency on data in cache, environment needs to be set for using SQL Dependency. Please follow the steps below to set up the environment for using SQL Dependency.

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 notifications and receives notifications from database in case of any change in the registered query result set. On the basis of these received notifications, cache invalidates the corresponding data and removes it from cache.

Step 1: Enable Broker Service

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 as it receives notifications from the database.For further details on enabling the query notifications and see the supported queries for event notifications refer to the Microsoft links below.

Enable the service broker in SQL Server before NCache can use SqlCacheDependency. This allows SQL Server to send event notifications to NCache to let it know that data has changed in the database. In order to verify that the broker is running, properties in the database server can be checked using SQL Management Studio or you can use the following query to verify that:

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

If the result is “1”, it means broker has been enabled. If the broker is not enabled (the result is 0), it is important to enable the broker. Following command enables broker service for using SQL notifications.

ALTER DATABASE Northwind SET ENABLE_BROKER;

Step 2: Database Permissions

Database permissions need to be enabled in order to use NCache SQL Cache Dependency. These permissions must be granted on the database where the query is executed. For more detail please refer to Microsoft Query Notification Permissions.

Default Mode

Following commands need to be executed in order to grant database permissions to a particular user. 

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <database_user>
GRANT CREATE QUEUE TO <database_user>
GRANT CREATE SERVICE TO <database_user>
GRANT CREATE PROCEDURE TO <database_user>
Note

Replace the <database_user> with the user name.

Custom Mode

If you do not want to enable notifications in the default mode, custom mode is provided by NCache. This mode is used when you want to grant the minimum required permissions to the user.

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.

Once the environment is set, use SQL dependency using Sync Cache with SQL in Programmer's Guide.

See Also

Setup Oracle Database Environment
Setup Ole​Db Environment
Setup SQL Server for CLR Procedures
Monitor Caches