In this article, we will discuss a number of interview questions that you could be asked about the SQL Server Always On Availability Groups feature when attending an interview for the SQL Server database administrator role.
Being interviewed for a SQL Server Database Administrator position, and now interviewing candidates for that role, questions related to the High Availability and Disaster Recovery solutions in SQL Server take the large weight of the overall interview questions, as it reflects your advanced knowledge and experience in the SQL Server administration field.
Q1: Why we need to establish a High Availability and Disaster Recovery Plan in large companies?
The High Availability and Disaster Recovery plan help in making sure that our database servers that host the company’s critical operational data and serve the main company systems will be available for the longest possible time with the least possible downtime.
Q2: List five of the High Availability and Disaster Recovery solutions that are available in Microsoft SQL Server.
- Log Shipping
- Transactional Replication
- Database Mirroring
- Always On Failover Cluster
- SQL Server Always On Availability Groups
Q3: What are the main differences between the SQL Server Always On Failover Cluster and the SQL Server Always On Availability Groups?
- In the Always On Failover Cluster, the resources such as the storage, the network name, and the virtual IPs, are shared and can be owned only by one of the cluster nodes at a given time
But in the Always On Availability Groups, each node will have its own resources, with no resource sharing concept
- In the Always On Failover Cluster, there is no option to read from the secondary server, as there are no database copies hosted in these secondary nodes
But the Always On Availability Groups supports up to eight secondary replicas, from where you can easily perform read operations or route the read-only workload
- Always On Failover Cluster can be used as a High Availability solution only, but the Always On Availability Groups can be used as a High Availability and Disaster Recovery solution
- The Always On Failover Cluster is configured at the instance level and the failover is performed at the instance level
But the Always On Availability Groups is configured at the database level and the failover is performed at the databases group level
Q4: What are the differences between the SQL Server Database Mirroring and the SQL Server Always On Availability Groups?
- In the Database Mirroring you cannot read from the secondary database, where you can read from the secondary databases in the Always On Availability Groups
- You can configure the mirroring between two servers only, where you can configure up to eight secondary replicas in the Always On Availability Groups
- You can perform automatic failover in the Database Mirroring only if there is a witness server configured, but the automatic failover is always possible between the primary and secondary replicas in the Always On Availability Groups
- Database Mirroring is configured for each database separately, and the failover is performed at the database level, where you can configure the Always On Availability Group to involve multiple databases at the same time using a single wizard and perform the failover at the databases group level
Q5: How does the synchronization process performed in the SQL Server Always On Availability Groups between the different replicas?
- The primary sends the transaction log record from the primary database to the secondary database
- At the secondary side, the transaction logs will be written to the secondary database transaction log file for caching, in order to redo the transactions in the secondary database
- With the asynchronous-commit availability mode, no need for the primary replica to wait for the secondary replica to write transaction log records to disk
Q6: What is the Availability Group stands for?
A group of databases hosted in a SQL Server instance called primary replica that is copied to and synchronized with up to eight servers, called secondary replicas.
Q7: What are the availability modes that are used in the SQL Server Availability Group to synchronize between the replicas?
- Synchronous-commit mode, in which the primary replica will wait until getting confirmation from the secondary replicas, that the logs are written to the database transaction log file before committing that transaction at the primary side
- Asynchronous-commit mode, in which the primary replica will commit the transaction directly without waiting to receive the confirmation from the secondary replicas that the logs are written to the transaction log file of the secondary database
Q8: What is the failover process and when will the SQL Server decide to perform failover in the SQL Server Always On Availability Group?
The failover is a process in which the primary role and the secondary role are interchangeable between the availability replicas.
The failover process will be issued in case of any server-level failure.
No failover process will be performed in case of any database issue, such as data loss or database corruption, unless the Database Level Health Detection feature is enabled.
Q9: What is the Availability Group Listener?
A virtual network name that allows the users to connect to the Primary or the secondary replicas.
Q10: When defining a Windows Failover Cluster to build an SQL Server Always On Availability Group over it, will we use this cluster option “Add all eligible storage to the cluster”? why?
No. We need to uncheck the Add all eligible storage to the cluster option.
This is due to the fact that Always On Availability Group feature works based on having dedicated storage for each replica without having shared storage between the replicas.
Q11: What are the quorum configuration modes that are available in Windows Failover Clustering?
- Node Majority Quorum configuration mode in the Windows Failover Cluster, with each cluster node, gives one Vote. This option fits a cluster with an odd number of nodes
- Node & Disk Majority Quorum configuration mode in the Windows Failover Cluster, with each cluster node, gives one vote, with an additional vote for the cluster quorum disk. This option fits the clusters with an even number of nodes
- Node & File Share Majority Quorum configuration mode in the Windows Failover Cluster, with each cluster node, gives one vote, with an additional vote for a shared file
- No Majority, where the status of the cluster depends completely on the availability of the Quorum disk
Q12: Is it considered as a best practice to create a SQL Server Always On Availability Group with two replicas and configure the quorum using Node Majority mode? Why?
No. with each node has one vote, the cluster will be down when one of the nodes goes down, losing the advantages of the high availability concept.
Q13: How could we enable the Always On Availability Group for a specific SQL Server instance?
Using the SQL Server Configuration Manager, expand the SQL Server Services and browse for the SQL Server Database Engine service -> right-click on the service and select the Properties option. Move to the Always On Availability page under the SQL Server Service properties window, check the “Enable Always On Availability Groups” option, then restart the service to take effect.
Q14: List some of the pre-requisites that the SQL Server Engine will check before adding a database to the Availability Group?
- The database is configured with a Full recovery model
- A full backup is taken from that database
- The database is not participating in another Availability Group
Q15: What is the backup preference options that are supported in SQL Server Always On Availability Group?
- Preferred Secondary
- Secondary only
- Primary
- Any replica
Q16: How many secondary replicas could we configure in SQL Server 2016?
SQL Server 2016 supports up to eight replicas.
Q17: What is the difference between configuring the secondary replica as readable or read-intent only?
- The readable secondary allows read-only access to the secondary databases
- Read-Intent Only secondary replica allows the secondary server to serve the read-only workload ONLY, where the connection string of the application should explicitly mention the Application Intent=Readonly parameter
Q18: What is SQL Server Always On Availability Group Read-Only Routing List?
A new option introduced in SQL Server 2014 version, in which the read-only workload will be redirected to the first available secondary replica specified in a predefined routing list, taking into consideration that the application explicitly mentions the Application Intent=Readonly parameter.
Q19: What is the difference between the Direct Seeding and Full Backup and Log Backup initial synchronization processes in Always On Availability Group?
In the Full backup and log backup method, the initial synchronization between the primary and secondary replicas is performed by taking full and transaction log backups of the primary database to a predefined shared folder then restore it to the secondary replicas.
The Direct Seeding method, introduced in SQL Server 2016, the secondary databases will be initialized automatically using Microsoft SQL Server Virtual Device Interface (VDI) backup that is performed to the secondary replica over the network without the need for any network share.
Q20: How Could we configure an SQL Server Always On Availability Group with a multi-subnet network?
We need to configure the Availability Group listener with one IP from each subnet.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021