Building a Two-Node SQL Server Always On Availability Group on Proxmox

This guide describes how to build a two-node SQL Server Always On availability group on Proxmox virtual machines: a Windows failover cluster with no shared storage, a contained availability group with a listener, synchronous commit, manual failover, and a file share witness on a third host. It is based on a real build and rebuild, so it concentrates on the ordering problems and pitfalls that cost time. Names and addresses are placeholders; replace them with your own.

Read this first: use Windows Server 2022, not 2025

On our Proxmox VMs, Windows Server 2025 (build 26100) produced a constant ucsactivitytrace.cpp assertion in SQL Server that dropped the AG connection every minute or so and made automatic seeding fail. The same SQL Server 2025 build ran clean on Windows Server 2022. SQL Server 2025 on Windows Server 2022 is supported. Everything below assumes Windows Server 2022 nodes. (There is a separate write-up of the assertion and how we diagnosed it.)

What this builds

Design and placeholders

Item Example value
Domain example.lan
Nodes SQLNODE1, SQLNODE2
Cluster name object (CNO) SQL-CLUS at 10.20.1.13
AG / listener MYAG / SQL-LSTN at 10.20.1.14, port 1433
Server VLAN (static, no gateway) 10.20.0.0/21, nodes 10.20.1.11 and 10.20.1.12
LAN (DHCP, has the gateway and DNS) 192.168.10.0/24
SQL service account EXAMPLE\svc_sql
Witness \\WITNESS-HOST\ClusterWitness (on a third Proxmox host)

Decisions that mattered:

Proxmox VMs

Network on each node

# Server-VLAN NIC: static, no gateway, no DNS servers, no DNS registration
New-NetIPAddress -InterfaceAlias "ClusterNetwork" -IPAddress 10.20.1.11 -PrefixLength 21
Set-DnsClientServerAddress -InterfaceAlias "ClusterNetwork" -ResetServerAddresses
Set-DnsClient -InterfaceAlias "ClusterNetwork" -RegisterThisConnectionsAddress $false

# LAN NIC: DHCP (gateway and DNS come from DHCP), registers in DNS
Set-DnsClient -InterfaceAlias "LAN" -RegisterThisConnectionsAddress $true

Only the LAN NIC should have a default route. Router advertisements can add IPv6 default routes, so check Get-NetRoute -DestinationPrefix "::/0" and compare the two nodes.

Always start a block of network commands with a hostname guard, so a command meant for one node cannot run on the other:

if ($env:COMPUTERNAME -ne 'SQLNODE2') { throw "WRONG MACHINE: $env:COMPUTERNAME" }

On both nodes, open the firewall for SQL and the AG endpoint. The server-VLAN NIC often classifies as Public (there is no domain controller on that VLAN), so make the rules apply to all profiles:

New-NetFirewallRule -DisplayName "SQL AG Endpoint"      -Direction Inbound -Protocol TCP -LocalPort 5022 -Profile Any -Action Allow
New-NetFirewallRule -DisplayName "SQL AG Listener 1433" -Direction Inbound -Protocol TCP -LocalPort 1433 -Profile Any -Action Allow

Active Directory

Join both nodes to the domain into a dedicated OU, for example:

Add-Computer -DomainName example.lan -OUPath "OU=SQLCLUSTER,DC=example,DC=lan" -Credential EXAMPLE\admin -Restart

The cluster creates its computer object (the CNO) in the same container as the node that creates it, and the CNO must then be allowed to create the listener's computer object. If it isn't, adding the listener fails with event 1194 ("failed to create its associated computer object"). Do one of these before creating the listener:

Windows settings and the cluster

On both nodes:

Install-WindowsFeature Failover-Clustering -IncludeManagementTools
Add-LocalGroupMember -Group Administrators -Member 'EXAMPLE\svc_sql'     # see the troubleshooting table

Turn off automatic OS updates on these servers and update in a maintenance window. A SQL update delivered through Microsoft Update can install itself and leave the instance in a long "script upgrade mode" start.

From the first node:

Test-Cluster -Node SQLNODE1,SQLNODE2       # storage warnings are expected (no shared storage)
New-Cluster -Name SQL-CLUS -Node SQLNODE1,SQLNODE2 -StaticAddress 10.20.1.13 -NoStorage

Both cluster networks must show ClusterAndClient, because the listener will not come online on a cluster-only network:

Get-ClusterNetwork | ft Name, Address, Role
(Get-ClusterNetwork "Cluster Network 2").Role = 3       # if the server VLAN shows 'Cluster'

Witness

Put it on the third host:

# on the witness host
New-Item -ItemType Directory -Path C:\ClusterWitness
New-SmbShare -Name ClusterWitness -Path C:\ClusterWitness -FullAccess 'EXAMPLE\SQL-CLUS$'
icacls C:\ClusterWitness /grant 'EXAMPLE\SQL-CLUS$:(OI)(CI)F'

# on a cluster node
Set-ClusterQuorum -NodeAndFileShareMajority \\WITNESS-HOST\ClusterWitness

Both the share permission and the NTFS permission are needed.

Cluster functional level

A cluster created on Windows Server 2025 nodes (or upgraded in place) sits at a higher functional level than one created on Server 2022, and the level cannot be lowered. A Server 2022 node cannot join such a cluster (Add-ClusterNode fails with "incompatible operating system versions"). Check Get-Cluster | ft Name, ClusterFunctionalLevel before planning to add older nodes; the fallback is a new cluster.

SQL Server install

Use a configuration file so both nodes are identical. The settings that matter:

FEATURES=SQLENGINE
SQLSVCACCOUNT="EXAMPLE\svc_sql"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
INSTANCEDIR="E:"
SQLUSERDBDIR="E:\MSSQL\Data"       ; the same on both nodes, and for tempdb (SQLTEMPDBDIR)
SQLUSERDBLOGDIR="E:\MSSQL\Data"
SQLBACKUPDIR="E:\MSSQL\Backup"
SQLMAXDOP="8"                      ; match the core count, the same on both nodes
UpdateEnabled="False"
USEMICROSOFTUPDATE="False"         ; True opts the whole OS into Microsoft Update

Do not put passwords in the file; enter them in the wizard. Then apply the same cumulative update or GDR on both nodes and check that SELECT @@VERSION; matches. If you typed a wrong tempdb path, move it with ALTER DATABASE tempdb MODIFY FILE ... for each file, restart, and delete the stray folder.

Enable Always On, in this order

  1. Both nodes are in the cluster first.
  2. Restart the SQL service so it sees the cluster.
  3. Enable Always On Availability Groups in SQL Server Configuration Manager and restart the service.
  4. Check:
SELECT SERVERPROPERTY('IsHadrEnabled');                           -- 1
SELECT cluster_name FROM sys.dm_hadr_cluster;                     -- your cluster name
SELECT member_name, member_state_desc FROM sys.dm_hadr_cluster_members;
Get-ClusterResourceType | Where-Object Name -like '*Availability*'   # must be listed

If Always On was already enabled before the cluster existed, the AG resource type is not registered in the new cluster and CREATE AVAILABILITY GROUP fails with Msg 41105. Untick Enable Always On, restart, tick it again, and restart again.

Endpoints (both nodes)

CREATE ENDPOINT [Hadr_endpoint] STATE = STARTED
  AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
  FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [EXAMPLE\svc_sql];

Check the port both ways with Test-NetConnection <other node> -Port 5022. A missing CONNECT grant shows up as a replica that will not connect, with no obvious reason.

Prepare the databases and create the AG (primary only)

Each database must be in FULL recovery and have had a full backup since. Take full backups of everything first; they are your rollback point. Turn on compressed seeding (runtime only, so repeat it after a restart):

DBCC TRACEON (9567, -1);
CREATE AVAILABILITY GROUP [MYAG]
WITH (CLUSTER_TYPE = WSFC, CONTAINED, AUTOMATED_BACKUP_PREFERENCE = SECONDARY, DB_FAILOVER = ON, DTC_SUPPORT = NONE)
FOR DATABASE [db1], [db2], [db3]
REPLICA ON
  N'SQLNODE1' WITH (ENDPOINT_URL = N'TCP://10.20.1.11:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                    FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)),
  N'SQLNODE2' WITH (ENDPOINT_URL = N'TCP://10.20.1.12:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                    FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE (ALLOW_CONNECTIONS = NO));

Using the server-VLAN IPs in ENDPOINT_URL keeps AG traffic on that network and does not depend on DNS. If a name resolves to the LAN address instead, AG traffic uses the wrong NIC.

Never run CREATE or DROP AVAILABILITY GROUP on the secondary. A drop issued there removes the AG cluster-wide.

On the secondary:

ALTER AVAILABILITY GROUP [MYAG] JOIN WITH (CLUSTER_TYPE = WSFC);
ALTER AVAILABILITY GROUP [MYAG] GRANT CREATE ANY DATABASE;    -- without this, seeding fails with "Request Denied"

Watch seeding from the primary:

SELECT local_database_name, role_desc, internal_state_desc, failure_message,
       transferred_size_bytes/1048576 AS mb_sent, database_size_bytes/1048576 AS mb_total
FROM sys.dm_hadr_physical_seeding_stats;

SELECT adc.database_name, s.current_state, s.failure_state_desc, s.error_code
FROM sys.dm_hadr_automatic_seeding s
JOIN sys.availability_databases_cluster adc ON adc.group_database_id = s.ag_db_id
ORDER BY s.start_time DESC;

If several databases fail together with Seeding Check Message Timeout, seed them one at a time (REMOVE then ADD DATABASE, dropping any leftover copy on the secondary first).

Listener and logins

ALTER AVAILABILITY GROUP [MYAG] ADD LISTENER N'SQL-LSTN' (WITH IP ((N'10.20.1.14', N'255.255.248.0')), PORT = 1433);

The mask must match the server VLAN. When it works you get a listener name resource and an IP resource, both Online.

Logins in a contained AG live in the AG's own MYAG_master, not in the instance master. Create them through the listener (connect SSMS to tcp:10.20.1.14,1433; the tcp: prefix forces TCP), with the original SIDs so database users attach automatically:

CREATE LOGIN [app_login] WITH PASSWORD = '<password>', SID = 0x<original SID>, CHECK_POLICY = ON;

To read the SIDs and hashes from an existing contained AG, connect through its listener and query sys.sql_logins (password_hash, sid). Then check for orphaned users in each database:

SELECT dp.name, CASE WHEN sp.sid IS NULL THEN 'ORPHANED' ELSE 'OK' END AS status
FROM sys.database_principals dp LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid
WHERE dp.type = 'S' AND dp.principal_id > 4;

Point applications at the listener IP. Consider automatic failover only when every database is SYNCHRONIZED and you have tested a manual failover in both directions.

Health checks

-- replicas
SELECT ar.replica_server_name, rs.role_desc, rs.connected_state_desc, rs.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states rs JOIN sys.availability_replicas ar ON ar.replica_id = rs.replica_id;

-- databases
SELECT DB_NAME(drs.database_id) db, ar.replica_server_name, drs.synchronization_state_desc,
       drs.is_suspended, drs.log_send_queue_size, drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states drs JOIN sys.availability_replicas ar ON ar.replica_id = drs.replica_id
ORDER BY db, ar.replica_server_name;
Get-ClusterNode | ft Name, State
Get-ClusterResource | ft Name, State, OwnerNode
Get-ClusterOwnerNode -Resource "MYAG"          # both nodes listed

Day-to-day operations

Troubleshooting quick reference

Symptom Likely cause Fix
Msg 41105 ... resource type is not registered in the WSFC cluster Always On enabled before the cluster existed Untick and re-tick Enable Always On, restarting the service each time
SQL log says "waiting for the host computer to start a WSFC cluster" Service started before the node joined the cluster, or the SQL service account can't access the cluster Restart SQL after joining; add the service account to local Administrators or use Grant-ClusterAccess
Msg 19471 / event 1194 "failed to create its associated computer object" The CNO has no Create Computer objects right on its OU Grant it, or pre-stage the listener account with Full Control for the CNO
Listener or cluster IP won't come online, event 1223 "not configured to allow client access" Cluster network role is Cluster (1) (Get-ClusterNetwork "<name>").Role = 3
Add-ClusterNode ... incompatible operating system versions The node's OS is older than the cluster's functional level Create a new cluster on the older OS (the level cannot be lowered)
Msg 47135 on MYAG_master Contained system databases cannot be removed Use SET HADR SUSPEND then RESUME instead
Seeding: Request Denied Missing GRANT CREATE ANY DATABASE on the secondary Run it, and re-set SEEDING_MODE = AUTOMATIC on the replica
Seeding: Database With Name Already Exists Leftover copy on the secondary SET HADR OFF if needed, DROP DATABASE, delete leftover files, retry
Seeding: Seeding Check Message Timeout for many databases Too many at once, or a busy primary Seed one at a time
SSMS to the listener: "Named Pipes ... error 40" SSMS fell back to named pipes Connect as tcp:<listener-ip>,1433
Owner lists reset after upgrades, evictions or restarts Cluster possible-owner lists reverted Set-ClusterOwnerNode on the AG, listener name and IP resources
Constant 10054 disconnects plus ucsactivitytrace assertions Windows Server 2025 on this hypervisor setup Use Windows Server 2022
Application login fails through the listener only The login exists in the instance master, not in MYAG_master Recreate it through the listener with its SID
Backup fails with "Remote harden ... failed" on a busy database A flapping synchronous secondary stalled the write Fix the secondary first, then rerun the backup

Checklist