I have a potential client that wants to use SQL 2000 Merge Replication to provide near real-time synchonized databases at their central office and three remote locations. End users will need to make updates at their local SQL Databases and have those updates replicated to the other offices in as close to real-time as possible.
So the customer came up with a few questions that I'm hoping you can help out with:
1. How reliable is this configuration and what happens if the network link between the locations goes down?
2. How close to real-time replication can be reasonably achieved?
3. How much regular administration is required once the replication is setup and running?
4. How comfortable would you be with doing this on SQL 2000? Would waiting for SQL 2005 be a better choice?
5. Are there any common things to watch out for when trying to setup this type of environment?
Here is some additional information about the planned setup:
--Initially, there will be two databases setup for replication between the 4 offices with an additional 8 databases setup for replication if the first two work successfully.
--All of the databases are part of commercial applications and may not necessarily be designed with replication in mind.
--Updates must be made at any of the 4 office locations and be visible at the other offices as soon as possible after the update is made in one location.
--A single update can affect multiple tables and thus must be handled as a single transaction by the replication.
--This customer had a consultant come in a couple years ago that told them not to do replication because it was not reliable.
Thanks in advance for any advice you can provide.
Shawn Smiley, MCDBA