Always On Availability Groups on Two Windows VPSes

In short

Always On Availability Groups (AGs) on two Raff Windows VPSes give you automatic database failover with <10 seconds downtime for unplanned outages. Setup: install MSSQL on both VPSes, create a Windows Failover Cluster (WSFC), enable Always On in SQL Server Configuration Manager, then create the AG via SSMS or T-SQL. Standard Edition supports basic 2-node AGs (one DB per AG, no readable secondary). Enterprise needed for multi-DB AGs, readable secondaries, or 3+ replicas.

What you'll need

• Two Raff Windows VPSes of identical spec, same Server version (2022 or 2025), same MSSQL version + edition
• Active Directory domain — both VPSes joined (see Promote a Windows VPS to a Domain Controller)
• Static IPs on both nodes
• Network connectivity between nodes — TCP 1433 (MSSQL), TCP 5022 (AG endpoint), DCOM/RPC for cluster
• Domain admin (or delegated) credentials for cluster setup
• MSSQL Server Standard or Enterprise — Express does NOT support AGs
• Estimated time: 2-3 hours for first setup; <30 min for additional databases

Step 1 — Install Failover Clustering on both nodes

Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

Reboot when prompted (or Restart-Computer -Force).

Step 2 — Create the Windows Failover Cluster

From either node, with domain admin credentials:

# Validate cluster (run before creating)
Test-Cluster -Node "SQLNODE01.contoso.local","SQLNODE02.contoso.local"

# Create the cluster
New-Cluster -Name "SQLCLUSTER" -Node "SQLNODE01","SQLNODE02" -StaticAddress 10.0.0.20 -NoStorage

-NoStorage: AG doesn't need shared storage (each node has its own DB copy). StaticAddress: cluster's virtual IP — separate from node IPs.

Step 3 — Enable Always On in SQL Server Configuration Manager

On EACH node:

1. Open SQL Server Configuration Manager
2. SQL Server Services → right-click SQL Server (MSSQLSERVER) → Properties → Always On Availability Groups tab
3. Tick Enable Always On Availability Groups
4. Restart the SQL Server service

Or via PowerShell:

Enable-SqlAlwaysOn -ServerInstance "SQLNODE01" -Force
Enable-SqlAlwaysOn -ServerInstance "SQLNODE02" -Force

Step 4 — Prepare the database

The database to add must be in FULL recovery model with at least one full backup taken:

-- On primary node
ALTER DATABASE [YourDB] SET RECOVERY FULL;
BACKUP DATABASE [YourDB] TO DISK = 'C:\SQL\Backups\YourDB_init.bak';
BACKUP LOG [YourDB] TO DISK = 'C:\SQL\Backups\YourDB_init.trn';

Step 5 — Create the Availability Group via T-SQL

-- On the PRIMARY node
USE [master];
GO

CREATE AVAILABILITY GROUP [AG_YourDB]
WITH (
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    DB_FAILOVER = ON,
    DTC_SUPPORT = NONE
)
FOR DATABASE [YourDB]
REPLICA ON 
    N'SQLNODE01' WITH (
        ENDPOINT_URL = N'TCP://SQLNODE01.contoso.local:5022',
        FAILOVER_MODE = AUTOMATIC,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
    ),
    N'SQLNODE02' WITH (
        ENDPOINT_URL = N'TCP://SQLNODE02.contoso.local:5022',
        FAILOVER_MODE = AUTOMATIC,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
    );

SYNCHRONOUS_COMMIT = transactions commit on both nodes before returning success — guarantees zero data loss on failover but adds latency. Use only between low-latency nodes (same region).

AUTOMATIC failover requires SYNCHRONOUS_COMMIT mode. Both go together.

Step 6 — Join the secondary to the AG

On SECONDARY:

ALTER AVAILABILITY GROUP [AG_YourDB] JOIN;

-- Restore the database from primary's backups (NORECOVERY)
RESTORE DATABASE [YourDB] FROM DISK = 'C:\SQL\Backups\YourDB_init.bak' WITH NORECOVERY;
RESTORE LOG [YourDB] FROM DISK = 'C:\SQL\Backups\YourDB_init.trn' WITH NORECOVERY;

-- Add the database to the AG
ALTER DATABASE [YourDB] SET HADR AVAILABILITY GROUP = [AG_YourDB];

The database now syncs to the secondary. Verify in SSMS → Always On High Availability → Availability Groups → AG_YourDB → Availability Replicas.

Step 7 — Create the Listener

The Listener is a virtual network name that always points to the current PRIMARY. Apps connect to the listener, not to a specific node.

ALTER AVAILABILITY GROUP [AG_YourDB]
ADD LISTENER N'AGListener' (
    WITH IP ((N'10.0.0.21', N'255.255.255.0')),
    PORT = 1433
);

Update applications to connect to AGListener,1433 (or AGListener if 1433 is default).

Step 8 — Test failover

-- On the SECONDARY (now becoming primary)
ALTER AVAILABILITY GROUP [AG_YourDB] FAILOVER;

Verify in SSMS — primary role should switch nodes. Apps connecting via the listener get reconnected automatically (~5-15 seconds depending on connection retry settings).

Standard Edition limitations

Per Microsoft Learn:

• Basic AG (Standard): 1 primary + 1 secondary, 1 database per AG, no readable secondary, no backups on secondary
• Always On AG (Enterprise): up to 8 secondaries, multiple DBs per AG, readable secondaries, distributed AGs

For SMB high availability on a budget, basic AG with Standard is usually enough.

Common mistakes

1. Skipping the cluster validation — Test-Cluster catches AD permissions, network, time sync issues before they bite later
2. Using ASYNCHRONOUS_COMMIT between same-region nodes — async means potential data loss on failover. Use sync between low-latency nodes
3. Mixing MSSQL versions/editions across replicas — must be identical (e.g. both 2022 Standard CU11)
4. Apps still hardcoded to one node's hostname — must use the Listener for HA to work
5. No witness for the cluster — 2-node clusters need a quorum witness (file share, cloud witness) to handle node failures
6. Forgetting to restore secondary with NORECOVERY — secondary stays in restoring mode; the AG join completes the chain

Tested on

[HUMAN-REQUIRED: Replace before publishing.]

Tested on two Raff Windows VPS, both $128 plan (8 vCPU / 32 GB), Windows Server 2022 build [VERIFY], MSSQL 2022 Standard, basic Always On AG with synchronous commit, [DATE]. Tester: [Engineer name].

What's next

• Install MSSQL Server 2022 / 2025 on a Windows VPS — install on each node first
• MSSQL Standard vs Enterprise — When to Pay More — pick the right edition
• MSSQL Backup Strategy — backup the AG (preferably from the secondary)
• Microsoft Learn — Always On Availability Groups

Sources

• Microsoft Learn — Always On Availability Groups
• Microsoft Learn — Basic availability groups
• Microsoft Learn — Create a Windows Server Failover Cluster
• Date last verified: 2026-04-20