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

David Meego - Click for blog homepageThis 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.

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.


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.

16 thoughts on “Why can’t I run Microsoft Dynamics GP via ODBC over a WAN?

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


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


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


  4. 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?


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


  6. Thanks David,
    Riverbed make protocol acceleration appliances that target high latency applications e.g. SQL ODBC, SMB/CIFS etc. Below is some information that speaks to it. The second links is to a case study where a company was able to eliminate the use of a terminal server and centralize its dynamics installation using this technology. Granted, the case study makes reference to Dynamics so could be referring to CRM and not GP. not sure if this makes any difference.
    Many thanks


  7. David,

    Thank you very much. Can you please help me understand if this would work?

    We have a Virtual Machine running the GP Software in the Azure Cloud. All of our users connect to this virtual server to use Great Plains. Could I migrate my existing Great Plains Database over to a purely Azure SQL database and pay for enough performance to ensure that we could eliminate our SQL VM server?

    1. Get a high performance database


    • Hi Larry

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


      PS: Please note that there are a number of hosted GP partners who have done all the hard work already and you can use their services.


Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.