December 9, 2019 at 1:48 pm
Hello everyone!
I would glad to know, what is the difference in Always on between Standard and Enterprise edition?
And also there is something that change in 2012 and above?
Thanks!
December 10, 2019 at 10:53 am
As far as I know, AlwaysOn in Standard Edition is just for as a workaround for Mirroring. Mirroring feature has been depreciated by Microsoft. You cannot have multiple secondary replicas in Standard Edition. Also, you cannot leverage most feature of AlwaysOn such as Read Load Balancing, Availability Group can have only 1 database etc. But you can have Secondary Node with Synchronous Commit mode and also implement the Automatic Failover in Standard edition of SQL 2016. It was even new discovery for me after someone pointed out my mistake.
Whereas, if you go with Enterprise Edition, then you can have up-to 8 secondary replicas. You can have In SQL 2019 "up-to 5", in 2017 "up-to 3", in 2016 "up-to 2", in 2012 & 2014 "only 1" node with synchronous commit mode including the primary node. Synchronous Commit Mode means the control won't be returned unless all the nodes have confirmed the WRITE (INSERT/UPDATE/DELETE) has completed. This can be used in DR's. AlwaysOn has made the Automatic Fail-over possible. With Synchronous Commit Mode there won't be any data loss and with Automatic Failover there won't be any downtime. This was not possible earlier with Logshipping and Mirroring.
Additionally, In SQL 2012, there could be up-to "2" secondary replica, in SQL 2014 "up-to 4" and SQL 2016 onward "up-to 8" secondary replicas. There is something called as "Availability Group Listener" which has an IP mapped called "AG Listener IP". This acts as Load Balancer for the Read requests. Since SQL Server follows the master slave architecture as other RDBMS's to support ACID. In Master Slave Architecture only the Master (Primary node) can do both READ and WRITE whereas the Slaves (Secondary Nodes) can only do READ.
AlwaysOn is like a Savior for the DBA's and Organizations for implementing the High Availability, Disaster Recovery and Load Balancing the Read requests solution at a finger tip. This was a kind of nightmare earlier.
AlwaysOn can be implemented either using Availability Group or with WFC (Windows Failover Cluster). Read here for more information.
For more info, you can checkout the Microsoft documentation for each version and edition of the SQL Server.
I hope you would find this info useful !
December 10, 2019 at 2:42 pm
Also, you cannot leverage most feature of AlwaysOn such as Read Load Balancing, Synchronous Commit, Automatic Failover etc.
I am not sure which version you are referring to but I think Automatic Failover and Synchronous commit are available in Standard version on 2016.
December 10, 2019 at 3:21 pm
I think you have got it correctly.
It also refreshed my understanding on the subject. Many thanks !
I got this article quite useful to clear all the myths on AlwaysOn.
December 10, 2019 at 9:16 pm
Just a bit of clarification:
"Always On" is a label MS applies to some of its availability solutions, including Availability Groups and Failover Cluster Instances.
While it is often used interchangeably with "Availability Groups", that is not strictly correct.
See the first note here, for example.
Cheers!
December 11, 2019 at 8:31 am
Thanks for the explanation!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply