Notifying app from SqlServer

Question

Given a typical 3 tiers app deployed at two remote sites. The db behind the two installations contains exactly the same structure and same data. There needs to be a replication mechanism between the two backend db to keep them in synch. The native replication feature of SqlServer would do the job. However, the business layer of the app keeps a lot of data cached. If I use db level replication, the cache in the business layer gets out of sync.

Is there a way to create SQL triggers that notify the business layer about changes?

DB: SqlServer2005 or 2008, business layer: C#

Answer 1

Complex Option A: If you have static IP's you can use WCF (TCP/IP or SOAP) in the app and call it using SQL CLR, otherwise you could use WCF Peer-to-Peer although it may be hard with the limits of SQL CLR. To go one step further you could have the SQL CLR talk to the local program which then tells the others to update.

Simpler Option B: Site A clears the cache on it's side and sets a flag in the DB, SQL replication grabs it, inform site B which clears it's cache and the flag. The cache is cleared by either site whenever a disparity is found.

Answer 2

Take a look at Query Notifications, here are a couple of articles about the subject:

SQL Server 2005 Query Notifications Tell .NET 2.0 Apps When Critical Data Changes Using Query Notifications in .NET 2.0 to handle ad-hoc data refreshes

Answer 3

Sql Cache Invalidation might help.

Answer 4

I've used Message Queuing for this functionality. This solution also copes with downtime between the apps. Just make sure you create the queue as a transactional queue to ensure messages are saved in case of power failure.

See http://www.codeproject.com/KB/database/SqlMSMQ.aspx for an example of how to use it on SQL Server 2005.