13 basic SQL Server Cluster interview questions you should know.

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.

Quorum:

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

Thanks VV!!

SQL Server Cluster Interview Questions.

SQL Server AlwaysOn Availability group interview questions.

What is Checkpoint. When it occurs.

Checkpoint is like a save button for SQL Server. Whatever the un-saved data (uncommitted data to say technically) in the database gets saved (in technical terms, will be written to disk i.e.., to data file) whenever checkpoint occurs. Checkpoint occurs automatically in SQL Server.

When will checkpoint occur in a database:

                                                                                                                                   

·         Recovery interval option at the server level is used by SQL Server to calculate when checkpoints to be issued. This is the time in which SQL server should recover all the databases of the instance after a restart. So we can think of it as a threshold SQL server keeps monitoring, the recovery of the database depends on the number of log records in it, so SQL server keeps calculating with the current number of log records in the database how much time it takes to recover it and when it finds the recovery time becomes equal to setting ‘recovery interval’ option then SQL Server issues a CHECKPOINT.

·         If any DB files are added or removed by using ALTER DATABASE command.

·         If the instance has been stopped. Either by SHUTDOWN command or by stopping the SQL service.

·         During backup and database snapshot creation.

·         A minimally logged operation is performed in the database; for example, a bulk-copy operation is performed on a database that is using the Bulk-Logged recovery model.

·         Also depending on number of data modifications going on in the DB, SQL Server automatically issues CHECKPOINT on the DB. If there are many number of data modifications going on then there would be frequent CHECKPOINTs and vice versa.

Thanks!!