Keeping a cache cluster in sync with MSSQL Server database

[fealves78]

I wonder what would be the best approach for keeping my cache cluster in sync with a MSSQLServer database.  The cache will be used mostly for reading. However, the database will suffer updates with variable frequency throughout the day.

I am thinking about using MSSQLServer 2008 R2 new features such as Change Data Capture and Change Tracking (http://www.microsoft.com/download/en/details.aspx?id=21599), but I wonder if there is a better and more generic approach (compatible with older versions of MSSQLSever) for keeping the cache synchronized.

Any thoughts?

Thanks!

[fealves78]

... ?

[galder.zamarreno]

Hmmm, where are these database updates coming from?

Do they always come from the app with the cache? If so, you could have the app invalidate/clear the cache when the database is updated. Either selectively, or globally.

If the updates come from somewhere else, you need to somehow notify the system containing the cache that the database was updated and the data it contains must be invalidated.

[fealves78]

The database is being updated by other applications and I need to communicate the changes with some frequency to the HotRod cache cluster.  The data is originally loaded from the database to the cache cluster by a specific application that uses JDBC and HotRod's RemoteCache object. Once the data is in the cache it is "read-only".

According to my previous e-mail, MSSQL Server 2008 R2 has a notification mechanism that seems to be able to fulfill my needs (for more info, please refer to: http://www.microsoft.com/download/en/details.aspx?id=21599 ). I was just wondering if anybody has used it before or has successfully used another mechanism to provide such notifications.