MSSQL AlwaysOn Install

Databases

SQL Server

MSSQL Availability

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

Windows Lifecycle

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

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)

MSSQL Install

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.

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 Recovery Model

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)

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

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

https://www.brentozar.com/archive/2011/07/how-set-up-sql-server-denali-availability-groups/https://www.brentozar.com/archive/2015/02/alwayson-availability-groups-faq/https://www.sqlservercentral.com/search/always+onhttps://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server?view=sql-server-ver15https://techcommunity.microsoft.com/t5/datacat/alwayson-availability-groups-listener-named-instances-port/ba-p/305210https://docs.oracle.com/en-us/iaas/Content/Resources/Assets/whitepapers/deploy-sql-server-availability-groups.pdfhttps://techcommunity.microsoft.com/t5/sql-server-blog/alwayson-availability-groups-listener-named-instances-port/ba-p/305210
AlwaysOn/Azurehttps://learn.microsoft.com/en-us/answers/questions/288802/setting-up-alwayson-availability-group-on-sql-serv
Basic Availability Groupshttps://www.brentozar.com/archive/2015/06/how-to-set-up-standard-edition-alwayson-availability-groups-in-sql-server-2016/

Page updated

Google Sites

Report abuse