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

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.

Comments

• Anonymous
  July 27, 2010
  Very informative post, David. Thanks so much. It did shed some light on my own doubts as well. Vaidy

• Anonymous
  July 27, 2010
  David, Thanks for clearing this up. Having developed a smart client application that worked over the internet, I concur and agree that users do not love an - "application only does validation when you click submit. This means that you could enter an entire transaction before it is rejected. " That especially becomes problematic when the app goes offline and there are multiple transactions to validate when it comes online. The patterns and practices group had a Smart Client Offline Application Block, but I believe it was discontinued (I might be wrong). msdn.microsoft.com/.../ff650489.aspx I think such an architecture just wouldn't scale up for an Enterprise Level Application considering the greater usage of VOIP technologies as you pointed up.   I am happy GP does its job well and now I have a more complete answer when people have this question. Another thing people like is that one terminal server is easier to maintain than multiple client PCs over the internet.

• Anonymous
  July 28, 2010
  Very informative article David, Thanks for sharing this.

• Anonymous
  July 29, 2010
  Thanks for the explanation, David, as this is a question that comes up quite often with customers, and it's good to have a documented answer available.

• Anonymous
  July 29, 2010
  The comment has been removed

• Anonymous
  August 31, 2010
  David, fabulous article and great reference to point to for anyone asking this question.  Thank you so much for putting this together.

• Anonymous
  October 10, 2010
  The comment has been removed

• Anonymous
  October 11, 2010
  Hi Chris If you are able to provide the equivalent of a 100Mbps network, that should be suitable.  Most LAN environments (at least until recently) were 100Mbps Ethernet anyway. If you do go 100Mbps, make sure you the fibre can be used at 1000Mbps as well.  This will give you future expansion if you find that you have too much data going down the fibre. Disclaimer: I must clarify that I have not used fibre like this and so cannot talk from experience. David

• Anonymous
  July 17, 2011
  Wonderful article and great explanation!

• Anonymous
  July 20, 2015
  This is an old topic so apologies for the bump. I hope its still relevant and some will get benefit. The above explanation makes sense and I agree that in general, ODBC does not work well over a WAN. However, what is your opinion of WAN acceleration technologies like Riverbed Steelhead that does protocol acceleration and specifically for SQL data. Has anyone had experience with Dynamics GP and Riverbed over a WAN?

• Anonymous
  July 20, 2015
  Hi Muzamil I have never heard of Riverbed Steelhead. So cannot help you here. It might work better, but still is risky. Microsoft Dynamics GP while using client server technology is not a true client server application as it validates data as it is entered. This means there is a lot of continuous communication with the server. If you are connecting over a WAN, the only supported method are terminal server (optionally with citrix) or using the Web Client. So, you could try using the acceleration software, but it will not be supported and the chance of data corruption is still much higher than the supported methods. David

• Anonymous
  July 21, 2015
  The comment has been removed

• Anonymous
  March 08, 2017
  The comment has been removed

  • Anonymous
    March 08, 2017
    Hi LarryRegardless of how fast the SQL server is, Dynamics GP will still now run well with the SQL Server when it is not on the same local area network as the client. The problem is the communication between the client and server, not SQL Server performance.The only way to host Dynamics GP in Azure is to set up SQL Server and Terminal Services on Azure hosted virtual machines using IAAS (infrastructure as a service).I don't even think that Azure SQL will work as GP needs multiple databases.DavidPS: Please note that there are a number of hosting partners that have done all the hard work already and you can use their services.