{"id":1585,"date":"2015-09-22T16:30:18","date_gmt":"2015-09-22T14:30:18","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=1585"},"modified":"2015-09-20T14:26:55","modified_gmt":"2015-09-20T12:26:55","slug":"repairing-a-replication-subscriber","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2015\/09\/22\/repairing-a-replication-subscriber\/","title":{"rendered":"Repairing a replication subscriber"},"content":{"rendered":"

It\u2019s a question which has come up a couple of times. If a subscriber of a transactional replication publication becomes corrupt, is running CheckDB with repair allow data loss safe?<\/p>\n

The theory is, since the subscriber is a copy of another database, allowing CheckDB to discard data in the process of repairing won\u2019t actually cause data loss (the data is still there in the publisher).<\/p>\n

As with many things, it\u2019s partially true, however there\u2019s a little more to it that just that.<\/p>\n

To see why, let\u2019s set up some replication and intentionally corrupt some data in the subscriber and run a repair.<\/p>\n

First, the source DB. I\u2019m going to keep it really simple so that we can see the effects. A database with a single table called Customers, populated with 100 000 rows via SQLDataGenerator. The filler column simulates other columns that would be in a real table.<\/p>\n

CREATE TABLE Customers (\r\nCustomerID INT IDENTITY PRIMARY KEY,\r\nFirstName VARCHAR(50),\r\nSurname VARCHAR(50),\r\nAccountNumber CHAR(15) UNIQUE,\r\nAccountStatus VARCHAR(25),\r\nFiller CHAR(200) DEFAULT ''\r\n)<\/pre>\n
Once populated, we set up a transactional replication publication with default settings, and for simplicity have the subscriber be a second database on the same instance.\u00a0 we query the table on the subscriber, we have exactly the same number of rows as the publisher has.<\/p>\n
\"ReplicationSubscriber\"<\/a><\/p>\n
To simulate a misbehaving IO subsystem, I\u2019m going to take the subscription DB offline, open the mdf in a hex editor, scribble on top of a few pages in the user table, then bring the DB back online. A checkDB returns a couple of pages of errors ending with:<\/p>\n
CHECKDB found 0 allocation errors and 16 consistency errors in table ‘Customers’ (object ID 421576540).<\/p>\n
CHECKDB found 0 allocation errors and 18 consistency errors in database ‘ReplicationSubscriber’.<\/p>\n
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ReplicationSubscriber).<\/p><\/blockquote>\n
Minimum level to repair is REPAIR_ALLOW_DATA_LOSS and, based on the error messages, doing so will deallocate five pages. Let\u2019s run the repair and then see what\u2019s left in the table.<\/p>\n
\"ReplicationSubscriber<\/a><\/p>\n
We\u2019ve lost 155 rows from the Customers table. The replication publisher still has them, but the subscriber now does not. So what happens if someone makes some data modifications on the publisher and changes one of the rows that was lost at the subscriber?<\/p>\n
UPDATE dbo.Customers\r\nSET AccountStatus = 'Closed'\r\nWHERE CustomerID = 48700;\r\nGO\r\n\r\nINSERT INTO dbo.Customers\r\n(FirstName,\r\nSurname,\r\nAccountNumber,\r\nAccountStatus\r\n)\r\nVALUES\u00a0 ('Mary',\r\n'White',\r\n'4985563214AN'\r\n'Pending'\r\n)<\/pre>\n
Give it a few seconds to replicate and then let\u2019s check the table in the publisher. The new customer isn\u2019t there\u2026 If we go and have a look at the replication, it\u2019s throwing errors.<\/p>\n
\"BrokenReplication\"<\/a><\/p>\n
\u201cThe row was not found at the Subscriber when applying the replicated command. \u201c, which is quite true, the row\u2019s not there any longer.<\/p>\n
What\u2019s happened is that one of the rows updated on the publisher was lost when CheckDB repaired the database. The rows lost due to the repair weren\u2019t automatically fetched from the publisher, there\u2019s no mechanism in transactional replication for that to happen. Hence when the row was updated on the publisher, the update statement was replicated and the row on the subscriber couldn\u2019t be found, causing the replication to fail, retry and fail again. It will keep failing until it is either reinitialised or the missing row is manually added to the subscriber.<\/p>\n
What we needed to have done, to prevent this outcome, was after the CheckDB deallocated pages, to manually (or with something like SQLDataCompare) to sync the subscriber with the publisher and explicitly add back the rows which were on the deallocated pages. The primary key values must be kept the same, as that\u2019s what replication uses to identify which rows in the subscriber need to be updated.<\/p>\n
So to go back to the original question, yes it\u2019s probably fine to run CheckDB with repair on a replication subscription, providing that the data deallocated as part of the repair is manually synced from the publisher afterwards. If it isn\u2019t, the replication will break as soon as one of the rows affected by the repair is updated or deleted at the publisher.<\/p>\n","protected":false},"excerpt":{"rendered":"
It\u2019s a question which has come up a couple of times. If a subscriber of a transactional replication publication becomes corrupt, is running CheckDB with repair allow data loss safe? The theory is, since the subscriber is a copy of… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"New Blog Post: Repairing a replication subscriber","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[22,15,16],"tags":[],"class_list":["post-1585","post","type-post","status-publish","format-standard","hentry","category-corruption","category-sql-server","category-syndication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-pz","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1585","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=1585"}],"version-history":[{"count":13,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1585\/revisions"}],"predecessor-version":[{"id":1619,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1585\/revisions\/1619"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=1585"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=1585"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=1585"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}