SQL Clustering Interview FAQs

What is the difference between SQL Server clustering methods: - Active/Active - Active/Passive
Active/Active means that both nodes are active and accessing the shared disk resources, but are running independent instances. When a node fails, you need to be sure that the remaining node has the resources available to handle the additional databases that fail over. You can think of it like this. Node A has 1 database on it, and Node B has 1 database on it. Node A goes down, the resources fail over to Node B, and now Node B has 2 databases running on it.
In an Active/Passive cluster, you would only have 1 database running on a single node at any given time. Node A is active with 1 DB, Node B is passive with no DBs. Node A goes down, the resources fail over to Node B. Node B is now active with 1 database running on it.
I'm sure more experienced cluster admins or SQL admins will savage me for my terminology, but that's it in a nutshell.

FAQ:

What is the alive & Looks alive

LooksAlive check: 

LooksAlive is a basic check in which the Cluster service queries the Windows Service Control Manager to check if the SQL Server service is still running. By default this check happens every 5 seconds.

Cluster service calls looksAlive function every 5 seconds and LookAlive function Queries the service status by using the Windows NT Service Control Manager. When the LooksAlive test fails ISAlive test is called immediately.

ISalive Check: A more rigorous IsAlive function is called every 60 second and monitors the health of the SQL Server by opening up a connection to SQL Server and issuing “select @@servername” query over the connection. If the checks fail the online Thread reports this failure to the Cluster Service.

During IsAlive check the Cluster Service connects to the SQL Server instance with the help of c:\windows\system32\sqsrvres.dll and runs SELECT @@SERVERNAME against the instance. 

By default, LooksAlive is fired every 5 seconds and IsAlive is fired every 60 seconds. The LooksAlive and IsAlive polling intervals can be changed in Cluster Administrator or failover cluster manager from the advanced tab for the SQL Server resource or using the cluster.execommand prompt utility.

Question: What is meant by Active – Passive and Active – Active clustering setup?

An Active – Passive cluster is a failover cluster configured in a way that only one cluster node is active at any given time. The other node, called as Passive node is always online but in an idle condition, waiting for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this becomes the Active Node, the previous Active Node now being a Passive Node.

An Active – Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point of time. That is, one Instance of SQL Server is running on each of the nodes always; when one of the nodes has a failure, both the Instances run on the only one node until the failed node is brought up (after fixing the issue that caused the node failure). The instance is then failed over back to its designated node.

Question: List out some of the requirements to setup a SQL Server failover cluster.

Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Hearbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk and MSDTC Disk.

Question: On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is active?

Using Cluster Administrator, connect to the cluster and select the SQL Server cluster.  Once you have selected the SQL Server group, in the right hand side of the console, thecolumn “Owner” gives us the information of the node on which the SQL Server group is currently active.

Question: How do you open a Cluster Administrator?
From Start -> Run and type CluAdmin (case insensitive) and the Cluster Administrator console is displayed OR you can also go to Start -> All programs -> Administrative Tools -> Cluster Administrator.

1. How will you restart your sqlserver on cluster without failing over ..?

Choose option ( Take offline and Bring online option by right clicking node)

2. What will you if want to add a disk to the SQL Group cluster ..?

Need to choose Add Dependancy option after doing that in Cluster administrator tool (or) in Failover Cluster admin tool from 2008 version

3. As a DBA how will you design active/active cluster requirement . (i.e), how will you manage resource if failed over ..?

Please read article from MSDN on this to have better understanding

4. Steps for failover ..?

Please red MSDN on this with full view

5. Difference between SQLSERVER 2005 and SQLSERVER 2008 Cluster Installation ..?

In sql2005 we have the option of installing sql in remaining nodes from the primary node ., But in sql2008 we need to go seperately(Login to the bith nodes) for installing sql cluster .

6.  What is the status of services on passive node for failover cluster in SQL server?

SQL services will be in manual and stopped. Cluster service will be in automatic and started mode on both the nodes.

7.  Can you move the resources after pausing the node?

Yes resources can be moved after pausing the node. But we can't move them back till the node is paused.

8. Where is the prevent failback option?

This is the resource group property. It doesn't allow the resources to move back to the preferred node once the node is ready to take the connections after failover.

9. How does the failover happen? What checks are performed to ensure that another node is up?

LooksAlive - The node which host the SQL server resources is verifed whether this node (server) is up

IsAlive - The node which host the SQL server resources is verifed whether the SQL service is up or not. Basically running SELECT @@SERVERNAME

10. What will happen if you try to start the fultext service on the passive node.

This can be started on both the nodes as this doesn't have any dependecy on SQL service or any resource which is possessed by active node.

11. What is Clustering. Briefly define & explain it ?

Clustering is a technology, which is used to provide High Availability for mission critical applications. We can configure cluster by installing MCS (Microsoft cluster service) component from Add remove programs, which can only available in Enterprise Edition and Data center edition.  

12. Types of Clusters ?

In Windows we can configure two types of clusters

1. NLB (network load balancing) cluster for balancing load between servers. This cluster will not provide any high availability. Usually preferable at edge servers like web or proxy.
2. Server Cluster: This provides High availability by configuring active-active or active-passive cluster. In 2 node active-passive cluster one node will be active and one node will be stand by. When active server fails the application will FAILOVER to stand by server automatically. When the original server backs we need to FAILBACK the application

13. What is Quorum ? A shared storage need to provide for all servers which keeps information about clustered application and session state and is useful in FAILOVER situation. This is very important if Quorum disk fails entire cluster will fails.

14. Why Quorum is necessary ?

When network problems occur, they can interfere with communication between cluster nodes. A small set of nodes might be able to communicate together across a functioning part of a network, but might not be able to communicate with a different set of nodes in another part of the network. This can cause serious issues. In this “split” situation, at least one of the sets of nodes must stop running as a cluster.

To prevent the issues that are caused by a split in the cluster, the cluster software requires that any set of nodes running as a cluster must use a voting algorithm to determine whether, at a given time, that set has quorum. Because a given cluster has a specific set of nodes and a specific quorum configuration, the cluster will know how many “votes” constitutes a majority (that is, a quorum). If the number drops below the majority, the cluster stops running. Nodes will still listen for the presence of other nodes, in case another node appears again on the network, but the nodes will not begin to function as a cluster until the quorum exists again.

For example, in a five node cluster that is using a node majority, consider what happens if nodes 1, 2, and 3 can communicate with each other but not with nodes 4 and 5. Nodes 1, 2, and 3 constitute a majority, and they continue running as a cluster. Nodes 4 and 5 are a minority and stop running as a cluster, which prevents the problems of a “split” situation. If node 3 loses communication with other nodes, all nodes stop running as a cluster. However, all functioning nodes will continue to listen for communication, so that when the network begins working again, the cluster can form and begin to run.

15. Different types of Quorum in Windows server 2008 ?

1.Node Majority - Used when Odd number of nodes are in cluster.

2.Node and Disk Majority - Even number of nodes(but not a multi-site cluster)

3.Node and File Share Majority - Even number of nodes, multi-site cluster

4.Node and File Share Majority - Even number of nodes, no shared storage

16. Different types of Quorum in Windows server 2003 ?

Standard Quorum : As mentioned above, a quorum is simply a configuration database for MSCS, and is stored in the quorum log file. A standard quorum uses a quorum log file that is located on a disk hosted on a shared storage interconnect that is accessible by all members of the cluster.   
Standard quorums are available in Windows NT 4.0 Enterprise Edition, Windows 2000 Advanced Server, Windows 2000 Datacenter Server, Windows Server 2003 Enterprise Edition and Windows Server 2003 Datacenter Edition.

Majority Node Set Quorums : A majority node set (MNS) quorum is a single quorum resource from a server cluster perspective. However, the data is actually stored by default on the system disk of each member of the cluster. The MNS resource takes care to ensure that the cluster configuration data stored on the MNS is kept consistent across the different disks.
Majority node set quorums are available in Windows Server 2003 Enterprise Edition, and Windows Server 2003 Datacenter Edition.

17. Explain about each Quorum type ?

Node Majority: Each node that is available and in communication can vote. The cluster functions only with a majority of the votes, that is, more than half.

Node and Disk Majority: Each node plus a designated disk in the cluster storage (the “disk witness”) can vote, whenever they are available and in communication. The cluster functions only with a majority of the votes, that is, more than half.

Node and File Share Majority: Each node plus a designated file share created by the administrator (the “file share witness”) can vote, whenever they are available and in communication. The cluster functions only with a majority of the votes, that is, more than half.

No Majority: Disk Only: The cluster has quorum if one node is available and in communication with a specific disk in the cluster storage.

18. How is the quorum information located on the system disk of each node kept in synch?

The server cluster infrastructure ensures that all changes are replicated and updated on all members in a cluster.

19. Can this method be used to replicate application data as well?

No, that is not possible in this version of clustering. Only Quorum information is replicated and maintained in a synchronized state by the clustering infrastructure.

20. Can I convert a standard cluster to an MNS cluster?

Yes. You can use Cluster Administrator to create a new Majority Node Set resource and then, on the cluster properties sheet Quorum tab, change the quorum to that Majority Node Set resource.

21. What is the difference between a geographically dispersed cluster and an MNS cluster?
A geographic cluster refers to a cluster that has nodes in multiple locations, while an MNS-based cluster refers to the type of quorum resources in use. A geographic cluster can use either a shared disk or MNS quorum resource, while an MNS-based cluster can be located in a single site, or span multiple sites.

22. What is the maximum number of nodes in an MNS cluster?

Windows Server 2003 supports 8-node clusters for both Enterprise Edition and Datacenter Edition.

23. Do I need special hardware to use an MNS cluster?

There is nothing inherent in the MNS architecture that requires any special hardware, other than what is required for a standard cluster (for example, there must be on the Microsoft Cluster HCL). However, some situations that use an MNS cluster may have unique requirements (such as geographic clusters), where data must be replicated in real time between sites.

24. Does a cluster aware application need to be rewritten to support MNS?

No, using an MNS quorum requires no change to the application. However, some cluster aware applications expect a shared disk (for example SQL Server 2000), so while you do not need shared disks for the quorum, you do need shared disks for the application.

25. Does MNS get rid of the need for shared disks?

It depends on the application. For example, clustered SQL Server 2000 requires shared disk for data. Remember, MNS only removes the need for a shared disk quorum.

26. What does a failover cluster do in Windows Server 2008 ?

A failover cluster is a group of independent computers that work together to increase the availability of applications and services. The clustered servers (called nodes) are connected by physical cables and by software. If one of the cluster nodes fails, another node begins to provide service (a process known as failover). Users experience a minimum of disruptions in service.

27. What new functionality does failover clustering provide in Windows Server 2008 ?           
New validation feature. With this feature, you can check that your system, storage, and network configuration is suitable for a cluster.

Support for GUID partition table (GPT) disks in cluster storage. GPT disks can have partitions larger than two terabytes and have built-in redundancy in the way partition information is stored, unlike master boot record (MBR) disks.

28. What happens to a running Cluster if the quorum disk fails in Windows Server 2003 Cluster ?

In Windows Server 2003, the Quorum disk resource is required for the Clusterto function. In your example, if the Quorum disk suddenly became unavailableto the cluster then both nodes would immediately fail and not be able torestart the clussvc.

In that light, the Quorum disk was a single point of failure in a MicrosoftCluster implementation. However, it was usually a fairly quick workaround toget the cluster back up and operational. There are generally two solutionsto that type of problem.

1. Detemrine why the Quorum disk failed and repair.

2. Reprovision a new LUN, present it to the cluster, assign it a driveletter and format. Then start one node with the /FQ switch and throughcluadmin designate the new disk resource as the Quorum. Then stop andrestart the clussvc normally and then bring online the second node.

29. What happens to a running Cluster if the quorum disk fails in Windows Server 2008 Cluster ?

Cluster continue to work but failover will not happen in case of any other failure in the active node.

30. What is Failover clusters in sql server

As all of us need every service should be 24 into 7 and available every time we need that. So every services provider need to technically strong. TO overcome this problem new concept comes into picture that is failover cluster. It is group of independent computers that work together to increase the availability of applications and services. Here each clustered server connected by cables and by software. And this clustered server called nodes. If one cluste

r nodes fails then another node begins to work. It is good technique which is not dependent on single server.

31.  What is the standard setting of Lookslive, IsAlive and Pending Timeout?

LooksAlive - 5 sec IsAlive - 30 sec Pending Timeout - 180 sec

Note- Do not modify Pending Timeout. The value, represented in seconds, is the amount of time the resource in either the Offline Pending or Online Pending states has to resolve its status before the Cluster Service puts the resource in either Offline or Failed status.

32. Can you change failover policy? If Yes then how?

To configure the failover policy, in the Threshold box, enter the number of times the group is allowed to fail over within a set span of hours. In the Period box, enter the set span of hours. For example, if Threshold is set to 10 and Period is set to 6, the Cluster Service fails the group over a maximum of 10 times in a 6-hour period. At the 11th failover in that 6-hour period, the server cluster leaves the group offline. This affects only resources that were failed over; therefore, if the SQL Server resource failed 11 times, it would be left offline, but the IP could be left online.

33.  What is the status of the Cluster Service and SQL service on both the nodes? Would they both were stop on the passive node?

Cluster service is automatic and started mode on all the nodes. But SQL Service will run only on the active node.

34.  Is it possible to put Cluster Group and SQL Group on different nodes?

Yes it is Possible. If you have one group on one node and another group on another node... that will run.

35.  Do you know any kind of limitation for Clustering?

Yes, We cannot do clustering with Windows 2000 Professional or lower server versions. Clustering is only available on servers running Windows 2000 Advanced Server (which supports 2-node clusters), Windows 2000 Datacenter Server (which supports up to 4-node clusters), or Windows 2003 Enterprise Edition and Windows 2003 Datacenter Server (which support up to 8-node clusters).

36.  How does Cluster perform at the time of failover?

Clusters use an algorithm to detect a failure, and use failover policies to determine how to handle the work from a failed server. These policies also specify how a server is to be restored to the cluster when it becomes available again.

37.  What are all resources controlled by Cluster?

The short list of resources controlled by the cluster will be-
•Physical Disks (Q:—Quorum disk, E:—Shared disks, F:, so on)
•Cluster IP Address
•Cluster Name (Network Name)
•MS DTC
•SQL Server Virtual IP Address
•SQL Server Virtual Name (Network Name)
•SQL Server
•SQL Agent
•SQL Full Text Service Instance (if installed)

38.  How to configure MSDTC as a cluster resource?
Setup DTC using following command
C:\Windows\System32> DTCSetup.exe
                        “OR”
From a DOS command prompt, run comclust.exe on node A. This will configure MS DTC for use with cluster services.
C:\Windows\System32> comclust.exe

39. Whatis isalive and look alive?

A:

Looks Alive check:Looks alive check is a basic resource health check to verify that the service(SQL service in our context) is running properly.To perform this , cluster service queries the windows service control manager to check the status of the service.By default looks alive check will happen in every five seconds.

Is Alive check: An exhaustive check to verify that a resource is running properly. If this check fails, the resource is moved offline and the failover process is triggered. During the Is alive check  the cluster service connects to the SQL server instance and execute select @@SERVERNAME.It will check only the SQL server instance availability and does not check the availability of user databases.

You can specify two polling intervals and a timeout value for resources. The polling intervals affect how often the MSCS Resource Monitor checks that the resource is available and operating. There are two levels of polling; they are known in Cluster Administrator as "Looks Alive" and "Is Alive." These values are named for the calls that the Resource Monitor makes to the resource to perform the polling. In "Looks Alive" polling, MSCS performs a cursory check to determine if the resource is available and running. In "Is Alive" polling, MSCS performs a more thorough check to determine if the resource is fully operational. The timeout value specifies how many seconds MSCS waits before it considers the resource failed.

40. What is Quorum?

A:

Quorum is the cluster's configuration file.This file (quorum.log) resides in the the quorum disk (one disk from shared disk array).Quorum is the main interpreter between all nodes. It stores latest cluster configuration and resource data. This helps the other nodes to take ownership when one node goes down.