MSSQL AlwaysOn Install

Databases

SQL Server

MSSQL Availability

Prerequisites

Network Firewall

Enable AlwaysOn Availability Group

SSCM (SQL Server Configuration Manager)

ADUC (Active Directory Users and Computers)

SQL Server Management Studio

Next Steps

Bibliography

This page applies to SQL Server 2012 specifically but should generally apply to all versions up to and including SQL Server 2016.

For SQL Server 2017 and above the steps may be different since the servers no longer need to be part of a Windows Failover Cluster before starting the Availability Group configuration.

AlwaysOn AG Failover Scenarios

Choose the number of nodes and witnesses and allocate Names and IP Addresses.

MSSQL Editions

Enterprise Edition for full AG features, Standard Edition for Basic AG

Prerequisites

Active Directory

Active Directory Domain with reliable DNS

IP Addressing

Static IPs for all nodes and cluster name object (CNO) and Listener

Hardware

Hardware with same CPU count, RAM, and storage throughput

Windows Lifecycle

Install Windows on all new nodes that will form part of the cluster.

Windows Network Diagnostics

Low‑latency network (<1ms) between synchronous replicas.
Separate subnets for DR if using multi‑site AG.

Windows Storage Management

Dedicated SQL data, log, backup and TempDB volumes, ideally on independent disks.
Ensure all drive letters match across all nodes. Make sure to create top-level directories like E:\SQLData, F:\SQLLog etc

Windows Firewall

Configure the Firewall appropriately. To avoid warnings during SQL Install you can Disable it (but note this may not be appropriate for a production environment).

Windows Availability

Add Windows Failover Clustering Feature (with a "Witness" if you will be using an even number of voting nodes)

(TODO: expand)
Use domain-based, dedicated service accounts for SQLService (engine) and SQLAgentService.
Grant Perform Volume Maintenance Tasks (instant file init)
Ensure Kerberos SPNs are registered

MSSQL Install

Do a "New SQL Server stand-alone installation" on all nodes (if SQL is not installed already).

MSSQL Patch

Ensure same version and latest Cumulative Update on all nodes

Windows File Share

Create a file share visible to all nodes that can be used later for transferring backup files etc between instances.

MSSQL Restore

Your Primary Instance needs a database on it. Either restore one (perhaps use AdventureWorks if you are just testing)...

MSSQL CREATE DATABASE

Or start from scratch. Create a database on your Primary instance and create/populate tables and other objects to be replicated.

Note that there is nothing preventing you from adding AlwaysOn to an existing standalone database instance.

MSSQL Encryption

It is Best Practice to use TDE for all AG databases

MSSQL Recovery Model

The database must be configured to use the FULL recovery model (and the first backup must have been taken).

Network Firewall

The following ports need to be open...

TCP 5022

Used by the database mirroring endpoint / AG endpoint. Handles data replication and synchronization between replicas. Can be changed, but must match across replicas.

TCP 1433

SQL Server engine and AG listener port (unless changed). Open any custom TCP port if you have overridden the default.

UDP 1434

SQL Server Browser (named instances / port resolution) (Only required if you use SQL Server Browser)

TCP 135

RPC endpoint mapper

TCP/UDP 3343

Cluster service / heartbeat

TCP 445

SMB (file share witness, AD, etc.)

UDP 137, 138 / TCP 139

NetBIOS (often still required in some environments)

TCP/UDP 53

DNS

TCP/UDP 88

Kerberos

TCP/UDP 389

LDAP

TCP 5985 / 5986

WinRM (PowerShell remoting)

TCP 49152–65535

(default ephemeral range)Used by RPC/DCOM and cluster operations

Enable AlwaysOn Availability Group

SSCM (SQL Server Configuration Manager)

Enable AlwaysOn Availability Groups on all SQL Server instances to be added to the AlwaysOn Availability Group...

Repeat these steps on all SQL Servers

Restart-Service -Name MSSQLSERVER -Force

ADUC (Active Directory Users and Computers)

Create a new AD Group containing the Service Accounts for all database instances to be added to the AlwaysOn Availability Group...
Add the Service Account for each database instance to the new group...

SQL Server Management Studio

Create a Login for the AD Group (created above) in all database instances to be added to the AlwaysOn Availability Group...

Note that granting sysadmin to this group may not be the most secure way of giving us what we need here. TODO: Update this page with better recommendations.

Setup the Availability Group...

Assumes the Database exists on the Primary but not yet on any Replicas

In this example SQL01 and SQL02 are configured to failover to each other automatically. Ticking the Automatic Failover boxes automatically ticks the Synchronous Commit boxes.
SQL02 has been configured as a readable secondary. Note that this means it needs to be licensed (i.e. it is not 'passive').
In a production environment, it is not recommended to have a readable replica that is also configured for automatic failover and synchronous commit. i.e. in this scenario, heavy query load on the replica could lead to slower commits on the Primary.

The Endpoints tab shows URLs and ports and also allows you to select whether data should be encrypted (this defaults to yes).

The screen on the left shows the default backup configuration.
The SQL2019 Licensing Guide indicates that the following activities are allowed on a write-only replica without the need for additional licenses (where the Primary is licensed and has active Software Assurance)...
* Database consistency checks* Log Back-ups* Full Back-ups* Monitoring resource usage data

The screen on the right assumes we will set up an availability group Listener later.
TODO: Listener ConfigurationSee: https://docs.oracle.com/en-us/iaas/Content/Resources/Assets/whitepapers/deploy-sql-server-availability-groups.pdf