MSSQL AlwaysOn Install

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.

Prerequisites

Install Windows on all new nodes that will form part of the cluster.
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).
Add Windows Failover Clustering Feature (with a "Witness" if you will be using an even number of voting nodes)
Do a "New SQL Server stand-alone installation" on all nodes (if SQL is not installed already).
Ensure all drive letters match across all nodes. Make sure to create top-level directories like E:\SQLData, F:\SQLLog etc.
Create a file share visible to all nodes that can be used later for transferring backup files etc between instances.
Your Primary Instance needs a database on it. Either restore one (perhaps use AdventureWorks if you are just testing)...
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.
The database must be configured to use the FULL recovery model (and the first backup must have been taken).

Enable AlwaysOn Availability Group

SSCM (SQL Server Configuration Manager)

Repeat these steps on all SQL Servers

Restart-Service -Name MSSQLSERVER -Force

ADUC (Active Directory Users and Computers)

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
Use the Windows Share you identified in the Pre-Requisites section at the start of this page.
We get the listener configuration warning because we chose not to set one up in an earlier step.

Bibliography