How to keep client side cache in sync ?

Question

I have a SQL server and couple Windows clients and cache of some tables as objects on clients. I currently have a pull mechanism where every one minute or so clients query one row in DB to understand if cache is still good if changed they sync everything, but I want to change this mechanism to push based. I mean I want server to “ping” clients in the event of an update. On server side I assume I can use triggers but on client side what do I need to implement?

Answer 1

Query Notifications is the only mechanism for SQL to push to client a change notification. The client side is best known as the SqlDependency. See http://rusanu.com/2006/06/17/the-mysterious-notification/