SQL Server High Availability Interview Questions

In this post, we are going to talk about SQL Server High Availability Interview Questions. It will cover topics like Clustering, Shared Storage, Log Shipping, Database Mirroring, Replication etc. It is a crucial part of DBA interviews, especially for senior DBA’s who handles planning and deployment architectures in addition to their day to day activities.

So, here is the compiled list of SQL Server High Availability Interview Questions and Disaster Recovery Planning:

Question: What is SQL Server Always On Availability Groups (AG)?

Answer: An Availability Group is a combination of Database Mirroring and Clustering for maximum benefits for both the methods. For performance reasons, the number of user databases can consist of up to 100 databases. It allows you to fail over a group of databases rather than a single database. It differs from database mirroring and log shipping as they are configured at a single database level. It’s a disaster recovery & high availability solution in one.

Question: Does AG (Availability Groups) use a Windows cluster on the infrastructure side?

Answer: Yes, Even though it does not use a “Shared Disk” it uses the cluster for virtual name mapping and provides the failover capabilities.

Question: What are Availability Databases?

Answer: The Databases that are a part of an Availability Group are called “Availability Databases”

Question: How many read-write and read-only secondary database replica can you configure in SQL Server 2012 and 2014?

Answer: You can only have One Primary Database in both SQL Server 2012 and 2014. For Read Only secondary replicas, you can have 2 Sync and 2 A-Sync a total of 4 in SQL 2012. For SQL 2014, you can have a total of 8 secondary replicas (2 Sync and 6 A-Sync Plus 1 A-Sync Azure Replica)

Question: Can you perform database backups on the primary replica?

Answer: Yes, can take backups on either the Primary or any of the secondary.

Question: Can we perform database backup on secondary replicas?

Answer: Yes, you can.

Question: Can you perform a database backup on more than one copy of databases in AG?

Answer:  Yes, you can.

Question: In Always On AG, at what level does a failover happen? On AG level or database level?

Answer: The failover is always on the Availability Group level and not on the database level. For example, if there are 100 Databases in an AG, if a failover happens, all the Databases will failover to the other server.

Question: Suppose the primary database became corrupted and is in suspect mode. Will the AG failover to the secondary replica?

Answer: If a Database who is a part of the Always On availability group transitions to a “recovery pending” or “suspect” state in SQL Server 2012 or 2014 on the primary replica, database availability is affected. In this situation, you cannot access the database through the listener or client applications. Additionally, will not be able to drop or remove the database from the availability group.

In this case, you need to do a manual failover to the secondary server resolve any problems and fail back to the primary.

Question: Can you delete a primary or a secondary replica database?

Answer: Yes you can, but you need to take the database out of an Availability Group. See more here.

Question: Can we rename primary or secondary replica database?

Answer: No, you cannot rename any of the databases who is a part of the Availability Group.

Question: What will happen in case primary database accidentally get deleted? Will it failover?

Answer: No, there will not be a failover.

Question: What are benefits of using AG? What are the advantages of AG when compared to mirroring & Clustering?

Answer: Always On Availability groups addresses all the major concerns of both Database Mirroring and Failover Clusters.

• AG does not need a shared storage, unlike FCI. So the customer needs to invest in expensive SAN to achieve this requirement. Also, the shared disk is the single point of failure.
• AG uses a Listener to route traffic to the Primary Server and handles the failover process. For the application, this failover is transparent. Unlike in Database Mirroring, where the “Failover Partner” name needs to be mentioned in the connection string. Else, the failover process is manual.
• AG can have 8 secondary replicas and they can be read-only. In database mirroring, you can only have 1 Primary and 1 secondary where the secondary cannot be accessed. The only around it was to take a Database snapshot which is an Enterprise Only feature.
• AG takes a couple of seconds to failover. In the case of FCI, the failover time can run into minutes.

Question: What is an availability replica?

Answer: The databases on secondary servers which are a copy of the primary database and is capable of a warm standby.

Can we have two primary availability replica?

Answer: No, you can only have 1 primary replica.

Question: Can we add an availability replica/database which resides on the WSFC node?

Answer: No you cannot. You need to have all the servers to have the failover service installed and added to the same cluster.

Question: Suppose there is a two node Windows cluster. There are one FCI instance and one standalone instance on node B. can we configure AG between these two instances database?

Answer: Yes you can, provided both the nodes have the FCI service installed and added to the same cluster and a member of the same domain. You can configure both SQL Server installed as a clustered instances as well as a Standalone instance. The only Pre-requisite is the Cluster Service which needs to be installed.

Question: What are different availability mode available in AG? In mirroring it was called operating modes.

Answer:  AG uses a multi-option Wizard-based configuration. You have the following options:

Synchronous-Commit Availability Mode

• Synchronous-Commit Mode with Only Manual Failover
• Synchronous-Commit Mode with Automatic FailoverAsynchronous-Commit Availability Mode

See the official documentation for more detailed explanation.

Question: What is asynchronous-commit mode and synchronous-commit mode?

Answer:  In asynchronous-commit, the secondary database is not the exact copy of the primary database. The primary server ships the transactions to the secondary server, which tries to keep up with the primary. Typical use cases are DR sites with WAN links with high latency.

In the case of synchronous-commit, the secondary server is in sync with the primary server. This ensures an exact copy of the primary database with zero possibilities of data loss. Use cases include High Availability in the same datacenter where network latency is low.

Question: Please explain how transactions are being committed to each replica in both the different mode?

Answer:  In the case of synchronous-commit, the transactions are committed first on the secondary. An acknowledgment is sent back to the primary and then the primary commits the transaction onto its own database.

In asynchronous-commit, the secondary database does not send any acknowledgment to the primary. The secondary only tries to keep up with the primary. The primary does not wait for the secondary to commit the transaction and keep on going.

Question: Please explain automatic failover, manual failover and forced manual failover?

Answer: In an automatic failover, the failover causes a qualified secondary replica to automatically transition to the primary role after the primary replica becomes unavailable. Automatic failover is best suited in scenarios when the WSFC node that hosts the primary replica is local to the node that hosts the secondary replica. This is because data synchronization works best with low message latency between computers and because client connections can remain local.

In manual failover, a synchronized secondary replica to transition to the primary role after a database administrator issues a manual failover command on the server instance that hosts the target secondary replica. To support manual failover, the secondary replica and the current primary replica must both be configured for synchronous-commit mode, if any.

Forced Manual Failover: Forcing a failover of an availability group (with possible data loss) is a disaster recovery method that allows you to use a secondary replica as a warm standby server. Because forcing failover risks possible data loss, it should be used cautiously and sparingly. We recommend forcing failover only if you must restore service to your availability databases immediately and are willing to risk losing data.

Question: What is availability group listener?

Answer: An availability group listener is a virtual network name (VNN) to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. An availability group listener enables a client to connect to an availability replica without knowing the name of the physical instance of SQL Server to which the client is connecting. The client connection string does not need to be modified to connect to the current location of the current primary replica.

Detailed Documentation here.

Question: Can you configure a single listener for all the availability groups in the SQL Server Instance?

Answer: A Listener is configured per Availability Group. You can have multiple databases as a part of a single Group. But, you cannot use a single listener for multiple availability groups.

Question: Does AG support automatic page repair for protection against any page corruption happens?

Answer: Yes, Automatic page repair is supported by Always On Availability Groups. After certain types of errors corrupt a page, making it unreadable, an availability replica (primary or secondary) attempts to automatically recover the page. The partner/replica that cannot read the page requests a fresh copy of the page from its partner or from another replica. If this request succeeds, the unreadable page is replaced by the readable copy, and this usually resolves the error. Detailed documentation.

Question: Can AG support encryption and compression?

Answer: Yes, supports encryption and compression, in addition to log compression over endpoints providing a secure, high performing transport.

Question: How you add a database to an availability group?

Answer: Using the “Availability group wizard”

Question: What is always on “Dashboard“?

Answer: Database administrators use the Always On Dashboard to obtains an at-a-glance view the health of an Always On availability group and its availability replicas and databases in SQL Server 2016.

Some of the typical uses for the Always On Dashboard are:

• Choosing a replica for a manual failover.
• Estimating data loss if you force failover.
• Evaluating data-synchronization performance.
• Evaluating the performance impact of a synchronous-commit secondary replica

The AlwaysOn Dashboard provides key availability group states and performance indicators allowing you to easily make high availability operational decisions using the following types of information.

• Replica roll-up state
• Synchronization mode and state
• Estimate Data Loss
• Estimated Recovery Time (redo catch up)
• Database Replica details
• Synchronization mode and state
• Time to restore log

Question: Can we configure Log Shipping and AG both on the same database?

Answer: Yes you can. In scenarios, there are cases where AlwaysOn and Log-Shipping is getting combined

Question: Can you configure Replication and AG on the same database?

Answer: Configuring SQL Server replication and AlwaysOn availability groups involve seven steps.

See them in detail here.

Question: Does AG support FILESTEAM filegroup?

Answer: Yes, you can. See the official blog.

Question: What are the prerequisite, restriction, and recommendation for setting up AG?

Answer: See below:

Prerequisites are as follows:

• Ensure that the system is not a domain controller.
• Ensure that each computer is running either x86 (non-WOW64) or x64 Windows Server 2008 or later versions.
• Ensure that each computer is a node in a Windows Server Failover Clustering (WSFC) cluster.
• Ensure that the WSFC cluster contains sufficient nodes to support your availability group configurations.
• Ensure that all applicable Window hotfixes have been installed on every node in the WSFC cluster.

Recommendations for Availability Replicas

Question: Is AG supported in domain controller?

Answer: Availability groups are not supported on domain controllers.

Question: Can we configure AG between 32 and 64-bit computer?

Answer: Yes, you can, but WOW64 is not supported.

Question: What is dedicated network adaptor? And why dedicated network adaptor is recommended for AG?

Answer: You need a dedicated network adaptor for all the inter-node communication. For performance reasons, it is recommended to use a dedicated network adaptor.

Question: Can you add nodes to an Availability Group which have different drive letters?

Answer: Yes you can, but restrictions will apply. You can only due to a manual add of secondary replicas in case the drive letters does not match.

Question: Is AG account needs to have system admin to all the node?

Answer: No, a member of the local admin group and a domain user account is required.

Question: Is power shell is supported to configure AG?

Answer: Yes. Absolutely!

Question: Can system database participate in AG?

Answer: No, you cannot have system Databases in AG.

Question: Does AG support Bulk-Logged recovery model?

Answer: No, it does not. In the case of AG, only Full Recovery model is supported.

Question: Can a database belong to more than one availability group?

Answer: No, a database can be a part of only 1 availability group at a given point in time.

Question: What is an active secondary replica?

Answer: The AlwaysOn Availability Groups active secondary capabilities include support for read-only access to one or more secondary replicas (readable secondary replicas). A readable secondary replica allows read-only access to all its secondary databases. However, readable secondary databases are not set to read-only. They are dynamic. A given secondary database changes as changes on the corresponding primary database are applied to the secondary database.

Question: What is session timeout period?

Answer: The session-timeout period is a replica property that controls how many seconds (in seconds) that an availability replica waits for a ping response from a connected replica before considering the connection to have failed. By default, a replica waits 10 seconds for a ping response.

Question: What are the different system tables, catalog, DMV’s, and functions are available to monitor AG?

Answer: Use DMV, Always On Dashboard, Extended Events etc.

Question: What is ENDPOINT? And what its default port number?

Answer: To host an availability replica for an availability group, a server instance must possess a database mirroring endpoint. The server instance uses this endpoint to listen for Always On Availability Groups messages from availability replicas hosted by other server instances.

What is the query to check ENDPOINT?

SELECT type_desc, port FROM sys.TCP_endpoints

Question: What is a backup priority setting in AG?

Answer: This setting decides which server to go-to for taking database backups. By default, it is preferred to use a secondary replica.

What are different synchronization preferences are available?

What is full, join only and skip initialization synchronization preference?

Question: Do we need to configure quorum to support automatic failover?

Answer: Yes, you do need to configure the cluster quorum setting in order to ensure the cluster have enough votes to stay up in case a node goes down.

Question: What is the process of role switching?

Answer: The Primary and secondary roles are typically interchangeable in a process known as role switching.

Question: What is read intent option?

Answer: ‘Read-intent-only’ is used to provide a directive to AlwaysOn secondary replica to accept connections that have the property ApplicationIntent=ReadOnly set. The word ‘intent’ is important here as there is no application check made to guarantee that there are no DDL/DML operations in the application connecting with ‘ReadOnly’ but an assumption is made that customer will only connect read workloads.

Question: What is a split-brain scenario in SQL Server always on availability group?

Answer: The Connection Timeout between the endpoints, there is a change where the connection timeout between the endpoints. This time-out threshold could be used as an indication that a SQL Server instance is isolated network wise and hence we could decide to move an instance out of the principal role to avoid a split brain scenario.

Question: What is the difference between offload and load balancing? Which features does AG support?

Answer: SQL Server does not support Load balancing since only one SQL instance can do read/writes to any one database at any point in time. Always On Availability Groups support Offloading, where it can offload backup, read-only queries to the secondary databases.

Hope these questions on SQL Server High Availability Interview Questions are helpful. If you have any questions or suggestions, feel free to comment below in the comment section. Thanks and good luck!

Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.