Always on in standard and enterprise edition

  • Nivs719

    SSC Veteran

    Points: 206

    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!

  • John Mitchell-245523

    SSC Guru

    Points: 148771

    I typed "Always On feature list SQL" into my favourite search engine and I found this.

    I don't know where you'll find information for SQL Server 2012 now it's no longer in mainstream support.  I'm sure it's out there somewhere, though.

    John

  • Brahmanand Shukla

    SSC Eights!

    Points: 938

    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 !

    • This reply was modified 9 months, 2 weeks ago by  Brahmanand Shukla. Reason: Made few amendments/corrections in the original comment in relation to the observations reported by fellow members
  • as1981

    SSCrazy

    Points: 2761

    Brahmanand Shukla wrote:

    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.

     

  • Brahmanand Shukla

    SSC Eights!

    Points: 938

    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.

  • Jacob Wilkins

    One Orange Chip

    Points: 27891

    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!

  • Nivs719

    SSC Veteran

    Points: 206

    Thanks for the explanation!

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply