SQL Server Cluster interview questions. Set 1
MSCS(Microsoft Cluster Server):
· What is Cluster Service? Or What is Microsoft Cluster Service?
· Main component is Cluster Service, if this is down cluster itself will be unavailable. This service communicates with Resource Monitor and it controls cluster activities and performs tasks like failovers, managing configuration, facilitating communication between cluster components. It tries to minimize the downtime during any failover or failure. Resource monitor is an interface between cluster service and cluster resources.
· How MSCS verifies cluster resources/service are up and running fine?
· MSCS cluster manager uses 2 functions LooksAlive & IsAlive to check the availability of the SQL Server resource. LooksAlive checks service status through Windows NT Service Control Manager every 5 seconds. IsAlive checks every 60 seconds by connecting to SQL as a user probe to perform a simple query. These default timings can be changed.
· Which command MSCS run frequently?
· MSCS connects to SQL using the MSCS Service Account for IsAlive checks. It should have public rights to SQL and it runs ‘select @@servername’ every time.
· What is pending timeout in a cluster?
· Pending Timeout, it can be either Offline pending or Online Pending, is the amount of time application or service required to go offline or online. The default value is 3 mins (180 secs) and it can be changed.
Private and Public Network:
· What are Private and Public networks?
· Public network is for internal communication of cluster nodes. A private network is for communication to the outside. Regular internal communication would be heartbeat between nodes.
· What is Quorum?
· What is the use of Quorum?
· Quorum is a cluster concept that store cluster resource information and configuration details in a log file ideally quorum.log. The main use of quorum is during communication failure between nodes.
· Quorum configuration will have the threshold for the number of failures a cluster can withstand before going down. If any failures happen beyond the threshold cluster will stop running.
· During any failure, quorum log will be referred based on the voting algorithm, the cluster will make sure cluster services are running on only one node and make sure only one owner for the group. This helps in avoiding “Split” or “split-brain” scenarios.
· What is Split-Brain or Split in Cluster?
· Split or Split Brain is a scenario during communication failure between nodes, each individual node tries to bring the resources of another node online assuming the other node is down.
· What is the recommended size of a Quorum disk?
· 512MB is the recommended size by MS for Quorum disk. Most of the online resources suggest having 1GB size disk for the quorum.
· It is recommended not to place any IO intensive programs in the quorum. Heavy input/output traffic from another source could interfere with the cluster's ability to write to the disk, which may cause the quorum resource to fail. If the quorum resource fails, the entire cluster may fail as well.
· What are types of Quorums?
· Node Majority, Node & Disk Majority, Node & File Share Majority, Disk Only Majority are the 4 types of Quorum.
MSDTC(Microsoft Distributed Transaction Coordinator):
· Do we need to install MSDTC while installing SQL Server Cluster?
· MSDTC is required if you are using DBEngine & SSIS, Workstation Components or distributed transactions.
· If only DBEngine or only SSAS is running then MSDTC is not required.
· How to connect to a failover cluster instance?
· You must use the SQL Server failover cluster instance name to connect to the SQL Server failover cluster, not the name of the node.
· Which services of SQL server are cluster unaware?
· SQL SERVER REPORTING SERVICE (SSRS) and SQL SERVER INTEGRATION SERVICE (SSIS) are cluster unaware.
Please add other important questions related to SQL Server cluster in the comment section below which I have missed.