Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

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

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

  • Comments 9

David Meego - Click for blog homepageI 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 Secure Link (CustomerSource)
 
System Requirements for Microsoft Dynamics GP 2010 Secure Link (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.

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

    Vaidy

  • 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.

  • Very informative article David, Thanks for sharing this.

  • 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.

  • Posting from Mark Polino at DynamicAccounting.net

    msdynamicsgp.blogspot.com/.../why-you-cant-run-dynamics-gp-over-wan.html

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

  • David,

    I understand and support all of the above when considering a typical wide-area-network.

    However, if we are able to link offices via high-speed low-latency fibre-optic networks, the question becomes "How much bandwidth is enough for each GP 10 client?"

    As we plan on moving people to a new building, we are looking at whether or not we need to provision 1gbps fibre links or whether 100mbps would be suitable.

    Thanks,

    Chris

  • 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

  • Wonderful article and great explanation!

Page 1 of 1 (9 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 4 and 5 and type the answer here:
  • Post