Configuring SQL Server Always On Availability Groups from Scratch

We design and implement Always On AG for clients from scratch. In practice, the first problem when configuring independently: the WSFC cluster is up, SQL Server is installed on both nodes, the AG is created — but automatic failover does not occur. The cause is most often incorrectly configured quorum votes or the fact that the SQL Server Agent on the secondary replica does not monitor the health state of the listener. Then it turns out that the application uses a connection string with the server name directly. We guarantee that after our deployment, such surprises will not happen. Get a consultation with an engineer for an audit of your infrastructure.

Why failover doesn't work? — ms sql setup

Most common causes: quorum not configured (no witness), secondary replica not in synchronous mode, or listener not added. WSFC expects votes from all participants, and if one node is lost, the cluster may stop if a witness is missing. The solution — use Cloud Witness (Azure Blob Storage) or file share witness.

How to configure quorum and listener?

First, create WSFC with -NoStorage parameter, then add a witness. Cloud Witness is configured via Set-ClusterQuorum. After that, the listener is created as a virtual IP with a DNS record — the application connects to it, not directly to the node. The connection string for .NET clients must include MultiSubnetFailover=True. More about the architecture can be found in the documentation about Always On Availability Groups.

Synchronous mode provides zero data loss, but asynchronous is 30–50% faster — suitable for geographically distant replicas.

What is direct seeding and how does it simplify initialization?

Direct seeding is automatic initialization of a secondary replica without manual backup and restore. SQL Server transfers data through the endpoint. It's convenient for small databases (up to 200–300 GB), but for large databases it may take time. In such cases, manual seed via SMB or backup/restore is better.

How does AG work under the hood?

Always On AG runs on top of WSFC. Each node gets a vote, the witness adds a third vote for quorum. At the database level, AG uses availability groups with one primary and multiple secondary replicas. For automatic failover, replicas must be in synchronous mode and WSFC must be operational.

Preparing WSFC and SQL Server

Before creating the cluster, check node compatibility: same Windows versions, single AD domain, separate network adapters for heartbeat, SQL, and client traffic. Creating a cluster without shared storage:

Test-Cluster -Node SQL01, SQL02 -Include "Inventory","Network","Storage"
New-Cluster -Name SQLCLUSTER -Node SQL01, SQL02 -StaticAddress 10.0.1.10 -NoStorage
Set-ClusterQuorum -CloudWitness -AccountName "sqlwitness" -AccessKey "base64key=="

Enable Always On on each node:

Enable-SqlAlwaysOn -ServerInstance "SQL01" -Force Enable-SqlAlwaysOn -ServerInstance "SQL02" -Force 

Creating an Availability Group

The database must be in FULL recovery model and have a full backup before adding. Use direct seeding for automatic initialization:

CREATE AVAILABILITY GROUP [AG_Production]
WITH
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    FAILURE_CONDITION_LEVEL = 3,
    HEALTH_CHECK_TIMEOUT = 30000
FOR DATABASE [AppDB], [AuditDB]
REPLICA ON N'SQL01'
WITH
    ENDPOINT_URL = N'TCP://SQL01.corp.local:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC,
    SEEDING_MODE = AUTOMATIC,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
,
N'SQL02'
WITH
    ENDPOINT_URL = N'TCP://SQL02.corp.local:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC,
    SEEDING_MODE = AUTOMATIC,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY);
ALTER AVAILABILITY GROUP [AG_Production]
ADD LISTENER N'AG-Listener'
(
    WITH IP ((N'10.0.1.20', N'255.255.255.0')),
    PORT = 1433
);

On the secondary, execute:

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

Read-scale routing

For analytics on the secondary, configure a routing list:

ALTER AVAILABILITY GROUP [AG_Production] MODIFY REPLICA ON N'SQL01' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (N'SQL02')));
ALTER AVAILABILITY GROUP [AG_Production] MODIFY REPLICA ON N'SQL02' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL02.corp.local:1433'));

Common operational issues

• Synchronization drift: if the secondary shows SYNCHRONIZING, check sys.dm_hadr_database_replica_states. The log_send_queue_size field will indicate the lag. Causes — network overload or heavy transactions.
• Failover without switch: the application holds a connection pool to SQL01 — after failover, queries fail. Solution — use only the listener and MultiSubnetFailover=True.
• Backup on secondary: configure jobs with a check sys.fn_hadr_backup_is_preferred_replica(), otherwise backup will be on all nodes or none.

What is included in the work?

• Audit of current infrastructure and design of AG topology.
• Deployment of WSFC with quorum (witness) and enabling Always On.
• Creating AG with direct seeding, configuring listener and read-scale routing.
• Failover testing under load with RTO measurement (typically under 30 seconds).
• Documentation of runbooks and training for your team.
• Configuration warranty and support for one month after deployment.

Our experience — over 8 years in DevOps and SQL Server, over 50 projects on Always On AG. If you face failover issues or want to design a fault-tolerant architecture, order an infrastructure audit.

Timeline: 3 to 5 days for a standard configuration (2 nodes, up to 10 databases). For a DR replica in a different datacenter — up to 2 weeks. Cost is calculated individually after the audit. Get enterprise-grade fault tolerance — contact us for a consultation.