Configure SQL Server AlwaysOn Availability Group


In the following post I’ll go over how to configure a Microsoft SQL Server AlwaysOn Availability Group.  NOTE: Before configuring make sure the Failover Cluster is correctly installed and the SQL servers are identically configured.  Also, make sure the computer accounts exist on each SQL server and have been granted the connect access on the endpoint (I show images of this below).  To learn more about SQL Server on Nutanix Check out the Microsoft SQL Server on Nutanix Best Practices!

This will be a three part series covering the following:

  • Failover Cluster Configuration
  • SQL Server AlwaysOn Availability Group Configuration – You’re here!
  • Adding Databases to a AlwaysOn Availability Group

Let’s get started!

Configure DRS Anti-affinity rules to ensure VM aren’t on the same host


For each SQL Server goto the SQL Configuration Manager, and Right click on the SQL Server instance running and select ‘Properties’


Navigate to the ‘AlwaysOn High Availability’ tab and check ‘Enable AlwaysOn Availability Groups’. Click ‘Ok’ You will need to restart the SQL Server service and perform this on all SQL servers in the AlwaysOn Availability Group


Open SQL Server Management Studio, expand ‘AlwaysOn High Availability’ and select ‘New Availability Group Wizard…’  Here is a good reference:


Type in the name for the AlwaysOn Availability Group and click ‘Next’


Select the Databases which you’d like to protect and click ‘Next’. NOTE: The recovery model must be full a full backup must have been performed previously.


Click on ‘Add Replica…


Enter the connection details for the other SQL Server and click ‘Connect’


The secondary SQL server will now appear.  Here is a good reference:


Check the ‘Automatic Failover…’ and ‘Synchronous Commit’ check boxes


On the Endponts tab verify the SQL endpoints are correct


On the Backup Preferences tab make sure ‘Prefer Secondary’ is selected


On the Listener tab select ‘Create an availability group listener’ and enter the DNS name, ports (1433 used here) and select ‘Static IP’ for Network Mode  Here is a good refence:


Add the static IP and click ‘Ok’


Click ‘Next’


Make sure the initial data synchronization is set to ‘Full’ and enter a share where the data can be staged. In this example I used a Microsoft DFS share. Click ‘Next’


Ensure the validation tests are successful and click ‘Next’


Click ‘Finish’


In my case I had an error joining the availability group on my secondary server


After looking at the logs it was clear that there were logon failures occurring on both the primary and secondary servers.  The logon error will look similar to “Database Mirroring login attempt by user ‘<DOMAIN>\<USER>.’ failed with error: ‘Connection handshake failed. The login ‘<DOMAIN>\<USER>’ does not have CONNECT permission on the endpoint. State 84.”


After validating that the accounts trying to be used by the availability group existed servers I ran the following SQL command to grant the account connect access to the endpoint. NOTE: This will need to be performed on both SQL servers for the connecting servers account. The SQL syntax is similar to the following:  GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [SPLAB\svc_sqlserver];  An example is shown below


After resolving the logs showed the connection for the availability group was successful


The Availability Group Dashboard now shows the replica and database status



  • Rui Mao

    I got same CONNECT permission error. The login was [TESTSP2$] in my testbed. I tried your grant command, but SQL Server told me the login doesn’t exist. Actually the TESTSP2 was the Windows Server name, not an real account. What shall I do? Did your SQL Server has an account same as computer name?

Legal Mumbo Jumbo

Copyright © Steven Poitras, The Nutanix Bible and, 2014. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Steven Poitras and with appropriate and specific direction to the original content.