Always On availability group - Standard Edition



  • I need advice regarding Always On for SQL Server Standard Edition 2019. My question is... is that even possible and if that makes sense to use that on production?

    I have two servers (A- primary and B-passive replica) the idea is

    • to install Standard Edition on both.
    • put each database from A server into a single basic availability group and to use Virtual IPs to link witnesses.

    The more I am thinking about that, more I have a feeling that it seems like a bad idea.

    First concern is that the basic availability groups have some limitations (no backups and checkdb jobs on secondary replica) which in that case can be a little bit problematic.

    But, secondly (which is more important) having multiple availability groups seems to be an overly complicated architecture.

    Could you please advise if anyone is using something like that on production? Are there any gotchas?

    Lastly, do I have to license server B as a passive replica?



  • is that even possible and if that makes sense to use that on production?

    Yes it's possible, but if it makes sense for your production environment really depends on your needs.

    As you've already noted, there's https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver15#limitations . One of the other big limitations is the fact it can't be used as a read replica (so it just sits dormant until you need to failover to it):

    Basic availability groups include the following limitations:

    Limit of two replicas (primary and secondary). Basic Availability Groups for SQL Server 2017 on Linux support an additional configuration only replica.

    No read access on secondary replica.

    No backups on secondary replica.

    No integrity checks on secondary replicas.

    No support for replicas hosted on servers running a version of SQL Server prior to SQL Server 2016 Community Technology Preview 3 (CTP3).

    Support for one availability database.

    Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition.

    Basic availability groups are only supported for Standard Edition servers.

    Basic availability groups cannot be part of a distributed availability group.

    You may have multiple Basic availability groups connected to a single instance of SQL Server.

    If you don't need to use the secondary to read from, and your goal is strictly to have a copy of the database to failover to during a disaster event, and you can tolerate the other aforementioned limitations then Basic Availability Groups can work for you in production.

    Though if you have a large number of databases you want to add to an Availability Group, then you might find it unmanageable with just Basic Availability Groups, and you may prefer regular AlwaysOn Availability Groups instead which allow multiple databases to be added to a single Availability Group. That's for you to decide though.

    Lastly, do I have to license server B as a passive replica?

    We don't normally answer specific licensing questions, but this one is simple enough to say yes, whenever you're doing production work on a SQL Server instance (even a dormant Basic Availability Group secondary replica), you need to adhere to all normal licensing rules for that instance. As AMTwo and Marcin point out, it's possible those rules are already adhered to via SA (Software Assurance) but we wouldn't be able to answer that, only your licensing vendor could.

    To find out more specific information on licensing, please see https://www.microsoft.com/en-us/licensing/product-licensing/sql-server and / or discuss with your vendor. All of the aforementioned information on licensing is not guaranteed fact, and should be discussed with your vendor regardless. Neither I nor StackExchange take any responsibility legally or otherwise, to how it's interpreted or used.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2