SQL Server AlwaysOn Interview Questions and Answers – Part 2

By: Manvendra Singh   |   Comments   |   Related: More > Professional Development Interview Questions DBA

Problem

I am looking to change jobs as a SQL Server DBA and know that during the interview process, I will be asked questions about SQL Server AlwaysOn.  Can you provide some questions and answers to help me prepare for my next interview?

Solution

SQL Server AlwaysOn is an advanced feature introduced in SQL Server 2012 to support High Availability (HA) and Disaster Recovery (DR) solutions. Below are SQL Server AlwaysOn questions and answers to help you prepare for your next SQL Server DBA job interview. Before you get started with this tip, I would also recommend that you read Part 1 of this tip series.

What are the differences between a SQL Server Availability Group and Replication?

Answer - Below are the main differences between an Availability Group and Replication.

• AlwaysOn Availability Group is advanced feature that was introduced with SQL Server 2012 whereas Replication is legacy technology that was supported in the earliest SQL Server versions.
• AlwaysOn Availability Group uses endpoints to transfer data to their replica servers whereas Replication uses SQL Server Agent Jobs for replicating data from the Publisher to the Distributor and then the subscribers.
• Availability Groups transfer data directly to their secondary replicas whereas replication first send data to Distributor then Subscribers get data from the Publisher.
• You cannot control data/indexes in Availability Group configurations, all secondary replicas would have the same data as their primary replica.  Whereas with Replication, you can customize the data and indexes on the subscribers. For example: You can have a different set of indexes on the Publisher database for OLTP workload and you can have a different set of indexes on the Subscribers for a reporting workload.
• You can use an Availability Group if you have a requirement for automatic failover whereas it’s not possible if you are using Replication for HA or DR purposes.

Can we take a SQL Server Availability Group offline?

Answer - Yes, we can take a SQL Server Availability Group offline by running the ALTER statement as shown below:

ALTER AVAILABILITY GROUP group_name OFFLINE

Have you heard the term "Automatic Seeding" in SQL Server AlwaysOn Availability Group? If yes, can you explain it?

Answer – Automatic Seeding is a term that is used for automatically initialization of Availability Groups. This feature was introduced in SQL Server 2016. When you create an Availability Group with automatic seeding, SQL Server automatically creates the secondary replicas for every database in the group. You no longer have to manually backup and restore the secondary replicas.

How many secondary replicas can we configure in a SQL Server AlwaysOn Availability Group?

Answer – We can configure eight secondary replicas for any Availability Group.

Can we add system databases to Availability Group?

Answer – No, we cannot configure SQL Server Availability Groups for system databases.

Can we configure automatic failover for a SQL Server Availability Group with the asynchronous mode of data transfer?

Answer – No, we must use synchronous commit data transfer to configure SQL Server Availability Groups with automatic failover.

Can we change failover modes for SQL Server Availability Group replicas? If yes, how?

Answer – Yes, we can change failover modes of a SQL Server Availability Group with these steps:

1. In SQL Server Management Studio, navigate to the replica under the Availability Group node and launch the properties window.
2. In the Availability Replica Properties dialog box, you can select correct Failover mode.
3. Choose any value from the drop down as per your requirements and close the windows to apply this change.

Can you explain how many types of Availability Groups there are in SQL Server?

Answer – There are a few variants of Availability Groups in SQL Server.

• Basic Availability Group: AlwaysOn Basic Availability Groups replaces the deprecated Database Mirroring feature and provides a similar level of features. Basic Availability Groups enable a primary database to maintain a single replica. This replica can use either synchronous commit mode or asynchronous commit mode. We can configure an AlwaysOn Basic Availability Group on any two SQL Server 2016 Standard Edition servers.
• Distributed Availability Group: As per MSDN "A distributed availability group is a special type of availability group that spans two separate availability groups. The availability groups that participate in a distributed availability group do not need to be in the same location. They can be physical, virtual, on-premises, in the public cloud, or anywhere that supports an availability group deployment. This includes cross-domain and even cross-platform – such as between an availability group hosted on Linux and one hosted on Windows."
• Traditional Availability Group: A traditional availability group has resources configured in a WSFC cluster. A distributed availability group does not configure anything in the WSFC cluster. Everything about it is maintained within SQL Server.
• Read-Scale Availability group: SQL Server 2017 introduces Read-Scale Availability Groups without a cluster. If your business requirement is to conserve resources for mission-critical workloads that run on the primary replica, you can use read-only routing or directly connect to readable secondary replicas. You don't need to depend on integration with any clustering technology.

Do we need to copy SQL Server Agent Jobs and Logins for Availability Group databases or will these objects automatically be replicated to their respective secondary replicas?

Answer – No, SQL Server Agent Jobs and Logins will not be replicated automatically. We need to manually replicate them to secondary replicas.

What impact will there be on the AlwaysOn Availability Group if we drop and recreate the Windows cluster?

Answer - If we drop and re-create the Windows Server Failover Cluster (WSFC), we must disable and re-enable the AlwaysOn Availability Groups feature on each instance of SQL Server that hosted an availability replica on the original WSFC cluster.

Does SQL Server compress data while transferring it to a secondary replica with AlwaysOn Availability Group?

Answer - By default SQL Server compresses data where appropriate while replicating it to secondary replica with SQL Server AlwaysOn. But this is not always true. It depends on the failover mode or type of operation that we choose to perform in AlwaysOn. The table below shows when SQL Server uses compression for Availability Group log streams:

Next Steps

• Read the first part of the SQL Server AlwaysOn Availability Group interview questions.
• Read more articles on SQL Server AlwaysOn.
• You can also read more SQL Server DBA Interview Questions.
• Explore more knowledge on SQL Server Database Administration Tips.

About the author

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