MSSQL 2014 AlwaysOn Availability Group Cluster & Gratuitous ARP (GARP) Issue

MSSQL 2014 AlwaysOn cluster running on Windows 2012 R2 doesn’t send Gratuitous ARP (GARP) packets by default!

I have recently come across gratuitous arp (GARP) issues while working on Microsoft SQL 2014 AlwaysOn Availability Group cluster setup. I experienced the following –

  1. MSSQL 2014 AlwaysOn cluster with AlwaysOn Availability Group (AG) setup was done as per best practices and experts recommendations; all cluster related services were running OK without any issue.
  2. clients sitting on the same IP network/same VLAN were able to connect to the AlwaysOn AG listener Virtual IP (VIP) address immediately after a cluster failover happen from Node-A to Node-B and vice versa.
  3. however, clients sitting on different IP subnets were NOT able to connect to the VIP immediately after a cluster failover.
  4. clients sitting on different IP subnets waited for 20MIN to get connect to the VIP.
  5. this 20minutes is MAC address lifetime on the ethernet switch (I use Juniper EX-series switches) where the servers are connected (connected to physical Hypervisor).
  6. on the network layer the switch “ARP table” was showing previously learnt MAC address for the AG Listener VIP; the switch didn’t updated MAC address after a cluster failover triggered. The switch flushed out the old MAC and re-learnt the new correct MAC address after the MAC age time (20min) expired on the switch.

I was looking for a solution and found “GARP Reply” needs to be enabled on the Juniper EX switch manually – I have done that but still NO improvement!

Also looked at Microsoft KB documents and forums – people are saying GARP needs to be turned on the network switch which I have DONE already without any success.

After doing further digging inside I found that the Windows 2012 R2 servers were not sending any GARP packets so the switch was not updating the ARP table although it is configured to work with GARP.

To get this working – Windows server registry object “ArpRetryCount” needs to be added; Microsoft said the following about this –

“Determines how many times TCP sends an Address Request Packet for its own address when the service is installed. This is known as a gratuitous Address Request Packet. TCP sends a gratuitous Address Request Packet to determine whether the IP address to which it is assigned is already in use on the network.”

Add the registry entry as following –

-HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
-REG_DWORD > ArpRetryCount
-Value is between 0-3 (use value 3)

0 – dont send garp
1 – send garp once only
2 – send garp twice
3 – send garp three times (Default Value – actually not present on Windows 2012 R2)

To enable “GARP reply” on Juniper EX & SRX platform – user the following command –

#set interface interface_name/number gratuitous-arp-reply

The interface can be a physical interface, logical interface, interface group, SVI or IRB.

To enable GARP on Cisco IOS – use interface command “ip gratuitous-arps“.

References:
https://technet.microsoft.com/en-us/library/cc957526.aspx
http://www.juniper.net/techpubs/en_US/junos13.2/topics/usage-guidelines/interfaces-configuring-gratuitous-arp.html
http://www.cisco.com/web/techdoc/dc/reference/cli/nxos/commands/l3/ip_arp_gratuitous.html

Ruby program as Windows Service – the windows way

Recently I come across Ruby for the first time while installing few cloud-based network monitoring applications on Windows Servers; these are Ruby applications called Ruby “gem”.

After finished installation and configuration – I found I am able to run the Ruby gem without any problem on the command line (very easy – open CMD > go to Ruby bin directory > execute >application_name run). But if I close CMD – the application immediately stop working. I need this application running as Windows Service.

There are couple of ways to make a Ruby windows service –
i. the Ruby way – there are couple of Ruby utilities & gems already available; one of them is called “win32-services”.
ii. the Windows way – by using the OLD “sc.exe” & “SrvAny.exe”; this works OK on Windows 2008 & Windows 2012. This is the easiest one!

I am no Ruby expert – I will describe here how to create Ruby windows service using SC & SrvAny.

Configuration details are following –

i. Get the “SrvAny.exe” and place it in a directory; this can be even inside Ruby directory “C:\Ruby21\mywinservice\srvany.exe”.

ii. Open CMD with admin priv; execute the following sc command to create a windows service –
>sc create MyRubyService binPath= “C:\Ruby21\mywinservice\srvany.exe” DisplayName= “My Ruby Application”

This will create the windows service “MyRubyService” and registry key with the same name. The registry key & entries should look like following–

RubySrvAny

iii. Open regedit; go to “HKLM\SYSTEM\CurrentControlSet\Services\MyRubyService”. Create a new key name “Parameters”. Enter the following entries (String value) under “HKLM\SYSTEM\CurrentControlSet\Services\MyRubyService\Parameters” –

AppDirectory     -this is the Ruby bin directory
Application         -this is the “ruby.exe” file location
AppParameters -this is the ruby gem application “run” command

RubyAppSrv

You might need to stop or disable “Interactive Services Detection” on Windows 2008; by default this is not enabled on Windows 2012.

Windows NTP Server – Windows NTP Cookbook

When we talk about standard NTP server that could provide time sync to cross-platform infrastructure objects such as Windows, Linux, Unix, Cisco, HP, F5, VMware etc – a lot of us prefer Linux based NTP server. However, a Windows based NTP server is also equally capable of providing cross-platform time sync just like a Linux NTP and very easy to configure. I assume when people think of cross-platform – they think Windows NTP is only good for Microsoft environment which actually not true.

I often do install and configure centralized NTP sync for customers. Previously I was always stick to Linux based NTP – however, few Windows shop customers pushed me to find a suitable solution for the same on Windows server, hence I come across this; I found this is working perfectly and very stable. There is no 3rd party software required to get this done on Windows 2003/2008/2012 Servers.

Here is below step-by-step configuration – most of these configurations are based on registry settings – to edit registry use “regedit” utility.

In this doco I have discussed the following –

1. How to configure a stand-alone NTP server on a Windows 2008/2012 Server
2. How to configure NTP service on Window AD DS environment
3. How to configure NTP sync to a non-domain joined Windows computer acting as  a NTP client.

1. Configure a stand-alone NTP server on a Windows 2008/2012 Server

Following configuration will enable “NTP server” service on a stand-alone Windows 2008/20012 Server (this is equivalent as a stand-alone Linux based NTP server) –

i. Enable “NTP server” service on the machine-

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\TimeProviders\NtpServer

Set the value data to: 1Default value is 0 – which means NTP server service is not enabled.

ii. Change the server type to NTP on this machine-

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Parameters\Type

Set the value data to: NTPThis is “standard” NTP server that can provide time sync to cross-platform. Other value for this is “NT5DS” – which depends on active directory.

iii. Set the announce flag-

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Config\AnnounceFlags

Set the data value to: 5; value 5 means – sync time to an external time source. Default value is 10 – this tells the server to sync time to local CMOS clock.

iv. Specify external time sync peers-

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Parameters\NtpServer

Set the value to: 0.pool.ntp.org,0x01 1.pool.ntp.org,0x01 2.pool.ntp.org,0x02 (these are free public NTP servers on the Internet) or your preferred external NTP servers. Make sure you maintain a white space between servers.

The “0x01” flag indicate sync time with external server in special interval configured in “SpecialPollInterval” registry value.

Value “0x08” means – use client mode association while sync time to external time source.

Value “0x09” means – use special interval + client mode association to external time source. This is a good value when your machine sync time to an external time source.

Value “0x02” means – use this as UseAsFallbackOnly time source – if primary is not available then sync to this server.

Value “0xa” means – UseAsFallbackOnly + client mode association.

v. Set time sync pool interval (special interval) –

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\TimeProviders\NtpClient\SpecialPollInterval

Set the value to: 900; Microsoft TechNet & lots of other reference documents recommend a value of 900 seconds (every 15 min).

vi. Set the time correction settings-

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Config\MaxPosPhaseCorrection

Set the value to: 3600; Microsoft recommends a value of 3600 or 1800 seconds. Default value is 54000.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Config\MaxNegPhaseCorrection

Set the value to: 3600Microsoft recommends a value of 3600 or 1800 seconds.

Original description for this time correction is > “Specifies the largest positive time correction, in seconds, that the Windows Time service is allowed to make. If the service determines that a change larger than this is required, then the service logs an event instead”.

vii. Make sure to restart windows time server called “w32time”-

>net stop w32time

>net start w32time

viii. Make sure to start Windows Time service to start automatically with operating system reboot.

Services.msc > Windows Time > Startup Type > Automatic.

At this stage all the required registry settings are DONE – this server is ready to serve as a standard NTP server that can provide time sync to cross-platform.

Let’s verify NTP server configurations are entered correctly and the NTP server is syncing time to external source correctly –

>w32tm /query /status /verbose; this will display last sync status or any error

w32tm-query

>w32tm /query /peers; this will display NTP external peers

>w32tm /query /source; this will display current NTP time source

>w32tm /query /configuration; this will display current configurations

>w32tm /resync; this will force immediate time resync

2. Configure NTP Service on Window AD DS Environment

Windows Active Directory time sync works a bit differently – not all the domain controllers are responsible to sync time to external time sources. Only the domain controller have the PDC emulator role sync time to external time sources. NTP server service is enabled by default on PDC emulator.

Active Directory Domain Service time sync flow is following –

External time sources>> Domain Controller with PDC emulator>> all other Domain Controllers>> all domain members.

The external time sources can be the standalone NTP server just been described in the above section 1 – or this can be NTP servers sitting on the Internet. Make sure you have outbound internet connection allowed for the DC with PDC role – also windows firewall not blocking NTP in/out on this DC.

To find out which domain controller is PDC emulator – execute “Netdom Query FSMO” command –

netdom-pdc

Following settings will make the PDC emulator DC to sync time to external time source –

>w32tm /config /update /syncfromflags:manual /manualpeerlist:myntp01.test.local,0x09

Or edit the registry value “NtpServer” and enter NTP servers DNS address.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Parameters\NtpServer

The above command tells the server to sync time to myntp01.test.local.

“0x09” flag tells the server to use a client-mode association with special interval.

>w32tm /config /reliable:yes /update

This command tells the PDC emulator server to mark itself as reliable time source to domain member computers.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\TimeProviders\NtpClient\SpecialPollInterval

Set the value to: 900; Microsoft & lot of other documents recommend a value of 900 seconds (every 15 min).

Set the time correction settings –

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Config\MaxPosPhaseCorrection

Set the value to: 3600; Microsoft recommends a value of 3600 or 1800 seconds. Default value is 54000.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Config\MaxNegPhaseCorrection

Set the value to: 3600; Microsoft recommends a value of 3600 or 1800 seconds.

Finally restart windows time service.

>net stop w32time

>net start w32time

Configure all other domain controllers to use time sync from Active Directory Hierarchy automatically. The following commands need to be executed on every non-PDC emulator domain controllers –

>w32tm /config /syncfromflags:domhier /update

At this stage ALL AD DS domain controllers are ready to serve time sync to all domain members.

Verify configuration by using “w32tm /query /status /verbose” and also check “system logs” in the “event viewer” for any w32time warning or error.

No configuration need to be done on domain members – if you execute “w32tm /query /source” on a domain member – this should return FQDN of a domain controller.

3. Configure NTP Sync to a Non-Domain Joined Windows Computer as  NTP Client

Sometimes you might find non domain join Windows computers that need be to configure time sync to NTP server(s). The NTP server can be the one configured at section 1 or can be NTP servers sitting on Internet – make sure windows firewall configuration allow NTP sync.

Change the following registry value to configure time sync  to an external server –

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Config\AnnounceFlags

Set the data value to: 5; Default value is 10.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\TimeProviders\NtpClient\SpecialPollInterval

Set the value to: 900; Microsoft recommends a value of 900 seconds (every 15 min).

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Parameters\NtpServer

Enter myntp01.test.local,0x09 or your preferred external NTP server address; this can be IP address instead of DNS name as well.

You can also enter “time correction” registry values described in the above sections.

Now restart windows time service –

>net stop w32time

>net start w32time

Check Windows event logs and “w32tm /query” commands to make sure time sync is working fine.

4. Reset NTP Registry Settings to the Default

There might be some situation when you need to reset NTP related registry settings to Windows default values; following are the commands –

>net stop w32time

>w32tm /unregister

>w32tm /register

>net start w32time