Alternatives to Clustering for SQL Server

SQL Server clustering is just one of many options available to help provide high availability within your SQL Server 2012 instances, and high-availability solutions consist of multiple layers of solutions to ensure uptime. This article takes a brief look at alternatives to clustering, starting with the least expensive and easy-to-implement options and working along to the more expensive and more difficult-to-implement options.

Cold Backup Server

A cold backup refers to having a spare physical server available that you can use as your SQL Server 2012 server should your production server fail. Generally speaking, this server does not have SQL

Server 2012 or any database backups installed on it. This means that it can take time to install SQL Server 2012, restoring the databases and redirecting 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 last transaction logs from the failed production server and you have only your most recent database backups to restore from.

If being down a while or possibly losing data is not a major concern, having a cold backup server is the least expensive way to ensure that your organization stays in business should your production SQL Server 2012 server go down.

Warm Backup Server

The major difference between a cold backup server and a warm backup server is that your spare server (the “warm” one) has SQL Server 2012 preinstalled, may be used as a development server where it has some less recent production databases installed. This means that you save a lot of installation and configuration time, getting back into production sooner than you would with the use of a cold backup server. You still need to redirect your database applications, refresh the data to the most current, and you may lose some of your data should you not recover the last transaction logs from the failed server.

Log Shipping

In a log-shipping scenario, you have two SQL Servers, includes the primary (production) server and a secondary. The secondary server also has SQL Server 2012 installed and configured. The major difference between a warm backup server and log shipping is that log shipping adds the capability not only to restore database backups from the production server to the spare server automatically, but also to ship database transaction logs and automatically restore them. This means there is less manual work than with a warm backup server, and 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 lose only 15 minutes of data.

Replication

Many experts include SQL Server replication as a means to increase high availability, but the authors are not among them. Although replication is great for moving data from one SQL Server to others, it’s not a good high-availability option. It is much too complex and limited in its capability to easily replicate entire databases to be worth the effort of spending any time trying to make it work in failover scenarios unless you already have the replication expertise.

Database Mirroring

Database mirroring in many ways is a good alternative to clustering SQL Server. Like clustering, you can use database mirroring to automatically failover a failed SQL Server instance to the mirror server, on a database-by-database basis. The biggest difference between clustering and database mirroring is that data is actually protected when there are two different copies of the data stored in mirroring. In clustering, the shared disk can be a single point of failure. In addition, database mirroring can operate over long distances, is less expensive than clustering, requires less knowledge to set up and manage, and the failover can be fully automated in some circumstances, like clustering is. In some cases, database mirroring may be a better choice instead of clustering for high availability.

AlwaysOn Availability Groups

AlwaysOn is a high-availability and disaster recovery solution new to SQL Server 2012 that enables you to maximize availability for one to many user databases as a group. Deploying AlwaysOn involves configuring one or more availability groups. Each availability group defines a set of user databases that can failover as a single unit by leveraging a Windows Failover Cluster and its clustered SQL Server name and IP address. The availability group involves a set of five failover partners, known as availability replicas. Each availability replica possesses a non-shared copy of each of the databases in the availability group where the data can be maintained either synchronously or asynchronously. One of these replicas, known as primary replica, maintains the primary copy of each database. The primary replica makes these databases, known as primary databases, available to users for read-write access. For each primary database, other availability replicas, known as secondary replicas, maintain failover copy for each database that can also be configured for read-only access.

Third-Party Clustering Solutions

In addition to Microsoft, there are third-party partners that also offer high availability solutions for SQL Server. In general, these options may be more expensive than and as complex

What to Do?

Although this brief introduction clarifies your options, it may not be enough information for you to make a good decision. If the best solution is not self-evident, then you need to spend time researching the preceding options before you can determine what is best for your organization.