Clustering and Your Organization

Many DBAs seem to have difficulty understanding exactly what clustering is. What do we (and Microsoft) mean when we refer to SQL Server 2005 clustering? Here's a good working definition:

• Microsoft SQL Server 2005 clustering is a high-availability option designed to increase the uptime of SQL Server 2005 instances. A SQL Server 2005 cluster includes two or more physical servers (called nodes) identically configured. One is designated as the active node, where a SQL Server 2005 instance is running in production, and the other is an inactive node, where SQL Server is installed but not running. If the SQL Server 2005 instance on the active node fails, the inactive node will become the active node and continue SQL Server 2005 production with minimal downtime.

• This definition is straightforward and to the point, but it has a lot of implications that are not so clear, and this is where many clustering misunderstandings arise. One of the best ways to more fully understand what clustering can and cannot do is to drill down into the details.

What Clustering Can Do

The benefits of using SQL Server 2005 clustering are very specific. Clustering is designed to boost the availability of physical server hardware, the operating system, and the SQL Server services. When any of these aspects fail, it causes an instance of SQL Server 2005 to fail. If any of these three fail, another server in a cluster can automatically be assigned the task of taking over the failed SQL Server 2005 instance, reducing downtime.

The use of clusters can help reduce downtime when you're performing maintenance on cluster nodes. For example, if you need to change out hardware on a physical server or add a new service pack to the operating system, you can do so one node at a time. First, you would upgrade the node that is not running an active SQL Server 2005 instance. Next, you would manually failover from the production node to the now upgraded node, making it the active node. Then you would upgrade the currently inactive node. Once it is upgraded, you would fail back to the original node. This cluster feature helps to reduce the overall downtime caused by upgrades.

What Clustering Cannot Do

The list of what clustering cannot do is much longer than the list of what it can do. This list is long because of the many myths of clustering. Clustering is just one part of many required parts to ensure high availability. In many ways, it is not even the most important part; it is just one of the many pieces of the puzzle. Other aspects of high availability, such as the power supply, are actually much more important. Without power, the most expensive cluster hardware in the world won't work. If all of the pieces of the puzzle are not in place, spending a lot of money on clustering may not be a good investment. We'll talk about this more later.

Clustering does not guarantee a complete absence of downtime. Some DBAs believe that a cluster will reduce downtime to zero. This is not the case. It can help reduce downtime but not eliminate it. Clustering is also not designed to protect data. This is a great surprise to many DBAs. Data must be protected using other options, such as backups, log shipping, or disk mirroring.

Clustering is not designed for load balancing, either. Many DBAs, especially those who work for large commercial Web sites, think that clustering provides load balancing between the nodes of a cluster. This is not the case. Clustering only helps boost the uptime of SQL Server 2005 instances. If you need load balancing, you must look for a different solution.

Clustering requires expensive hardware and software. It requires certified hardware and the Enterprise or Data Center versions of the operating system and SQL Server 2005. Many organizations cannot cost-justify this expense. Clustering is designed to work within the confines of a data center, not over geographic distances. Because of this, clustering is not a good solution if you want to failover to another data center located far from your production data center.

Clustering requires more highly trained DBAs. As you will quickly become aware as you read this chapter, SQL Server 2005 clustering is not for beginning DBAs. DBAs with clustering experience command greater salaries.

Although SQL Server 2005 is cluster-aware, not all front-end applications that use SQL Server 2005 as the backend are cluster-aware. For example, even if the failover of a SQL Server 2005 instance is relatively seamless, a front-end application may or may not be so smart. There are many applications that require users to exit and then restart the front-end application after a SQL Server 2005 instance failover.

Choosing SQL Server 2005 Clustering for the Right Reasons

When it comes right down to it, the only justification for a SQL Server 2005 cluster is to boost the high availability of SQL Server instances, but this justification only makes sense if the following are true:

• The cost (and pain) of being down is more than the cost of purchasing the cluster hardware and software and maintaining it over time.

• You have in place the ability to protect your data. Remember, clusters don't protect data.

• You don't need to be able to failover to a geographically separate data center, unless you have a Microsoft certified third-party hardware and software solution. SQL Server 2005, out of the box, does not support geographically dispersed clustering.

• You have in place all the necessary pieces required to support a highly available server environment, such as backup power and so on.

• You have DBAs on staff qualified to install, configure, and administer a SQL Server 2005 cluster.

If all of these things are true, your organization has all the right reasons for installing a SQL Server 2005 cluster, and you should proceed. But if all of these pieces are not in place, and you are not willing to put them into place, you are most likely wasting your time and money with a SQL Server 2005 cluster and would probably be better off with an alternative, high-availability option, such as one of those discussed next.

Alternatives to Clustering

SQL Server 2005 clustering is just one of many options available to help ensure the high availability of your SQL Server 2005 instances. In this section, we take a brief look at alternatives to clustering. We start with the least expensive and easy to implement options and then take a look at the more expensive and harder-to-implement options.

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 repointing applications to the new server before you are up and running again. It also means that you may lose some of your data if you cannot recover the transaction logs from the failed production server and you only have your most recent database backups to restore from.

If being down awhile or possibly losing data are not big issues, 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 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 repoint 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 not only to send database backups from the production server to the spare server automatically; it also can send database transaction logs and automatically restore them. This means that there is less manual work than with a hot backup server and that there is less chance for data loss, as the most data you might lose would be the equivalent of one transaction log. For example, if you create transaction logs every 15 minutes, in the worst case, you would only lose 15 minutes of data. Log shipping is covered in detail in Chapter 19.

Replication

Many experts include SQL Server 2005 replication as a means of increasing high availability, but we are not of this camp. Although replication is great for moving data from one SQL Server to one or more 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. Replication is covered in more detail in Chapter 16.

Database Mirroring

Database mirroring is new to SQL Server 2005 and in many ways is a very good alternative to SQL Server 2005 clustering. Like clustering, database mirroring can be used to automatically failover a failed instance of SQL Server 2005 to a spare server, on a database-by-database basis. 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 is fully automatic, like clustering. In many cases, database mirroring is a much better choice than clustering for high availability. Database mirroring is covered in detail in Chapter 17.

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.

What to Do?

While we hope that this brief run-down has helped you clarify your options, it is not enough information for you to make a good decision. If the best solution is not self-evident, you will need to spend a lot of time researching the preceding options before you can really determine what is best for your organization.