Standby Server Setup
By Kalman Toth, M.Phil., M.Phil., MCDBA
January 27, 2005
Principal Trainer at: http://www.sqlusa.com

If you worried about being down for more than hour, you need a standby server.

Ideally the standby server should be identically configured to the production server, frequently however is not. That creates problems after failover.

Microsoft has an excellent facility to keep the standby server up-to-date: log shipping. You can write your own log-shipping or use a prepackaged version from Microsoft. If you do, you need a third server which serves as a monitor server for log-shipping. In typical setup the servers are linked both ways and full backup synchronized monthly, weekly or daily.

You also have to setup an alert. Assume you logship each hour. The alert should be set if you fresh data in a key table has not changed for an hour and a half.

Log-shipping is excellent to verify your data as well, since it has to be restored on the standby server.

Where should the standby server located? For big companies, mandatory to have an alternate site. For small companies, production and standby maybe side by side, purely for reasons of economy.

To make the standby read-only database available, you just have to change database options the following way:

use master

go

RESTORE DATABASE GeneralLedger WITH RECOVERY

go

Server name/ip address change is necessary vis-à-vis several software packages or application servers.