How to get Synchronisation between two SQL Servers to perform better
Posted on 2008-06-20
We originally had a web site which used a MS Access database. Using SQL DTS we downloaded the database via FTP updated the master SQL Server 2005 database and then created a refreshed Access database and uploaded back to the website using FTP. The entire process took about 40 mins.
We have come to replace the web site database with a hosted MS SQL 2005 database. We have tried all sorts of approaches - replication, data comparison utliities, etc. all of which take at least 1.5 hours to do the same job !
Any suggested approaches to get the two databases to sync quickly and correctly without FTP (as this was a regular point of failure with the original approach). For example, are there any compression products out there that will compress the traffic?