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.
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.
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!
Featured Online Courses:
- AI Essentials: A Beginner’s Guide to Artificial Intelligence
- SQL Server 2022: What’s New – New and Enhanced Features
- Working with Python on Windows and SQL Server Databases
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- Data Management for Beginners – Main Principles
- A Guide on How to Start and Monetize a Successful Blog
Related SQL Server Administration Articles:
- Essential SQL Sever Administration Tips
- How to Patch a Standalone SQL Server Instance
- The SQL Server Browser Service and UDP Port 1434
- The Maximum Number of Concurrent Connections Setting in SQL Server
- Top 10 SQL Server DBA Daily Tasks List
- There is no SQL Server Failover Cluster Available to Join
- Encrypting a SQL Server Database Backup
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior Database and Software Architect, Certified Database, Cloud and AI professional, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Moreover, Artemakis teaches on Udemy, you can check his courses here.