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 are the different states of a Resource in Windows cluster?

All resources can have following states

· Offline

· Offline_Pending

· Online

· Online_Pending

· Failed

3) 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.

4) 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.

5) 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

6) 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

7) 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.

8) 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.

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

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

10) How will you add a disk to the SQL Group cluster?

After adding the shared disk in the storage, we can add disk to the respective SQL Server Group.

11) What kinds of permissions are required in the active directory to setup the SQL Server cluster objects?

Service account needs create object permissions in the Active Directory.

12) What is “Look Alive”?

LooksAlive: Verifies that the SQL Server service runs on the online node every 5 seconds by default.

13) What is “IS Alive”?

IsAlive: Verifies that SQL Server accepts connections by executing sp_server_diagnostics. This health detection logic determines if a node is down and the passive node then takes over the production workload.

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

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

15)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.”