• Category: Microsoft

SQL 2014 Availability Group with Windows 2016 Cloud Witness

Page 4 of 5

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.

list

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.
    delegate 00
  3. Select add to add the computer object
    delegate 01
  4. Select a principal
    delegate 02
  5. Adjust the object types to look for computer accounts as well
    delegate 03
  6. Select Computers
    delegate 04
  7. Type in the name of the cluster created earlier. ('$' is used to lookup a computer account)
    delegate 05
  8. Now edit the permissions of the computer account
    delegate 06
  9. Select 'Create Computer Objects' and keep all the other defaults
    delegate 07

Create Availability Group Listener

  1. Within SQL managment studio on the primary replica, run 'Add Listener'
    aglis 01
  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.)
    aglis 03
  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.

dns 01

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'}
    dns 02
  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
    dns 03