Creating multiple AG for multiple DB in Always on with Standard Edition.

Question

Can I create two or three AG, one for each database i have and use only one Listener. I am using Standard Edition ?.

Answer 1

Starting from SQL Server 2019 the Standard edition includes the "Basic availability groups" feature, with support for two replicas, with one database.
Reference: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver16
So basically, the answer is no, you cannot create more than one AG on a SQL 2019 Standard Edition.

Answer 2

Hi @Luis Azario ,

First of all, you can refer to this document to see the differences between the versions of SQL Server 2016. We can skip directly to the RDBMS High Availability section:
https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-ver16#RDBMSHA

Now let's look at the specific limitations:
Basic availability groups use a subset of features compared to advanced availability groups on SQL Server 2016 Enterprise Edition. 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.

Among the things you need to be concerned about are the following.
You can only join one database per basic availability group.
While you can have an unlimited number of Availability Groups within a replica as your hardware resources permit, you can only have one database in a Basic Availability Group.
You can only have two (2) Availability Group replicas – one primary and one secondary.
Traditional Availability Groups in Enterprise Edition allow you to have up to four (4) secondary replicas with SQL Server 2012 and eight (8) secondary replicas with SQL Server 2014 and higher.

So, if you use the standard version, you can deploy 2-3 AGs on two nodes. there is only one availability database in each AG. one AG can have multiple listeners. In your requirements, you can have one AG corresponding to one listener.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.