AlwaysON Basics

This Ppt covers the basics of AlwaysON.

1. HADR with SQL Server 2016 AlwaysON Session- 2 AlwaysON Architecture and internals  1

2. Uday Bhanu Pathaniya PFE-SQL Server [email protected]

3. 1. How AlwaysON works 2. What's AlwaysON Availability Groups /Replicas/ Listener 3. AlwaysON supportability and pre-requisites 4. Demo of AlwaysON HA Configuration 5. Backups on AlwaysON secondary replica 3

4. Legacy Architecture: Database Mirroring for local HA, Log Shipping for DR

5. Connect to the Primary Replica Server= tcp : AGListener,1433;Database= MyDB;IntegratedSecurity =SSPI Connect to Read-Only Secondary Replica Server=tcp:AGListener,1433;Database= AdventureWorks;IntegratedSecurity = SSPI;ApplicationIntent = ReadOnly Connect to Multisubnet failover Server=tcp:AGListener,1433;Database= AdventureWorks;IntegratedSecurity =SSPI; MultiSubnetFailover =True

6. Restrictions for SQL Server failover cluster instance(FCI) The cluster nodes of an FCI can host only one replica for a given availability group FCIs do not support automatic failover by availability groups Changing FCI network name Restriction for SQL Server Availability Group Availability replicas must be hosted by different nodes of one WSFC cluster Unique availability group name Only 8 secondary replicas are supported. Either all can be configured as async commit mode or up to 3 can be configure as sync-commit mode Maximum number of availability groups and availability databases per computer depends on Hw configuration. MS has tested 10AGs with 100 DBs per AG. Restriction for SQL Server Availability Databases If file path is different then New Availability Group Wizard/Add Database to Availability Group Wizard the database files must be RESTORED WITH MOVE on each instance of SQL Server that hosts a secondary replica . A later add-file operation on the primary replica might fail on the secondary databases. 

7. Copy-Only full backup of databases , files & filegroup is supported on secondary replica. Differentail backup is not supported on secondary replica. Backup log supports only regular backup & log chain remain consistent irrespective of backup log location or availability mode( Synchronous-commit or Asynchronous-commit) Secondary replica must be in SYNCHRONIZED or   SYNCHRONIZING state. In case of more than two nodes backup preferences(AG property) can be set to ‘Prefer Secondary’ , ‘Secondary Only’ , ‘Primary’ ,’Any Replica’. Additionally Replica backup priorities can be set to ‘Server Instance’, ‘Backup Priory(Lowest=1, Highest=100)’ , Exclude Replica. It is recommended to configure script based backup jobs on each replica. Use the  sys.fn_hadr_backup_is_preferred_replica   function to determine whether the current replica is the preferred backup replica or not. This function return 1 in case if the current replica is a preferred replica else 0.