Creating AlwaysOn Availability Group - SQL Server 2016 -Step By Step

AlwaysOn Availability Groups is an enterprise-level high-availability and disaster recovery solution introduced in SQL Server 2012 to enable you to maximize availability for one or more user databases. AlwaysOn Availability Groups requires that the SQL Server instances reside on Windows Server Failover Clustering (WSFC) nodes.

Prerequisites

• Ensure that the system is not a domain controller.
• Ensure that each computer is running Windows Server 2012 or later versions.
• Ensure that each computer is a node in a Windows Server Failover Clustering (WSFC) cluster.

My Environment 

OS - Windows 2012 R2

SQL Server - SQL Server 2016 Enterprise Edition (Eval)

Fail-over Cluster Installation

First we need to add the Windows Fail-over Cluster Feature to all the nodes running the SQL Server instances that we will configure as replicas.

Open the Server Manager  => select Add roles and features 

Click Next till Select Features dialog box.

    Select the Failover Clustering checkbox

Click Install to install the Failover Clustering feature.

Windows Failover Clustering Configuration for AlwaysOn Availability Groups

Open Server Manager and select "Fail over Cluster Manager"

Select Fail over Cluster Manager and Select the Option Create Cluster OR 

You can Right Click on the Fail over Cluster manager and select Create Cluster menu

You will Create Cluster Wizard

 Add the Nodes you want be in the Cluster and then click next

You will be getting a validation warning , Select Yes to Validate the Cluster nodes

 Select the option -  Run all tests

NOTE: The Cluster Validation Wizard is expected to return several Warning messages, especially if you will not be using shared storage. other than that if you find any Error messages, you need to fix  prior to creating the Windows Server Failover Cluster.

In the Access Point for Administering the Cluster dialog box, enter the Cluster name and virtual IP address for Windows Server Failover Cluster , then Click next.

Verify that the configuration is successful in Summary.

  Configure Cluster Quorum Settings

Quorum is that it is a configuration database for the cluster and is stored on a shared location, accessible to all of the nodes in a cluster.

In Case of Even number of nodes (but not a multi-site cluster) Node and Disk Majority Qurum configuration is recommended . If you dont have no shared storage Node and File Share Majority is recommended. Here it will be configuring a FileShare Witness quorum


It is recommended that you configure the quorum size to be 500 MB; this size is the minimum required for an efficient NTFS partition. Larger sizes are allowable but are not currently needed.

Click on Your Cluster Select Configure Cluster Quorum Setting from the More action menu

 In the Select Quorum Witness page, select the Configure a file share witness option. Click Next.

Type path of the file share that you want to use in the File Share Path: text box. Click Next.
It is recommended to have the share folder on a different node than the node participating on Cluster

 In the Confirmation page, click Next.

In the Summary page, click Finish.

Enable AlwaysOn Availability Groups Feature on  SQL Server 2016

we can now proceed with enabling the AlwaysOn Availability Groups feature in SQL Server 2016. This is possible after install and configuring the Windows Fail-over Cluster on all the nodes

Open SQL Server Configuration Manager - > SQL Server Properties - [SQL Server (SQLAG01)]

In the Properties dialog box, select the AlwaysOn High Availability tab. Check the Enable AlwaysOn Availability Groups check box. This will prompt you to restart the SQL Server service. Click OK.
 
Restart the SQL Server service.

Configure SQL Server 2016 AlwaysOn Availability Groups

 Go to Management Studio, right click Availability Groups and click New Availability Group Wizard.

Specify Availability Group Name .  my group name SQLAVG2016 then click Next.

Choose Database

Here you can see whether the db meets the prerequisites

• Database should be in full recovery mode. 
• You should make a full backup to add the DB into the Availability Group

Specify Replicas .
This page applies to the New Availability Group Wizard and the Add Replica to Availability Group Wizard of SQL Server 2016

If a server instance that you to use to host a secondary replica is not listed by the Availability Replicas grid, click the Add Replica button.

Add Azure Replica button to create virtual machines with secondary replicas in Windows Azure.

Adding Secondary Replica

Endpoints

Use this tab to verify any existing database mirroring endpoints and also, if this endpoint is lacking on a server instance whose service accounts use Windows Authentication, to create the endpoint automatically.

Backup Preference

Use this tab to specify your backup preference for the availability group as a whole and your backup priorities for the individual availability replicas.

Listener

An availability group listener is a virtual network name (VNN) to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. You point applications to the listener (which is registered with DNS) and directs traffic in the AG

Select Data Synchronization

Use the Always On Select Initial Data Synchronization page to indicate your preference for initial data synchronization of new secondary databases. This page is shared by three wizards—the New Availability Group Wizard, the Add Replica to Availability Group Wizard, and the Add Database to Availability Group Wizard.

The possible choices include Full, Join only, or Skip initial data synchronization. Before you select Full or Join only ensure that your environment meets the prerequisites.

In My I have choose Full 

For each primary database, the Full option performs several operations in one workflow: create a full and log backup of the primary database, create the corresponding secondary databases by restoring these backups on every server instance that is hosting a secondary replica, and join each secondary database to availability group.

Select this option only if your environment meets the following prerequisites for using full initial data synchronization, and you want the wizard to automatically start data synchronization

 

 

AlWaysOn Availability Group - Create New Availability Group Error - Checking for compatibility of the database file location on the Server Instance that hosts secondary replica (Microsoft.SqlServer.Management.HadrModel)

This is one of common error message While Creating a New Availability Group   Here is full error I received when I attempted to Create a New Availability Group.

Here is my environment

OS - Windows Server 2012 R2
SQL Server - SQL 2016 Enterprise Edition ( The same solution will solve for SQL 2014/2012 too)
Primary Server - SQLAG01
Secondary Server - SQLAG02


We used ‘Full’ option in Select Initial Data Synchronization Page.  The error says Primary replica is trying to search for data folder location in secondary and as a result they don’t match, and failed.

If you have choosen Full option  You have to create same file location on both the instances
e,g: Create Same Data path on both instances.

Data Path on SQLAG01 -  "C:\Program Files\Microsoft SQL  Server\MSSQL13.SQLAG01\MSSQL\DATA\"

Data Path on SQLAG02- "C:\Program Files\Microsoft SQL  Server\MSSQL13.SQLAG01\MSSQL\DATA\"

other wise choose other options for Initial Data synchronization ' Join Only' and manually restore the database on secondary

Add Databases to AlwaysOn Availability Group Error - The operating system returned the error '5(Access is denied.)

This is one of common error message While Creating a New Availability Group or Adding a Database to an availability group. Here is full error I received when I attempted to Add a database to an Availability Group

--------------------------------------------------------------------------------------------------------------------------------------------------------

Restore Database AdventureWorks fail (Microsoft.SqlServer.Management.HadrModel)

ADDITIONAL INFORMATION:

Restore failed for Server 'CGSQL02\SQLAG02'.  (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' 

while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLAG01\MSSQL\DATA\AdventureWorks.mdf'. (Microsoft.SqlServer.Smo)

--------------------------------------------------------------------------------------------------------------------------------------------------------

Solution

Here is my environment

OS - Windows Server 2012 R2
SQL Server - SQL 2016 Enterprise Edition ( The same solution will solve for SQL 2014/2012 too)
Primary Server - SQLAG01 

Secondary Server - SQLAG02

SQL Installation was done on Default Paths  

Data Path on SQLAG01 -  "C:\Program Files\Microsoft SQL  Server\MSSQL13.SQLAG01\MSSQL\DATA\"

Data Path on SQLAG02- "C:\Program Files\Microsoft SQL  Server\MSSQL13.SQLAG02\MSSQL\DATA\"

SQL Service is running under  domain acccount ( condoso/sql_service )

it is a problem with the Permission of the specified folder to the user account that SQL Server service is running. The Path Which you have created on Secondary instance to perform synchronization of secondary server databases using backup/restore. in my scenario ( "C:\Program Files\Microsoft SQL  Server\MSSQL13.SQLAG01\"  Which is the Path Created for Back/restore on Secondary node ) , This path should have Full Permission on SQL Service Account. condoso\sql_service

AlWaysOn Availability Group Enhancements in SQL Server 2016

• AlWaysOn Feature is there available since SQL Server 2012 and There are some enhancements in SQL Server 2014 is the increased maximum number of secondaries. SQL Server 2012 supported a maximum of four secondary replicas. AlwaysOn Availability Groups on 2014 supports up to eight secondary replicas. Also use SQL Server 2014 AlwaysOn Availability Groups to provide high availability for SQL Server databases hosted in Windows Azure etc .

• Database Level Fail over Trigger SQL Server 2016 is coming with solving pains on Failover , Currently Failover is happening based on instance health, on 2016 it can fail-over based on Database health as well. If any DB in an availability group fails the whole Group of Database will fail over to other. 

• More Auto fail-over Targets : Another Enhancement is increase in number of Automatic Failover partners , Which means you can have two more fail-over partners apart from Primary. In this 3 node Scenario , one node fails still we can have high availability with other two nodes.

• Domain independent Availability Group You can create an availability group in Work Group Computers .Earlier All the nodes which is participating in a availability group must be in same domain

• Load balancing on Availability group secondaries , Earlier Only first replica in secondaries get all the read only traffic even if you have more than one replicas. On 2016 you can Distribute the Workload for read only transaction across multiple secondaries 

• Distributed transactions are supported with AlwaysOn Availability Groups. 

This applies to distributed transactions between databases hosted by two different SQL Server instances. It also applies to distributed transactions between SQL Server and another DTC-compliant server.

The following requirements must be met:

Availability Groups must be running on Windows Server 2016 or Windows Server 2012 R2. For Windows Server 2012 R2, you must install the update in KB3090973 available at https://support.microsoft.com/en-us/kb/3090973.

Availability Groups must be created with the CREATE AVAILABILITY GROUP command and the WITH DTC_SUPPORT = PER_DB clause. You cannot currently alter an existing Availability Group.

• Basic Availability Group Feature will be supported by Standard Edition of SQL Server ( 2 Replicas Only and One Database per Group) , Basic Feature wont have readable secondary , no Backup on secondary replica etc , for those you have to choose advanced Availability group feature on Enterprise Edition. Whole idea behind basic Availability group is to retire Mirroring in Future
• Mirroring wont be deprecating on 2016 May be in future versions

The database transaction log file increasing for the DBs Participating In AlwaysOn Availability Groups

Issue

After Migrating High Available SQL Server 2012 Cluster Instance to SQL Server 2014  AlWays on Availability Group  transaction Log file keep growing and within couple of weeks almost the hard disk is full. Earlier My Db was in Simple recovery mode , I changed it to Full recovery mode to add it on Availability Group because of that the transaction log keeps increasing

Solution

It is  a very common problem the setting database recovery mode to FULL and then forgetting to backup the transaction log (LDF file). Let me explain how to fix it.

If you ready loose  a some data between backups, just set the database recovery mode to SIMPLE, then forget about LDF - it will be small. the solution for most of the cases. But for SIMPLE Recovery mode will not supporting by Availability group. In this case you have to take Transaction Log backups.
Recommended to have  a full backup per day. If you do a full backup per day, The frequency of the log backup will determine how much data you are allowed to lose in case of a failure. If you run your log backup every 15 minutes, expect to loose up to the last 15 minutes of data that changed. 15 minutes was good frequency for my environment.


AlwaysOn Availability Groups allows the offloading backups to a secondary replica. If you setup a log backup on secondary replica it will be truncating both primary and secondary logs. no need to have it on both nodes.

Where should backups occur? select the automated backup preference for the availability group, one of:

Prefer Secondary 

Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default option.

Secondary only

Specifies that backups should never be performed on the primary replica. If the primary replica is the only replica online, the backup should not occur.

Primary

Specifies that the backups should always occur on the primary replica. This option is useful if you need backup features, such as creating differential backups, that are not supported when backup is run on a secondary replica.

Any Replica

Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups. Note backup jobs might evaluate other factors such as backup priority of each availability replica in combination with its operational state and connected state.

To take the automated backup preference into account for a given availability group, on each server instance that hosts an availability replica whose backup priority is greater than zero (>0), you need to script backup jobs for the databases in the availability group. To determine whether the current replica is the preferred backup replica, use the sys.fn_hadr_backup_is_preferred_replica function in your backup script. If the availability replica that is hosted by the current server instance is the preferred replica for backups, this function returns 1. If not, the function returns 0. By running a simple script on each availability replica that queries this function, you can determine which replica should run a given backup job.

If you use the Maintenance Plan Wizard to create a given backup job, the job will automatically include the scripting logic that calls and checks the sys.fn_hadr_backup_is_preferred_replica function. However, the backup job will not return the “This is not the preferred replica…” message. Be sure to create the job(s) for each availability database on every server instance that hosts an availability replica for the availability group.
:

Comparison of AlWays On Failover Cluster Instances (AOFCI) and AlWays on Availability Groups(AOAG)

Comparison of Failover Cluster Instances and Availability Groups

Both are called AlWaysOn Features only ( FCI & AG),AlwaysOn is not a single, specific feature. Rather, it is a set of availability features whose most salient components are the Failover Cluster Instances feature and the Availability Groups feature. 

 Regardless of the number of nodes in the FCI, an entire FCI hosts a single replica within an availability group. The following table describes the distinctions in concepts between nodes in an FCI and replicas within an availability group.

	Nodes within an FCI	Replicas within an availability group
Uses WSFC cluster	Yes		Yes
Protection level	Instance		Database
Storage type	Shared		Non-shared1
Storage solutions	Direct attached, SAN, mount points, SMB		Depends on node type
Readable secondaries	No2		Yes
Applicable failover policy settings	WSFC quorum FCI-specific Availability group settings3		WSFC quorum Availability group settings
Failed-over resources	Server, instance, and database		Database only

1While the replicas in an availability group do not share storage, a replica that is hosted by an FCI uses a shared storage solution as required by that FCI. The storage solution is shared only by nodes within the FCI and not between replicas of the availability group.

2Whereas synchronous secondary replicas in an availability group are always running on their respective SQL Server instances, secondary nodes in an FCI actually have not started their respective SQL Server instances and are therefore not readable. In an FCI, a secondary node starts its SQL Server instance only when the resource group ownership is transferred to it during an FCI failover. However, on the active FCI node, when an FCI-hosted database belongs to an availability group, if the local availability replica is running as a readable secondary replica, the database is readable.

3Failover policy settings for the availability group apply to all replicas, whether it is hosted in a standalone instance or an FCI instance.

https://msdn.microsoft.com/en-us/library/ff929171.aspx?f=255&MSPPError=-2147217396