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 )
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
