It’s been a while since I’ve had to troubleshoot application performance over a latent WAN connection. So here goes:
– Customer use to have LAN Connection to Server, prior to Datacentre migration.
– Customer has a Microsoft Access Database on a local PC with ODBC SQL Native 11.0 connection to Microsoft SQL Server
– Customer site is 1gbps to managed WAN service
– Datacentre connected at 10gbps to the same managed WAN
– Riverbed Steelheads exist in-path (client side and server side)
– Layer 4-7 Firewalls also exist in-path
– Customer reported via a helpdesk ticket that a database table copy (128MB) that took 2 mins prior to the server moving across the WAN is taking up to 90mins. Huge increase.
To troubleshoot this issue, the first thing I did was to check what the RTT from the client the server was, approx. 2-3ms. I then proceeded to check a separate protocol to ensure it was not bandwidth related. I copied a large 4GB file from the source subnet to the destination server using CIFS. The transfer ran at around 180mbit/s and only took < 2 mins. Therefore, we now know that a different protocol has no issue to the same server. OK, so let us check the firewall, is any traffic being blocked? Is the fancy L7 inspection causing latency? I created a rule on the server side firewall to permit anything between src and dst with no content inspection. The customer tried again and had the same issue. I also bypassed the traffic from the Riverbed. The customer tried again and reported that it the table copy from Access to the SQL server was down to 20 mins. I double checked the monitor logs on the firewall and could see the flow at both 90 mins and 20 mins. Pictured below is the PA firewall log for a test copy of 180MB table that took 40mins:
At this point I sure that however the table copy was being done with Microsoft Access was the culprit, as there was nothing else in path to cause any issue. I also knew that another protocol performed fine for the same source and destination. Therefore, I decided to do some google foo and research the issue. What I found is that using MS Access with ODBC connection across a latent WAN connection (no matter the bandwidth) performs poorly due to the chattiness of the application and TDS.
A lot of forums and blog posts mentioned using SQL Management Studio and the Data Transfer Wizard for importing Access tables to a MS SQL Server. I asked the customer to try this and on the first go it was down to sub 30 seconds. I also performed my own test with MS Access, a linked table, and the TDS (Tabular Data Stream) sends multiple RPC and response packets and then sends the SQL transaction.
Moral of the story: MS Access linked tables ODBC interface to SQL server is highly sensitive to latency. I was able to re-enable inspection after the customer changed to use SQL Management studio. To provide a performance increase I disabled server response inspection on the inbound Palo Alto firewall, so that only Client to Server flow inspection is completed.