; SQL Server Clustering Fundamentals and Introduction
Learning Center
Plans & pricing Sign in
Sign Out
Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

SQL Server Clustering Fundamentals and Introduction


  • pg 1
									   SQL Server Clustering
Fundamentals & Introduction
 SQL Server Cluster Overview
 SQL Server Cluster - High Availability Features
 SQL Server Clustering Architecture – Cluster Types in SQL Server
 SQL Server Clustering Architecture - Basic Requirements
 SQL Server Clustering Architecture – Diagram
 Quorum in SQL Server Cluster
 SQL Server Cluster Introduction - Fetch Server Info.
 Cluster Administrator
 Manually Cluster Failover
 Test Cluster Failover
 How SQL Server Cluster Failover Works
 Conclusion & References
                   SQL SERVER CLUSTER

SQL Server 2005 introduces several high availability solutions to improve the
  availability of servers and databases. These solutions mask the effect of
hardware or software failure and maintain the availability of applications so
 that users perceive a minimum of downtime. One of the SQL Server High
   availability feature is SQL Server Clustering. This feature provides high
                        availability of SQL Databases.
                        SQL SERVER CLUSTER
                             High Availability Features
High Availability: With server clusters, ownership of resources such as disk drives and
Internet protocol (IP) addresses is automatically transferred from a failed server to a surviving
server. When a system or application in the cluster fails, the cluster software restarts the
failed application on a surviving server, or disperses the work from the failed node to the
remaining nodes. As a result, users experience only a momentary pause in service.

Failback: The Cluster service will automatically re-assign the workload in a cluster when a
failed server comes back online to its predetermined preferred owner. This feature can be
configured, but is disabled by default.

Manageability: You can use the Cluster Administrator tool (CluAdmin.exe) to manage a
cluster as a single system and to manage applications as if they were running on a single
server. You can move applications to different servers within the cluster. Cluster
Administrator can be used to manually balance server workloads and to free servers for
planned maintenance. You can also monitor the status of the cluster, all nodes, and resources
from anywhere on the network.

Scalability: Cluster services can grow to meet increased demand. When the overall load for a
cluster-aware application exceeds the cluster’s capabilities, additional nodes can be added.
            SQL Server Clustering Architecture

                                Two Types of Clusters
1.   Active vs. Passive – Active vs. Passive clustering provides high availability, at a
     time only one server will have access on all resources like Virtual IP, quorum disk,
     Shared disk etc.

2.   Active vs. Active – Active vs. Active clustering provides very high availability as
     well as at a time all servers will be online and we can use it for various critical
     applications. It has got a complex architecture.

       Here we will discuss Active vs. Passive SQL Server Cluster Architecture
            SQL Server Clustering Architecture
                Basic Requirements for Cluster Configuration

1.   Two Servers with Windows Server OS (compatible for clustering)
2.   Two LAN cards on both servers.
3.   SQL Server Enterprise Edition & above.
3.   Total 5 IPs are required.
        2 IPs for Private LAN connection (server 1 to server 2), which is used for heartbeat
        2 IPs are connected to Switch and used for public network.
         1 IP is used for SQL Server Virtual name, which will represent SQL Server Cluster to
         all users. It will configure on Switch.
4.   Common storage system for both servers like SCSI, SAN etc.
           SQL Server Clustering Architecture
                system like
                 SCSI, SAN                              Connected to
                    etc.                              common storage
Connected to                                          system like SAN,
                                  Private (IP)              SCSI
 system like                       network
  SAN, SCSI                     connection for
                                                                     Server 2
Server 1
                                    Private (IP)
                                  connection for
         Public IP for              heartbeat           Public IP for
       connection with                                connection with
         Switch and                                     Switch and
          network                                        network
                              virtual IP configured
                                   for cluster.
               SQL Server Cluster Introduction
                           Quorum in SQL Server Cluster

It is possible that you could make a change on the active node, but before the change is sent
over the network and the same change made on the passive node (which will become the
active node after the failover), that the active node fails, and the change never gets to the
passive node. Depending on the nature of the change, this could cause problems, even causing
both nodes of the cluster to fail.

To prevent this from happening, a SQL Server 2005 cluster uses what is called a quorum, which
is stored on the quorum drive of the shared array. A quorum is essentially a log file, similar in
concept to database logs. Its purpose is to record any change made on the active node, and
should any change recorded here not get to the passive node because the active node has
failed and cannot send the change to the passive node over the network, then the passive
node, when it becomes the active node, can read the quorum file and find out what the
change was, and then make the change before it becomes the new active node.

In order for this to work, the quorum file must reside on what is called the quorum drive. A
quorum drive is a logical drive on the shared array devoted to the function of storing the
              SQL Server Cluster Introduction

       How to Know Which Servers are in Cluster and Server Info.
Use “srvinfo” command line utility for getting server related information. As given in
following screenshot we can know the cluster nodes name and cluster name as well.
              SQL Server Cluster Introduction
                      How to Open Cluster Administrator
1.   You can open Cluster Administrator using Start -> Administrative Tools -> Cluster
2.   Run the command on Start -> Run -> CluAdmin.exe
3.   Select option “Open connection to cluster”.
4.   Write the cluster name or you can use only “.” (dot) and click open, it will
     automatically detect cluster and will open it.
              SQL Server Cluster Introduction
                           Manually Cluster Failover
1.   Open Cluster Administrator.
2.   Drill down to the Groups. Right click on “Cluster Group” and “SQL” one by one
     and click on “Move Group” button. This will move resources to another node.
               SQL Server Cluster Introduction
                                 Test Cluster Failover
1.   Select “Cluster Group”.
2.   Select any resource from right hand side pane, Right click on it.
3.   Select “Initiate Failure” option, now cluster will try three times to failover.
                 SQL Server Cluster Introduction
                       How SQL Server Cluster Failover Works
For whatever reason, the active node stops working. The passive node, which is monitoring the
heartbeats from the active node, now notices that it is not receiving the heartbeat signal. After a
predetermined delay, the passive node assumes that the active node has failed and it initiates a
failover. As part of the failover process, the passive node (now the active node) takes over control of
the shared array and reads the quorum, looking for any unsynchronized configuration changes. It
also takes over control of the virtual server name and IP address. In addition, as the node takes over
the databases, it has to do a SQL Server startup, using the databases, just as if it is starting from a
shutdown, going through a database recovery. The time this takes depends on many factors,
including the speed of the system and the number of transactions that might have to be rolled
forward or back during the database recovery process. Once the recovery process is complete, the
new active nodes announces itself on the network with the virtual name and IP address, which
allows the clients to reconnect and begin using the SQL Server 2005 instance with minimal

 That's the big picture of how SQL Server 2005 clustering works. If you
  are new to SQL Server clustering, it is important that you understand
   these basic concepts before you begin to drill down into the detail.

References :


Thank You

To top