Book Image

Microsoft SQL Server 2008 High Availability

By : Hemantgiri S. Goswami
Book Image

Microsoft SQL Server 2008 High Availability

By: Hemantgiri S. Goswami

Overview of this book

<p>Every business has it's mission critical applications and data. Therefore, it is very important to keep database servers up and running all the time – 24 X 7. There is a need to store and process terabytes of data to cater for business needs, and it is vital to make data highly available.<br /><br />High availability is all about the site being accessible all the time. High availability solutions minimize the downtime for these mission critical applications.</p> <p>Microsoft SQL Server is a powerful relational database engine, widely used to store and maintain data in Enterprises of various levels be they small, medium or large.</p> <p>This book will teach you how best to use these readily-available options and save you time in making your websites highly available.<br /><br />This Microsoft SQL Server 2008 High Availability book will take you through pre and post installation concepts and common issues you come across while working with SQL Server HA. It will teach you how these various HA solutions can be installed using GUI and the command line. It will also show you how to troubleshoot common issues you may encounter whilst installing or managing the HA option in your environment. It provides references to external links for more advanced learning on the topic.<br /><br />This book starts with an introductory chapter into the windows domain, domain users and various handshake methods available with Windows server. It also offers information the different authentication methods available with SQL Server - giving you an insight into the importance of security. After you are through with the security aspects, your journey to installing SQL Server HA will start. It will cover the concepts of database mirroring, log shipping, clustering, and replication.<br /><br />By the end of this book you will be confident enough to take up the challenge to install any of the SQL Server HA options.</p>
Table of Contents (18 chapters)
Microsoft SQL Server 2008 High Availability
About the Author
About the Reviewers

Types of clusters

There are four types of clustering solutions available with SQL Server 2008, which are the following:

  • Failover Cluster: Single-instance Cluster

  • Failover Cluster: Multi-instance Cluster

    • All nodes with active instances

    • N+1

  • Failover Cluster: Multi-site Cluster

  • Failover Cluster: Guest Cluster

Single-instance Cluster

This is the most common and widely used architecture for configuring SQL Server clusters. In this type of clustering, there will be two participating nodes in which one will be active or owner of the resources and the second will be passive. Whenever something crashes or fails, the active node passes ownership of all the resources to the second node — this is called failover.

As there is only a single node that is active or holds a resource like SQL Server services in running mode, this type of clustering is called Single-instance Clustering.


Note: There is a misconception that I often see with the terms Active/Active and Active/Passive. Let me clarify the terms here, and illustrate why the cluster is being referred to as Active/Passive or Active/Active; however, in any case both are possible with Single-Instance Clusters only.

The most common and widely used configuration is called an Active/Passive cluster (Single-instance Failover Cluster). There will be two nodes that are configured as a cluster node, and one of them is active and the second will remain passive or idle. In case of failure of any kind on Node 1, it fails over to second (or passive) server and now Node 2 will become the primary or active node for the cluster. Refer to the following scenario. If there is a failure in the active node the Heartbeat (refer to the How clustering works section) will be interrupted and ownership of the any resources will be taken by Node 2, which was passive:

Multi-instance Cluster

In this type of clustering, there can be more than one node failover. This means in case of failure there can be more than one node actively available to take over the ownership of the resources.

As its name says, there will be one or more than one instance or node running in the failover cluster. In the first type of configuration all the nodes are equal. When a node fails, all the failover instances will fail over to another node. What we should remember here is that we need to tolerate multiple node failures. The reason is simple: the node that will take over the ownership should be capable enough of serving the peak hours work load. And, for this, we should use AntiAffinityClassNames to set priority.

The second type of configuration is referred to as n+1. In this type of cluster, there will be a dedicated node available to take on the workload of any other node in case of failure. This configuration increases capacity when all nodes are available, in other words — optimum utilization of resources. We may also have an option to limit the number of dedicated nodes within the total limit of nodes that the SQL Server clustering solution supports. To have better control over instance allocation during failover or balancing, we should use preferred owner or AntiAffinityClassNames on the resource group.

In this type of configuration, there will be two nodes that are active and so it can be called a multi-instance cluster. This means that at any given point there will be two separate SQL Server instances running in a clustered environment. In case of failure (say Node 1 fails), all the users, applications, or database(s) that are connected to Node 1 will start using Node 2 and its resources. So, here it is more important to consider that the load might increase on Node 2 while designing or sizing, such as setting minimum and maximum memory; leaving default settings for minimum and maximum memory will consume all available memory and release memory to the OS as and when required, which is not desired. We can refer to the following screenshot.

Multi-site Failover Cluster

This was introduced in SQL Server 2005 clustering, and is also known as geographically dispersed failover or stretch cluster as it is designed to cope with the situation of a disaster at one site. This is how it functions: There are two sites at physically dispersed locations or sites or datacenters. If there is damage or failure at one site, the other will be up and running, providing a much more robust solution and high availability.

We can refer to the following screenshot:

There are six things you should not forget when you decide to use multi-site clustering:

  • Our data should get replicated consistently and continuously on the other site. This will ensure that we have the latest data and can tolerate disk failover. If our database and logs are on separate disk drives, we need to ensure that they both get replicated to avoid any data losses and maintain consistency in the state of the data.

  • Use Stretch VLAN so that a single subnet masks across all the participating nodes. Windows Server 2008 Cluster supports different subnet masks across cluster nodes; the same feature is yet to be added to SQL Server 2008. Most of the data centers at different locations do not share a single subnet, and it is necessary to have single subnet to have connectivity between these sites.

  • Quorum should be configured in a way that either site would run in case of failure of the other. The configuration of the Quorum is the same as it is with single-site failover.

  • Heartbeat configuration is supposed to tolerate increased latency; this can be achieved by increasing latency in the Windows server failover cluster inter-node heartbeat so that it does not detect it as stress.

  • Ensure that the disk resources are a part of the same group, and there are no non-disk resource dependencies to any of the disks. By default, Node and Disk Majority mode is selected, and in the case of odd number of nodes, Node Majority is selected.

  • Having a single subnet will ensure that the store replication and clustering between two geographically dispersed locations is similar to a single-site failover cluster.

Guest Failover Clustering

Guest Failover Clustering is nothing but installing and configuring clustering using virtual machines. In guest failover configuration you may have the cluster nodes on the same physical server, although it is recommended that you configure cluster nodes on different physical servers. The reason is that you will run in to pain when there is damage in the physical box; so to have the application or database available you must configure cluster nodes on separate physical nodes.

Both the versions, SQL Server 2005 and SQL Server 2008, support Guest Failover Clustering. The pre-requisites are:

  • The host operating system should be running on a supported virtualization environment such as:

    • Windows Server 2008 with Hyper-V

    • Microsoft Hyper-V server 2008

    • Certified configuration from Server Virtualization Validation Program (SVVP)

  • The guest operating system should be running on Windows Server 2008.

  • The environment should meet the requirements mentioned in the knowledge-based article at