{"id":1124,"date":"2007-11-16T03:31:48","date_gmt":"2007-11-16T03:31:48","guid":{"rendered":"\/blogs\/paul\/post\/Conference-Questions-Pot-Pourri-6-How-to-cope-with-losing-the-mirror-AND-the-witness-in-a-mirroring-session.aspx"},"modified":"2017-08-01T17:56:31","modified_gmt":"2017-08-02T00:56:31","slug":"conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/","title":{"rendered":"Conference Questions Pot-Pourri #6: How to cope with losing the mirror AND the witness in a mirroring session?"},"content":{"rendered":"<p style=\"text-align: justify;\">Here&#8217;s a question that came up yesterday in our chalk-talk on database mirroring at TechEd IT Forum\u00a0that Kimberly and I talked about this morning (here in Barcelona).<\/p>\n<p style=\"text-align: justify;\"><strong>Q) <\/strong>I have a database mirroring session where the witness and mirror servers are in one physical location, and the principal server is in another. The mirroring session is running synchronously with the witness to allow automatic failover. A disaster happens to the site where the mirror and witness are, so the principal database is unavailable. I can&#8217;t seem to access the principal at all to bring it back online by removing the witness and the mirror and witness won&#8217;t be available for hours. What can I do?<\/p>\n<p style=\"text-align: justify;\"><strong>A)<\/strong> The behavior you&#8217;re seeing (the principal database becoming unavailable) is expected. In a mirroring configuration with a witness, the principal needs to have quorum with (i.e. be able to see) at least one of the other partners, either the mirror, the witness, or both. If it can&#8217;t see either, it doesn&#8217;t know whether the witness and mirror can still see each other and the mirror may have brought itself online as the new principal. (Kimberly likes to say that the principal thinks the witness and mirror are conspiring against it :-)) In this case though, the customer knows that the mirror and witness are actually down and so he wants to bring the principal database back online.<\/p>\n<p style=\"text-align: justify;\">I reproduced this situation in a VPC with three SQL Server 2008 instances running mirroring between them (the behavior is exactly the same in 2008 and 2005). I did a <span style=\"font-family: 'Courier New';\">net stop<\/span> on the mirror and witness servers and the principal database went offline. Trying to get into the principal database results in the following error:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nUSE &#x5B;TicketSalesDB];\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nMsg 955, Level 14, State 1, Line 1\r\nDatabase TicketSalesDB is enabled for Database Mirroring, but the database lacks quorum: the database cannot be opened.  Check the partner and witness connections if configured.\r\n<\/pre>\n<p>This is what I&#8217;d expect. The customer tried to remove the witness so let&#8217;s try that:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nALTER DATABASE &#x5B;TicketSalesDB] SET WITNESS OFF;\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nMsg 1431, Level 16, State 4, Line 1\r\nNeither the partner nor the witness server instance for database &quot;TicketSalesDB&quot; is available. Reissue the command when at least one of the instances becomes available.\r\n<\/pre>\n<p style=\"text-align: justify;\">That doesn&#8217;t work either because removing the witness needs to happen on one of the partners as well as the principal. The only way to get out of this situation is to break the mirroring partnership completely.<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nALTER DATABASE &#x5B;TicketSalesDB] SET PARTNER OFF;\r\nGO\r\nUSE &#x5B;TicketSalesDB];\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nCommand(s) completed successfully.\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Here&#8217;s a question that came up yesterday in our chalk-talk on database mirroring at TechEd IT Forum\u00a0that Kimberly and I talked about this morning (here in Barcelona). Q) I have a database mirroring session where the witness and mirror servers are in one physical location, and the principal server is in another. The mirroring session [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26,32,35],"tags":[],"class_list":["post-1124","post","type-post","status-publish","format-standard","hentry","category-conference-questions-pot-pourri","category-database-mirroring","category-disaster-recovery"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Conference Questions Pot-Pourri #6: How to cope with losing the mirror AND the witness in a mirroring session? - 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\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Conference Questions Pot-Pourri #6: How to cope with losing the mirror AND the witness in a mirroring session? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Here&#8217;s a question that came up yesterday in our chalk-talk on database mirroring at TechEd IT Forum\u00a0that Kimberly and I talked about this morning (here in Barcelona). Q) I have a database mirroring session where the witness and mirror servers are in one physical location, and the principal server is in another. The mirroring session [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-11-16T03:31:48+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-08-02T00:56:31+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\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/\",\"name\":\"Conference Questions Pot-Pourri #6: How to cope with losing the mirror AND the witness in a mirroring session? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2007-11-16T03:31:48+00:00\",\"dateModified\":\"2017-08-02T00:56:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Conference Questions Pot-Pourri #6: How to cope with losing the mirror AND the witness in a mirroring session?\"}]},{\"@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":"Conference Questions Pot-Pourri #6: How to cope with losing the mirror AND the witness in a mirroring session? - 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\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/","og_locale":"en_US","og_type":"article","og_title":"Conference Questions Pot-Pourri #6: How to cope with losing the mirror AND the witness in a mirroring session? - Paul S. Randal","og_description":"Here&#8217;s a question that came up yesterday in our chalk-talk on database mirroring at TechEd IT Forum\u00a0that Kimberly and I talked about this morning (here in Barcelona). Q) I have a database mirroring session where the witness and mirror servers are in one physical location, and the principal server is in another. The mirroring session [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/","og_site_name":"Paul S. Randal","article_published_time":"2007-11-16T03:31:48+00:00","article_modified_time":"2017-08-02T00:56:31+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\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/","name":"Conference Questions Pot-Pourri #6: How to cope with losing the mirror AND the witness in a mirroring session? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2007-11-16T03:31:48+00:00","dateModified":"2017-08-02T00:56:31+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-6-how-to-cope-with-losing-the-mirror-and-the-witness-in-a-mirroring-session\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Conference Questions Pot-Pourri #6: How to cope with losing the mirror AND the witness in a mirroring session?"}]},{"@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\/1124","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=1124"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1124\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1124"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1124"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1124"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}