I have a scenario and I haven't found a solution yet. I have two databases in two separate SQL Server 2008 R2 database servers. There's only one method I can use to exchange information between servers: CD/DVD. The servers are far from each other (about
300 km) and the process of backing up one instance and moving data to the other and getting information back takes abut 2 days. Both database instances have the same schema, so schema synchronization is not an issue.
One of the databases is the Master, and another has only portion of data which I call Partial. People insert records in Partial, and these records should be moved to the Master, and after some processing, the results should be transferred back to the Partial.
There are more than 15 tables involved, two of them have Identity fields with different ranges set for Master and Partial. I couldn't find a way to synchronize these DBs. One of the major problems here is that data transfer can only be made using db backups
and file transfer using CD/DVD. Another problem is that while the Partial data backup is on its way to the Master, we cannot stop the application to prevent new changes to Partial. The same is true about the Master.
Is there any solution?
