Network wide data synchronization

Question

We have developed an C# WinForms application running on a MS SQL Server. Until today we are using a very simple self-made OR-Mapper (using Reflection) with a cheap caching mechanism implemented with a singleton pattern. There's a user triggered "reset" function. The architecture is a simple two tier (client, server) three layers (presentation and logic on the client, MS SQL as data tier on the server).

Typically we load about 300 rows with about 30 columns, which are changing quite often (and some other tables which change rarely). Different instances of this application are running on different client computers have the same 300 rows loaded and are changing data on a random row. The application update the data immediately on the database. Before they do so, the integrity are checked so the user get's a feedback if something is wrong. But for some of the rows the user sees old values on the screen, he has to manually reset the cache when he wants to be sure having up-to-date data.

It would be nice if the applications consider the changed rows itself. There are updates every few seconds. But only some of these updates are relevant for the other applications (depending on the active filter...)

Solutions which I have in mind:

1. Using a OR-Mapper with a common cache (e.g. NHibernate with a clustered cache)
   • Open questions: Are these L2 caches made for client apps? Can I trigger UI updates when cache changes happen? Other OR-Mapper based solutions?
2. Implementing another tier using an application server, moving logic to this application server. Run only one instance, which caches the data and sends events on the Logic Object's which notify the other clients of updated data. Don't cache any data on the clients.
   • Open questions: Load on App Server? Overhead communication to the app server? Amount of work moving a logic DLL into an App Server..? Data source for Reports (directly/App server)?
   • Something like this user considers: https://stackoverflow.com/questions/7593884/what-technology-to-use-for-data-persistence-cache-and-synchronization-in-n-tier
3. Add some kind of data changes notifications on the logic level (broadcast based noticiation...)
   • Open questions: Existing libraries? Load on SQL Server?
4. Completly other solution?

I'm aware that I don't get the solution here. Just need some thoughts about these solution (or new ones)... Thanks for any inputs!

Answer 1

I have not used this myself, but I believe that SQL Server has dependency functionality that can notify your application when changes are made to previously queried data.

I don't know how well that scales with the number of applications that are subscribing to those notifications, though. There is a note on that page that I linked saying that this approach was not designed to be used by large numbers of clients.

Answer 2

i would like to share my experience here.In similar scenario where all clients were on a single corporate LAN. I implemented a UDP client(sender/receiver) in my client application that broadcasted "DataRefreshMessage" on Network BroadCast address whenever a client updated the data.and all other clients updated their views upon receiving the "DataRefreshMessage".

I am aware that UDP is not reliable but was reliable enough for my requirements and my solution is working perfectly fine.

Answer 3

Honestly, it sounds to me like you are making a mountain out of a mole hill. if you are just loading 300 records just reload the data from the database every so often and don't bother with caching. The data set is so small its going to be really fast anyway and you wont have the additional complexity and points of failure of using a distributed cache and all that.

If you need to be sure that only one person writes to the database, and when that person writes its the most recent data. You could implement some sort of database lock table, and check if someone else is writing before you do so.