1

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?

5 Answers 5

3

Fist 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.

I would not even let this be part of the equation. In fact, whichever route you go, I'd advise to run your backups and integrity checks on your primary replica. While offloading them to a secondary replica sounds like a great idea in theory, there are too many risks of data loss or corruption.

Data can become corrupted in a data file on a primary replica, which would never be replicated to the secondary replicas, and vice versa. Likewise, backups types are limited, and the preferred backup replica setting is not as straight forward as one would think.

I talk about pit falls of offloading backups (and partially touch on integrity checks) in more detail here.

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

This is going to be your biggest driving factor, as you can only have one database per Basic Availability Group. If you need all of those databases to failover at the same time, you'll have to look at enterprise edition as your solution. I would not consider a basic Availability Group for anything more than a single database.

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

Please reach out to your licensing vendor for any licensing related questions.

2

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 limitations on Basic Availability Groups. 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 this documentation from Microsoft 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.

4
  • 2
    Regarding licensing, the big IF relates to whether you have Software Assurance on the primary. SA includes a benefit that allows for free licenses for secondary replicas, in certain situations. Details and enforcement of SA benefits is inconsistent, so check with your licensing vendor to confirm your highly available secondary server will be covered by SA at your next true up. (The inconsistency is why licensing is considered off-topic. Different answers for different people)
    – AMtwo
    Feb 17, 2022 at 12:55
  • 1
    Where is source for the part of your answer regarding licensing? If you don't have a reputable source, then please don't answer licensing questions. You may be leaning out on a limb. Otherwise please consider adding a disclaimer that you do not take any responsibility should a legal issue arise.
    – John K. N.
    Feb 17, 2022 at 13:10
  • @JohnK.N. I didn't say anything specific about licensing if you read what I actually said closely "for production work...you need to adhere to all normal licensing rules". I don't state what those rules are specifically in OP's case, rather just gave a generic statement that production work requires some kind of licensing (which can possibly be provided by SA, as I added that point too). This is the blanket statement Microsoft goes by, and sure I probably could find it written in a few places on their website to link to.
    – J.D.
    Feb 17, 2022 at 13:13
  • @JohnK.N. Per your advice I added a Microsoft resource supporting the generic statement I made and added a disclaimer anyway.
    – J.D.
    Feb 17, 2022 at 13:19
1

If you have many databases on Standard Edition, consider alternatves to Basic AGs for HA.

  1. SQL Server Standard Edition supports AlwaysOn Failover Cluster Instances,

and

  1. Consider whether you can provide sufficient HA/DR for your workload by running SQL Server on a VM that will automatically reboot on another hypervisor host in case of a hardware failure.
1

If you don't need to use the secondary to read from, and you ae looking for a HA solution then this this option can be used in production.

-1

I agree with J.D. except licensing part. If you have Software Assurance, then passive replica is free if you don't have production workload on it. In Basic AG you can't have production workload.

Here are the links regarding licensing:

https://cloudblogs.microsoft.com/sqlserver/2019/10/30/new-high-availability-and-disaster-recovery-benefits-for-sql-server/

https://www.starwindsoftware.com/blog/cost-and-license-considerations-between-always-on-availability-groups-and-always-on-basic-availability-groups

In my organization we are mostly using enterprise AGs but we have one Basic AG and it works. So it is depend on your needs.

3
  • 1
    That is basically one of the reasons we don't support/answer licensing questions, because of the subtle differences. In this answer you are assuming that OP has Software Assurance. This can be misleading. I would recommend not answering licensing questions.
    – John K. N.
    Feb 17, 2022 at 13:05
  • 1
    ...or add a disclaimer where you state that you do not take any responsibility should a legal issue arise.
    – John K. N.
    Feb 17, 2022 at 13:18
  • That is why I stated "If you have Software Assurance" and provided one link directly from MS.
    – Marcin
    Feb 17, 2022 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.