SQL Cache Dependency with Cluster SQL Server & Web Farm



    I have implemented SQL cache dependency to invalidate cache whenever the result of the stored procedure gets changed. The stored procedure is written obeying all the notification rules.


    Our problem:

    As long as we are working on Local machine, Development server or Pre production server its working fine means the cache gets invalidated whenever there is any change in the result set. But the same implementation doesn’t work in Production. Cache does not get invalidated.


    Difference in Production environment and other environment:

    Production uses cluster SQL server, others use standard SQL server. Production has load balancing means application is deployed on 6 servers which hits same clustered SQL server. In application’s global.asax file i have Start dependency so I see 6 different notifications started in SQL server which is right. But whenever there is a change in result set the cache doesn’t get invalidated in any of the application.


    My question is

    Do we have to follow any different kind of implementation of SQL cache dependency for web farm and cluster SQL server scenario?


    On the same note I would like to add, on the same SQL server I have one more database and a different application is accessing this database. This application is also using SQL cache dependency and its working fine. The only thing this .Net application is NOT deployed on web farm. Its deployed on single application server.

    2008年6月2日 18:05


  • Did you ever find any viable solution for it?

    2008年11月18日 16:06
  • Any solution??
    we are also in same scenario and did not found any solution!!!


    Chinmay Vartak | MCTS - Windows Sharepoint Services 3.0 Application Development |
    2008年12月23日 9:00
  • sorry for late reply but we never found perfect solution for this, we developed service that will explicitly invalidates cache on server.

    I think SQL cache dependency does not work with load balancer

    2012年1月3日 16:14