We have three Servers running SQL Server 2008 which all contain a Product database. These are currently kept in sync once per day by sending a copy of the MDF to the two Subscribing servers and detaching the existing DB and replacing it with the latest copy.
The two subscribers contain a Customer database which is not present on the publisher. The Customer database contains customer purchase information, so it needs to reference ProductIDs in the Product table.
We have implemented Merge Replication on a similar set up in our testing environment and are preparing to implement these changes on our live system.
The end result should look something like:
In order to implement these changes, we will need to generate a snapshot and synchronise the data between the Publisher and the Subscribers. While this is occurring, any client application which relies on the data in the Product database will fail, and any Stored Procedures on the Customer database which make cross database joins will also fail.
We'd like to keep this downtime to a minimum or eliminate it completely.
We've had some crazy ideas thrown around which may or may not work (using synonyms to point a subscriber to another server's Product table while snapshot generation/synchronisation takes place) but they don't seem very elegant, if they'll work at all. I'm hoping someone else has had a similar issue and might have some insight :)