SQLCacheDependency not working in Load balanced environment with sql server 2008

Question

We have implemented sqlcachedependency in our application. We have multiple caches depending on one database table and set the sql cache policy with a sql monitor as shown below.

        policy = new CacheItemPolicy();

        //connection string name
        var connectionString = connectionstring;

        //SQlDependency Cache
        SqlDependency.Start(connectionString);

        SqlChangeMonitor monitor = null;
        using (var connection = new SqlConnection(connectionString))
        {
            using (var command = new SqlCommand("SELECT col1,col2 FROM dbo.table1", connection))
            {
                var dependency = new SqlDependency(command);
                connection.Open();
                command.ExecuteReader();
                monitor = new SqlChangeMonitor(dependency);
            }
        }

        policy.ChangeMonitors.Add(CreateMonitor());

This method is called while adding multiple cache. Everything is working fine when we check on the development machine. But once we upload the application in load balanced QA machine(QA1,QA2,QA3) it is not working.

All the grant permissions and sql broker enabling is done on the database.

When we check the sql profiler, we see that subscriptions are being registered in the database. But the notification from the sql broker and not working back in the application when an update is done on the table. We see the following error in profiler

**Cannot find the remote service 'SqlQueryNotificationService- ab49a23a-9beb-4a6f-a8b0-299bcfddbeda' because it does not exist. and

This message has been dropped because the TO service could not be found. Service name: "SqlQueryNotificationService-58237ce1- aa4d-4999-9fd8-d0b78c1d932b". Message origin: "Local".**

Any help regarding this will be highly appreciable.

You're not draining the data reader (in fact, discarding it). Maybe that prevents the dependency from being set up completely. — usr

Nayana Setty Nayana Setty · Accepted Answer · 2014-08-20T10:29:06

Finally, was able to find the answer. Basically it is the permission issue. Below link helped me grant permissions which fixed the issue

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/bd195da8-93b2-43c6-8f59-674f5fb9d618/cannot-find-the-queue-sqlquerynotificationserviceguid?forum=sqlservicebroker

SQLCacheDependency not working in Load balanced environment with sql server 2008

1 Answers