What is Always On SQL Server, Availibility Groups & Failover Clustering?
To know what is Always On SQL Server, the answer is that Always On availability group feature of SQL server is simply an alternative to database monitoring but on a bigger scale say enterprise level. Always On feature is a disaster recovery & high availibity feature that was introduced with the launch of SQL Server 2012.
As users were aksing about this form us, finally the entire article consisting all the relevant topics is here to provide answers to users. All the technology including FCI & AG of Always On feature are mentioned here as well. Let’s dive deeper into this topic.
Table of Content
Two Major Technologies of Always On SQL Server
Now that we know what’s AlwaysOn feature it’s time to understand the entire topic in detail. Here, to be more specific, There are two features or we can tcehnologies that are part of this AlwaysOn feature of SQL Server.
- AlwaysOn Failover Clustering Instance
- AlwaysOn Availibility Groups
Well, both these technologies are qute similar to each other when we look at them. This is because they both take in use the Windows Server Failover Clustering – WSFC. However, when we look at these technologies with the AlwaysOn perspective, they seems to be significantly different from each other.
To understand this, let’s have a look at both these technologies one by one respectively. This can help users know them as well as utilize them well according to business needs.
What is SQL Always On Cluster – Failover Clustering Instances
To understand how this failover clustering works. Let’s bgin with the very foundation. Now, you all must be aware of the fact that Fibre Channel SAN & iSCSI requires shared storage. This AlwaysOn FCI also requires similar shared storage that is accessible by all the nodes in a server.
Now, some users have limited storage & they tend to use virtual machines or prefer cloud. In that case, the option of third party tool for data replication si also here to fulfill storage requirements. Furthermore, the multiside clustering across subnets which enables failover of SQL Server instances through out data centers is also upported here. However, for this, replication of data between the shared storage of all data centers is necessary.
Despite of the SQL server edition, this AlwaysOn FCI is available on both SQL Server Standard & Enterprise. But, there’s limitation of 2-Node on the SQL Server Standard edition. All you need to do is just opt for the “New SQL failover cluster installation” at the time of installation. Understanding the FCI is help for users to know what is Always On SQL Server in depth.
Users can observe an example of Two Node AlwaysOn FCI in the below image.
In the above image, We were using disk majority & quorum mode node. Quorum node’s role is to identify available nodes & then allocate the primary node. Another object finds out whether the failover should occur based on the determination of the lost communications between machines.
Keeping in mind that a symmetrical storage refers to cluster disk shared among all WSFC nodes. This way, the shared disk storage can be available to all nodes that canbe potential failover in WSFC cluster.
Below are the three most common examples shown:
- Node Majority
- Node & Fileshare Majority
- Node & Disk Majority (Symertical)
What is SQL Always On Availability Group
Unlike FCI, the ALwaysOn Availability Groups do not require shared storage for SQL server hosting. This is only a Enterprise edition fetaure & if you have SQL server version below than 2016, you can’t use it on the Standard edition.
However, if users are having SQL Server version 2016 or above, they can get a very minimal option to create availability groups as explained below:
Opt for “New SQL stand-alone installation” at the time of istalling the SQL Server. Just have a look at the below image to understand AlwaysOn Availibility Groups Failover in HA & DR.
To know what is Always On SQL Server, here’s an example of quorum mode node in AlwaysOn Availability Groups configuration:
- Node Majority
- Node & Fileshare Majority
- Node & Disk Majority (Asymertical)
When we talk about asymmetric disk storage, we understand that the storage disk is only shared between a subset of nodes. If you dig a little history, you can find that this asymmetric disk capablity was first introduced only in the Windows Server 2008.
Also Read: How to Migrate SQL Server Database to Lower Version?
Difference Between AlwaysOn FCI & AlwaysOnAG Tech
The purpose as well as the use of both these differs from each others. If users want. They can even merge both these technologies to get the best out of it. For a local high availability, FCI plays a good role & AG for the the cross data center recovery.
However, before we proceed to know the differences, there are some similarities that we need to address. Below are the three similarities in both the features to understand what is Always On SQL server database feature.
- The single AlwaysOn deployment is a WSCF deployment in all cases.
- Users can run these on both physical servers as well as virtual machines.
- With various implementations, both FCI & AG stretch through multiple data centers.
| AlwaysOn FCI | AlwaysOn AG |
|---|---|
| Stoarge is shared. | Stoarge is not shared. |
| HA instance level. | HA database level. |
| Virtual Server name for connection | AG listenername for connection |
| Secondary database not accepted, have active/passive nodes. | Replica can be Active Secondary but read only. |
| There’s no data protection for I/O subsystem failure. | Data protection is there for I/O subsystem failure. |
| No compulsion for database recovery models. | Full recovery mode for database is compulsory. |
| Instance-level protection – non data rebundant. | All secondary groups are redundant copy of primary. |
Disaster Management After Knowing What is Always On SQL Server Feature
Now, the questions arises what to do if users get stuck in a disastrous situatioh even after these fetaures. In such cases, the traditional & the best solution is to be ready with a data recovery solution that can makethe server running within the least possible time. Therefore, having the SQL Recovery Tool can act as a good old friend.
Download the software & then with just five simple steps, users can get heir files back.
No matter if know what is SQL always on cluster or what is SQL always on availability group in depth, having a backup plan is always a good step to take everytime.
Bringing It All Together
Finally, at the end of this article, we can say that now we know what is Always On SQL Server along with both the technologies involved in it. Using them wisley can help in maintaining a healthy server. Having a backup plan for disastrous situations is just a cherry on top.