Recommended Stretagy for Bi-Directional SQL DB Synchronization

Hi,

Our company is an independent Voice applications solution provider with number clients using our suite. We have a CT application suite which is running with Application Server and SQL Server 7 / 2000 as DB Engines at the back end.

The SQL server has two databases configured:

Logging Database - Massive updates every second, the data grows rapidly,

Configuration Database - Generally small-sized and updated occasionally.

Now we want to have the reslience implemented on the server. We have to synchronize the two databases 'real-timely' and in 'efficient' manner, so that if Primary server or its Databases gets unavailable, the users are seamlessly switched over to the Secondry server that will have its own set of data updated and well synchronized.

Typically, it can be explained as follows:

1. We will have 2 database servers A - Primary (acting as publisher) and B - Secondary (acting as subscriber). Our application will be initially connected to A.
2. When A becomes unavailable (for whatever reason), the application will fail-over to B.
3. All the users will be switched to server B and the updates are being done accordingly without being replicated on Server A temporarily.
4. When A is back on-line, A needs to be brought up-to-date with B automatically (In other words, I shouldn't have to manually export all the data from B to A ).

Our requirements are:

• The system should support Bi-directional Synchronization between both the servers for their set of databases (the logging and configuration).
• There will be constant and heavy activity in Logging Database, thus if one server gets down the data should be logged and maintained as it is on second server and on fail-back no data-loss should occur with minimum latency time.
• There could be a scenario when a server fails-over for a week's time, there will be constant logging each second! Once it fails-back the system should rapidly synchronize the data without noticeable delay among the two server database sets.
• The system should also work fine if certain amount of records are purged over a time period.

Our concern is, observing the above scenario, how any of your SQL server replication strategy can help us achieve the requirements.  

Thanks
John Received on Fri Feb 11 2005 - 07:59:28 CST