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: http://technet.microsoft.com/en-us/library/hh403415.aspx
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: http://technet.microsoft.com/en-us/library/hh213088.aspx
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: http://technet.microsoft.com/en-us/library/hh213080.aspx
Add the static IP and click ‘Ok’
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’
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