1

I have a one server with db (sql server) and on client machines every one has local db (sql sever) and a C# desktop application. Now I have multiple clients running the application to show records and marking the record as shown and server db is in constant change. Clients communicate with server via wireless network which can be dropped sometime.
Its an event attendance system. Before event begin we have pre-registered guests in server db so do in local db. On event day people come to the event but cannot bypass without getting a registered at server db (so new people are added to server db only). Client is just notebooks placed at doors of event location where each guest is scanned by his/her card (which he will get once he register at the server). The client scans the card fetches the information against that card from server and mark his attendance. It is done every time he come in. (attendance marked every time)
So the problem is:
Case 1: What I am doing right now is, on client the application first look for server availability if found fetches data from server db and mark as read back on server db (no local db involved). When the connection fail the application connect to local db and searches for the record there and mark as read in local db. The local db can be synced manually by the sure (the mark as read on local db will be sent to server db and new records from the server are fetched into local db).
Case 2: It can be the case that I never look for server but only my local db for every record and save the mark as read on the local db as well. The manual sync operation can be automatic by writing a stored procedure and a job which run on server db and local db which perform sync between two as soon as database changes on either side. Which approach can be better in terms of performance and reliability. Or do you have a 3rd case?

user2524772
  • 13
  • 3
  • What are you doing when the server is ofline, 2 clients make change to the same data and they both sync when the server is offline? Is your server often offline? Why is it needed to have local db? Most solution have only server db. – Milan Halada May 02 '14 at 12:27
  • No the server never goes offline but the connection between server and client which is via wireless can drop and in that case application cannot connect to server. If I don't use local db at client machine then that machine will be useless until the network is back on (say 15 min), so with the local db which the user has synced say 5 minutes ago can still show records from the local db and save mark as read on the local db and one the network comes up it will push the changes (mark as read) back to server db. (I know that the records inserted in the server db in those 5min will not be shown) – user2524772 May 02 '14 at 12:37
  • In my opinion a combination of both cases might be good for you - While server is availale, use it, but store the data in local db, since you already have them in your app. Every hour/day/week/client start/....(depending on data ammounts and how often they are changed) sync local db with server. – Milan Halada May 02 '14 at 12:51
  • You mean while server is available client will select rows from server db over the network and mark as read (time stamp) in local db? Actually the mark as read(time stamp) is inserted into separate table with record's primary key and timestamp. So why should I save it on local db if I am selecting rows from server db over network, isn't it should also mark the timestamp on server db also. And note that data is likely to change every 30 seconds or a minute. So I have to update the local db often (sqldependency?). – user2524772 May 02 '14 at 13:16
  • Problem is i have no idea what your app does. Are the data in local and server db the same? I imagine it as lets say book list in server db. Client wants to viewit so he requests it from server db. Also it stores it right away into local db. So next time client wants to view books and server is not available he can. – Milan Halada May 02 '14 at 13:32
  • Its an event attendance system. Before event begin we have pre-registered guests in server db so do in local db. On event day people come to the event but cannot bypass without getting a registered at server db (so new people are added to server db only). Client is just notebooks placed at doors of event location where each guest is scanned by his/her card (which he will get once he register at the server). The client scans the card fetches the information against that card from server and mark his attendance. It is done every time he come in. (attendance marked every time) – user2524772 May 02 '14 at 13:39
  • So if connectivity is a problem, and people can come and go through multiple exits/registration points you have to sync automatically and often, or even maybe try to add newly registered people to local databases right away(maybe some udp broadcast?). – Milan Halada May 02 '14 at 14:03

0 Answers0