SQL Server Alwayson Interview Questions & Answers
I have published SQL Server Interview Questions and Answers on many topics. Visit attached link to get All SQL Server Interview Question and Answers published on this website on different topics. Here, I am going to publish SQL Server Alwayson Interview Questions and Answers.
SQL Server Interview Questions and Answers on Alwayson
Question- How Always ON Availability Group is different from Database Mirroring?
Answer- Always On Availability Group is advanced version of Database Mirroring. We cannot configure database mirroring for more than one database per configuration whereas we can group multiple databases as one availability group and do the failover as one unit in AOAG.
Question- How many Replicas we can create in an AOAG Configuration?
Answer- We can create single primary replica and one to eight secondary replicas in AOAG Configuration.
Question-How many Availability groups we can create in an AOAG configuration?
Answer- The actual number of availability groups you can create on a Server depends on the hardware and workload, but there is no enforced limit.
Question-How many databases can be added in an AOAG configuration?
Answer- The actual number of databases you add to the AOAG depends on the available worker threads on the server, as such there is no enforced limit for databases as well.
Question- I have an AOAG configuration between an FCI and standalone server. Why AOAG is not able to perform automatic failover?
Answer- Automatic Failover is not supported for Availability groups that are configured on Failover Cluster Instances. That is why AOAG is not able to perform automatic failover on such configurations.
Question-What are basic prerequisites for a database to be a part of AOAG?
Answer-Below are the list of prerequisites for a database that needs to be added in Availability Group.
- Database should be Online for Read-Write Operation.
- Database must be in Full Recovery Model.
- Database should be a user database because you cannot configure AOAG for system database.
Question-Can we configure database mirroring on a database that is part of Availability group?
Answer- No, we cannot configure database mirroring for an availability database.
Question- Can we configure Log shipping for a database that is part of Availability Group?
Answer-Yes, we can configure Log shipping for an availability database.
Question- Can we configure Replication for a database that is part of availability group?
Answer- Yes, we can configure replication of a AOAG database.
Question- Can we configure AOAG for master database or any system databases?
Answer- No, we cannot create AOAG for system database.
Question- What is difference between Synchronous-commit mode and Asynchronous-commit mode?
Answer- Under synchronous-commit mode, primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log before committing transactions on primary replica.
Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log.
Question- What are the benefits of Readable Secondary Replicas?
Answer- Readable Secondary Replicas can be used for reporting purpose. We can divide some workload from primary replica and route all SELECT statements to run on readable secondary replicas. Even we can run database backups as well from these secondary replicas.
Question- How can we run backups from secondary replicas?
Answer- Performing backups on a secondary replica to offload the backup workload from the primary production server is a great benefit. We can perform log backups and copy_only full backups from secondary replicas. We need to change Backup Preferences setting of AOAG configuration to run these backups from secondary replica. Read below attached article to understand AOAG Backup Preferences and how it works.
Question-If one of your availability database got corrupted or showing in suspect mode, will AOAG initiate failover to secondary replica?
Answer- No, failover will not be initiated due to any issue at database level.
Question- Can we configure AOAG of a database that is running in bulk logged recovery model?
Answer- No, database must be in full recovery model to configure AOAG.
Question- Can we redirect the read only transactions to secondary replica instead of primary replica?
Answer- Yes, we can specify the read_only intent in the connection string and add only secondary replicas to the read_only_routing list.
Question- What is read intent option?
Answer- If Read Intent option is set that means read-only connections are allowed to secondary databases on secondary replica.
Question- What is default settings for read intent or read access transactions?
Answer- By default both read-write and read-intent access are allowed to the primary replica and no connections are allowed to secondary replicas of an Always On availability group.
Question- Why is asynchronous commit mode is faster than synchronous commit mode?
Answer- Asynchronous-commit is faster because primary replica does not wait for any acknowledgement received from secondary replica to harden the logs there. Read More about these in attached article Always On Availability Group.
Question- Is it necessary to have shared storage for AOAG?
Answer- No, it is not necessary to have shared storage of AOAG.
Question- Can we configure AOAG without windows cluster?
Answer- We can create clusterless AOAG in SQL Server 2017 but rest of all previous version you must have windows cluster to create AOAG.
Question- Can we restore a database participating in Always on Availability Group?
Answer- No, we cannot restore a database that is participated in AOAG. Neither we can delete or drop these databases.
Question- How can we Restore a database that is participated in AOAG?
Answer- To Restore a database that is part of Availability Group, first we need to remove it from availability group. Once database will be removed from availability group, you can restore it and later you can add it to the same availability group. Read attached article to learn how to restore a database that is part of AOAG.
Question- What will be the difference between configuring AOAG in multi-subnet network and same subnet network?
Answer- When we need to configure AOAG between multi-subnet cluster, we need to have one IP from each subnet to configure Listener whereas we need only one IP for listener in same subnet.
I hope you like this set of SQL Server Interview Questions & Answers on Alwayson Availability Group. Please follow our Facebook page and Twitter handle to get latest updates.
Read More Interview Questions:
- SQL Server Interview Q&A on Architecture
- SQL Server Interview Q&A on Indexes
- MSSQL Backup and Recovery Interview Q&A
- SQL Server General DBA Interview Q&A
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
Good set of questions. Thank you.
Thank you for this opportunity.