The ‘Always On Availability Groups’ feature is a high-availability and disaster-recovery solution that combines the capabilities of ‘Fail-Over Clustering’, ‘Database Mirroring’, ‘Log shipping’ for group of SQL databases.

Key Components of AAG.

Availability Group: A container for a set of databases, availability databases, that fail over together.

Availability database: A database that belongs to an availability group. For each availability database, the availability group maintains a single read-write copy (the primary database) and one to four read-only copies (secondary databases).

Primary Replica (OR Database): The read-write copy of an availability database, also responsible for sending transaction log records every secondary replica.

Secondary Replica (OR Database): A read-only OR standalone copy of an availability database and serves as a potential fail over targets for the availability group. Optionally, a secondary replica can support read-only access to secondary databases can support creating backups on secondary databases.

Listener: A server name to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica.

Quorum: Collector of votes among nodes within the Availability Group to determine if state is healthy.

Witness (File Share or Disk): needed in cases where there are an even number of nodes in Availability Group for tie-breaker

Availability modes

Synchronous Commit Mode: Prior to transaction commit, secondary must confirm the log has been hardened; Both Manual & Automatic failovers are available with Synchronous; COMMITTED TRANSACTIONS ARE FULLY PROTECTED, INCREASED TRANSACTAIONAL LATENCY

Asynchronous Commit Mode: Primary commits without waiting for secondary verification of a hardened log; Only Manual failovers are available with Asychronous; MINIMAL TRANSACTIONAL LATENCY, POSSIBLE LOSS OF DATA

Setting up AAG.

AlwaysOn Availability Group (AAG) can be configured either using wizard available in SSMS (SQL Server Management Studio) OR SQL queries.

Followings steps are tested on SQL 2016 installed on Windows 2012 R2 Machine.We have 3 SQL servers and SQL Server service running with domain user credential (Domain\sqlsvc).

AAG configuration using wizard (Slideshow )

This slideshow requires JavaScript.

Setting up MSCS Cluster

Open Powershell on one of domain joined machine with Domain Admin credential and Install ‘Fail-over clustering’ windows feature on all SQL server.

$servers = "sql1","sql2","sql3"
foreach ($server in $servers)
{
Install-WindowsFeature -Computername $server –Name File-Services, Failover-Clustering –IncludeManagementTools
Restart-computer $servers -Force
}

Create a new cluster.

New-Cluster -Name MyCluster -Node SQL1, SQL2, SQL3 -StaticAddress 192.168.2.151 –NoStorage

Open PowerShell in one of the SQL Node using Domain Admin credential and enable AlwaysOn for all SQL servers.

$servers = "sql1","sql2","sql3"
foreach ($server in $servers)
{
Enable-SqlAlwaysOn -ServerInstance $server -Force
}

Creating AAG

Following queries need to run in SQLCMD mode (Query >SQLCMD mode)

:Connect SQL1
USE [master]
CREATE DATABASE DB1
CREATE DATABASE DB2
GO

/* Make sure you have share location for backup.*/
BACKUP DATABASE [DB1] TO DISK = N'\\FileServer\SQL2016\Backup\AAG\DB1Backup.bak'
BACKUP DATABASE [DB2] TO DISK = N'\\FileServer\SQL2016\Backup\AAG\DB2Backup.bak'
GO 

/* You may see warning “Cannot grant, deny permissions to sa, dbo, entity owner, information schema,
sys, or yourself.” Ignore if you are running this script using SQL service account. 
*/ 
/*Creating Endpoints on SQL1 */
CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDOMAIN\sqlsvc]
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
GO 

:Connect SQL2
/*Creating Endpoints on SQL2 */
USE [master]
CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDOMAIN\sqlsvc]
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
GO

:Connect SQL1
/*Creating AAG */ 
USE [master]
CREATE AVAILABILITY GROUP [CompanyAG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = OFF, DTC_SUPPORT = NONE) FOR DATABASE [DB1], [DB2] REPLICA ON 
N'SQL1' WITH (ENDPOINT_URL = N'TCP://SQL1.MYDOMAIN.LOCAL:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
N'SQL2' WITH (ENDPOINT_URL = N'TCP://SQL2.MYDOMAIN.LOCAL:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO 

/*Adding Listener */
ALTER AVAILABILITY GROUP [CompanyAG] 
ADD LISTENER N'DBaccess' (WITH IP ((N'192.168.2.160', N'255.255.255.0')), PORT=1433);
GO

:Connect SQL2
/*Adding SQL2 as AAG partners */
ALTER AVAILABILITY GROUP [CompanyAG] JOIN;

Adding a new node (SQL) into Existing AAG.

/* Addding a node to existing AAG. */
:Connect SQL3
USE [master]
CREATE ENDPOINT [Hadr_endpoint] 
       AS TCP (LISTENER_PORT = 5022)
       FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDOMAIN\sqlsvc]
GO

:Connect SQL1
USE [master]
ALTER AVAILABILITY GROUP [CompanyAG]
ADD REPLICA ON N'SQL3' WITH (ENDPOINT_URL = N'TCP://SQL3.MYDOMAIN.LOCAL:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
BACKUP DATABASE [DB1] TO  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\AAGDB1.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
BACKUP DATABASE [DB2] TO  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\AAGDB2.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO

:Connect SQL3
ALTER AVAILABILITY GROUP [CompanyAG] JOIN;
RESTORE DATABASE [DB1] FROM  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\AAGDB1.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DB2] FROM  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\AAGDB2.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO

:Connect SQL3
ALTER DATABASE [DB1] SET HADR AVAILABILITY GROUP = [CompanyAG];
ALTER DATABASE [DB2] SET HADR AVAILABILITY GROUP = [CompanyAG];
GO

Adding a new user database into AAG

/* Create & add new DB into AAG. */ 
:Connect SQL1
CREATE DATABASE DB3
BACKUP DATABASE [DB3] TO DISK = N'\\FILESERVER\SQL2016\Backup\AAG\DB3Backup.bak'

:Connect SQL1
USE [master]
ALTER AVAILABILITY GROUP [CompanyAG]
ADD DATABASE [DB3];
BACKUP DATABASE [DB3] TO  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\DB3.bak' WITH  COPY_ONLY

:Connect SQL2
RESTORE DATABASE [DB3] FROM  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\DB3.bak' WITH  NORECOVERY
ALTER DATABASE [DB3] SET HADR AVAILABILITY GROUP = [CompanyAG];

:Connect SQL3
RESTORE DATABASE [DB3] FROM  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\DB3.bak' WITH  NORECOVERY
ALTER DATABASE [DB3] SET HADR AVAILABILITY GROUP = [CompanyAG];

Manual (planned) Failover

-- Manual failover AAG to SQL3
:Connect SQL3
ALTER AVAILABILITY GROUP [CompanyAG] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Delete AAG configuration.

/* Cleanup of AAG, Endpoints & DB*/ 
:Connect SQL3
USE [master]
DROP AVAILABILITY GROUP [CompanyAG];
DROP DATABASE DB1,DB2,DB3
DROP ENDPOINT Hadr_endpoint
GO 
:Connect SQL1
DROP DATABASE DB1,DB2,DB3
DROP ENDPOINT Hadr_endpoint
GO 
:Connect SQL2
DROP DATABASE DB1,DB2,DB3
DROP ENDPOINT Hadr_endpoint
GO