SQL Server DBA Interview Questions and Answers – SQL Server Cluster 1

Today I am writing a Blog Series on one of my Favorite topic – SQL Server Cluster.  This will include all the possible Questionnaire which are asked in the Interviews related to Cluster.

1) What is Windows Cluster?

Clustering can be best described as a technology that automatically allows one physical server to take over the tasks and responsibilities of another physical server that has failed. The obvious goal behind this, given that all computer hardware and software will eventually fail, is to ensure that users running mission-critical applications will have very less downtime when such a failure occurs.

2) What is a Cluster Node?

A cluster node is a server within the cluster, and it has Windows Server and the Cluster service installed.

3) What is Cluster Service?

The cluster service manages all the activity that is specific to the cluster. One instance of the cluster service runs on each node in the cluster. The cluster service does the following

• Manages Cluster Objects and Configurations
• Manages the local restart policy
• Coordinates with other instances of the cluster service in the cluster
• Handles event notification
• Facilitates communication among other software components
• Performs failover operations

4) What is called a Resource in Windows cluster?

A resource is a physical or logical entity, which has below properties:

• Can be brought online and taken offline
• Can be managed in the failover cluster
• Can be owned by only one node at a time

To manage resources, Cluster service communicates with a resource DLL through Resource Monitor.

5) What are the different states of a Resource in Windows cluster?

All resources can have following states

• Offline
• Offline_Pending
• Online
• Online_Pending
• Failed

6) What is a Cluster Group?

Conceptually, a cluster group is a collection of logically grouped cluster resources. It may contain cluster-aware application services, such as SQL Server Group, File Server.

7) What is Public Network?

A public network (also called as External network) provides client systems with access to cluster application services and IP address resources are created on networks that provide clients access to cluster services.

8) What is Private Network?

A private network (sometimes called as interconnect or heartbeat connect) is a network that is setup between the nodes of the cluster and it carries only internal cluster communications.

9) What is Heartbeat in Windows cluster?

Heartbeats are messages that Cluster Service regularly sends between the instances of Cluster Service that are on each node to manage the cluster.

10) What Failover and Failback terms mean in Windows Cluster?

Failover: Failover is the process of moving a group of resources from one node to another in the case of a failure. For example, in a cluster where Microsoft SQL Server is running on node A and node A fails, SQL Server automatically fails over to node B of the cluster.

Failback: Failback is the process of returning a resource or group of resources to the node on which it was running before it failed over. For example, when node A comes back online, SQL Server can fail back from node B to node A.

11) What is Quorum Drive?

This is a logical drive assigned on the shared disk array specifically for Windows Clustering. Clustering services write constantly on this drive about the state of the cluster. Corruption or failure of this drive can fail the entire cluster setup. It also acts as a voter in the fail over process in case of odd number of nodes.

12) Different types of Quorum Models supported in windows Server 2008?

• Node Majority – Used when Odd number of nodes are in cluster.
• Node and Disk Majority – Even number of nodes (but not a multi-site cluster)
• Node and File Share Majority – Even number of nodes, multi-site cluster
• No Majority: Disk Only – This is the traditional MSCS quorum model, where a shared quorum disk must be online and nodes must be able to communicate with that disk

13) What is Node Majority model?

This type of quorum is optimal for clusters having an odd number of nodes. In this configuration, only the nodes have votes. The shared storage does not have a vote. A majority of votes are needed to operate the cluster.

14) What is Node and Disk Majority model?

Nodes and a shared disk get votes. This configuration allows a loss of half the nodes, providing the disk witness is available, or over half the nodes are available without the disk witness being available. This is recommended for even number of nodes in the cluster.

15) What is Node and File Share Majority model?

This type of quorum is optimal for clusters having an even number of nodes when a shared witness disk is not an option. Other characteristics include the following:

• each node and the file share “witness” gets a vote
• it does not require a shared disk to reach a quorum
• the file share has no special requirements
• the file share should be located at a third site, making this type of quorum the best solution for geographically dispersed clusters

16) What is No Majority: Disk only mode?

The disk witness must be available to have quorum, but the number of available nodes doesn’t matter. If you have a four-node cluster and only one node is available, but the disk witness is available, you have quorum. If the disk witness isn’t available, then even if all four nodes are available you can’t have quorum.

17) What I Split Brain situation in Cluster?

Cluster nodes communicate with each other over the network (port 3343). When nodes are unable to communicate with each other, they all assume the resources of the other (unreachable) nodes have to be brought online. Because the same resource will be brought online on multiple nodes at the same time, data corruption may occur. These results in a situation called “Split Brain.”

18) How Spilt Brain situation is resolved?

To prevent Split Brains we need to bring the cluster resource online on a single node (rather than multiple nodes).  Each of the online node cast vote for majority and the resources come online on that group which has more votes or has majority. In case of Even number of nodes Quorum also acts as a voter to eliminate split brain situation.

19) What are the Hardware requirements for Windows Server Cluster?

Windows Cluster

• Two windows servers (nodes)
• At least one shared disk array that supports, either SCSI or fibre channel.
• Each server must have a SCSI or fiber channel adapter to talk to the shared disk array. The shared disk array cannot use the SCSI controller used by the local hard disk or CD-ROM.
• Each server must have two PCI network cards (one for the private connection and one for the public connection)
• 1 IP Address for Windows virtual cluster name

20) What are the Hardware requirements for SQL Server Cluster?

• 1 IP Address for MSDTC service
• 1 IP Address for SQL Server Active\Passive Instance or 2 IP address for SQL Server Active\Active Instance
• 1 IP Address for SQL Server Analysis services (if needed)

21) How many IP Addresses we require for setting up Active\Passive SQL Server cluster?

• 2 Windows nodes – Public
• 2 Private IP Addresses – Private
• 1 Windows Virtual Cluster Name
• 1 MSDTC
• 1 SQL Server Virtual Network Name

22) How many IP Addresses we require for setting up Active\Active SQL Server cluster with Analysis services?

• 2 Windows nodes – Public
• 2 Private IP Addresses – Private
• 1 Windows Virtual Cluster Name
• 1 MSDTC
• 1 SQL Server Virtual Network Name
• 1 SQL Server Analysis Services

23) How do you open a Cluster Administrator?

Start Menu > Run >  Cluadmin.msc

24) What is SQL Server Network Name (Virtual Name)?

This is the SQL Server Instance name that all client applications will use to connect to the SQL Server.

25) Different types of SQL Server Cluster?

• Active\Passive
• Active\Active

26) What is the difference between Active\Passive and Active\Active cluster?

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.

27) 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 separately (Login to the both nodes) for installing SQL cluster

28) Can we change the Quorum settings after installing the windows cluster?

Yes, we can change the Quorum setting after the Windows Cluster installation.

29) Is it mandatory to configure MSDTC in Windows 2008 cluster before installing SQL Server cluster?

No it’s not mandatory to configure MSDTC service to install SQL Server in Windows 2008 cluster. Installation will give you a warning but will not stop the installation.

30) What are the Benefits of SQL Server Cluster?

• Reduces downtime to a bare minimum.
• Permits an automatic response to a failed server or software. No human intervention is required.
• It allows you to perform upgrades without forcing users off the system for extended periods of time.
• It allows you to reduce downtime due to routine server, network, or database maintenance.
• Clustering doesn’t require any servers to be renamed. So when failover occurs, it is relatively transparent to end-users.
• Failing back is quick, and can be done whenever the primary is fixed and put back on-line.

31) What are the Drawbacks of SQL Server Cluster?

• More expensive than other failover alternatives, such as log shipping or stand-by servers.
• Requires more set up time than other alternatives.
• Requires more on-going maintenance than other alternatives.
• Requires more experienced DBAs and network administrators.

References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

http://www.academia.edu/7124746/Windows_Cluster_Interview_Questions_and_Answers

http://windowsitpro.com/windows/q-what-are-windows-server-2008-quorum-models