Why can’t I run Microsoft Dynamics GP via ODBC over a WAN?

This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

I had an interesting support case last week where the partner was asking why running Microsoft Dynamics GP via ODBC over a WAN was so slow?  They had already told the customer that this was not supported, but the customer wanted reasons.

Below is my response:

I should start off saying that we definitely do expect performance issues when attempting to use Microsoft Dynamics GP across WAN (Wide Area Network) or the Internet with a VPN (Virtual Private Network) connection without using a Terminal Server.  This is why the ODBC (Open Data Base Connectivity) connection over WAN configuration is not supported.  The ONLY supported environment to use Microsoft Dynamics GP remotely is using Terminal Server or similar remote desktop technology.

The System Requirements page discusses the Terminal Server requirements.

• System Requirements for Microsoft Dynamics GP 2010  (CustomerSource)
• System Requirements for Microsoft Dynamics GP 2010  (PartnerSource)

The reason that the application is not expected to perform well in a ODBC over WAN is because while the application uses client/server technologies it was designed to be in continual high speed contact with the database via a LAN (Local Area Network).  Below are some examples to explain:

• Microsoft Dynamics GP validates data and updates tables as information is entered into each field.  This provides a richer experience for the user as information is verified as it is entered and additional data is displayed as required.  This means that there is a continual flow of data between the client and the SQL Server. A true client/server application only does validation when you click submit.  This means that you could enter an entire transaction before it is rejected.
• Also, whenever data is retrieved from the SQL Server, the record set contains a default cursor size of 25 records.  This means that when scanning data for reports or lookups or just scrolling through records, the system can be faster as it does not need to obtain data from the server as often.  It does however mean that there is often data retrieved and then discarded when only the first record in the record set is needed.  This method provides an optimised approach when the performance bottleneck is the SQL Server itself.  When the network communication becomes the bottleneck, the additional data retrieved takes longer to transfer to the client and this affects performance.
• Finally, not all functionality in the application is wrapped in SQL Transactions.  This means that a break in the data connection between the SQL Server and the client could cause data corruption that cannot be rolled back.  The chance of a communication problem via a LAN environment is low, while in a WAN environment the chances are relatively high.

Another aspect of working with a WAN environment is network performance such as bandwidth, latency and quality.  WAN or internet connections are always slower than LAN, i.e. the ping time for data to make a round trip is higher.  Also the available bandwidth is usually much lower, i.e. how much data can be sent at one time.  How good is the quality of this connection?  If packets are being dropped due to corruption and have to be resent, the available bandwidth will be decreased by the error correction activities. These factors by themselves will decrease performance when compared to a LAN.

We also need to look at usage of the WAN connection.  What else is the connection being used for?  How many other applications are running over the limited connection?  How many other users are trying to use Microsoft Dynamics GP?

One last point to add to the complexity of the ODBC over WAN configuration is the increasing use of VOIP (Voice Over Internet Protocol) technologies.  Sending Voice packets uses a reasonable amount of bandwidth, but also prioritizes the VOIP data packets in preference to standard data packets.  The reason for this is to prevent the breaking up of the voice conversation to maintain a high quality of service.  The downside is that if you are using VOIP on your WAN connection, the bandwidth available for other data is decreased and other data will be delayed when bandwidth is limited.

Now the supported environment using a remote desktop client means that no data is transferred over the WAN, all that is communicated is screen data from the server and keyboard & mouse data from the client.  With technologies such as compression and bitmap caching the traffic can be reduced even further.  A break in communication, might cause a disconnect or a bit of garbage on the screen or a delay, but there is no risk of data corruption.

Also have a look at the following post on Troubleshooting Performance issues on a Wide Area Network.

I hope this explains everything.

David

15-Feb-2012: Added link to Troubleshooting Performance issues on a Wide Area Network post.

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.