Setup AlwaysOn Availability group when SQL Server instances are installed in Cluster Mode - SQL Server DBA Tutorial

In this video you will learn following:

Scenario: 


Setting up AlwaysOn Availability Group between SQL Server instances installed in cluster mode on Two node cluster. In this video you will learn following:
1- Overview of existing Two nodes cluster
2- Failover cluster configuration of existing SQL Server instances installed on both nodes in cluster mode
3- Step by step Creating Availability group
4- Resolutions to the below errors during Availability group setup
5- Limitations of Availability group when SQL Server instance is installed in cluster mode
6- Configuring Cluster to compensate Availability Group setup
7- Limitation of Windows Failover Cluster in 2 nodes setup for Availability group

Step by Step Configuration and Resolution of Availability Group in 2 nodes cluster:


Check out the videos to follow step by step configuration and resolution of below errors during successful Availability group when SQL Server instances are installed in cluster mode

Error 1 (If both SQL Server instances are on ONE node)

SQL Server instance TBSSQLUAT\SQLUAT is installed on the same computer as server instance TBSSQLCLUSTER\SQLPROD, which is already selected for this availability group. Each Windows Server Failover Clustering (WSFC) node can host only one availability replica per availability group. Specify a server instance on a different WSFC node. (Microsoft.SqlServer.Management.HadrTasks)

Important Warning:

Summary for the replica hosted by TBSSQLUAT\SQLUAT

Replica mode: Asynchronous commit
This replica will use asynchronous-commit availability mode and support only forced failover (with possible data loss).

Note: This is a Failover Cluster Instance. Failover Cluster Instances do not support AlwaysOn automatic failover.

Readable secondary: Yes
In the secondary role, this availability replica will allow all connections for read access, including connections running with older clients.

Error 2 : (Using Normal Wizard with Default selection)

Checking for compatibility of the database file location on the secondary replica resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)
ADDITIONAL INFORMATION:
The following folder locations do not exist on the server instance that hosts secondary replica TBSSQLUAT\SQLUAT:
F:\MSSQL11.SQLPROD\MSSQL\DATA; L:\MSSQL11.SQLPROD\MSSQL\Data;
 (Microsoft.SqlServer.Management.HadrTasks)

Error 3 : (After Passing the AG validation)

Create failed for Availability Group 'AG_TEST_SQLClusterMode'.  (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Failed to create, join or add replica to availability group 'AG_TEST_SQLClusterMode', because node 'TBSNODE1' is a possible owner for both replica 'TBSSQLCLUSTER\SQLPROD' and 'TBSSQLUAT\SQLUAT'. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again. (Microsoft SQL Server, Error: 19405)

Error 4:

Joining database on secondary replica resulted in an error.  (Microsoft.SqlServer.Management.HadrTasks)
Failed to join the database 'SalesOrder_New' to the availability group 'AG_TEST_SQLClusterMode' on the availability replica 'TBSSQLUAT\SQLUAT'. (Microsoft.SqlServer.Smo)
Database "SalesOrder_New" is not in a recovering state which is required for a mirror database or secondary database. The remote database must be restored using WITH NORECOVERY. (Microsoft SQL Server, Error: 1464)

Error 5:

Joining database on secondary replica resulted in an error.  (Microsoft.SqlServer.Management.HadrTasks)
Failed to join the database 'SalesOrder_New' to the availability group 'AGTest_SQLinclustermode' on the availability replica 'TBSSQLUAT\SQLUAT'. (Microsoft.SqlServer.Smo)

The mirror database, "SalesOrder_New", has insufficient transaction log data to preserve the log backup chain of the principal database.  This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)



Setup AlwaysOn Availability Group When SQL Server Installed in Cluster Mode Part1



Setup AlwaysOn Availability Group When SQL Server Installed in Cluster Mode Part2