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

The Quorum

Dictionary says that a Quorum is the minimum number that should match to perform a task further or to make a decision. Here, in the case of clustering, the meaning is no different.

As we all know by now, the cluster nodes send and receive information over the network. It is very possible that sometimes a change is done at the node that is the owner or active node, which then fails before sending the data back to another node in the cluster; in this case the cluster will not work. The nodes in clusters vote and based on that it will be decided whether or not the node should continue; for example, there is a four-node cluster and three of them can talk to each other but not the node3.

Quorum is a resource where all events pertaining to SQL Server Clusters get logged or recorded on a separate disk. Events can include change in the configuration, in active node, or owner of the resources changed, among others.

With SQL Server 2008, Microsoft has made significant changes, which will help easily create Quorum on the basis of its application and to do so automatically. There are four types of quorum available, including:

  • Node Majority: In this mode, each cluster node can vote. Majority is decided upon 50 percent of the nodes. If votes come for 50 percent or greater than 50 percent, then the cluster will function. This is the preferred selection when there is no shared disk or storage provided and numbers of nodes are odd. Consider an example where a three-node cluster is set up. Now if one of the nodes fails to communicate, what will happen? In this case, the votes will be more than 50 percent as two out of three nodes are functioning and communicating with each other and that is why it will continue to function. On the contrary, if only one node is functioning properly of the three, then the cluster will fail to work.

  • Node and Disk Majority: In this configuration, every cluster node plus a designated disk (as a witness) can vote, and here too if more than 50 percent votes are calculated to keep running as a cluster, the cluster will continue to function. This means if there is a four-node cluster with a disk witness, the total number of disks adds up to five as there are four disks on the system plus one witness disk; so, in this case, the cluster will continue to work if three or more votes come.

    This configuration is recommended where there is a two-node failover cluster or there is an even-node cluster configured. Please note that while configuring an even number of nodes, if the wizard automatically selects Node and Disk Majority, it will also select a witness disk. The wizard also considers selecting the smallest size disk of more than 512MB as a witness disk.

    Keep the following things in mind while implementing this configuration:

    • Use a small LUN that has a minimum size of 512 MB.

    • Make sure that this disk is dedicated to be the disk witness and contains no user data.

    • It is not required to assign a drive letter to this LUN; we can decide this based on our cluster need.

    • This LUN should be a single volume and should be added as a cluster resource.

    • Ensure that this LUN is validated and configured using hardware RAID and is formatted using the NTFS file system.

  • Node and File share majority: In this mode, there will be a designated file share that is created as a file share witness. There will be a replica for each of the shares, which is kept on a system disk across all the nodes; however, this is not stored on the file share, which is the core difference between node and disk majority and disk and file share majority.

    This file share keeps an eye on each of the disks so it knows which has the most up-to-date replica. This configuration is recommended in a geographically dispersed failover cluster.

    Keep the following in mind while implementing this configuration:

    • Use Server Message Block (SMB).

    • Ensure that the file share has a minimum 5 MB of free space.

    • The file share should be dedicated to the cluster.

    • It should not be on a server that is a cluster node or would become cluster node in future — file server would be a good choice.

    • The file share should be on the same forest of the domain.

    • Ensure that the administrator has the full permission for the share and NTFS.

  • No majority - Disk only: This mode of Quorum is similar to the previous versions of Quorum with Windows Server 2003. This is recommended in only selected configurations. This is because in this case, if a single disk fails, then entire cluster will fail.

    The cluster replica will help here as it stores the exact and most up-to-date replica of the cluster database on the shared disk, which is accessible by all the nodes. In case of failure of a single node, this replica is considered as an authoritative replica to help repair or replace the Quorum that has been corrupted or damaged.

Once the configuration for the Quorum is over and we want to have a look at it, there are two options: we may either open a GUI (a cluster management snap-in) or use the command prompt by typing Cluster / quorum.

When it comes to making changes to existing Quorum configuration, we may do it using:

  • Failover cluster management snap-in

  • Managing a cluster

  • Action

  • More action

  • Configuring cluster Quorum settings

  • Following the wizard the way we want our Quorum to be configured