Synchronize a client database with the central database

Question

I need to update existing data or insert new data from client database say DB1 into central database say DB2 both holding same schema and both databases reside in same machine. The updates are not biderectional. I just want changes to be reflected from client(DB1) to server(DB2).

The client database(DB1) is nothing but the backup database(Full database backup consisting of mdf and ldf files) which is attached to the same server where the central database(DB2) exists. I am not going to make any changes to the backup database(DB1) once it is attached to the server. The backup database(DB1) already has the modified data which i want to update it to central database(DB2) . So how do i do programatically using C# .NET?.Can you give any example code?

I have tried transactional replication with push subscription without sending the snapshot. The problem is that the i want to update the modified data from DB1 to central database DB2 at the first shot itself but transactional replication will not allow me to do so. It will not send any modified data which is already present in DB1. So the initial data present in DB1 is untouched when you try to send without snapshot. The backup database (DB1) already has the modified data prior to replication. How do i tackle this as i am not going to insert any new or modify data into the backup database(DB1) after i set replication.

Thanks and regards, Pavan

Answer 1

To achieve this you have the following options:

1.) Use SQL Server Transactional Replication. Make DB1 as Publisher, DB2 as Subscriber and go for Pull or Push based subscription. All changes in DB1 will be simply reflected to central. If any changes we there in Central for the same tuple, they will be overwritten by DB1 changes.

Advantages: Easy to implement and reliable Disadvantages: Very little customization

2.) Use Microsoft Sync Framework SQLDataBaseProvider. Advantages: Very Flexible Disadvantages: I have heard bad things about it but never tried.

3.) Custom Implementation: This is a bit hard as you need to track changes on DB1. One option can be reading transactional logs which Transactional Replication does internally or other option is to use trigger and build knowledge of changes. Then you need to write a library or routine which will get you change knowledge then it will apply to central.

Edit: For backup and restore database progmatically:

http://www.mssqltips.com/tip.asp?tip=1849

Answer 2

Microsoft Sync framework is the best solution, especially if you are using express editions (in which case replication will not work).

Sync framework is quite straight-forward if used with SQL server change tracking in sql server 2008. You can define your mode of synchronization as well (bi-directional, upload only, download only) and also define what happens when there are conflicts (for instance constraints get violated, etc).

And yeah - just google for an example there are several straight forward walk throughs available on the topic, including peer-peer synchronization (might be the one you require) and client-server synchronization (client should be sql server compact edition).

Answer 3

You may also want to explore SQL Server's merge replication functionality. It is the replication type designed to allow satellite databases to automatically post back their results to a central repository.