Setup SQL Server Environment

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

Configuring Notifications on SQL Server

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

Step 1: Enable Broker Service

Notifications must be enabled in the SQL Server database for NCache. Unlike time based expirations, where data is invalidated by the 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 the supported queries for event notifications, refer to the Microsoft Documentation linked below.

Enable the service broker in SQL Server before NCache can use SQL Cache Dependency. 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 the broker has been enabled. If the broker is not enabled (the result is "0"), it is important to enable the broker. The 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 details, please refer to Microsoft's Query Notification Permissions.

Default Mode

The 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 username.

Custom Mode

If you do not want to enable notifications in the default mode, the 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 [username];
GRANT RECEIVE ON "NCacheSQLQueue-[ip-address]" TO [username];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [username];
GRANT SEND ON SERVICE::"NCacheSQLService-[ip-address]" TO [username];
Note

Replace [ip-address] with the node IP address and [username] with the database username.

Important

  • In a clustered environment, users are supposed to create SQL Service and Queue per NCache process by selecting the “Use custom SQL Notification Mode” checkbox in the Options tab in the NCache Management Center.

  • You can also create the required SQL Service and Queue using script NCacheServiceQueue.sql located at %NCHOME%\bin\resources (Windows) or /opt/ncache/bin/resources (Linux).

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

See Also

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