SQL Server AG Deep Dive: Standard Edition Gotchas, Distributed AGs, and Clusterless AGs

A client called me six months after going live with SQL Server Standard Edition. They had built out a two-node Windows Server Failover Cluster, configured an Availability Group, and were proud of the architecture — until their DR team asked why the secondary replica was not serving any reads and the overnight backup job was still hammering the primary. The answer was simple: Basic Availability Groups on Standard Edition do not do those things. They never could. Nobody told them.

That call is why I wrote this. If you are evaluating AG topologies — Standard Edition basics, distributed AGs spanning clusters, or clusterless AGs on Linux — here is the unvarnished version.

Standard Edition Availability Groups — What You Actually Get

Basic Availability Groups Are Not Enterprise AGs

SQL Server 2016 introduced Basic Availability Groups as the Standard Edition equivalent of Always On AGs. The name implies parity. The feature set does not deliver it. Here is what Basic AGs actually give you:

• One database per AG. Not one per replica — one total. If you want HA for five databases, you build five separate AGs.
• No readable secondary. The secondary replica accepts no connections. It sits there and receives log records.
• No backup offloading. You cannot shift full or differential backups to the secondary.
• Synchronous commit only. Every transaction waits for the secondary to harden the log before committing on the primary.
• No contained databases. If your database uses contained users, Basic AGs will not work.

The Licensing Trap

Standard Edition licensing is per-core with a 24-core cap. If you need HA for a single, modest workload database and you have accepted the above constraints, Basic AG is a legitimate tool. The trap is assuming you are buying Enterprise HA at a Standard price. You are not.

If your workload needs any of the following, Standard Edition is the wrong license and Basic AG will not help you:

• More than one database under a single AG
• Readable secondaries for reporting or read-scale
• Backup jobs moved off the primary
• Asynchronous commit for a geographically distant DR site

When Basic AG Makes Sense — and When It Does Not

Basic AG makes sense when: you have one critical database, Standard Edition is the right license for your workload size, and you need automatic failover with a shared listener.

It does not make sense when: you have multiple databases, need secondary reads, or are planning to grow. In those cases, log shipping is often a better fit — it is simpler, cheaper to manage, and makes no promises it cannot keep.

Creating a Basic AG on Standard Edition

-- Run on the primary replica
-- Assumes WSFC is already configured and database is in FULL recovery

BACKUP DATABASE [AppDB]
TO DISK = N'\\backup-share\AppDB_full.bak'
WITH FORMAT, INIT, STATS = 10;

BACKUP LOG [AppDB]
TO DISK = N'\\backup-share\AppDB_log.bak'
WITH INIT, STATS = 10;

CREATE ENDPOINT [Hadr_endpoint]
  STATE = STARTED
  AS TCP (LISTENER_PORT = 5022)
  FOR DATABASE_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);

CREATE AVAILABILITY GROUP [AppAG]
  WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY, BASIC)
  FOR DATABASE [AppDB]
  REPLICA ON
    N'SQL-NODE1' WITH (
      ENDPOINT_URL      = N'TCP://sql-node1.corp.local:5022',
      FAILOVER_MODE     = AUTOMATIC,
      AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
      SEEDING_MODE      = AUTOMATIC,
      SECONDARY_ROLE    (ALLOW_CONNECTIONS = NO)
    ),
    N'SQL-NODE2' WITH (
      ENDPOINT_URL      = N'TCP://sql-node2.corp.local:5022',
      FAILOVER_MODE     = AUTOMATIC,
      AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
      SEEDING_MODE      = AUTOMATIC,
      SECONDARY_ROLE    (ALLOW_CONNECTIONS = NO)
    );

ALTER AVAILABILITY GROUP [AppAG]
  ADD LISTENER N'AppAG-Listener' (
    WITH IP ((N'10.0.1.50', N'255.255.255.0')),
    PORT = 1433
  );

ALTER AVAILABILITY GROUP [AppAG] JOIN;
ALTER AVAILABILITY GROUP [AppAG] GRANT CREATE ANY DATABASE;

Distributed Availability Groups

What a Distributed AG Is

A Distributed AG is an AG whose replicas are not members of the same Windows Server Failover Cluster. Instead of replicating log records directly between replicas, a Distributed AG replicates between two independent AGs — each AG sits on its own WSFC, and the Distributed AG links them together.

This architecture requires Enterprise Edition. It is available from SQL Server 2016 onward.

Use Cases

• Cross-datacenter DR where you cannot or will not extend a WSFC across a WAN.
• Cluster migrations — moving databases from one WSFC to another with minimal downtime.
• Hybrid on-prem to Azure — the on-prem cluster is the primary AG, the Azure cluster is the secondary AG.

Configuration Gotchas

Seeding mode matters more here. With a Distributed AG, automatic seeding has to traverse whatever network sits between your clusters. On a 1Gbps WAN link with a 2TB database, automatic seeding is not your friend. Seed manually using backup/restore.

Endpoint security across clusters. The database mirroring endpoints on each cluster's primary replica must be reachable from the other cluster's primary.

Latency and RPO. A Distributed AG with ASYNCHRONOUS_COMMIT mode means you have data loss exposure equal to your replication lag. Monitor that lag.

Creating and Monitoring a Distributed AG

-- Create the Distributed AG (run on primary AG's primary replica)
CREATE AVAILABILITY GROUP [DistAG]
  WITH (DISTRIBUTED)
  AVAILABILITY GROUP ON
    N'PrimaryAG' WITH (
      LISTENER_URL = N'TCP://primaryag-listener.corp.local:5022',
      AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
      FAILOVER_MODE     = MANUAL,
      SEEDING_MODE      = MANUAL
    ),
    N'SecondaryAG' WITH (
      LISTENER_URL = N'TCP://secondaryag-listener.dr.local:5022',
      AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
      FAILOVER_MODE     = MANUAL,
      SEEDING_MODE      = MANUAL
    );

-- Monitor replication lag
SELECT
  ag.name                       AS ag_name,
  ar.replica_server_name,
  ars.role_desc,
  drs.synchronization_state_desc,
  drs.log_send_queue_size       AS log_send_queue_kb,
  drs.log_send_rate             AS log_send_rate_kb_sec,
  drs.redo_queue_size           AS redo_queue_kb,
  drs.redo_rate                 AS redo_rate_kb_sec
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
JOIN sys.dm_hadr_database_replica_states drs ON ar.replica_id = drs.replica_id
WHERE ag.name IN ('PrimaryAG', 'DistAG')
ORDER BY ag.name, ar.replica_server_name;

Clusterless Availability Groups

What Clusterless Means

SQL Server 2017 introduced the ability to create an AG with CLUSTER_TYPE = NONE — no WSFC, no Pacemaker, no underlying cluster at all. There is no cluster health monitor watching node state and no automatic failover. If the primary dies, someone has to manually force the failover.

SQL Server on Linux and Pacemaker

The dominant clusterless-adjacent scenario is SQL Server on Linux. When you deploy AGs on Linux, you have two paths:

• Pacemaker cluster — the Linux equivalent of WSFC. This is the production HA path for Linux AGs.
• CLUSTER_TYPE = NONE — no cluster manager at all. Failover is entirely manual.

The Hard Limitation: No Automatic Failover

Without a cluster manager, there is no automatic failover. Period. For production systems where RTO matters, this is typically not acceptable without an external orchestration layer.

Creating a Clusterless AG

CREATE AVAILABILITY GROUP [ClusterlessAG]
  WITH (
    CLUSTER_TYPE = NONE,
    DB_FAILOVER  = OFF,
    AUTOMATED_BACKUP_PREFERENCE = PRIMARY
  )
  FOR DATABASE [AppDB]
  REPLICA ON
    N'sql-primary' WITH (
      ENDPOINT_URL      = N'TCP://sql-primary.example.com:5022',
      FAILOVER_MODE     = MANUAL,
      AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
      SEEDING_MODE      = AUTOMATIC,
      SECONDARY_ROLE    (ALLOW_CONNECTIONS = ALL)
    ),
    N'sql-secondary' WITH (
      ENDPOINT_URL      = N'TCP://sql-secondary.example.com:5022',
      FAILOVER_MODE     = MANUAL,
      AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
      SEEDING_MODE      = AUTOMATIC,
      SECONDARY_ROLE    (ALLOW_CONNECTIONS = ALL)
    );

ALTER AVAILABILITY GROUP [ClusterlessAG] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER AVAILABILITY GROUP [ClusterlessAG] GRANT CREATE ANY DATABASE;

Should You Use This? A Decision Framework

Basic AG (Standard Edition)

• Use it when: One database, Standard Edition is the right license, you need automatic failover with a listener.
• Do not use it when: Multiple databases, need readable secondaries, or need async commit for DR.
• Consider instead: Log shipping — simpler, no WSFC required.

Distributed AG (Enterprise)

• Use it when: HA/DR across two geographically separated WSFCs and you cannot stretch the cluster across the WAN.
• Watch for: Forwarder latency, certificate expiration across domains, automatic seeding over slow WAN links.

Clusterless AG (CLUSTER_TYPE = NONE)

• Use it when: Dev/test parity, Linux without Pacemaker, cross-domain read replicas, edge deployments.
• Do not use it when: You need automatic failover and cannot build an external orchestrator.