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


כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *