SQL 2014 Availability Group with Windows 2016 Cloud Witness

A listener is virtual object that allows connections to a database running on a node\replica of an availability group.  The listener will dynamically point to the primary replica and is used in ODBC connections and connection strings of applications. A virtual IP will be required for each subnet used in the group   In the example below 'ag16listen' is the configured listener with virtual IPs for each subnet.

Delegate Cluster account in AD

In order for the Availability group to create a listener computer object  the cluster computer account needs rights to create computer objects.

1. Locate the cluster computer object within AD.  For this example 'always16' is the computer account located in the LAB OU.
2. Right click on the OU and select security and then advanced.  If this option is unavailable make sure 'advanced features' is enabled under the view menu.
   
3. Select add to add the computer object
   
4. Select a principal
   
5. Adjust the object types to look for computer accounts as well
   
6. Select Computers
   
7. Type in the name of the cluster created earlier. ('$' is used to lookup a computer account)
   
8. Now edit the permissions of the computer account
   
9. Select 'Create Computer Objects' and keep all the other defaults
   

Create Availability Group Listener

1. Within SQL managment studio on the primary replica, run 'Add Listener'
   
2. Enter the name you would like the computer object and DNS name of the listener to be (this will be created in AD)
3. Add a unique IP address for each subnet and port.(Recommend sticking with the SQL default of 1433.)
   
4. Hit OK and verify the process runs correctly.  Most of the issues encountered are around the AD delegation of the cluster computer account.

DNS Considerations

By default all subnet IPs will have a DNS entry created which is no problem for newer applications since modern ODBC drivers will be able to figure out what IP to connect with dynamically.  However, this might cause issues for applications that utilize older connection methods.  For example Citrix XenDesktop can utilize the more modern connection driver but until recently Provisioning Services (<7.11) would have issues when resolving to the secondary replica in the other subnet.

This is the current NSLOOKUP which shows each AG listener IP appearing and will cause a round robin lookup.

To resolve the round robin result we can run PowerShell commands to register only the 'active' subnet of the primary. And if a failover does occur it will register the new IP to DNS in the secondary subnet

1. First get the 'network name' needed

   Get-ClusterResource | where {$_.resourcetype -eq 'Network Name'}
   

2. Next run the following commands reflecting of the name discovered in the above command

   Get-ClusterResource "always16-ag_ag16listen"|Set-ClusterParameter -Name HostRecordTTL -Value 5

   Get-ClusterResource "always16-ag_ag16listen"|Set-ClusterParameter -Name RegisterAllProvidersIP -Value 0

   Stop-ClusterResource "always16-ag_ag16listen"

   Start-ClusterResource "always16-ag_ag16listen"

   Get-ClusterResource "always16-ag_ag16listen" |Update-ClusterNetworkNameResource

3. Verify NSLOOKUP and only 1 IP should appear