{"id":4881,"date":"2018-04-04T13:19:59","date_gmt":"2018-04-04T20:19:59","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4881"},"modified":"2018-04-04T13:19:59","modified_gmt":"2018-04-04T20:19:59","slug":"sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/","title":{"rendered":"SQLskills SQL101: Why does repair invalidate replication subscriptions?"},"content":{"rendered":"<p style=\"text-align: justify;\"><em>SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0<strong>SQLskills SQL101<\/strong>\u00a0blog posts, check out\u00a0<a href=\"https:\/\/www.sqlskills.com\/help\/SQL101\" target=\"_blank\" rel=\"noopener noreferrer\">SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n<p style=\"text-align: justify;\">Whenever I&#8217;m teaching and recovering from corruption, I always stress that if the <em>REPAIR_ALLOW_DATA_LOSS<\/em> option of <em>DBCC CHECKDB<\/em> (which I&#8217;ll just call &#8216;repair&#8217; from now on) has to be used, you should do the following:<\/p>\n<ul style=\"text-align: justify;\">\n<li>Run another <em>DBCC CHECKDB<\/em> to make sure that repair fixed everything and no new problems were exposed (by the repair fixing something that was blocking <em>DBCC CHECKDB<\/em>&#8216;s access to a portion of the database)<\/li>\n<li>Run <em>DBCC CHECKCONSTRAINTS<\/em> on any tables or indexes that were repaired and are involved in constraints (stated in Books Online too)<\/li>\n<li>Reinitialize any replication subscriptions that are based on tables that were repaired (stated in Books Online too)<\/li>\n<li>Reevaluate your HA\/DR strategy so you don&#8217;t have to use repair again in future<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">\n<p style=\"text-align: justify;\">One question I&#8217;m often asked is about why replication\u00a0can be broken by running repair.<\/p>\n<p style=\"text-align: justify;\">There are two ways that replication can be affected: repairs on replication metadata tables, and repairs on anything else to do with a subscription.<\/p>\n<p style=\"text-align: justify;\"><strong>Repairs on replication metadata tables<\/strong><\/p>\n<p style=\"text-align: justify;\">This is the simplest case to explain. If the repair operation affects any of the replication metadata tables (i.e. deleted some data from them), the entire replication publication\u00a0will be in an inconsistent state and you should remove replication completely from the database an reinitialize it. This isn&#8217;t limited to a single subscription &#8211; all replication should be reconfigured.<\/p>\n<p style=\"text-align: justify;\"><strong>Repairs on anything else<\/strong><\/p>\n<p style=\"text-align: justify;\">Transaction replication captures changes to the publication database by analyzing the transaction log, looking for transactions that change data in any of the publications, and converting those operations into logical operations that can be applied to the subscribers. Merge replication captures changes to the publication database using DML triggers\u00a0and converting those operations into logical operations that can be applied to the subscribers.<\/p>\n<p style=\"text-align: justify;\">Neither of these mechanisms\u00a0can\u00a0capture of operations performed by repair. Repair operations are always direct physical changes to the database structures to fix inconsistencies in the structures (e.g. a database page, table record, or a linkage between two pages), as opposed to physical changes because of queries performing inserts, updates, or deletes on tables.<\/p>\n<p style=\"text-align: justify;\">These repair operations cannot translated into logical operations that can be applied to replication subscribers because there are no logical operations than can be expressed using T-SQL for the equivalent of the direct structural changes that repair is performing. Replication does not preserve the exact physical location of a particular record between the publication and subscription databases, so a direct change to record Y on page X in the publication database would not be able to be replayed on the subscription database (remember, replication ships logical changes, not physical changes). This means that if any table that is part of a replication publication is changed by a repair operation, the replication subscription is no longer valid and must be reinitialized.<\/p>\n<p style=\"text-align: justify;\">As an example, imagine a repair operation is forced to remove a data page from a unique clustered index (essentially deleting some table records), and the subscription is NOT reinitialized. Those records would still exist on the replicated copy of the table. If a subsequent insert operation inserted records with cluster key values corresponding to the records deleted by the repair operation, the Distribution Agent would fail when trying to apply the inserts to the replicated copy \u2013 because the repair operation was not applied to the subscription database and a duplicate key violation error will occur when attempting to apply the insert to the replicated table.<\/p>\n<p style=\"text-align: justify;\">A replication subscription must always be reinitialized if any table in the publication is affected by a repair operation, or the replication metadata tables are repaired.<\/p>\n<p style=\"text-align: justify;\">Thanks<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Whenever I&#8217;m teaching and recovering from corruption, [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[74,75,108],"tags":[],"class_list":["post-4881","post","type-post","status-publish","format-standard","hentry","category-repair","category-replication","category-sql101"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQLskills SQL101: Why does repair invalidate replication subscriptions? - 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\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: Why does repair invalidate replication subscriptions? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Whenever I&#8217;m teaching and recovering from corruption, [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2018-04-04T20:19:59+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=\"3 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\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/\",\"name\":\"SQLskills SQL101: Why does repair invalidate replication subscriptions? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2018-04-04T20:19:59+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: Why does repair invalidate replication subscriptions?\"}]},{\"@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":"SQLskills SQL101: Why does repair invalidate replication subscriptions? - 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\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: Why does repair invalidate replication subscriptions? - Paul S. Randal","og_description":"SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Whenever I&#8217;m teaching and recovering from corruption, [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/","og_site_name":"Paul S. Randal","article_published_time":"2018-04-04T20:19:59+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/","name":"SQLskills SQL101: Why does repair invalidate replication subscriptions? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2018-04-04T20:19:59+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-repair-invalidate-replication-subscriptions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: Why does repair invalidate replication subscriptions?"}]},{"@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\/4881","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=4881"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4881\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4881"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4881"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4881"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}