CODEWITHSUNDEEP
c#sqlsql-servercachingsqldependency
Sean Newell
Sean Newell

Reputation: 1113

SqlDependency not updating cache

I'm on a project using .NET 4.5, MVC, EF 6

I had naively implemented a caching system using the HttpRuntime cache and needed to invalidate the data I cache on updates to that data; except I forgot to take into account that our production server is published to a load balanced set of two servers... :|

So on production, after the data was updated, the app would sometimes serve the right data, and sometimes the old data depending on which server the request was hitting. Bad news bears.

So I decided to define a dependency on the SQL table AcademicTerms which is where my data is coming from. But I did something wrong, and I'm not sure what.

SQL that I ran to set up the permissions after enabling the Service Broker

EXEC sp_addrole 'sql_dependency_role'

GRANT CREATE PROCEDURE to sql_dependency_role
GRANT CREATE QUEUE to sql_dependency_role
GRANT CREATE SERVICE to sql_dependency_role
GRANT REFERENCES on
  CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to sql_dependency_role
GRANT VIEW DEFINITION TO sql_dependency_role
GRANT SELECT to sql_dependency_role
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sql_dependency_role
GRANT RECEIVE ON QueryNotificationErrorsQueue TO sql_dependency_role

EXEC sp_addrolemember 'sql_dependency_role', 'MY_ASPNET_APP_USERNAME'

My implementation of inserting new data after fetching and thus setting up the SqlDependency (hopefully less naive!): 

private void insertIntoCache(
  AcademicTermLockingInfo newItem,
  string itemKey,
  Guid termID) {

  var dbContextConnection = db.Database.Connection;

  var connectionString = dbContextConnection.ConnectionString;
  // important step otherwise it won't work
  SqlDependency.Start(connectionString);

  CacheItemPolicy policy = new CacheItemPolicy {
    AbsoluteExpiration = DateTime.UtcNow.AddMonths(6)
  };

  CacheItem item = new CacheItem(itemKey, newItem);

  using (SqlConnection connection = new SqlConnection(connectionString)) {
    connection.Open();
    // command which will be used to notify updates - probably want to parametrize this
    using (SqlCommand command = 
     new SqlCommand(
        String.Format("SELECT Name, LockDate FROM dbo.AcademicTerms WHERE ID = '{0}'",
          termID),
        connection)) {

        SqlDependency dependency = new SqlDependency(command);
        SqlChangeMonitor monitor = new SqlChangeMonitor(dependency);

        policy.ChangeMonitors.Add(monitor);
        MemoryCache.Default.Set(item, policy);

        // execute once otherwise dependency not registered
        command.ExecuteNonQuery();

    }
  }
}

Any help would be very much appreciated!

Things I've done:

Upvotes: 0

Views: 1146

Answers (1)

Sean Newell
Sean Newell

Reputation: 1113

The secret sauce I was missing was alter authorization on database::DevUMS to [sa]; which I found on the linked SO post's answer.

There are a number of other steps, like adding a Role to use the appropriate login, but honestly, I'm really unsure as to whether or not those are actually necessary.

I'm going to publish a little later on today, and then I'll try to do the minimal amount of steps and document that here. I found the documentation in the wild to be very scattered and poor, so I hope to have this answer be a definitive place to refer to in the future

Upvotes: 1

Related Questions