So what is SQL AlwaysOn
So what is SQL "AlwaysOn"?
The Technical term of "Always on"
refers to a system that is constantly available, operational and responding to
demands from users.
Microsoft's AlwaysOn technology was first presented
on SQL Server 2012.
Microsoft presented it as the new Active-Active technology for SQL server high
availability solution. The AlwaysOn technology,
is basically based on the mirroring feature,
that exists for a very long time (Since SQL 2005), but includes better and
advanced features. This technology is served up as a whole solution for your
critical databases.
The AlwaysOn solution,
is SQL server new way to accomplish HADR – High Availability Disaster Recovery, since it covers both server
and database storage in case of disaster. The AlwaysOn solution is installed on
top of WSFC – Windows Server Failover Cluster,
and it is based on two technologies:
1. FCI – Failover Clustered Instances, which gives us redundancy through multiple SQL instance

2.
AG –
Availability Groups that gives us redundant copies of our data across multiple
servers

The strongest architecture of SQL server AlwaysOn, presented in SQL 2016. It has
the ability to combine between FCI and AG,
:as well as creating a Distributed AG, as described in the sketch

The strongest architecture of SQL server AlwaysOn, presented in SQL 2016. It has
the ability to combine between FCI and AG,
as well as creating a Distributed AG, as described in the sketch:
Now, let's review the new AlwaysOn
features:
·
AlwaysOn Cluster configuration supports multi Subnet
failover capabilities, including Azure servers, as part of the cluster solution
·
AlwaysOn Availability Group can contain 1 primary
instance and up to 4 Replicas (SQL servers). The primary replica is the active
read/write copy on the database, 2 of the 4 replicas can work with synchronous
data replication whereas the additional 2 need to be asynchronous. All the
replicas are read only databases
·
The Availability Group allows us to connect to the AG
listener that will always point to the Primary replica, but on the other hand,
we can also access directly each one of the read only
replicas. This is the best solution for reporting and other activities we want
to perform on production data, but not on production database!
·
Availability
group can contain multiple user databases
·
Automatic
Failover is possible to the synchronous replicas and is almost un noticed by the users
What are the limitations of AlwaysOn Availability Group?
·
In SQL 2012
and SQL 2014 AlwaysOn was only available on Enterprise Edition. Since SQL 2016 AlwaysOn
is also available on Standard Edition, but it has a lot of limitations
·
System Databases
cannot be part of the Availability Group. As result, all server objects (like
Jobs, Logins, Linked servers etc.) are not part of the Always On replication
and needs to be synchronized manually