Quick note:
Availability Group(AG) is similar to database cluster manager.
AG is capable managing single or multiple databases to replica into secondary nodes which will be exactly like the primary database.
Basic AG features explained below:
- Failover mode( auto or manual)
- Auto: AG switchover/failover primary database to secondary node if node or service is unavailable in primary node.
- Manual: system admin can switch database to any available replica for planned maintenance activity.
- Commit modes( sync or asycn)
- sync: 2 nodes can be maximum configured to sync with primary database( including primary it is 3 nodes)
- Primary node will wait untill data is committed into the other nodes.
- It adds to a latency for the applications, as write operation in multiple node happens simultaneously.
- the good part is all nodes configured to have sync commit will have exact replica of the primary database, which is great as high availability of data.
- Read only databases can be used as reporting system without causing performance impact on the primary database.
- Async: 5 nodes can be configured as async commit.
- async commit is not real time, this is used when real time data query is not a concern, rather historic data retrieval is important and heavy read intensive queries are fired.
- sync: 2 nodes can be maximum configured to sync with primary database( including primary it is 3 nodes)
- Readable secondary( yes, no, read intended)
- yes: allows connection but no write operation
- read intended: allows only read-only connection.
- No: database is not available to read.
- yes and read intended are used by applications which explicitly connects to database for fetching data.
Microsoft SQL Server 2014 