SQL Server Tip of the Week – AlwaysOn Introduction

Here’s some notes I recently wrote to help explain the basics of AlwaysOn Clustering in SQl – originally written for fairly technical people, i/e/ they know how to install SQL and use SSMS!

 
What is Always On?
SQL’s new (from 2012) failover clustering solution, built in to SQL that allows automatic failover of SQL servers without interruption to the applications using a database.

What is required?
2 fully licensed SQL servers at the same version with replication module installed
Windows Failover Clustering role installed
IP addresses for cluster (1 for cluster, min. 1 per node)
Windows Domain – cluster is a domain object

How to install/configure
Microsoft documentation/Brent Ozar is best source.
e.g. https://www.brentozar.com/archive/2015/06/how-to-set-up-standard-edition-alwayson-availability-groups-in-sql-server-2016/ for 2016
For installing Failover clustering you could add the role via PowerShell e.g.

Install-WindowsFeature FailoverClustering,RSAT-Clustering-Mgmt,RSAT-Clustering-PowerShell


What to know in advance

For an installer there are a number of things you need to know in advance:
• What type of synchronization is to be used?
• Synchronous or Asynchronous – https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups
• Cluster Name – this is the name above all servers in a cluster and therefore where Epicor applications point
• Cluster IP address – ideally decided in advance so can be reserved/static , it’s the IP for the cluster not the server
• Node IP address – each server in the cluster will have a secondary IP used purely for clustering, this is not the IP address used for management of that server
• Secondary Node IP address – only if the servers exist in different subnets they need a node IP address in each range
• Witness Server file location – this is another server available to all SQL servers where a witness file is stored to maintain quorum, example \\FILESERVER\SQLWitness

Things to Note

• Asynchronous synchronization can mean a data loss, review the link above for more info, it is also not an automated failover. – designed for offsite replication over a large distance.
• Synchronous is a more accurate (live replica) sync, but “could” be a source of lag SQL side. i.e. increased overhead
• In 2016 Standard SQL you can only have 1 DB in each availability group, therefore from a config side it’s worth considering which DBs need to be available should the system failover.
• When restoring a database in an availability group (overwriting) you must take it out of the availability group first, and example of doing this with existing restore scripts is (with additions highlighted):

--Backup Source DB Code Goes Here
--Safety Backup of Destination DB Code Goes Here
USE [master]
ALTER AVAILABILITY GROUP MyDatabase_AG REMOVE DATABASE MyDatabase; 
--Restore Script GOes here, i.e. grab back of source and overwrite destination...
ALTER AVAILABILITY GROUP MyDatabase_AG ADD DATABASE MyDatabase
GO