Client Server DB Synchronization best Models or Practice ?

This question is not directly related to C#, but as a C# developer and frequent forum poster I decided to ask people here.
I have a complex application at work that is client/server application. Client DB and server DB is synchronized once a week using
MSSQL Replication Jobs. We were thinking to replace this model and I was just wondering if anybody has had any experience with it or knows any good model or practice to perform synchronizations. Like for example design Web Service or anything like that. We are open to completely change the Model if needed and proved to work better.

Thank for any suggestions !!!

Is there any reason why client's need their own copy of the database. To be honest this is really old thinking : i.e. batch processing databases.

In the old days batch processing often took the form of files being passed between machines. Mainly due to the lack of really high-speed and reliable networking links. Of course this isn't the case these days.

This leads to all sorts of problems, with client DB being out-of-step with server DB and of course server DB updates not being fed through to clients immediately.

MSSQL server is capable of handling thousands of connections at the same time : just change the connection string to be the server name of the MSSQL server.

If you need additional control you could think about writing a .NET remoting server which talks to the MSSQL server. Clients would then talk to the .NET remoting server. This is useful if you need clients to automatically pick up changes in the database (e.g. events), or you need finer control over locking of database nodes (e.g. if the database contains a tree structure).

Darwen.

Thanks Darwen for your answer.

Well, I took over existing application that was used for 3 years and 100s of cleints. The reason it was implemented this way is to take extra precautious on the client side in case when internet is not avaialable. I see what you are saying and I am with you on that but I do not think that the company will go with this idea, I was already tryin got push tham on that but they are claiming that that are not sure aboout clinet internet connectivity and do not want ot rely on it completly. Also they are afraid that they will get many clients not able to use the application in case of internet failure. I know it is old thinking but I am trying my best to convince them

Actually they do have a point. Redundancy of databases is pretty standard to handle the case of a net failure.

However there is nothing stopping you from effectively writing 2 systems : one which works then the net is up, and other other which works when it is down.

Transfer of an entire database in one block isn't necessarily the right answer. It might be better to only download the changes which have occurred since the last connect. And machines are so fast these days they might be surprised how quickly one database can get in sync with another.

I'm sure there's a solution in SQL server for this very thing. I'd be surprised if there wasn't. Try looking up "data redundancy" or something.

Darwen.

I like this idea better:) I thought about it too, it seems like most optimal one that will take care of net failure and reduce data redundancy.

However I am not sure what to use to sync DBs after net failure to fill the server DB with all client changes. Should we stick with replicationson SQL or there are some other better solutions for that?

Darwen, do you have any idea?

Thanks

Why not try this :

Don't write to the SQL databases directly. Instead all interaction with the SQL databases go through an application (using .NET remoting or something).

Have one server app (which you've written) which writes all data to BOTH local & remote databases.

It talks to a similar server app on the remote site which does the writing to the remote database.

If the connection goes down both servers will know, and so both local & remote servers can keep a log of changes.

When the connection comes back up both servers request the changes which have occurred in the given time to bring their databases back into sync.

This could be tricky : but it should be a starting point.

Darwen.