SQL Server DBA Interview Questions and Answers – SQL Server Cluster 2
If you have enjoyed reading my First post about Questions about Cluster. Here is another post on SQL Server Cluster services and on its components and features.
1. 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.
2. 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.
3. 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 Cluster to function. In your example, if the Quorum disk suddenly became unavailable to the cluster then both nodes would immediately fail and not be able to restart the cluster service.
4. What are Virtual Servers?
Groups that contain an IP address resource and a network name resource (along with other resources) are published to clients on the network under a unique server name. Because these groups appear as individual servers to clients, they are called virtual servers. Users access applications or services on a virtual server the same way they access applications or services on a physical server. They do not need to know that they are connecting to a cluster and have no knowledge of which node they are connected to.
5. How do you bring the SQL Server down?
In the Cluster Administrator, rick click on the SQL Server Group and from the popup menu item choose Take Offline.
6. 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.
7. What is the maximum number of nodes in an MNS cluster in Windows Server 2008, Enterprise x64 Edition?
Maximum 16.
8. 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.
9. What are Services and Application folder represent?
Services and applications are managed as single units for configuration and recovery purposes. If a resource depends on another resource, both resources must be a member of the same service or application. For example, in a file share resource, the service or application containing the file share must also contain the disk resource and network resources (such as the IP address and NetBIOS name) to which clients connect to access the share. All resources within a service or application must be online on the same node in the cluster.
10. 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.
11. Why do we keep SQL Services in manual mode on each of the instance?
SQL Services should always be in manual mode in case of cluster because these are managed by the Cluster service and it’s taken online on its respective owner node based on the failover.
12. What is Distributed lock management?
Distributed lock management (DLM): Distributed lock management (DLM) enables two servers to access the same physical disk at the same time without corrupting the data. If a device is updating a particular file or piece of data, the device gets locked so that another controller can’t seize ownership and overwrite the data. NT does not currently support DLM, so disks are dedicated to one node or the other.
13. What is “Look Alive”?
LooksAlive: Verifies that the SQL Server service runs on the online node every 5 seconds by default.
14. 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.
15. What are SQL Server Cluster aware services?
- SQL Server Service
- SQL Server Agent Service
- SQL Server Analysis Service
16. What are SQL Server Cluster unaware services?
- SQL Server Reporting Service
- SQL Server Integration Service
17. What are Validation tests in Windows Cluster?
Validation test is a mechanism of verifying that all the components which are participating in the Windows cluster are fine and failover is happening between the nodes.
18. What are the basics tests done by the validation tests in Windows Cluster?
- Cluster Configuration tests: Validate important cluster configuration settings.
- Inventory tests: Provide an inventory of the hardware, software, and settings (such as network settings) on the servers, and information about the storage.
- Network tests: Validate that networks are set up correctly for clustering.
- Storage tests: Validate that the storage on which the failover cluster depends is behaving correctly and supports the required functions of the cluster.
- System Configuration tests: Validate that the system software and configuration settings are compatible across servers.
19. Where the results of validation tests are stored?
These reports are automatically stored for you in C:\Windows\Cluster\Reports as MHTML files.
20. Is SQL Server a Load balancing solution or not?
No, it’s not a Load balancing solution.
21. Will there be any downtime in Active\Active cluster in case of any failover?
Yes, definitely there will be downtime when SQL Server failover from one node to another.
22 Can we use other SQL Server cluster Nodes for reporting purpose as we can do in Logshipping and Database mirroring?
No it’s not possible in SQL Server Cluster feature.
23. Can we place out Non Critical SQL Server User Databases on a Clustered Instance on Disks that are not clustered to Save Money?
No, it’s not possible. SQL Server 2012 and all previous versions of SQL Server require databases be created on clustered resources. Internal drives or drives which are not part of the cluster group cannot hold user databases.
24. Can we configure Tempdb database on a local drive?
With the introduction of SQL Server 2012 Microsoft officially supports local disk TempDB in SQL Server cluster configurations.
25. Can we configure Windows cluster between two servers which are having different hardware and software configurations?
No it is not possible.
26. What is SMB share?
SMB stands for Server Message Block file server which can be used as a storage option starting SQL Server 2012 to store system databases (Master, Model, MSDB, and TempDB), and Database Engine user databases .
27. How can we check the current node/host name where SQL Server is running?
Select serverproperty(‘ComputerNamePhysicalNetBIOS’)
28. How to view the Cluster Nodes using command line?
C:\Windows\System32>cluster node --or C:\Windows\System32>cluster node /status
29. How to view the status for all cluster resource groups.
C:\Windows\System32>cluster group --or C:\Windows\System32>cluster group /status
30. How to get a listing of all available cluster resources?
C:\Windows\System32>cluster resource --or C:\Windows\System32>cluster resource /status
31. How to failover a service from one node to another?
C:\Windows\System32>cluster group "groupname" /move:nodeName
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
http://www.mssqltips.com/sqlservertip/2837/sql-server-clustering-myths/
