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 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:

In this step right click Availability Group and select New Availability Group Wizard as shown in below snapshot.

Step 2:

In this step click next to create a New Availability Group.

Step 3:

In this wizard give Availability Group Name and click next.

Step 4:

In this step select user databases which you want to add to the Availability Group and click next.

Step 5:

Here click on add Replica and add the other instances which will acts 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 allows Availability Group failover from primary to secondary server automatically without data lose when both replicas in synchronous mode. If secondary is asynchronous mode prefer manual failover.

Under synchronous commit if we check boxes that replica will acts 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:

In this wizard select backup preferences based on your requirement.

Step 9:

In this wizard 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:

In this Step select data synchronization preference as Join only because already we have restored databases in Secondary server with no recovery mode.

Step 11:

In this wizard showing 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.

In below snapshot we can see the Availability group is created on all SQL Server instances.