SQL Server Standard Edition - Limitation

Question

The below SQLServer License Edition comparison URL does NOT mention any limitation about the number of databases supported within an AlwaysOn Failover Cluster Instance - for standard edition. Is it safe to conclude that there is no enforced limits on number of databases supported within a single AlwaysOn FCI for Standard Edition License?

I get that for standard Edition there is limitation of 2 nodes within a single SQLServer FCI. I also understand for Standard Edition's Basic Availability groups there is limitation of ONE Database per Availability group.

https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15

Just to give a little background info, Our application & database design is such that, we have one database per client. we put multiple small clients' DBs (say 10 of them) into a single SQL Server instance.

Our present goal is just HA and there is no need for DR scenario and there is no need for ReadOnly secondary copy of database which AGs provide.

I certainly do not want to manage, say 10 different Basic availability groups (for 10 databases) within the same SQL Server instance for achieving HA goal. Hence avoiding the popular AGs and exploring SQL Server FailOver Cluster instance.

---

This is second question...

Additionally, what if we bring Software Assurance into the mix. The SQLServer 2019 Licensing guide says, with Software Assurance we get HA, DR (and an Azure) copy for free but there is no mentioning of Ent or Std edition license in this context.

Does the Standard edition's - 2 nodes cluster limitation still applies for AlwaysOn Failover Cluster Instance, even with Software Assurance? Can we have a 3 node SQL FCI cluster with Std edition license if we also buy Software Assurance? If so, that would be icing on the cake (Primary, HA & DR nodes within same SQL FCI !)

Any help here is greatly appreciated. Thanks!

Answer 1

As has been exhaustively commented, licensing questions are out of scope, however for technical clarification...

AlwaysOn is built on top of Failover Clusters, but is distinct from Failover Cluster Instances (FCI). The difference mostly comes up with whether they share storage or not.

This link https://straightpathsql.com/archives/2019/02/basic-availability-groups-myths-truths/ does a good job of dispelling / clarifying basic availability groups.

YES, basic AG only allow a single database. HOWEVER, you can have many, many availability groups with a database each. This does complicate things a bit as it means that the databases won't necessarily move in sync with each other. But you get what you get.

Given your scenario, I don't think managing one AG per database would be terribly problematic. You are going to have to script your failover commands but checking AG health through TSQL should let you keep an eye on the herd (flock? gaggle?) of AG's. I wouldn't stress about it.

As for using Failover Cluster Instances instead of AG's, go for it. They have no limitation and if you just want HA then it's easier to manage as you don't need to sync logins, jobs, etc. It does mean that you must rely on your storage layer's HA rather than potentially having completely separate HA, but using FCI will allow you to do rolling upgrades and survive compute (CPU, memory, O/S) failure.

Answer 2

There is no special limit to the number of databases you can have on a Failover Cluster Instance (FCI) on Standard Edition.

The Software Assurance benefits don't change any of the technical limitations of Standard Edition, only how many licenses are required. Standard Edition is still limited to basic AGs and to two nodes for an FCI.

You can deploy a 2-node FCI with Log Shipping to a DR standby server.