Successful Deployment Strategies - Professional SQL Server 2005 Performance Tuning - page 527

and file group backups. Log backups are critical for production deployments, as they are essential for
point-in-time recovery during database failures. A large number of third-party vendors exist that provide
backup solutions for SQL Server. If you have the budget or the need for extra features, you should take
a look at some of these vendors. Part of a sound backup strategy is a plan to restore databases in case of
failures. This plan should include regular testing of backups to ensure that backups are good and will not
fail to restore. SQL Server 2005 introduced the concept of online restores, which is applicable to databases
with multiple file groups. Provided the primary file group is online and active, the other secondary file
groups can be restored while users continue to access the date in the rest of the database, minimizing
downtime in case only a part of the database is affected and needs to be restored.
Best Practice
Always have a sound backup and restore strategy. If this is a production system you
must have log backups.
Clustering
SQL 2005 failover clustering provides high availability support for SQL Server 2005 instances. This setup
usually consists of two or more nodes that share a set of disks. One of the nodes is the primary node
and has control over all shared services and drives. The front end application connects to the database
using a ''virtual'' name as opposed to the actual host name. This provides for seamless failover between
the primary and secondary nodes without the need for any reconfiguration on the front-end application.
However, the clustering solution does not protect against disk failure. If the shared disk drives fail, there
could be potential damage to the SQL Server databases. However, most storage arrays today provide
some sort of fault tolerance to minimize the loss of data due to disk failures.
Clustering is a great choice when there is a need for automatic failover in case of hardware or software
failures and minimal downtime. However, it is important to note that even though SQL Server may
fail over automatically in case of a failure, the front end application may detect a lost connection to the
database, causing users to re-login in such a scenario.
Best Practice — Clustering
Be aware of the limitations of SQL Server clustering.
Database Mirroring
Database Mirroring, a new feature introduced in SQL Server 2005, helps overcome some limitations
posed by clustering. The process of database mirroring involves setting up a hot standby mirror database
and can be configured for automatic failover. There are three different configurations that provide vary-
ing levels of fault tolerance. Database Mirroring provides seamless failover capabilities for the database
as well as the applications, provided the application supports database mirroring. This feature can also
be used to avoid downtime incurred when doing hardware or software upgrades to the primary server,
provided all updates have propagated over to the secondary or standby node. This might seem like a