Configure SQL Always On

Here’s a clean, end-to-end walkthrough to stand up SQL Server Always On Availability Group (AG) on Windows (WSFC). This assumes 2–3 replicas, one AG listener, and synchronous commit + automatic failover between two primaries.

Prerequisites

1. Environment

   • Same AD domain; static IPs.

   • Windows Server (2016+ recommended) on each replica.

   • SQL Server Enterprise (AGs) or Standard (Basic AG limitations) same major version/patch level.

   • One domain service account for the SQL Engine service on all replicas (recommended), with “Connect SQL” rights.

   • Time sync and name resolution (DNS) working.

2. Windows Firewall

   • Open TCP 1433 (or your instance port) for the listener & engine.

   • Open TCP 5022 (default AG endpoint) between replicas.

3. Databases to replicate

   • FULL recovery model (Database > Properties > Options).

   • Take a FULL backup, then a LOG backup (required unless using Automatic Seeding).

4. Witness (for quorum)

   • In 2-node setups, prepare a File Share Witness or Cloud Witness.

Build the Windows Failover Cluster (WSFC)

On any node:

1. Open Server Manager > Tools > Failover Cluster Manager.

2. Validate Configuration… > add all replica servers > run all tests > ensure no critical failures.

3. Create Cluster…

   • Cluster Name: e.g., SQL-WSFC

   • IP Address: assign static IP for the cluster core resource.

   • Finish. (You don’t need shared storage for AGs.)

4. Add a Witness:

   • In Failover Cluster Manager, right-pane More Actions → Configure Cluster Quorum Settings…

   • Choose Select the quorum witness > pick File Share Witness (enter a share like \\fs\Witness$) or Cloud Witness.

Enable “Always On” at the SQL Service level (each replica)

On each SQL Server:

1. Open SQL Server Configuration Manager.

2. SQL Server Services → right-click your instance → Properties → Always On High Availability tab.

3. Check Enable Always On Availability Groups (cluster must be healthy).

4. OK, then Restart the SQL Server service.

Create the Availability Group in SSMS (on the future primary)

1. Connect in SQL Server Management Studio (SSMS) to the node you want as the primary.

2. Expand the instance > Always On High Availability > Availability Groups > New Availability Group Wizard…

3A. Specify AG name & databases

• Availability group name: e.g., AG_Sales.

• On Select Databases, choose only DBs that show Meets prerequisites = Yes (FULL recovery + recent full backup).

  • If not, cancel briefly to set FULL + take full/log backups; then rerun wizard.

3B. Specify Replicas

• Add Replica… > connect to the other SQL nodes.

• For the two main replicas:

  • Availability Mode: Synchronous commit (for HA pair).

  • Failover: Automatic for exactly two synchronous replicas.

• Optional third replica:

  • Asynchronous commit, Manual failover (DR).

• Readable Secondary: choose Yes or Readable intent only if you’ll route read workloads.

3C. Endpoints & Backups

• Wizard will create the Database Mirroring endpoint (default TCP 5022) if not present.

• Backup Preferences: typically Prefer Secondary (so backups run offloading to secondaries).

3D. Listener (virtual name for apps)

• On Listener tab > Create an availability group listener:

  • Listener DNS name: e.g., ag-sales-listener

  • Port: 1433 (or your custom port)

  • Network Mode: Static IP > Add… > assign the listener IP on the production subnet(s).

  • Multi-subnet? Add an IP per subnet (and ensure client connection string uses MultiSubnetFailover=True).

Azure note (if replicas are in Azure VMs): the listener needs a Load Balancer with a health probe + HA ports or specific rule; associate the LB frontend IP with the listener name. (Do this in Azure first; then, in the wizard, choose that static IP.)

3E. Data Synchronization

Choose one:

• Automatic seeding (SQL 2016+): check Enable automatic seeding. Simplest; ensure service account has rights and disk capacity is available.

• Full: supply a shared network path; wizard will back up and restore automatically.

• Join only: if you already restored with NORECOVERY manually.

3F. Validation & Create

• Review the Validation page; all green? Finish to create the AG.

Join & verify (secondaries)

If you used Automatic Seeding or Full method, replicas will auto-join. Otherwise:

1. On each secondary in SSMS:

   • Always On High Availability > Availability Groups > [AG_Sales] > Availability Databases

   • Right-click the database > Join to Availability Group…

2. Watch AG Dashboard:

   • Instance > Always On High Availability > Availability Groups > right-click Show Dashboard.

   • Expect Synchronized (primary/secondary), green checks.

Test a planned failover

1. In SSMS AG Dashboard, right-click the primary replica > Failover…

2. Next > choose a synchronous replica with Automatic Failover > Connect > Finish.

3. Confirm apps can connect via the Listener and the new primary accepts writes.

Post-build housekeeping

1. Jobs, Logins, Linked Servers

   • Copy/Sync SQL Agent Jobs to secondaries (use SSMS Object Explorer > Jobs > Script or a tool).

   • Create same Logins (with same SID) on replicas (SSMS > Security > Logins).

2. Backup jobs

   • Create backup jobs honoring Backup Preference (e.g., run on secondary only).

3. Read-only routing (optional via T-SQL; minimal GUI support)

   • If using read intent, set routing lists (requires a few T-SQL statements).

4. Monitoring

   • SSMS AG Dashboard; also set up SQL Agent Alerts on failover/errors.

Quick Checklist (TL;DR)

• WSFC validated + created + witness set.

• “Always On” enabled in SQL Server Configuration Manager on every replica (and services restarted).

• DBs in FULL recovery + full/log backups.

• New Availability Group Wizard in SSMS:

  • AG name, DBs, replicas (Sync/Auto on 2, Async on DR).

  • Listener name/IP/port created.

  • Data sync method chosen (Automatic Seeding is easiest).

• Verify Dashboard green, Synchronized.

• Test Failover in the GUI.

• Finish with jobs/logins/backup prefs/monitoring.