Database, software, cloud computing and data science courses, articles and tools - SQLNetHub

What are SQL Server Always On Availability Groups?

by

SQL Server AlwaysOn Availability Groups is a technology that was initially released by Microsoft with SQL Server 2012.

The main concept, is that you have your data available in more than one sites at the same time. To this end, you have groups of databases that have primary replicas and secondary replicas. These replicas are hosted on different servers and can be synchronized in two modes:

  • Asynchronous-Commit Availability Mode
  • Synchronous-Commit Availability Mode

 

Availability Groups Asynchronous-Commit Mode

With asynchronous-commit mode, the secondary replica is never fully synchronized with the primary replica. The reason for this, is that due to the asynchronous commit nature, any secondary database could lag behind at any point. This means that if you are using the asynchronous-commit mode and perform a forced failover (this is the only form of failover you can perform), then is highly possible to have data loss.

 

Availability Groups Synchronous-Commit Mode

With synchronous commit mode, after being joined to an availability group, a secondary database catches up to the corresponding database and enters the “Synchronized” state. The prerequisite for this of course, is that data synchronization (and thus, data movement) continues and it is not stopped for any reason (i.e. network issues, disk space issues, etc.). If data synchronization continues and synchronous-commit mode is used, then every transaction that is committed on a given primary database has also been committed on the secondary database.

 

Example of a SQL Server Always On Availability Groups Architecture

The below diagram illustrates a setup of a 2-node replica with SQL Server Always On Availability Groups.

What are SQL Server Always On Availability Groups? - Article on SQLNetHub
Are you a SQL Server DBA? Learn more about SQL Server via our course “Essential SQL Server Administration Tips“.

 

Let’s further discuss the concepts illustrated in the above architectural diagram.

 

Discussion and Recommendations

One thing which is very important to note, is that, based on the Availability Groups architecture, each replica, has its own dedicated storage, this after all, the way for having your data available on multiple sites. This is based on the principle of redundancy.

I have a geo-cluster with 2 nodes on 2 different sites, Site A and Site B. On each site, I have a replica of my SQL Server Availability Groups. At the example above, on Site A, I have my primary replicas, and on Site B, my secondary replicas. As discussed before, my replicas can be either synchronized with synchronous or asynchronous commit.

Another important thing to note, is that if you truly want to achieve high availability, you need a third site in order to server as a File Share Witness. That’s why in my above diagram, I have included a third site, that is Site C, which provides a file share in order to be used as the File Share Witness for the geo-cluster.

 

How to Connect my Application to a Database in a SQL Server Availability Group?

If you are new to SQL Server Availability Groups you might be wondering: “how will my application connect to the database?“. Well, don’t worry because this is very simple. Each Availability Group has its own listener ( a pair of virtual network name and virtual IP) which you need to set up during an Availability Group’s creation. In the connection string configuration for your application, you will need to enter that listener name/IP and its port number in order for your application to be able to “see” the database(s) in the specific availability group. After that, the underlying high availability architecture will be transparent for your application.

An important note here, you are advised to use Availability Groups for your solution only if your solution/application supports this high availability architecture.

 

What Happens During a Failover?

A failover in a SQL Server Availability Groups architecture is different (and faster) than a failover in a traditional SQL Server Failover Cluster Instance (FCI). The major difference is that whenever a failover takes place for a SQL Server Availability Group, the only thing that changes owner, is the listener. That means that after the failover, the specific availability group’s listener will be pointing to the other replica because that replica just became the new primary replica and other one (which until that time was the primary) became the secondary replica. All this of course, assumes that both replicas are synchronized.

 

Concluding Remarks

As you can see from the above discussion and example, all the above make SQL Server Always On a very powerful high-availability solution which must be definitely considered when designing active-active data centers.

Last, note that the above was a simplified example, in order to help you understand the very basics of SQL Server Availability Groups and how they contribute towards having highly available SQL Server instances.

With the SQL Server Always On Availability Groups offering, you can build sophisticated high availability database solutions with more than 2 sites and thus have a truly highly available infrastructure.

In subsequent articles, we will be discussing more topics on SQL Server Always On Availability Groups, so stay tuned!

 

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

If you really want to learn sophisticated SQL Server administration techniques, then you should check our on-demand online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
SQL Server Administration Tips (lifetime access – learn more).

Learn More

 

Featured Online Courses:

 

Related SQL Server Administration Articles:

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 9,963