Database Dependent Caching w/ Multiple Databases/SqlServers

Question

I'm working in an environment where a single web application (running in a web-farm) dynamically connects to different SQL Server databases (possibly on different servers) based on user selection (this part is non-negotiable, unless you can gift me $1,000,000 and/or some kind of time altering device).

I'd like to cache some of the commonly used data from the database(s) in the application, such as "settings", and am looking for a best practice to use with the SqlCacheDependency class or a related database cache dependent solution (not opposed to a custom implementation of SQL notification or polling; notification is preferred over polling).

So far, I'm thinking of using a collection of SqlCacheDependency objects (one for each database) that can be referenced by connection string name or related key. So when the connection is changed to a different database, the cache can be requested/populated by a key of <databasekey>+<datakey> and use the appropriate SqlCacheDependency object (probably handled by a cache-managing wrapper).

Any ideas, suggestions, or experience in this area?

Or is it just better to take the hit and always go to the database?

Answer 1

you can use nhibernate or hibernate with 2nd level cache in your data access code. for caching part you can use memcached or velocity (appfabric).

Answer 2

In my opinion, you should not store all settings from different databases in your web application. The better way is to store only settings of the database that's currently in use.

You told that, at some point, user selects the database and you creating connection, opening it, reading the data and so on. This is a good place to read your settings only for this database and use them in the application. At the same moment, you can adjust SqlCacheDependency to be notified when settings in your current database has changed.

When user selects another database, you just drop current settings and load another ones from newly selected database.

This is much easier to implement, in my opinion.