Does SQL Server 2005 Clustering Really Make Sense for My Organization's Needs?

Warm Backup Server

A warm backup refers to having a spare physical server available that you can use as your SQL Server 2005 server should your production server fail. Generally speaking, this server will not have SQL Server 2005 installed, or any database backups installed on it. This means that it will take time to install SQL Server 2005, restoring the databases, and re-pointing applications to the new server before you are up and running again. It also means that you may lose some of your data should you not be able to recover the transaction logs from the failed production server, and you only have your most recent database backups to restore from.

If being down for a while, or possibly losing some data, is not a big issue, then having a warm backup server is the least expensive way to ensure that your organization stays in business should your production SQL Server 2005 server fail.

Hot Backup Server

The major difference between a warm backup server and a hot backup server is that your spare server will have SQL Server 2005 preinstalled on it, and in addition, a copy of the most recent database backups on it. This means that you save a lot of installation and configuration time, getting back into production sooner than having a warm backup server. You will still need to re-point your database applications, and you may lose some of your data should you not be able to recover the transaction logs from the failed server.

Log Shipping

Log shipping is one step beyond what a hot backup server can provide. In a log-shipping scenario, you have two SQL Servers, like with the hot backup server. This includes the production server and a spare. The spare will also have SQL Server 2005 installed. The major difference between a hot backup server and Log Shipping is that Log Shipping adds the ability to automatically send not only database backups from the production server to the spare server, but also database transaction logs, and automatically restore them. This means that there is less manual work than with a hot backup server, and less chance for data loss, as the most data you might loose would be the equivalent of one transaction log. For example, if you create transaction logs every 15 minutes, then in the worst case, you would only loose 15 minutes of data.

Replication

Many authors include SQL Server 2005 replication as a means of increasing high availability, but I am not of this camp. While replication is great for moving data from one SQL Server to one or more other SQL Servers, it is a lousy high availability option. It is much too complex and limited in its ability to easily replicate entire databases to be worth the effort of spending any time trying to make it work in failover scenarios.

Database Mirroring

Database Mirroring is new to SQL Server 2005, and in many ways, it is a very good alternative to SQL Server 2005 Clustering. Like clustering, Database Mirroring is used to automatically failover a database to a backup server. But the biggest difference between clustering and Database Mirroring is that data is actually protected, not just the SQL Server 2005 instance. In addition, Database Mirroring can be done over long distances, does not require specially certified hardware, is less expensive than clustering, requires less knowledge to set up and manage, and can be fully automatic, like clustering. In many cases, Database Mirroring is a much better choice than clustering for high availability.

Third-Party Clustering Solutions

Microsoft is not the only company that offers a clustering solution for SQL Server 2005. Several third-party companies offer solutions. In general, these options are just as expensive and complex as Microsoft's clustering option, offering few, if any, benefits over what Microsoft offers.

Which High Availability Option Should I Select?

While I hope that this brief rundown has helped you clarify your high availability options, it is not enough information for you to make a good decision. If the best solution is not self-evident (is it ever?) you will need to spend a lot of time researching the above options before you can really determine what is best for your organization. There is no magic formula I can give you to determine what is best for your organization. As the DBA, it is your responsibility to evaluate your needs, become aware of the different ways to meet them, and then determine which option best meets those needs. SQL Server 2005 clustering may or may not be your best option.