Skip to content
DBAExperts
SQL Server High availability

How to set up Always On Availability Groups in SQL Server

Always On Availability Groups keep copies of your databases across several servers for automatic failover. Here are the steps and key setup commands.

  1. 1

    Prepare the prerequisites

    You need a Windows Server Failover Cluster (WSFC) with the nodes, and you must enable the feature on each instance. All replicas must be on the same compatible edition.

    -- Habilita Always On en la instancia (requiere reinicio del servicio)
-- Se hace en SQL Server Configuration Manager, o con PowerShell:
-- Enable-SqlAlwaysOn -ServerInstance 'SRV1\INST' -Force
  2. 2

    Create the Availability Group

    Define the replicas, their availability mode (SYNCHRONOUS_COMMIT for zero-loss HA, ASYNCHRONOUS_COMMIT for remote DR), and automatic failover.

    CREATE AVAILABILITY GROUP [AG_Produccion]
FOR DATABASE [MiBase]
REPLICA ON
  N'SRV1' WITH (ENDPOINT_URL = N'TCP://SRV1:5022',
     AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
     FAILOVER_MODE = AUTOMATIC),
  N'SRV2' WITH (ENDPOINT_URL = N'TCP://SRV2:5022',
     AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
     FAILOVER_MODE = AUTOMATIC);
  3. 3

    Join the secondary replica to the group

    From the secondary server, join the instance to the AG and prepare the databases (restoring full + log with NORECOVERY).

    -- En la replica secundaria (SRV2)
ALTER AVAILABILITY GROUP [AG_Produccion] JOIN;
ALTER DATABASE [MiBase] SET HADR AVAILABILITY GROUP = [AG_Produccion];
  4. 4

    Create the listener

    The listener is the virtual name that applications connect to. It automatically redirects to the primary replica after a failover.

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

// common mistake

SYNCHRONOUS_COMMIT guarantees zero data loss but adds latency to every commit: if the replica is far away, your operations slow down. Use ASYNCHRONOUS_COMMIT for remote sites. Automatic failover only works with synchronous replicas and a healthy quorum in the WSFC.

// when it's worth an expert

Always On has many moving parts (cluster, quorum, endpoints, listener, permissions), and one mistake leaves failover broken right when you need it. At dba.mx we implement and test high availability with real failover, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.