AlwaysOn Availability Groups (SQL Server)

I am going to deploy a SQL Server 2014 AlwaysOn Availability Group for SQL Server high availability.


Windows Server Failover Cluster (WSFC)
Same SQL Server collation for all replicas

The cluster name is new-cluster1.

To enable AlwaysOn Availability Groups

Go to SQL Server Configuration Manager > SQL Server Properites

Restart the SQL Server service.

AzureS DB is going to be user database for New Availability Group.

Backup the AzureS Database on W2012R2-N1

Restore the AzureS Database with NORECOVERY option on W2012R2-N2 which is replica server

Open Server Management Studio.

In Object Explorer, expand the AlwaysOn High Availability and click New AG Wizard.

Type the group name. 

Select AzureS Database

Add Replica servers.

I will create Listener later.

Select Join Only because I already restored database to replica server.

The wizard finished with error.

‘Joining ‘AzureS’ to availability group ‘AzureS_On’ on ‘W2012R2-N2’

Click the Error in order to check detailed information.

I can get some information from below blogs.

Create Availability Group Fails With Error 35250 ‘Failed to join the database

The connection to the primary replica is not active.

Open Port 1433 and 5022 or disable the Windows Firewall
1433 – SQL Server
5022 – Replication

Check Endpoints and port 5022 (netstat -an)

When I installed SQL Server, I assigned NT service\ account as Log on SQL Server.

If the instances of SQL Server run as the Network Service account, the login of the each host computer account (DomainName\ComputerName$) must be created in master on each of the remote server instances and that login must be granted CONNECT permissions on the endpoint. This is because a server instance running under the Network Service account authenticates using the domain account of the host computer.

Recommendation : Use the domain account for the SQL service account

After grant connection to endpoint, create new AG again.

W2012R2-N1 is Primary and W2012R2-N2 is Secondary.


Now, I add listener.

Connect to the listener using sqlcmd.

Manual Failover is more simple.