High Availability Group

High Availability Group: - it is advance version of existing Mirror DB
it is a group of user databases that fails over together, like in mirror HAG has Primary Replica and Secondary Replica concept, Primary Replica server which is hosting userdbs where the live data comes in and goes out where as Secondary Replica is a stand by server- like a copy of the Primary.

It supports Automatic and Manual Failovers, in case of primary goes down secondary server becomes primary.

This feature is dependent on Windows Failover Cluster, this is not a something like our traditional cluster where in we have shared disks, quorum disk and special network configuration. A cluster without share disk and quorum. Any person who has little knowledge on cluster can create/form a cluster. How to create a cluster we will discuss more in detail

How it works: -

There is term called Availability Group Listener, which plays a vital role in HAG, actually this listener provides a way of connecting to an availability group via SQL Network Name- Virtual Network Name. in simple terms… listener listens all incoming requests and routes the user to Primary Replica.

This process uses compression methodology to sync data from Primary to Secondary/Replica. At any given moment, only a single SQL Server instance may host the primary replica of an availability group's databases.

Advantages: -

Ø  It supports 1 Primary and up to 4 Secondary Replicas.

Ø  Active Secondary’s, Secondary Replicas are accessible and we can use them for read-only or reporting purpose

Ø  You can take all kinds of backups – Full/Log/ Secondary replica – differential is not allowed. Copy only backup is allowed on secondary where as direct log backup is allowed.

Ø  Automatic Page repair, it is something like whenever a page gets corrupted, first it checks whether that page is available on other replicas in HAG if available it takes the information from that server and repairs the page

Prerequisites & How to Configure:

Ø  All the servers (Primary & Secondary Replicas) should be in same domain and must partake in Single Windows Server Failover Cluster.

This cluster is not core/fully formed cluster; it doesn’t required Shared disks and Quorum disk.

Creating cluster is very simple all you need is cluster name and what are the nodes/replica servers you want to form.

·         Add failover feature on each server – in ServerManager à Feature -> Add feature

·         Form a cluster by clicking Create Cluster in Cluster Administrator.