SQL Server AlwaysOn Interview Questions and Answers - Part 1

By:   |   Comments (4)   |   Related: More > Professional Development Interview Questions DBA


Problem

If you are looking to advance your career as a SQL Server DBA then you should be prepared for your next interview on the most used features in the product. SQL Server AlwaysOn is one of those features that most interviewers will evaluate your expertise. Keep reading this article to learn about SQL Server AlwaysOn interview questions and answers.

Solution

SQL Server AlwaysOn is an advanced feature introduced in SQL Server 2012 to support High Availability (HA) and Disaster Recovery (DR) solutions. Have a look at the set of AlwaysOn questions and answers for your next job interview.

What is SQL Server AlwaysOn?

Answer –AlwaysOn is a term Microsoft has used since SQL Server 2012 for high availability and disaster recovery solutions. As of now, two features fall under the umbrella of AlwaysOn. These two features support high availability and disaster recovery for SQL Server databases:

  • SQL Server AlwaysOn Failover Cluster Instances (FCIs)
  • SQL Server AlwaysOn Availability Groups (AGs)

SQL Server AlwaysOn FCIs are SQL Server clustered instances whereas AGs are the new features introduced in SQL Server 2012 to support data high availability and disaster recovery. We can group each set of databases into one unit and execute a failover at one time with the help of the Availability Group.

What is the difference between AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups (AOAG)?

Answer – Please have a look at the main differences between both AlwaysOn solutions:

  • AlwaysOn Failover Cluster Instance needs shared storage between all of the nodes in the cluster.  Whereas AlwaysOn Availability Groups do not require shared disk storage for the server hosting the SQL Server.
  • AlwaysOn Failover Cluster Instance is available on both SQL Server Standard and Enterprise Edition whereas we need Enterprise Edition to configure SQL Server AlwaysOn Availability Groups until SQL Server 2014. There is now an option to create a basic Availability Group with SQL Server 2016 Standard edition, but it has lot of limitations.
  • SQL Server AlwaysOn Failover Clustered Instances work at an instance level whereas Availability Group works at a database level or for a set of databases.
  • We cannot use AlwaysOn Failover Clustered Instances while installing standalone instances whereas Availability Groups can be configured on both standalone as well as SQL Server Clustered Instances.

Can you explain Availability Group Listeners?

Answer - The Availability Group Listener is a virtual network name that we use to make connections to the databases whether it is running from a primary replica or secondary replica after failover.

Can we configure Availability Groups without configuring Availability Group Listeners?

Answer- Yes, we can configure an Availability Group without configuring listeners. Listeners are configured to make databases connections irrespective of their replica status.

When I tried to create a new Availability Group in SQL Server Management Studio, I saw that "New Availability Group Wizard" is grayed out and I am not able to proceed further to configure Availability Group. What could be the reason for this?

Answer – The "New Availability Group Wizard" option is disabled until you enable the Availability Group feature from the SQL Server service property.
  • You need to launch SQL Server Configuration Manager then open SQL Server service property.
  • Here you can see the AlwaysOn High Availability tab. Click on this tab.
  • You can see the Windows cluster name here if you have enabled the cluster feature. As we know Windows cluster is mandatory for AlwaysOn. If you haven’t configured a Windows cluster then first you need to configure it in order to enable AlwaysOn High Availability.
  • Once you will enable it, the Cluster Group name will appear in this property window and the grayed-out option "Enable AlwaysOn Availability Group" will be enabled. Just click on this option and click the OK button.
  • You need to restart the service to apply this change on the SQL Server Instance.

How do you configure an AlwaysOn Availability Group with a multi subnet network?

Answer – There is only one difference between configuring AOAG in a single vs. multi subnet. You will follow same process that we follow while configuring AOAG in a single subnet, but if you have a multi subnet network then we need one IP from each subnet to configure the AOAG listener. Read attached article where I have explained the step by step process to configure AOAG in a multi subnet network.

Can we add a new database to an existing Availability Group?

Answer – We can easily add new databases to an existing Availability Group. First, we need to prepare the secondary database by taking the full backup and subsequent transaction log backup then restore it on the secondary replicas in no recovery mode. Then we can right click on Availability Group name to launch the Add Database wizard. We should follow all required steps to proceed with this wizard. Once completed, your new database will be added to the identified Availability Group. You can read this whole process in detail in this article: How to Add New Database to Existing Availability Group?

Suppose you have 5 databases in an Availability Group. One database becomes inaccessible. Will the Availability Group initiate an automatic failover?

Answer – Until SQL Server 2014, the AlwaysOn Availability Group will not initiate a failover process if anything goes wrong at the database level. Microsoft introduced an option named Enhanced Database Failover in SQL Server 2016 to trigger the failover in case any database participating in an Availability Group loses the ability to write transactions. We also call it Database Level Health Detection in an Availability Group. By default, this option is not enabled. You need to configure it if you want to initiate a failover if anything goes wrong at the database level.

Can we add additional database files to a database that is part of an AlwaysOn Availability Group?

Answer – Yes, we can add database files to the databases that are configured as a portion of the AlwaysOn Availability Group.

Here are the high-level steps:

  1. First remove the database from the secondary replica. Now, the secondary database will be in a restoring state.
  2. Add the data file to your Availability database on the primary replica.
  3. Issue a transaction log backup of this availability database on the primary replica.
  4. Copy this transaction log backup to the secondary replica and restore it on its corresponding secondary replica using NORECOVERY and the WITH MOVE option.
  5. Now add the database back to AlwaysOn Availability Group.

Can we configure an Availability Group between SQL Server instances that are hosted on servers that are part of two different Windows server failover cluster groups?

Answer – No, we cannot configure AlwaysOn Availability Group between different Windows server failover cluster groups. All replicas must be part of same Windows server failover group. This is a basic prerequisite for AOAG.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, July 12, 2023 - 3:55:51 PM - Ayushi Gautam Back To Top (91380)
Hi, To add a data file to a participating database in Always ON, we do not need to take the secondary database out of Always ON, you simply make sure that we have permission and enough space on the secondary server as primary in the same location of the intended file to be created. Then add file on primary database and as the transaction the same operation will be automatically replicated to secondary replica.

Wednesday, July 12, 2023 - 4:27:26 AM - Mohammad Areefuddin Back To Top (91378)
Do we need WITH MOVE option while restoring the log?

Friday, November 4, 2022 - 9:50:46 AM - Bob Back To Top (90662)
Thanks! This is great information. I have just one question:
What about named instances? Can a named instance be part of an AG? If you have both the default instance and a named instance on a server, can they both be part of the AG?

Saturday, July 6, 2019 - 11:10:17 PM - Apulee Back To Top (81692)

Awesome!!!














get free sql tips
agree to terms