{"id":1172,"date":"2021-09-01T22:31:25","date_gmt":"2021-09-01T14:31:25","guid":{"rendered":"\/?p=1172"},"modified":"2021-09-01T22:31:25","modified_gmt":"2021-09-01T14:31:25","slug":"poor-performance-of-ms-access-over-wan","status":"publish","type":"post","link":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/","title":{"rendered":"Poor Performance of Microsoft Access to SQL Server over a WAN Link"},"content":{"rendered":"

It’s been a while since I’ve had to troubleshoot application performance over a latent WAN connection. So here goes:<\/p>\n

Scenario:<\/strong>
\n– Customer use to have LAN Connection to Server, prior to Datacentre migration.
\n– Customer has a Microsoft Access Database on a local PC with ODBC SQL Native 11.0 connection to Microsoft SQL Server
\n– Customer site is 1gbps to managed WAN service
\n– Datacentre connected at 10gbps to the same managed WAN
\n– Riverbed Steelheads exist in-path (client side and server side)
\n– Layer 4-7 Firewalls also exist in-path
\n– 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.<\/p>\n

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.\nOK, 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:\n\"LOG\"<\/a><\/p>\n

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.<\/p>\n

A lot of forums<\/a> and blog posts<\/a> 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.
\nMoral 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.<\/p>\n","protected":false},"excerpt":{"rendered":"

It’s been a while since I’ve had to troubleshoot application performance over a latent WAN connection. So here goes: Scenario: – Customer use to have…<\/p>\n","protected":false},"author":2,"featured_media":1176,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-1172","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-datacentre"],"acf":[],"yoast_head":"\nPoor Performance of Microsoft Access to SQL Server over a WAN Link - Insecure Wire<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Poor Performance of Microsoft Access to SQL Server over a WAN Link - Insecure Wire\" \/>\n<meta property=\"og:description\" content=\"It’s been a while since I’ve had to troubleshoot application performance over a latent WAN connection. So here goes: Scenario: – Customer use to have...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/\" \/>\n<meta property=\"og:site_name\" content=\"Insecure Wire\" \/>\n<meta property=\"article:published_time\" content=\"2021-09-01T14:31:25+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2021\/09\/sql.png\" \/>\n\t<meta property=\"og:image:width\" content=\"200\" \/>\n\t<meta property=\"og:image:height\" content=\"200\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"nikonau\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/insecurewire\" \/>\n<meta name=\"twitter:site\" content=\"@insecurewire\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"nikonau\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/\"},\"author\":{\"name\":\"nikonau\",\"@id\":\"https:\/\/www.insecurewi.re\/#\/schema\/person\/8ba08b41fc754b971a948ead6ccb777d\"},\"headline\":\"Poor Performance of Microsoft Access to SQL Server over a WAN Link\",\"datePublished\":\"2021-09-01T14:31:25+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/\"},\"wordCount\":548,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.insecurewi.re\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2021\/09\/sql.png\",\"articleSection\":[\"Datacentre\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/\",\"url\":\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/\",\"name\":\"Poor Performance of Microsoft Access to SQL Server over a WAN Link - Insecure Wire\",\"isPartOf\":{\"@id\":\"https:\/\/www.insecurewi.re\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2021\/09\/sql.png\",\"datePublished\":\"2021-09-01T14:31:25+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#primaryimage\",\"url\":\"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2021\/09\/sql.png\",\"contentUrl\":\"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2021\/09\/sql.png\",\"width\":200,\"height\":200,\"caption\":\"SQLMS\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.insecurewi.re\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Poor Performance of Microsoft Access to SQL Server over a WAN Link\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.insecurewi.re\/#website\",\"url\":\"https:\/\/www.insecurewi.re\/\",\"name\":\"Insecure Wire\",\"description\":\"A Network Engineer\u2019s Perspective.\",\"publisher\":{\"@id\":\"https:\/\/www.insecurewi.re\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.insecurewi.re\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.insecurewi.re\/#organization\",\"name\":\"Insecure Wire\",\"url\":\"https:\/\/www.insecurewi.re\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.insecurewi.re\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2023\/10\/cloud.png\",\"contentUrl\":\"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2023\/10\/cloud.png\",\"width\":32,\"height\":32,\"caption\":\"Insecure Wire\"},\"image\":{\"@id\":\"https:\/\/www.insecurewi.re\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/x.com\/insecurewire\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.insecurewi.re\/#\/schema\/person\/8ba08b41fc754b971a948ead6ccb777d\",\"name\":\"nikonau\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.insecurewi.re\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/2d1b9d9dc90da4f6d3da31b870f418c6b3553ba9be48d53e8ee3a35b0adb1d35?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/2d1b9d9dc90da4f6d3da31b870f418c6b3553ba9be48d53e8ee3a35b0adb1d35?s=96&d=mm&r=g\",\"caption\":\"nikonau\"},\"sameAs\":[\"https:\/\/x.com\/https:\/\/twitter.com\/insecurewire\"],\"url\":\"https:\/\/www.insecurewi.re\/index.php\/author\/nikon\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Poor Performance of Microsoft Access to SQL Server over a WAN Link - Insecure Wire","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/","og_locale":"en_US","og_type":"article","og_title":"Poor Performance of Microsoft Access to SQL Server over a WAN Link - Insecure Wire","og_description":"It’s been a while since I’ve had to troubleshoot application performance over a latent WAN connection. So here goes: Scenario: – Customer use to have...","og_url":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/","og_site_name":"Insecure Wire","article_published_time":"2021-09-01T14:31:25+00:00","og_image":[{"width":200,"height":200,"url":"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2021\/09\/sql.png","type":"image\/png"}],"author":"nikonau","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/insecurewire","twitter_site":"@insecurewire","twitter_misc":{"Written by":"nikonau","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#article","isPartOf":{"@id":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/"},"author":{"name":"nikonau","@id":"https:\/\/www.insecurewi.re\/#\/schema\/person\/8ba08b41fc754b971a948ead6ccb777d"},"headline":"Poor Performance of Microsoft Access to SQL Server over a WAN Link","datePublished":"2021-09-01T14:31:25+00:00","mainEntityOfPage":{"@id":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/"},"wordCount":548,"commentCount":0,"publisher":{"@id":"https:\/\/www.insecurewi.re\/#organization"},"image":{"@id":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#primaryimage"},"thumbnailUrl":"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2021\/09\/sql.png","articleSection":["Datacentre"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/","url":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/","name":"Poor Performance of Microsoft Access to SQL Server over a WAN Link - Insecure Wire","isPartOf":{"@id":"https:\/\/www.insecurewi.re\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#primaryimage"},"image":{"@id":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#primaryimage"},"thumbnailUrl":"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2021\/09\/sql.png","datePublished":"2021-09-01T14:31:25+00:00","breadcrumb":{"@id":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#primaryimage","url":"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2021\/09\/sql.png","contentUrl":"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2021\/09\/sql.png","width":200,"height":200,"caption":"SQLMS"},{"@type":"BreadcrumbList","@id":"https:\/\/www.insecurewi.re\/index.php\/2021\/09\/01\/poor-performance-of-ms-access-over-wan\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.insecurewi.re\/"},{"@type":"ListItem","position":2,"name":"Poor Performance of Microsoft Access to SQL Server over a WAN Link"}]},{"@type":"WebSite","@id":"https:\/\/www.insecurewi.re\/#website","url":"https:\/\/www.insecurewi.re\/","name":"Insecure Wire","description":"A Network Engineer\u2019s Perspective.","publisher":{"@id":"https:\/\/www.insecurewi.re\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.insecurewi.re\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.insecurewi.re\/#organization","name":"Insecure Wire","url":"https:\/\/www.insecurewi.re\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.insecurewi.re\/#\/schema\/logo\/image\/","url":"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2023\/10\/cloud.png","contentUrl":"https:\/\/www.insecurewi.re\/wp-content\/uploads\/2023\/10\/cloud.png","width":32,"height":32,"caption":"Insecure Wire"},"image":{"@id":"https:\/\/www.insecurewi.re\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/insecurewire"]},{"@type":"Person","@id":"https:\/\/www.insecurewi.re\/#\/schema\/person\/8ba08b41fc754b971a948ead6ccb777d","name":"nikonau","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.insecurewi.re\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/2d1b9d9dc90da4f6d3da31b870f418c6b3553ba9be48d53e8ee3a35b0adb1d35?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/2d1b9d9dc90da4f6d3da31b870f418c6b3553ba9be48d53e8ee3a35b0adb1d35?s=96&d=mm&r=g","caption":"nikonau"},"sameAs":["https:\/\/x.com\/https:\/\/twitter.com\/insecurewire"],"url":"https:\/\/www.insecurewi.re\/index.php\/author\/nikon\/"}]}},"_links":{"self":[{"href":"https:\/\/www.insecurewi.re\/index.php\/wp-json\/wp\/v2\/posts\/1172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.insecurewi.re\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.insecurewi.re\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.insecurewi.re\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.insecurewi.re\/index.php\/wp-json\/wp\/v2\/comments?post=1172"}],"version-history":[{"count":0,"href":"https:\/\/www.insecurewi.re\/index.php\/wp-json\/wp\/v2\/posts\/1172\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.insecurewi.re\/index.php\/wp-json\/wp\/v2\/media\/1176"}],"wp:attachment":[{"href":"https:\/\/www.insecurewi.re\/index.php\/wp-json\/wp\/v2\/media?parent=1172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.insecurewi.re\/index.php\/wp-json\/wp\/v2\/categories?post=1172"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.insecurewi.re\/index.php\/wp-json\/wp\/v2\/tags?post=1172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}