July 17, 2003 at 6:53 am
We are looking at setting up a SQL2000 database architecture whereby we have 1 primary server and an additional backup server. Users will connect to the Primary, with all transactions being mirrored onto the backup via continuous transactional replication. We are happy setting this up, but our issue is with what happens when we lose the primary? Users will then attach to the backup SQL box. As far as I'm aware Replication is one-way (Source-Destination), e.g. Primary to Backup. When we bring the Primary back online, is there a way of getting the Primary back in-synq with the Backup automatically? The only way I can think of doing this, is to drop replication, carry out a snapshot of the Backup to the Primary and then redo the Repliaction between Primary and the Backup (with users connecting to the up to date Primary server again). This requires manual intervention which we would like to avoid...if poss!!!
July 17, 2003 at 9:02 am
If you use merge replication then you wouldn't have to do anything. You can do transaction with queued updates, but it doesn't support text columns. Either is better than doing it manually since they will both manage identity key ranges for you.
Andy
July 17, 2003 at 10:27 am
You can also set transactional replication in both ways with loopback_detection ON.
With this all the transacctions from the primary will travel the the backup, what it wont return from the backup to the ´primary because of the loopback detection. When the primary fails, users will made changes to the backup and all those transacctions will be waiting in the distrib db of the backup servers. Once the primary is up again, you can replicate all the trans made at the backup, to tue primary.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply