SQL Server AlwaysOn Availability group interview questions.

Which High Availability features can be used along with AlwaysOn Availability group feature?

»    AlwaysOn Availability group can be combined with Log shipping and Replication.

»    DB mirroring cannot be combined with AlwaysOn Availability group.

Do we need shared storage to create AlwaysOn Availability group?

»    No. Shared storage is not mandatory for creation of AlwaysOn Availability group.

Can we take differential backup in the secondary replica of AlwaysOn Availability group?

»    No. We can only take a log and copy only FULL backups.

How many replicas we can have in AlwaysOn Availability GROUP?

»    1 Primary and 8 secondaries in SQL Server 2016/2014.

»    1 Primary and 4 secondaries in SQL Server 2012.

Which High Availability features support Automatic page repair, if any page becomes unreadable.

»    AlwaysOn Availability and DB mirroring.

How many ALWAYSON Availability groups and AlwaysOn Availability group databases we can have for instance?

»    There is no upper limit as such and it depends on your server configuration. But MS has tested with 10AGs and 100DBs per physical machine.

After adding ndf file to Primary of AlwaysOn Availability group my secondary went to suspended state. What could be the reason and how to fix it.

»    Reason could be file path differences between primary and secondary AlwaysOn Availability groups. If the same file path does not exist in secondary replica then that replica will be SUSPENDED and goes to NOT SYNCHRONIZING state.

»    To fix this: remove DB from the secondary replica of AlwaysOn Availability groups, take FULL backup of PRIMARY database and restore it to secondary, same way restore log backup as well, after restoring all pending log backups then add the secondary DB back to AlwaysOn Availability groups.

How to install service pack when Alwayson Availability Group feature is enabled in SQL instance.

»    Disable automatic failover.

»    First install the service pack in secondary replica.

»    Wait until AlwaysOn Availability groups state becomes SYNCHRONIZED.

»    Manually failover the DB to the secondary replica.

»    Now upgrade the earlier primary server.

»    Failover the DB to the initial primary server.

»    Make the AlwaysOn Availability groups state is SYNCHRONIZED at the end.

What are different availability modes in AlwaysOn Availability groups?

»    Asynchronous-Commit mode:

·         Ideal for DR scenario.

·         High performance.

·         No Automatic failover.

»    Synchronous-Commit mode:

·         High data availability and data protection.

·         Automatic and manual failovers are supported.

References:

https://msdn.microsoft.com/en-us/library/hh510230(v=sql.130).aspx

https://msdn.microsoft.com/en-us/library/dn178483.aspx

https://msdn.microsoft.com/en-us/library/hh403386.aspx

I will keep adding new questions to this list. Please share the questions you are aware of.

#AlwaysOn Availability group, #AG, #Alwayson, #AvailabilityGroups

SQL Server Cluster Interview Questions.

Thanks VV!!