Configure SQL Server AlwaysOn Availability Group

advNutanix_640

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

021314_2201_ConfigureSQ1.png

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

021314_2201_ConfigureSQ2.png

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

021314_2201_ConfigureSQ3.png

Open SQL Server Management Studio, expand ‘AlwaysOn High Availability’ and select ‘New Availability Group Wizard…’  Here is a good reference: http://technet.microsoft.com/en-us/library/hh403415.aspx

021314_2201_ConfigureSQ4.png

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

021314_2201_ConfigureSQ5.png

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.

021314_2201_ConfigureSQ6.png

Click on ‘Add Replica…

021314_2201_ConfigureSQ7.png

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

021314_2201_ConfigureSQ8.png

The secondary SQL server will now appear.  Here is a good reference: http://technet.microsoft.com/en-us/library/hh213088.aspx

021314_2201_ConfigureSQ9.png

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

021314_2201_ConfigureSQ10.png

On the Endponts tab verify the SQL endpoints are correct

021314_2201_ConfigureSQ11.png

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

021314_2201_ConfigureSQ12.png

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: http://technet.microsoft.com/en-us/library/hh213080.aspx

021314_2201_ConfigureSQ13.png

Add the static IP and click ‘Ok’

021314_2201_ConfigureSQ14.png

Click ‘Next’

021314_2201_ConfigureSQ15.png

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’

021314_2201_ConfigureSQ16.png

Ensure the validation tests are successful and click ‘Next’

021314_2201_ConfigureSQ17.png

Click ‘Finish’

021314_2201_ConfigureSQ18.png

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

021314_2201_ConfigureSQ19.png

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.”

021314_2201_ConfigureSQ20.png

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

021314_2201_ConfigureSQ21.png

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

021314_2201_ConfigureSQ22.png

The Availability Group Dashboard now shows the replica and database status

021314_2201_ConfigureSQ23.png

Whala!

asksteve_640

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…

advNutanix_640

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…

advNutanix_640

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…

vem_640

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 StevenPoitras.com, 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 StevenPoitras.com with appropriate and specific direction to the original content.