AlwaysOn Availability Groups notes

AlwaysOn can broadly categorized into 2 (AlwaysOn Failover Cluster Instances, and AlwaysOn Availability Groups). I will capture Availability group highlights…

AlwaysOn Failover Cluster Instances
failover cluster instance (FCI) is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes
AlwaysOn Availability Groups:
This is High availability and Disaster recovery solution. Availability Groups provides Failover environment for desired set of databases. There will be one primary read-write database and up to 8 secondary databases which can be used as Read-Only copy and backup copy.
Image source:

availability group:
o A container for a set of databases, availability databases, that fail over together.
availability database (Database Replica) :
o A database that belongs to an availability group. For each availability database, the availability group maintains a single read-write copy (the primary database) and one to eight read-only copies (secondary databases). Only one or set of databases can be added to availability groups.
o primary database : The read-write copy of an availability database.
o secondary database: A read-only copy of an availability database.
availability replica:
o An instantiation of an availability group that is hosted by a specific instance of SQL Server and maintains a local copy of each availability database that belongs to the availability group. Two types of availability replicas exist: a single primary replica and one to eight secondary replicas.
o primary replica: The availability replica that makes the primary databases available for read-write connections from clients and, also, sends transaction log records for each primary database to every secondary replica.
o secondary replica: An availability replica that maintains a secondary copy of each availability database, and serves as a potential failover targets for the availability group. Optionally, a secondary replica can support read-only access to secondary databases can support creating backups on secondary databases.
Availability Modes:
o Asynchronous-commit mode:
 • primary replica commits transactions without waiting for acknowledgement from     secondary replica. This minimized Transaction latency and some data loss is possible.
 • Forced Manual Failover:
• This is the only form of Failover mode available in Asynchronous-commit mode.
• There could be possible data loss and it is used for disaster recovery.
o Synchronous-commit mode:
  • before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log
 Failover depends on Settings of Failover Mode property.
 • Planned manual Failover:
• Admin issues manual failover command, one of the secondary replica will be converted to primary and earlier will be turned as secondary, there will not be any data loss
 • Automatic Failover:
• Both primary and secondary replicas must be running in Synchronous-commit mode along with Failover mode to Automatic.
availability group listener:
o A server name to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica.
Active Secondary Replica:
o Performing backup operations on secondary replica: Log backups can be taken on Secondary replica/databases.
o Read-Only access on one or more replicas: Read access can be provided on secondary replica/databases. Listener can divert client requests either to primary or one of secondary…
Session Timeout period:
o This is the time which can be defined (Default is 10 seconds, can be configured any value > 5 seconds) to identify session timeout errors. If there is no ping between primary and secondary replica during this time it will enter into DISCONNECTED STATE.
Automatic page repair:
o If page is corrupt in secondary replica, it can get the page from primary replica.
o If the page gets corrupted in primary, It broadcasts a message for the page to all secondary replicas and replace the page from first response.

• Useful DMVs : sys.dm_hadr_database_replica_states and sys.dm_hadr_database_replica_cluster_states
• When the role of an availability replica is indeterminate, such as during a failover, its databases are temporarily in a NOT SYNCHRONIZING state. Their role is set to RESOLVING until the role of the availability replica has resolved. If an availability replica resolves to the primary role, its databases become the primary databases. If an availability replica resolves to the secondary role, its databases become secondary databases.
• In Synchronous commit mode, if there is no PING between primary and secondary replica for more than Session Timeout period, the transaction will be committed without waiting for secondary, resynchronize takes place later.

Terminology/ Technologies:
WSFC : Windows Server Failover Clustering (WSFC)
FCI: Failover Cluster Instance

Features Supported by different Editions of SQL Servers(2016):

This entry was posted in High Availability, SQL Server and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s