Unanswered: How can I set up a SQL server 2000 backup / mirror Server?
Hope someone can help.
I have an urgent requirement to setup a backup SQL server 2000. This will run as a mirror of the live SQL server 2000 which processes about 5000+ statements per minute. I will need to keep the mirror server as close to the live as possible, so if we need to take down the live server we can switch users to the backup server (it’s ok I can do this in code – client side.) and I may also be reporting from this server to take some of the strain from the live server (if that’s wise – not sure yet).
Can someone please point me in the right direction? What other software (apart from MS SQL server 2000) will I need?
What sort of link do I need between both servers? (note: I don't want to do this link using triggers for each table if possible)
Are there any pitfalls to look out for (i.e. I seem to remember something about auto id fields in tables but this was a long time ago so I could be wrong).
Please note I am a programmer with a lot of experience, but I don’t know that much about setting up SQL servers (well I can install one locally on my dev machine, but that’s really as far as I go) but I am willing to learn and investigate anything, so just giving me sites that I can look at will be enough.
Keeping your secondary server "...as close to the live as possible ..." and available for reporting will mean transactional replication, because log shipping has an inherent delay from the time the log backup is taken until it is applied to the standby server, whereas transactional replication is near real-time (based on the network delay and any processes on the standby server which would prevent the transactions from being applied to the database(s).
From a software standpoint, you need just your OS and SQL Server Standard edition (licensed for active use) for your standby server. Ideally, you would also have a third server to act as your distributor (with active licenses also). Reporting from your standby server would help offload some work from the OLTP server, but if your users are holding active read locks on tables on your standby server, the transactions from your primary server cannot be applied until thise locks are released. This contention, if allowed to last too long, can cause the replication task(s) to fail (the voice of experience!).