A server cluster is comprised of the following essential elements:

As shown in Figure 3-1, for SQL Server, the external users or applications would connect to the SQL Server virtual IP address or name. In the example, Node 1 currently owns the SQL resources, so transparent to the client request, all SQL traffic goes to Node 1. The solid line connected to the shared disk array denotes that Node 1 also owns the disk resources needed for SQL Server, and the dashed line from Node 2 to the shared disk array means that Node 2 is physically connected to the array, but has no ownership of disk resources.

Figure 3-1. An example of a server cluster.

Now that the basics of the cluster components have been covered from a high level, it is important to mention two important cluster concepts that are exposed when a cluster is installed and used by an administrator:

If you are familiar with the Microsoft SQL Server 6.5 or Microsoft SQL Server 7.0 implementation of clustering, you will realize that SQL Server 2000 greatly enhances clustering functionality. In fact, with SQL Server 2000, clustering support was redesigned from the ground up. This is one of the reasons that the feature in SQL Server 2000 is called failover clustering, whereas in SQL Server 7.0, it was just called clustering.

One of the first differences with SQL Server 2000 failover clustering is the support for more than a two-node server cluster. Microsoft Windows NT 4.0 Enterprise Edition, the platform for which SQL Server 8.0 clustering was originally designed, only supported two-node clustering. SQL Server 2000 takes advantage of more nodes when they are available. With the introduction of Windows Server 2003, up to eight nodes can be supported by the operating system. As noted in Table 3-2, the 32-bit version of SQL Server is still limited to a maximum of four nodes per virtual server.

Instance support is the primary difference between SQL Server 2000 and earlier versions. An instance, simply put, is a separate installation of SQL Server that can run side-by-side with another SQL Server installation on the same machine. The instance can either be a default instance or a named instance. Think of a default instance as the way SQL Server has historically been architected. A named instance is just that—one that is given a name to make it distinct. SQL Server 2000 supports up to 16 instances (clustered or not), which can include 1 default and 15 named instances, or 16 named instances. Instances allow multiple SQL Server virtual servers to be installed on a cluster with ease. With SQL Server 6.5 and SQL Server 7.0 clustering, you were limited to at most two installations of SQL Server on any given server cluster.

SQL Server 6.5 and SQL Server 7.0 clustering relied on the concept of active/passive and active/active clustering. When you only have two installations of SQL Server, these concepts are easy to grasp. Active/passive clustering occurs when you have only two nodes, and one controls the active resources. Active/active clustering occurs when both nodes in the cluster have active resources (such as two SQL Servers). When instances were introduced with SQL Server 2000, these definitions proved problematic. Because the operating system and SQL Server itself can now support more than two nodes, the terms really do not fit. The new equivalent terms are single-instance cluster for active/passive and multiple-instance cluster for active/active. If you only have up to two nodes and two SQL virtual servers, active/passive and active/active might still apply, but it is better to use the new terminology. Single-instance clusters are also sometimes referred to as single active instance clusters, and multiple-instance clusters are sometimes referred to as multi-instance clusters or multiple active instance clusters.

Other enhancements with SQL Server 2000 failover clustering include the following:

Each node of the cluster is capable of "owning" a resource, whether it is SQL Server, a disk, and so on. The node currently owning the disk resource (whether it is a SQL Server disk, or the quorum disk), which hopefully is the designated primary owner, reserves the resource every three seconds. The point of the reservation process is to protect against physical access by another node; if a node loses the reservation, another node can now take ownership.

For example, if the node owning the instance fails due to a problem (network, disk, or something else) at second 19, the competing node detects it at the next reservation cycle. If the reservation cycle fails for the node that owned the disk resource three more times, the new node takes ownership, in this case at around second 34.

From a SQL Server perspective, the node hosting the SQL Server resource does the LooksAlive check. Remember that this does not mean that SQL Server is up and running. The IsAlive is then run, and that issues the aforementioned SELECT @@SERVERNAME. Again, this does not guarantee that the database you need can service a request, but SQL Server itself can. Should the IsAlive check fail (which means the query fails), it is retried five times. In the event all five retries fail, the SQL Server resource fails. Depending on the failover threshold configuration (discussed in Chapter 6) of the SQL Server resource, the server cluster either attempts to restart the resource on its current node or fails it over to the next preferred node that is still running (see Figure 3-3). The execution of the IsAlive tolerates a few errors, such as licensing issues or having a paused instance of SQL Server, but ultimately fails if its threshold is exceeded.

Figure 3-3. A failure detected on the node currently owning the SQL Server virtual server.

During the failover process from one node to another, the server cluster starts the SQL Server service for that instance on the new node and goes through the recovery process to start the databases. The entire switch of the SQL Server virtual server from one node to another takes a fairly short time. After the service is started and the master database is online, the SQL Server resource is considered to be running (see Figure 3-4). This process, on average, takes less than a minute or two. However, keep in mind the user databases will go through the normal recovery process, which means that any completed transactions in the transaction log are rolled forward, and any incomplete transactions are rolled back. The complete length of the recovery process depends on how much activity must be rolled forward or rolled back at startup, so the entire failover process takes longer than it takes to start the processes and bring up the master on the other node. This behavior is the same as a stand-alone instance of SQL Server that would be stopped and started.

Figure 3-4. The new node after failover.

As noted earlier, end users and applications access the SQL virtual server with either its name or IP address, not the name or IP address of the node hosting the resources. Users and applications do not need to worry about which node owns the resources. That is one of the best benefits of failover clustering—things are largely transparent to the end user. One noticeable result, depending on how the application is designed, is that during the failover process, any active connections are broken because the SQL Server is stopped and started. Therefore, whatever the user was working on when the server fails over might not be completed unless the transaction completes before the server goes down or the transaction and subsequent reconnect is handled within the application. This possibility must be taken into account in any decision to involve a cluster on the back end.

SQL Server 2000 failover clustering is always transactionally current: when the database is started on another node, it starts at the exact state it was in prior to failover. This is one of the benefits—no transactions are lost from a database, so the database is always current. Only transactions that were incomplete or issued after the failure and before SQL Server is restarted are lost, and that can be handled in the application. In fact, because the whole SQL Server instance fails over to another node, all databases and their objects (including logons, stored procedures, and so on) survive the switch intact.

This topic is dealt with in more depth in Chapter 6, but sometimes there is the notion—especially in a single-instance cluster or a multiple-node Windows Datacenter cluster with only one SQL virtual server—that unused nodes are being wasted. However, these nodes serve as an insurance policy in the event of a failure. If you start utilizing the "wasted" node for something else, what will happen in a failover? Will SQL Server have enough resources to allocate to it?

Because only one SQL Server can own a given disk resource (for more information, see Chapter 4, Chapter 5, and Chapter 6), you cannot share certain resources among clustered SQL instances. For example, if you have a drive D with 100 GB of space and need only 30 GB of it for one SQL virtual server, you cannot use any of the remaining disk space on drive D for another SQL virtual server.

Finally, because there is no load balancing with failover clustering, two SQL Servers cannot share the same underlying database because of the shared nothing mechanics of the server cluster. So you cannot, for example, use one SQL Server instance to do the reporting and another to do the inserting on the same database.

Log shipping is always transactionally consistent, although there is usually some sort of latency involved. It is not considered "real time," in which each transaction is sent as soon as it occurs. With log shipping, you are only as current as:

The log sent to the secondary is only as current as the last transaction log that is applied, so if the delta is five minutes off of the primary, your secondary should always remain five minutes behind if all is functioning properly. Analyzing these points is fairly straightforward given the previous sentence. If a transaction is completed, it is written to the transaction log. If the SQL Server experiences a failure before that transaction log is backed up, there is a chance that if the server cannot recover and read the transaction log, it will be lost, and you will only be as current as the secondary. If a transaction log is backed up but not copied over to the secondary, and either the copy process fails or the hard disk that contains that transaction log is lost, again, you are only as current on the secondary as what is available to it. If a transaction log is copied from the primary, and is still accessible to the secondary, it can be applied, so your secondary will be as current as the last transaction in the transaction log.

One benefit of log shipping is that anything that is recorded as a transaction for the database being log shipped is applied to the secondary. On the flip side, when the log shipped database is configured, only the objects found in that database exist at the secondary. Anything outside of the immediate database—logons, stored procedures used in another database, and so on—would have to be transferred using another process external to the core log shipping process.

Another use of log shipping, but not its main or intended use, is that the secondary database can potentially be used for read-only reporting-type access. The problem, however, is that to apply the transaction log, SQL Server needs exclusive access to the database, so no users can be accessing the database. If even one connection is left open, you could be putting your availability solution at risk. If the transaction logs are applied frequently, there might not be enough time between transactions to allow read-only access.

If full-text searching is required by the database that is being log shipped, you need to consider what to do in the event of a role change. Here is the crux of the issue: you cannot generate a full-text index on the secondary while the database is loading transaction logs. Even if you are backing up the full-text index and performing maintenance on it on the primary, even on the primary it will be out of sync if applied to a database that is at a later moment in time. Unless you can get to the full-text index and ensure that it is exactly at the same point as the log shipped secondary, you cannot "bolt it on" to the secondary. In that case, you will likely have to regenerate the full-text index after the database is brought online.

Another consideration regarding log shipping is its affect on clients and applications. Applications do need to take various things into account, including connecting to another server, as it will have another name. Application issues relating to log shipping are detailed in Chapter 7. Because the role change is not an automatic process, you need to have a tested plan in place to perform the switch in servers. There will definitely be some sort of availability outage during the switch. Network Load Balancing might be able to help abstract the name change of the server, but it certainly does not prevent downtime. The switch time could take anywhere from seconds if all available transaction logs are applied, to much longer if they are not.

There are some issues to consider when using replication as your method of high availability. One of the main issues is that certain objects associated with each database or that are created do not necessarily get re-created as part of the article or publication. It is important to understand what objects replication brings over and what it does not. Another issue is that because it was not designed for transactional replication, there is really no out-of-the box solution for switching from one server to the replicated warm standby. Tasks that ensure synchronization of the proper logons, stored procedures, and so on must also now have a process devised to move them to the standby.

Transactional replication is always transactionally consistent. With replication (any form), you are only as good as the following:

The scenarios are similar to the one detailed earlier for log shipping in terms of what the delta is between the publisher and the subscriber.

Obviously, one of the main uses of replication is that you are making data available elsewhere, whether it is reporting or another use. Therefore, you do not have to worry about accessing the secondary while it is being updated.

Full-text indexing will have to be configured against the replicated database. If you intend on possibly using the replicated database as a primary at some point, do not wait to start generating the full-text index. In addition, if only a subset of the data is replicated, and that database now needs to assume full functionality, you might not be able to use that database with the application. Different types of replication have limits on how much data is transferred in an article or publication.

Replication suffers from the same dilemmas as log shipping when it comes to clients: if a client or application expects to be connected to one server, how does it handle being changed to another server? The switch process also needs to be planned; it is not automatic. Replication does not detect the failure, other than, perhaps, showing up in the Replication Monitor as everything being out of sync. There will be downtime in the switch and the length of the downtime varies depending on the circumstances.

Look back at the guiding principles that came out of the questions posed in Chapter 1. Those guide the rest of the decision process, as they detail the overall availability goals for the solution. The technology should meet those goals, but there will obviously be some compromise involved. No one solution can be 100 percent perfect. The choice or choices made should meet your most important criteria. If perfection is the goal, the end implementation will never happen, and it will be costly on many fronts.

One crucial decision that comes up at this stage is the budget for the project. The amount of money you have to spend greatly affects what type of availability solution you can implement. Do not plan a solution—even down to rollout plans and support documents—only to find out you cannot afford it. As casinos tell gamblers, "Bet with your head, not over it." For example, a regional financial company provides online services 24 hours a day, including all aspects of banking (automated teller machines, bill payments, account balances, investments, and so on). Any moment of downtime could cost millions of dollars in lost transactions—an unacceptable situation on the surface. Geographically dispersed clusters seem like an ideal solution, right? They provide an automatic switch that log shipping does not, and should be transparent to end users. It also protects you from having one data center as a single point of failure.

Unfortunately, most geographically dispersed clusters, whether pure hardware or a combination of hardware and software, cost—at a bare minimum—hundreds of thousands of dollars. Realistically, the cost is probably in the millions. Add to that the cost of the network to ensure the cluster response time and overall network performance. A wide area network (WAN) of this type is very expensive to implement and requires specialized hardware as well. This combined price tag rules out even many enterprise customers. The sooner that you realize that the "ultimate" in availability might be out of your reach, the better off you will in planning a solution that will provide you with the best availability you can afford and manage. This is not to say that geographically dispersed clustering is not the solution for you—it might very well be, and it might work well. However, if you cannot afford it, it is not worth putting the company out of business and yourself out of a job just to say you achieved the ultimate in high availability.

With the cost factor out of the way, it is easier to focus on how to make the right decision for your environment—as long as you stick to the guiding principles. Other sample questions to ponder during this phase are:

Now that you have analyzed your internal situation, it is time to evaluate the technologies. First, pick the version of Windows that meets your current and future growth needs from a memory, processor, and feature standpoint. That said, your decision might change depending on the SQL Server technologies being considered. The operating system choice is usually a fairly straightforward one, but the SQL Server choice is not. For a direct comparison of the SQL Server-based technologies, see the section "A Comparison of the SQL Server Technologies" later in this chapter. Consult other chapters in the book for more information and attend conferences, ask consultants or salespeople, ask for trial versions, and perform actual proofs of concept. Go into the actual selection process with as much knowledge as you can to make the right decision. Hopefully this book will assist in that process, but it would be naïve to think that it is the only source of information on high availability on the Microsoft platform. It is better to take your time than to make the wrong decision.

With all of the relevant questions asked and answered, and everything thought through as much as possible, it’s time to make your choice. Again, this is easier said than done, given that you have multiple technology choices available from which to decide.

On the surface, log shipping and transactional replication are deceptively similar. They both offer the ability to send transactions from one server to another in an automated fashion to create a warm standby. They both involve a manual switch to a secondary server, both can be used for reporting, and so on. The subtle differences ultimately become the key points.

The biggest considerations are the ones loosely described in the earlier sections about log shipping and replication:

The answers must be in line with your business requirements.

Transactional replication stores the transactions both at the publisher and the distributor. At the publisher, they are kept in the publication database, and if for some reason they are not published, they still exist there. If they have been replicated, they also exist in the distribution database. Should the publication or distribution databases fail, the log reader process cannot read the transactions.

Log shipping stores the transactions only in the database (and subsequently in the transaction log). Depending on what transaction log backups are available, and assuming you can still get to the transaction log on the primary server if it has been updated after the last transaction log backup, you might still be able to get the remaining transactions. This means that with log shipping, not with transactional replication, there is a higher probability of transactional recoverability.

Replication might require more server overhead than log shipping. Both log shipping and replication will have input/output (I/O) overhead, but remember that replication has replication agents doing work that consumes system resources. If you are sending over every transaction in a highly utilized online transaction processing (OLTP) database, this cannot be discounted. Log shipping has overhead, but because it is run less frequently, the overhead might be less. Log shipping and replication both use SQL Server msdb database, but neither really uses it more than the other.

Latency between the primary server and the secondary server is also a concern. Although transactional replication might have a lower latency, it also requires a synchronized backup strategy, which log shipping does not. See the topic "Strategies for Backing Up and Restoring Transactional Replication" in SQL Server Books Online for more information. When the replication option Sync With Backup is set on both the publisher and distributor to ensure that both can be restored to the same point, you will get roughly the same latency as log shipping.

For transactional replication, it is paramount that the distribution database is available, so making that SQL Server available impacts the end solution. If you are using the built-in version of log shipping found in SQL Server 2000 Enterprise Edition, the monitor needs to be made available to report status. Not having all pieces available could affect the solution. The backup and restore situation with replication comes into sharper focus if for some reason you need to restore replication for disaster recovery, because if Sync With Backup is not set, replication has to be started from scratch, which could be troublesome.

With transactional replication, there might be an issue if the publisher, distributor, and subscriber are out of sync in terms of the SQL Server version. Generally, a distributor must be of a version equal to or greater than its publishers and a publisher must be of a version equal to or greater than the subscribers. There are sometimes exceptions to the rule in terms of subscribers. Log shipping should function no matter what version of SQL Server 2000 is installed, but all SQL Servers should be at the same level to ensure compatibility and similar behavior after the role change.

Because the distribution database contains the transactions that will be sent to the warm standby, a failure of the distribution database will impact transactional recoverability if the transactions are lost, as well as ability to recover the standby after a failure. You should weigh which one is more important to your business, as log shipping will give you better transactional recoverability. In a catastrophic failure, both log shipping and replication would need to be set up again.

If using the standby as a reporting server is crucial to your business and if you are choosing only one technology, replication might be the better option. Log shipping and its requirement to have exclusive database access, combined with frequent transaction log loads, makes it less than ideal for read-only access to that particular database. However, you also have the ability to stack transaction logs and apply them later. Obviously, this puts your log shipped secondary more out of sync with higher latency, but it can be used for reporting. If disaster recovery is more important than read-only databases, log shipping definitely has the edge in this category.

The process of bringing either the replicated database or log shipped database-online must be considered. Technically, the replicated database is online, but does it have all the elements to assume the production role? Does the log shipped database have all the transaction logs applied? How long does the entire process take? Is it tested? How will you notify the end users? The complexity will vary a little, but historically, it is much more defined from an availability standpoint for log shipping. Transactional replication and its lower latency might mean that a replicated standby is closer to the source for disaster recovery purposes, but the lack of processing would make log shipping a much more attractive option. You still need to weigh what is more important to your business.

Does introducing log shipping or transactional replication add more complexity because there are "moving parts" and dependencies? Be certain your availability solution cannot end up becoming a barrier to availability! Remember the maintenance of these added points of failure, too. All servers need to be well maintained. Managing and monitoring log shipping is generally easier than it is for replication.

If you base your decision purely on the choice between automatic or manual switching to a standby server, SQL Server 2000 failover clustering wins hands down. The problems for most people considering it are the aforementioned "wasting of resources" and the interruption in service that results from the automatic switch. These two factors, coupled with the fact that the hardware is a more specialized solution (even down to driver levels), might scare some away, but they should not. In reality, although SQL Server 2000 failover clustering is a little more complex in some ways than a log shipping or replication solution, in other ways it is not. Failover clustering has "moving parts," but you do not need to worry about things like Log Shipping Monitors or making sure that the distributor is up and running to keep the process going. As long as the underlying server cluster is properly configured, there should be no issues with the failover cluster built on top of it.

Now, log shipping or replication might be considered by some "poor man’s clustering" if Network Load Balancing is thrown in the mix to abstract the role change so that the back-end SQL Servers appear to act as one. In reality, however, it is not a server cluster or a failover cluster, so if you really do need the functionality provided by failover clustering, please consider it.

Here is a guide to the terminology used in Table 3-3: