Dynamics AX 2012 / R2 Retail: Switch from online to offline database is slow

Description:

In any environment you might have Network drops or have to do maintainance in your system. If you have configured the Retail Offline Database, you will be able to switch to this database if the connection is lost to the Store Database.

This process happens automatically, but we had Customer reporting that the time it takes to switch is too long. We have therefore created a hotfix to resolve the problem. The hotfix decreases the time, but if you want to get Down to a very low timeout, you need to adjust global TCP parameters.

 

Here is what you need to do. 

 

Connection Timeout parameter has been implemented in

KB 2836543 6.0.1108.4445 (Dynamics AX 2012 RTM)

and 6.2.1000.1869 (Dynamics AX 2012 R2).

 

Additionally you need to apply a Windows hotfix to get the InitailRtO parameter added.

Install the Windows hotfix from this KB article: http://support.microsoft.com/kb/2472264

Set the Initial RTO
value,
as described in the above KB. Note this warning in the article: 

Warning
: Setting a low value of Initial RTO could result in
failure to connect. 

During our testing, we tested with values as low as 300 msec (netsh interface tcp set global initialRto=300) and didn’t see any
problems. They may need to adjust this value a few times to find the setting that is right in your environment. 

  POS.EXE.CONFIG Example

<?xml version=”1.0″ encoding=”utf-8″ ?> <configuration>   <configSections>     <section name=”AxRetailPOS” type=”LSRetailPosis.Settings.ConfigFile.AppConfiguration, SystemSettings, Version=6.2.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35″ />   </configSections>   <AxRetailPOS OfflineDatabaseConnectionString=”Data Source=.\sqlexpress;Initial Catalog=AXPOSOFFLINE;Integrated Security=True;Persist Security Info=False;Pooling=True;Encrypt=True;TrustServerCertificate=True;Connection Timeout=1“     StoreDatabaseConnectionString=”Data Source=10.164.26.42\SQLEXPRESS;Initial Catalog=AXPOS;Integrated Security=True;Persist Security Info=False;Pooling=True;Encrypt=True;TrustServerCertificate=True;Connection Timeout=1“

 

  

The setting reduced the time the POS discovers that the Network connection to the Store database from 40-50 seconds to around 20 -22 seconds. Some retailers have higher demands and with the following TCP parameters, we can get Down to 3 seconds.

 (Parameter [connection timeout] in connection string of POS only works when SQL is gone, but network connection is still up.)

 

TCP Documentation: 

Have a look at TCPRetransmission Behavior article on the MSDN site:

A number of registry values under theHKEY_LOCAL_MACHINE\System\CurrentControlSetServices\Tcpip\Parameters registry
key adjust the behavior of TCP retransmission:

The per-adapter value TcpInitialRtt controls the retransmission timer. This timer identifies the number of milliseconds to wait for an acknowledgment
after the first transmission of a segment before retransmitting it.

The TcpMaxConnectRetransmissions value indicates the maximum number of retransmissions that can be sent for a new connection request.

The TcpMaxDataRetransmissions value indicates the maximum number of retransmissions that can be sent on an existing connection. The default is 5.

The retransmission time-out is adjusted on the fly to match the characteristics of the connection, using Smoothed Round
Trip Time (SRTT) calculations… The retransmission time-out for a given segment is doubled after each retransmission of that segment.

According to this document the default value of TcpInitialRtt is 3 andTcpMaxConnectRetransmissions is 2.

So the default connection timeout is calculated like this: 3 + 3*2 + 3*2*2 = 21 seconds,

after you set TcpMaxConnectRetransmissions to 1 it becames: 3 + 3*2 = 9 seconds.

 http://superuser.com/questions/339959/how-to-set-tcp-ip-abort-interval-or-timeout-in-windows-xp

  

 Modify following reg entries:

• HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters\Interfaces\interfaceGUID
  • Add DWORD key TcpInitialRTT with decimal value 1
  • HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters
    • Add DWORD key TcpMaxConnectRetransmissions with decimal value 1
    • Add DWORD key TcpMaxDataRetransmissions with decimal value 1

Reference

http://technet.microsoft.com/en-us/library/cc739819(WS.10).aspx

 

 

So to recap the total solution:

1.  Latest kernel or kernel above 6.0.1108.4445(RTM) and 6.2.1000.1869 (R2)
2. Connection Timeout parameter set In the POS.Exe.Config file
3. Windows hotfix Installation: http://support.microsoft.com/kb/2472264
4. Run Command prompt: “netsh interface tcp set global initialRto=300”
5. Change/add the following registry parameters:   TcpInitialRtt. TcpMaxConnectRetransmissions, TcpMaxDataRetransmissions 
6. Reboot machine 

 

RISK

Regarding the last point. If you lower it too low, you are more affected by suddenly network drops/timeout and the stability is affected. Increase it to the value that guarantee stability and acceptable time.

 

 

Author:

Kim Truelsen

Date 19/11-2013