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




Introducing: AskSteve

Monday, June 17, 2013

Collaboration and access to resources is one of the most important enablers when trying to learn or experiment with new technologies, concepts and applications.  In fact, this is…


Advanced Nutanix: “Basics”

Thursday, June 13, 2013

I have a few posts ready on some hacking of ILM and advanced configuration of the Nutanix platform, however in this post I wanted to focus on the…


Advanced Nutanix: Series Introduction

Monday, June 10, 2013

This will be a running series of posts that outline the tips and tricks for administering and optimizing Nutanix I personally use in my environment as well as…


Forcefully Removing the Cisco Nexus VEM on ESXi

Friday, June 7, 2013

I recently ran into an issue when trying to upgrade my ESXi hosts to 5.5 where the VUM upgrade would fail due to some inconsistent vibs for the…

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.