SETUP ALWAYS ON AVAILABILITY GROUP

Always on is new feature introduced in SQL server 2012. always on provides high availability and disaster recovery for critical user databases. In always on we can do failover a set of databases from one primary replica to one or more secondary replicas. The set of databases can be called as availability group.

We have different modes in always on Availability Group.

• Synchronous-commit mode
• Asynchronous-commit mode

Synchronous-commit mode:

In synchronous commit mode both primary and secondary replicas are in synchronous commit mode. If we perform any transaction first transaction will commit in secondary replica and it will send acknowledgment to primary. After receiving acknowledgment from secondary transaction will commits in primary then it will gives response. So in Synchronous commit mode we have to wait some time to get response.

Asynchronous-commit mode:

In asynchronous commit mode Primary in synchronous and secondary in asynchronous commit mode. If we perform any transaction primary replica will not wait for the acknowledgment from the secondary replica. Transaction will commit in primary without receiving acknowledgment from secondary replica and it will give response. So in asynchronous commit mode we will get response quickly.

Configuring Always On Availability Group:

Before configuring Always On Availability Group take Full and T_log backups of user databases from primary replica and restore in all secondary replicas with no recovery mode.

Step 1: Right click Availability Group and select New Availability Group Wizard.

Step 2: Click next to create a New Availability Group.

Step 3: Give Availability Group Name and click next.

Step 4: Select user databases which you want to add to the Availability Group and click next.

Step 5: Click on add Replica and add the other instances which will act as secondary replicas for selected user databases.

Step 6: Here the primary replica is the source server and secondary replica which maintain a backup copy of primary server user databases which participate in availability group.

In primary replica we can perform read and write operations and in secondary replica we can perform only read operations.

Automatic failover can allow Availability Group failover from primary to secondary server automatically without data loss when both replicas are in synchronous mode. If secondary is asynchronous mode prefer manual failover.

Under synchronous commit if we check boxes that replica will act as a synchronous commit mode otherwise it will be in asynchronous commit mode.

Step 7: Here we can specify the endpoint details, otherwise leave it as default.

Step 8: Select backup preferences based on your requirement.

Step 9: Give Listener DNS Name and Port number. In network mode select static IP then click add button and give IP address and click on ok and then click on next.

Step 10: Select data synchronization preference as Join only because already we have restored databases in Secondary server with no recovery mode.

Step 11: This wizard shows the results of Availability Group validation and click on next.

Step 12: Now click on finish to complete the configuring of Availability Group.

Step 13: Configuring of Availability group has been completed successfully.

Note: Here we are seeing warning because we don't have Quorum. If we configure Quorum we didn't get this warning.

The Availability group is created on all SQL Server instances. Running your servers without Active Directory? See how to configure a domain-independent availability group .