Standby servers are a great mechanism for ensuring the high availability of your enterprise applications. In this tip I'll define standby server and offer options and recommendations for configuring and maintaining such servers.
A standby server is a computer that is located in close proximity to the production server(s). A standby server can be used to temporarily replace a production server if it experiences a hardware failure. It could also be used to verify that you can recover databases from their full backups.
Important considerations when setting up a standby server
I will discuss this list of questions to ask yourself in greater detail below.
1. Will your standby server be used to verify backups or log shipping? Will it be used for both?
2. If the standby server is used strictly to verify production backups, what is the total size of all databases that you must verify?
3. How many production servers do you intend to replace with the standby server in case of a catastrophic event? What is the most powerful server you would need to replace with the standby server?
4. How much disk space would you need to restore all databases currently residing on production servers to be replaced with the standby server?
5. How much transaction log space is used when you perform index maintenance on your servers?
6. How many SQL Server logins do you use in your applications?
7. If your data must be available 100% of the time, even in the case of a natural disaster, do you have a safe, geographically separate location for storing your standby server?
8. Does your operating system and database management system have the same service packs on production and standby servers?
Using a standby server to ensure that database backups are valid
Let's say you're an enterprise DBA responsible for numerous mission-critical servers. You feel great about your disaster recovery strategy be
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
cause all of your backups seem to complete in a reasonable timeframe and your jobs never fail. Then one of your mission critical servers dies and you're left with a tape containing the full backup from the night before the failure. You quickly transfer the backup to the server you want to use in place of the one that is no longer available, fire up Enterprise Manager, choose "Restore Database" and … oops, you get an error similar to the following: "The backup set isn't valid." Murphy's Law never fails: Your backups may be useless when you need them most.
What can you do to avoid such situations? Make sure you can always restore databases from your backups. Fortunately SQL Server has come a long way from its humble beginnings. If you advise it to verify backups upon completion (as you absolutely should by using the RESTORE VERIFYONLY command), it will alert you to a backup problem. RESTORE VERIFYONLY was available in previous releases of SQL Server but wasn't always dependable. This statement has been enhanced with SQL Server 2005 to perform additional checking and increase the probability of detecting errors. However, the only true way to be 100% sure your backups are valid is to actually restore them and check the data on a standby server. Why, you would need a full-time employee to verify every single backup, you may complain. Judge for yourself: Would you prefer to lose mission critical data or hire an additional DBA to verify backups? In fact, if you automate the process of copying backups and restoring them to a standby server, you might not need an additional full-time DBA.
Using a standby server to continuously ship transaction log backups to a server replacing the production server in the event of a catastrophic failure
SQL Server provides a great mechanism for setting up a standby server – log shipping. Configuring log shipping is easily accomplished by using the Maintenance Plan Wizard. Note that log shipping is only supported in SQL Server Enterprise Edition. Any other edition requires you to configure log shipping manually, but you should be able to figure out how to copy and restore production database log backups to a standby server. Fortunately SQL Server 2005 supports log shipping with Standard Edition and Workgroup Edition as well as Enterprise Edition.
Whether you use the standby server to verify database backups or for log shipping you must ensure that the operating system and the database engine have the same service pack level as the production server. For example, if your production server is running Windows Server 2003 Service Pack 1, so should your standby server. This way if you fail over to the log shipping server, you can be sure your application will behave exactly the same as it does against the production server. The fewer variables you have to work with the easier it will be to switch over to the log shipping server when need arises.
About the author: Baya Pavliashvili is a DBA manager with Healthstream - the leader in online healthcare education. In this role, Baya oversees database operations supporting over one million users. Baya's primary areas of expertise include performance tuning, replication and data warehousing. He can be reached at
baya.pavliashvili@healthstream.com.
More information from SearchSQLServer.com
Tip: Log shipping a replicated database
Tip: How to restore from a transaction log
Tip: Worst practices for SQL Server backup and recovery
