Always On Availability Groups on Two Windows VPSes

SQL Server Always On availability groups provide high availability for SQL Server databases by replicating databases from a primary SQL Server replica to one or more secondary replicas.

For a Windows Server VPS setup, the normal high-availability architecture is:

App server
  ↓
Availability Group Listener
  ↓
Primary SQL Server replica
  ↔
Secondary SQL Server replica
  ↓
Windows Server Failover Cluster
  ↓
Quorum witness

The important rule:

Always On is high availability.
It is not a replacement for backups.

A good production setup needs:

• at least two Windows Server VMs;
• private networking between SQL nodes;
• Active Directory or a carefully designed cluster identity model;
• Windows Server Failover Clustering;
• SQL Server edition that supports the required AG features;
• same SQL Server version and patch level on replicas;
• comparable CPU, RAM, and disk layout;
• database in FULL recovery model;
• full and log backups before joining secondary databases;
• listener DNS/IP planning;
• quorum witness planning;
• firewall rules for SQL, cluster, and database mirroring endpoints;
• tested manual and automatic failover;
• backup and restore process independent of the AG.

If you are using SQL Server Standard edition, you are usually talking about a Basic Availability Group: two replicas, one database, and limited secondary capabilities.

If you need multiple databases failing over together, readable secondaries, backup offload to secondaries, more replicas, or advanced routing, you likely need SQL Server Enterprise.

Quick verdict

Simple rule:

Backups protect data.
Always On reduces downtime.
Monitoring tells you when either one is failing.

What Always On availability groups do

An Always On availability group is a SQL Server high-availability and disaster-recovery feature.

It replicates selected user databases from a primary replica to one or more secondary replicas.

The primary replica accepts read-write traffic.

Secondary replicas receive transaction log records from the primary and apply them to local database copies.

Depending on edition and configuration, secondary replicas can be used for:

• failover;
• read-only workloads;
• backup offload;
• disaster recovery;
• reporting workloads;
• maintenance flexibility.

A listener gives applications a stable connection name.

Instead of connecting to a physical SQL Server VM, applications connect to:

sql-prod-listener.yourdomain.local

During failover, the listener moves to the new primary replica.

The application should reconnect to the listener and continue against the new primary database.

What Always On does not do

Always On does not protect against every database problem.

It does not replace:

• full backups;
• differential backups;
• transaction log backups;
• restore testing;
• corruption detection;
• security controls;
• application-level retry logic;
• monitoring;
• patching discipline;
• runbooks.

If a user deletes rows, that delete can replicate.

If a bad deployment changes data, that change can replicate.

If an application corrupts data logically, the corruption may replicate.

The AG keeps databases available.

Backups let you go back in time.

You need both.

SQL Server editions: Standard vs Enterprise

Always On capability depends heavily on SQL Server edition.

SQL Server Standard: Basic Availability Groups

SQL Server Standard supports Basic Availability Groups.

A Basic AG is useful for one important database that needs a failover replica.

But it has limits:

• two replicas only;
• one availability database per basic AG;
• no read access on the secondary replica;
• no backups on secondary replica;
• no DBCC integrity checks on secondary replica;
• only Standard edition support;
• cannot be upgraded in place to an advanced Enterprise AG.

For a small business with one core database, Basic AG can be enough.

For a serious multi-database application, it usually becomes limiting.

SQL Server Enterprise: Advanced Availability Groups

SQL Server Enterprise supports full Always On availability groups.

Use Enterprise when you need:

• multiple databases in one AG;
• databases failing over together;
• multiple secondary replicas;
• readable secondary replicas;
• backup offload to secondary replicas;
• read-only routing;
• larger HA/DR topology;
• more flexible failover design;
• distributed availability groups;
• contained availability groups;
• advanced SQL Server HA features.

Enterprise costs more, but it avoids many Standard edition limits.

Do not design a multi-database production architecture around Standard edition if the application expects all databases to fail over together.

Always On vs Failover Cluster Instance vs log shipping

SQL Server has multiple high-availability and disaster-recovery patterns.

Availability Group

Best for:

• database-level failover;
• one or more replicated databases;
• listener-based application failover;
• readable secondaries on Enterprise;
• local HA and remote DR patterns.

Trade-off:

• does not protect instance-level objects automatically;
• logins, SQL Agent jobs, linked servers, credentials, and server-level settings must be managed separately;
• requires careful database eligibility and synchronization.

Failover Cluster Instance

Best for:

• instance-level failover;
• shared storage environments;
• applications that depend heavily on SQL instance name and server-level objects;
• scenarios where the entire SQL instance should move together.

Trade-off:

• depends on shared storage or storage replication design;
• failover is instance-level rather than database-level;
• different infrastructure model.

Log shipping

Best for:

• low-cost disaster recovery;
• delayed copy protection;
• simple warm standby;
• manual failover tolerance;
• environments where HA complexity is not justified.

Trade-off:

• no automatic failover;
• usually more manual;
• recovery point depends on log backup frequency;
• connection redirection is not automatic unless you build it.

For many SMB workloads, log shipping plus good backups can be more realistic than a full Always On architecture.

Do not choose Always On just because it sounds enterprise-grade.

Choose it when the recovery objective justifies the complexity.

Recommended architecture on a Windows VPS

For a two-node SQL Server Always On setup:

Private network:
10.20.0.0/24

SQL01:
Windows Server
SQL Server instance
Primary replica initially

SQL02:
Windows Server
SQL Server instance
Secondary replica initially

AD / DNS:
Domain controller and DNS

WSFC:
SQLCLUSTER

Availability Group:
AG_APP01

Listener:
sql-app01-listener

Quorum:
File share witness or cloud witness

A common Raff-style design:

App VM
  ↓ private network
SQL AG listener
  ↓
SQL01 + SQL02
  ↓
Private network replication
  ↓
Quorum witness

Do not place the application and both SQL replicas on the same failure boundary if the goal is high availability.

If all components fail together, the AG does not protect the business.

What you'll need

Prepare:

• two Windows Server VMs for SQL replicas;
• one domain controller or existing Active Directory environment;
• SQL Server installed on both SQL nodes;
• matching SQL Server version, edition, and patch level;
• private network between SQL nodes;
• static private IPs;
• DNS working correctly;
• Windows Server Failover Clustering feature;
• domain account for SQL Server service if Kerberos/listener requirements apply;
• SQL Server Management Studio;
• database in FULL recovery model;
• recent full and log backups;
• firewall plan;
• quorum witness;
• listener name and IP address;
• maintenance window for setup and failover testing.

For production, use dedicated SQL Server VMs.

Do not run SQL Server Always On replicas on domain controllers.

Step 1 — Decide the AG model

Before touching Windows clustering, decide the SQL Server AG model.

Ask:

• Standard or Enterprise?
• One database or many?
• Automatic failover or manual failover?
• Synchronous or asynchronous commit?
• Local HA or remote DR?
• Listener or direct connection strings?
• Readable secondary needed?
• Backup offload needed?
• How much data loss is acceptable?
• How much downtime is acceptable?
• Who performs failover?
• How will applications reconnect?

Do not build the cluster first and decide the business requirements later.

Step 2 — Prepare Active Directory and DNS

For most Windows SQL Server Always On deployments, Active Directory makes the architecture cleaner.

Prepare:

• domain-joined SQL01 and SQL02;
• DNS records working;
• time sync healthy;
• service accounts documented;
• cluster computer object permissions planned;
• listener name planned;
• SPN planning if Kerberos is required.

Check domain join:

(Get-WmiObject Win32_ComputerSystem).Domain

Check DNS:

Resolve-DnsName SQL01
Resolve-DnsName SQL02

If DNS is unhealthy, stop.

SQL Server clustering and listeners depend on reliable name resolution.

Step 3 — Prepare both SQL Server VMs

Both SQL replicas should be comparable.

Match:

• Windows Server version;
• SQL Server version;
• SQL Server edition;
• cumulative update level;
• collation where application requires it;
• data drive layout;
• log drive layout;
• TempDB design;
• service account model;
• firewall rules;
• SQL Server configuration.

Recommended folder layout:

D:\SQL\Data
L:\SQL\Logs
T:\SQL\TempDB
B:\SQL\Backups

If you use a single disk, keep the folder structure consistent on both nodes.

Always On setup is easier when file paths match.

Step 4 — Install Windows Server Failover Clustering

Run on both SQL nodes:

Install-WindowsFeature Failover-Clustering -IncludeManagementTools

Verify:

Get-WindowsFeature Failover-Clustering

Then run cluster validation.

From one node:

Test-Cluster -Node SQL01,SQL02

Review the validation report.

Do not ignore storage, network, or system warnings without understanding them.

For an AG without shared storage, storage warnings may need interpretation, but network and system failures should be fixed.

Step 5 — Create the Windows Server Failover Cluster

Create the cluster:

New-Cluster -Name SQLCLUSTER -Node SQL01,SQL02 -StaticAddress 10.20.0.50

Check:

Get-Cluster
Get-ClusterNode

Expected:

SQL01 Up
SQL02 Up

Use a private IP for cluster communication and listener traffic where possible.

Avoid exposing SQL cluster endpoints directly to the public internet.

Step 6 — Configure quorum witness

A two-node cluster needs a witness to avoid split-brain behavior.

Common witness options:

• file share witness;
• cloud witness;
• disk witness if shared storage exists.

For a two-VM setup, a file share witness is common.

Example:

\\DC01\SQLClusterWitness

Configure:

Set-ClusterQuorum -FileShareWitness "\\DC01\SQLClusterWitness"

Check:

Get-ClusterQuorum

Do not run a two-node production cluster without understanding quorum.

If both nodes disagree about who should be online, quorum determines which side can continue.

Step 7 — Open required firewall paths

Plan firewall rules intentionally.

Common SQL AG communication includes:

Open only between trusted private IPs.

Example database mirroring endpoint rule:

New-NetFirewallRule `
  -DisplayName "SQL Always On Endpoint 5022" `
  -Direction Inbound `
  -Protocol TCP `
  -LocalPort 5022 `
  -RemoteAddress "10.20.0.0/24" `
  -Action Allow

Do not expose port 5022 or SQL Server listener ports publicly unless there is a very specific, protected architecture.

Step 8 — Enable Always On availability groups in SQL Server

On each SQL Server node:

1. Open SQL Server Configuration Manager.
2. Go to SQL Server Services.
3. Right-click the SQL Server service.
4. Open Properties.
5. Go to the Always On High Availability tab.
6. Check Enable Always On Availability Groups.
7. Restart the SQL Server service.

Repeat on both nodes.

PowerShell can also be used, but SQL Server Configuration Manager is usually clearer for first setup.

Verify in SQL Server:

SELECT SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled;

Expected:

1

Step 9 — Create database mirroring endpoints

Always On availability groups use database mirroring endpoints for replica communication.

On SQL01:

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

Run the same on SQL02.

Grant connect permission to the SQL Server service account if needed:

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [DOMAIN\sqlsvc];
GO

If each SQL Server uses a different service account, grant endpoint permissions appropriately on each replica.

For production, document endpoint port, authentication, encryption, and service account.

Step 10 — Prepare the database

The database must be eligible for AG membership.

On the primary SQL Server:

ALTER DATABASE AppDB SET RECOVERY FULL;
GO

Take a full backup:

BACKUP DATABASE AppDB
TO DISK = 'B:\SQL\Backups\AppDB_full.bak'
WITH INIT, COMPRESSION, CHECKSUM, STATS = 10;
GO

Take a log backup:

BACKUP LOG AppDB
TO DISK = 'B:\SQL\Backups\AppDB_log.trn'
WITH INIT, COMPRESSION, CHECKSUM, STATS = 10;
GO

Copy backups to SQL02.

Restore on SQL02 with NORECOVERY:

RESTORE DATABASE AppDB
FROM DISK = 'B:\SQL\Backups\AppDB_full.bak'
WITH NORECOVERY, REPLACE;
GO

RESTORE LOG AppDB
FROM DISK = 'B:\SQL\Backups\AppDB_log.trn'
WITH NORECOVERY;
GO

The secondary database should remain in restoring state until joined to the AG.

Step 11 — Create the availability group

You can create the AG with SSMS or T-SQL.

For many admins, the SSMS wizard is easier for the first setup.

In SSMS:

Always On High Availability
  → Availability Groups
  → New Availability Group Wizard

Choose:

• availability group name;
• database;
• replicas;
• synchronization mode;
• failover mode;
• endpoints;
• backup preferences;
• listener if desired;
• seeding method.

For a local HA pair:

SQL01:
Synchronous commit
Automatic failover

SQL02:
Synchronous commit
Automatic failover

For remote DR:

Primary:
Synchronous or asynchronous depending on latency

Remote secondary:
Asynchronous commit
Manual failover

Synchronous commit protects data better but can add latency.

Asynchronous commit reduces latency but allows possible data loss during failover.

Step 12 — Add the listener

An availability group listener gives applications a stable name.

Example:

sql-app01-listener.yourdomain.local

Listener planning:

Applications should connect to the listener, not directly to SQL01 or SQL02.

Example connection target:

Server=sql-app01-listener;Database=AppDB;Integrated Security=True;TrustServerCertificate=False;

For multi-subnet deployments, client driver settings such as MultiSubnetFailover=True may matter.

For a single private subnet, listener configuration is simpler.

Step 13 — Join secondary databases

If the wizard does not automatically join the secondary database, run on SQL02:

ALTER DATABASE AppDB SET HADR AVAILABILITY GROUP = AG_APP01;
GO

Then check synchronization.

On either replica:

SELECT 
    ag.name AS availability_group,
    ar.replica_server_name,
    drs.database_id,
    db.name AS database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar
    ON drs.replica_id = ar.replica_id
JOIN sys.availability_groups ag
    ON ar.group_id = ag.group_id
JOIN sys.databases db
    ON drs.database_id = db.database_id;

Expected for a healthy synchronous setup:

SYNCHRONIZED
HEALTHY

If it shows NOT SYNCHRONIZING, investigate before testing failover.

Step 14 — Test application connection through the listener

Do not test only in SSMS.

Test from the application server.

From PowerShell:

Test-NetConnection sql-app01-listener -Port 1433

From SQL tools:

SELECT @@SERVERNAME AS ConnectedInstance;

Then test your real app connection string.

The application must use the listener name.

If the application still connects to SQL01 directly, failover will not help it.

Step 15 — Test manual failover

Before production, test failover intentionally.

In SSMS:

Always On High Availability
  → Availability Groups
  → right-click AG
  → Failover

Or with T-SQL on the target secondary:

ALTER AVAILABILITY GROUP AG_APP01 FAILOVER;
GO

Only perform planned manual failover when the secondary is synchronized.

After failover:

• listener should point to the new primary;
• application should reconnect;
• database should be read-write on new primary;
• old primary should become secondary;
• synchronization should resume.

Check:

SELECT 
    ag.name,
    ars.role_desc,
    ar.replica_server_name,
    ars.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar
    ON ars.replica_id = ar.replica_id
JOIN sys.availability_groups ag
    ON ar.group_id = ag.group_id;

Document the observed downtime.

If the app does not reconnect cleanly, fix the application connection string and retry.

Step 16 — Test automatic failover carefully

Automatic failover requires:

• synchronous commit;
• automatic failover mode on both replicas;
• synchronized secondary;
• WSFC quorum;
• healthy cluster;
• client connection through listener.

Test only in a maintenance window.

Common safe test:

1. Confirm AG healthy.
2. Confirm secondary synchronized.
3. Confirm application connects through listener.
4. Pause or stop SQL Server service on current primary.
5. Observe failover.
6. Confirm app reconnects.
7. Restore service.
8. Confirm old primary rejoins as secondary.
9. Review logs.

Do not perform destructive failover tests without backups and a rollback plan.

Step 17 — Configure backup strategy

Always On does not replace backups.

Use:

• full backups;
• differential backups where appropriate;
• transaction log backups;
• copy-only backups where required;
• off-server backup storage;
• restore testing;
• backup monitoring.

For Enterprise AGs, you can configure backup preference to offload backups to secondaries.

For Basic AGs on Standard edition, backups on secondary replicas are not supported.

Even when backups run on a secondary, you must monitor them.

A backup job that silently fails on the preferred replica is still a failed backup.

Step 18 — Monitor Always On

Use SSMS Always On Dashboard.

Also monitor:

• replica role;
• synchronization state;
• synchronization health;
• send queue;
• redo queue;
• last hardened LSN;
• listener status;
• cluster node status;
• quorum status;
• SQL Agent jobs;
• backup success;
• transaction log growth;
• disk space;
• endpoint connectivity;
• Windows event logs;
• SQL Server error logs.

Useful query:

SELECT 
    ag.name AS ag_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc,
    ars.operational_state_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar
    ON ars.replica_id = ar.replica_id
JOIN sys.availability_groups ag
    ON ar.group_id = ag.group_id;

If the AG is critical, monitoring should alert a human.

Do not rely on manually opening SSMS once a month.

Step 19 — Keep server-level objects in sync

Availability groups replicate databases.

They do not automatically replicate every server-level object.

Track and sync:

• SQL logins;
• SQL Agent jobs;
• linked servers;
• credentials;
• operators;
• alerts;
• SSIS packages;
• maintenance plans;
• server-level permissions;
• certificates;
• endpoints;
• custom server configuration.

This is one of the biggest Always On mistakes.

After failover, the database may be online, but the app can fail because the login, job, or linked server does not exist on the new primary.

Create a runbook for server-level object synchronization.

Step 20 — Document the failover runbook

A production AG needs a runbook.

Include:

• AG name;
• listener name;
• node names and IPs;
• cluster name and IP;
• witness path;
• SQL service accounts;
• endpoint port;
• database list;
• backup locations;
• monitoring links;
• manual failover command;
• forced failover conditions;
• application connection strings;
• rollback process;
• who can approve failover;
• who contacts application owners;
• post-failover checks.

Do not wait for an outage to decide who is allowed to fail over SQL.

Common mistakes

Mistake 1 — Treating Always On as backup

Always On replicates changes.

Bad changes replicate too.

Use backups and restore testing.

Mistake 2 — Using Standard edition for a multi-database app

Standard edition Basic AG supports one database per Basic AG.

If your app needs multiple databases to fail over together, use Enterprise or another architecture.

Mistake 3 — Connecting applications to the node name

Apps must connect to the listener.

If the app connects to SQL01 directly, the app stays pointed at SQL01 after failover.

Mistake 4 — No quorum witness in a two-node cluster

A two-node cluster needs a witness strategy.

Without quorum planning, failover behavior can be unsafe or unavailable.

Mistake 5 — Different disk paths on replicas

If SQL01 uses D:\SQL\Data and SQL02 does not have the same path, seeding, restore, and file placement become harder.

Keep layouts consistent.

Mistake 6 — No server-level object sync

AGs replicate databases, not all server-level objects.

Sync logins, SQL Agent jobs, credentials, linked servers, and certificates.

Mistake 7 — Exposing SQL ports publicly

Keep SQL replication and listener traffic on private networks where possible.

Restrict firewall rules by source IP and subnet.

Mistake 8 — No failover test

An untested AG is a theory.

Test planned failover before production.

Mistake 9 — Ignoring application retry behavior

The SQL side can fail over successfully while the app still fails because it does not reconnect properly.

Test the real app.

Mistake 10 — No monitoring

Always On can be unhealthy while databases still appear online.

Monitor synchronization state, queues, cluster health, backup jobs, and disk space.

Security checklist

Review:

• SQL ports restricted to trusted sources;
• endpoint port restricted to SQL nodes;
• listener not exposed publicly without a reason;
• Windows Firewall enabled;
• SQL service accounts documented;
• SPNs registered if Kerberos is required;
• least-privilege SQL admin access;
• backups encrypted where appropriate;
• certificates protected;
• RDP access restricted;
• patching process planned;
• forced failover permissions limited.

High availability should not weaken security.

How Raff fits SQL Server Always On

Raff Windows VM can provide the Windows Server infrastructure layer for self-managed SQL Server HA designs.

A Raff-based pattern can include:

• two Windows VMs for SQL replicas;
• private networking between nodes;
• separate app VM;
• Windows Server Failover Cluster;
• SQL Server Always On availability group;
• listener on private network;
• backups and snapshots;
• support for Windows VM infrastructure.

Raff does not replace SQL Server licensing, database administration, application architecture, or Microsoft SQL Server support.

You still need:

• SQL Server licenses;
• Windows Server design;
• AD/DNS design;
• cluster setup;
• database backup strategy;
• monitoring;
• DBA ownership;
• failover runbook.

Use Raff for the infrastructure foundation.

Use SQL Server expertise for the database architecture.

What Raff recommends

For SQL Server Always On on Windows VPS:

1. Start with backup and restore discipline before HA.
2. Use Standard Basic AG only for one-database HA needs.
3. Use Enterprise when the application needs multiple databases or readable secondaries.
4. Put replicas on comparable Windows VMs.
5. Use private networking for SQL replication and listener traffic.
6. Configure WSFC and quorum before creating the AG.
7. Keep disk paths consistent.
8. Use the listener in application connection strings.
9. Test manual failover and application reconnect.
10. Monitor synchronization, backups, logs, and cluster health.

The right mindset:

Do not build Always On because it sounds advanced.
Build it because the recovery objective requires it and the team can operate it.

Do not publish that exact claim until the environment has been tested.

What's next

• Install SQL Server 2025 on a Windows Server VPS
• MSSQL Backup Strategy on Windows VPS
• MSSQL Memory Tuning on Windows VPS
• Windows Server Version Comparison
• Active Directory Domain Controller on Windows VPS
• Active Directory Replication Topology
• Raff Windows VPS

Sources

• Microsoft Learn — What is an Always On availability group?
• Microsoft Learn — Prerequisites, restrictions, and recommendations for Always On availability groups
• Microsoft Learn — Basic Always On availability groups for a single database
• Microsoft Learn — Editions and supported features of SQL Server 2025
• Microsoft Learn — Create or configure an availability group listener
• Microsoft Learn — Windows Server Failover Clustering with SQL Server
• Date last verified: 2026-07-07