{"id":614,"date":"2011-02-08T10:02:00","date_gmt":"2011-02-08T10:02:00","guid":{"rendered":"\/blogs\/paul\/post\/Importance-of-network-latency-when-using-database-mirroring.aspx"},"modified":"2017-04-13T09:51:26","modified_gmt":"2017-04-13T16:51:26","slug":"importance-of-network-latency-when-using-database-mirroring","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/","title":{"rendered":"Importance of network latency when using database mirroring"},"content":{"rendered":"<p>\n<font face=\"verdana,geneva\" size=\"2\">Last week I kicked off a survey about network latencies and database mirroring. See <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/survey-database-mirroring-latency-between-servers\/\"><font face=\"verdana,geneva\" size=\"2\">here<\/font><\/a><font face=\"verdana,geneva\" size=\"2\"> for the original post. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Here are the results of the survey: <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">&nbsp;<img decoding=\"async\" src=\"\/blogs\/paul\/wp-content\/uploads\/2011\/2\/dbmlatency.jpg\" alt=\"\" \/><\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">I was really interested to see whether the proportion of people doing asynchronous mirroring became higher as the network latency increased. Although this isn&#39;t a statistically valid sampe by any means, it does show that the answer is no. However, we&#39;re missing some data that would help explain what we see here: how long are the average transactions and is there a response time SLA?<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">The latency between the principal and the mirror is a big deal for synchronous mirroring, because a transaction on the principal cannot be acknowledged to the user\/app as having committed until all of it&#39;s log records have been written to the mirror database&#39;s log drive.<\/font>\n<\/p>\n<blockquote>\n<p>\n\t<font face=\"verdana,geneva\" size=\"2\">NOTE: the transaction does NOT have to be replayed\/committed on the mirror, simply the log records have to be durable to guarantee the transaction is durable if the principal has a disaster. This is a very common misconception.<\/font>\n\t<\/p>\n<\/blockquote>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">If the average transaction length is quite long, say 20 seconds, then the addition of another 500ms of latency when the commit is issued is not a big deal. But if the average transaction length is 100ms then an extra 500ms is a *very* big deal. This is when using asynchronous mirroring starts being considered &#8211; as transactions on the principal do NOT have to wait, but at the expense of potential data loss if the principal experiences a disaster. However, if there is no response time SLA, then the company may be fine with the extra delay with synchronous mirroring to guaranteezero data loss (as long as the mirror session stays SYNCHRONIZED).<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">As always, the choice of HA and DR technology comes down to analyzing requirements and limitations before choosing a technology. I go into this in more detail in the whitepaper I wrote in 2009 for Microsoft: <\/font><a href=\"https:\/\/msdn.microsoft.com\/Areas\/Epx\/Content\/500.htm?aspxerrorpath=\/en-us\/library\/ee523927.aspx\"><font face=\"verdana,geneva\" size=\"2\">High Availability with SQL Server 2008<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">. There is also an excellent whitepaper on database mirroring: <\/font><a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/cc917681.aspx\"><font face=\"verdana,geneva\" size=\"2\">Database Mirroring Best Practices and Performance Considerations<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">If you&#39;re one of the people who responded that you don&#39;t know your network latency even though you&#39;re using mirroring, check out the post I wrote last week: <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-monitoring-a-database-mirroring-session\/\"><font face=\"verdana,geneva\" size=\"2\">Importance of monitoring a database mirroring session<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">.<\/font>\n<\/p>\n<p>\n<font size=\"2\">Thanks!<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Last week I kicked off a survey about network latencies and database mirroring. See here for the original post. Here are the results of the survey: &nbsp; I was really interested to see whether the proportion of people doing asynchronous mirroring became higher as the network latency increased. Although this isn&#39;t a statistically valid sampe [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,32,46,66,91],"tags":[],"class_list":["post-614","post","type-post","status-publish","format-standard","hentry","category-database-maintenance","category-database-mirroring","category-high-availability","category-performance-tuning","category-surveys"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Importance of network latency when using database mirroring - Paul S. Randal<\/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.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Importance of network latency when using database mirroring - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Last week I kicked off a survey about network latencies and database mirroring. See here for the original post. Here are the results of the survey: &nbsp; I was really interested to see whether the proportion of people doing asynchronous mirroring became higher as the network latency increased. Although this isn&#039;t a statistically valid sampe [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2011-02-08T10:02:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:51:26+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/\",\"name\":\"Importance of network latency when using database mirroring - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2011-02-08T10:02:00+00:00\",\"dateModified\":\"2017-04-13T16:51:26+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Importance of network latency when using database mirroring\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Importance of network latency when using database mirroring - Paul S. Randal","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.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/","og_locale":"en_US","og_type":"article","og_title":"Importance of network latency when using database mirroring - Paul S. Randal","og_description":"Last week I kicked off a survey about network latencies and database mirroring. See here for the original post. Here are the results of the survey: &nbsp; I was really interested to see whether the proportion of people doing asynchronous mirroring became higher as the network latency increased. Although this isn&#39;t a statistically valid sampe [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/","og_site_name":"Paul S. Randal","article_published_time":"2011-02-08T10:02:00+00:00","article_modified_time":"2017-04-13T16:51:26+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/","name":"Importance of network latency when using database mirroring - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2011-02-08T10:02:00+00:00","dateModified":"2017-04-13T16:51:26+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-network-latency-when-using-database-mirroring\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Importance of network latency when using database mirroring"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/614","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=614"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/614\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=614"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=614"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=614"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}