Setup Always On Availability Group In Sql Server
The introduction of the “Always On” feature in SQL Server 2012 marked a significant advancement. “Always On” offers enhanced high availability and disaster recovery capabilities for crucial production user databases. With Always On feature in SQL Server, We can perform automatic or manual failover for a database or set of databases part of Always ON Availability Group, Failover can be done from One replica to another replica which consists of One Primary Replica and multiple secondary replica’s upto 8 in the latest SQL version.
“Always On” Availability Group can have 2 types to configure replicas.
Synchronous Commit Mode: This mode ensures synchronous commit, where transactions are synchronized between the primary and secondary replicas, ensuring a consistent state across the availability group.
In this state transaction first commit in secondary and sends acknowledgement to Primary replica to commit the transaction. In this type of AG mode , impact of performance is based on the response from the secondary Replica.
Asynchronous Commit Mode: In this mode of asynchronous commit, transactions will be committed on the primary replica without waiting for acknowledgement from the secondary replicas. Asynchronous commit mode can provide increased performance but may have a slightly higher risk of data consistency between replicas, based on the data movement between primary and secondary.
Step 1:
To start the AG (Availability Group) configuration, right click Availability Group and select New Availability Group Wizard as shown in below snapshot.
Step 2:
Click next to create a New Availability Group.
Step 3:
Provide Availability Group Name and click on Next.
Step 4: Now , select databases which you want to add to the Availability Group and click next. Database backup should be taken to be eligible for adding to AG.
Step 5: Now click on add Replica and add SQL Instance, which is part of the Cluster, this will be secondary replicas for databases selected in the above step
Step 6.
Primary replica is the source server and secondary replica will have the same copy of data as primary.
We can have only one primary replica ,where read and write operations occur and in secondary replica can be used for read operation if using Enterprise Edition. With SQL Standard version secondary read operation is not supported.
In synchronous mode Automatic failover is possible from primary to secondary server without any data loss if the databases are healthy state. If asynchronous mode manual failover is preferred.
You can select synchronous or async based on the required.if bandwidth is good then can select synchronous if distance from primary replica to secondary replica is more ,then need to select asynchronous
Step 7:This window shows endpoint details, Leave it as default.
Step 8:Now need to select option ,on which server want backup to happen,to decrease load on primary recommended to select secondary only option.
Step 9:Under Listerner Tab ,provide inputs of Listener DNS Name and Port number.Can get DNS details from Windows /Network team from your organisation.
Network mode need to select static IP ,now click on add button and input IP address and click ok and next.
Step 10:
Now, need to select data synchronization option as Join only .
If the database in primary are restored in secondary replica ,then can select Join only , If small database can select Full option, and give shared path accessible by primary and secondary.AG wizard will perform backup in primary,copy to secondary,restore database to secondary and Add to Ag.
Step 11:Availability Group validation will happen in this windows and click on next.
Step 12:Click on Finish to complete the configuring of Availability Group.
Step 13: Check Ag Status ,where selected database added to the Ag Group