{"id":956,"date":"2018-10-16T07:00:05","date_gmt":"2018-10-16T14:00:05","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=956"},"modified":"2018-10-15T19:34:48","modified_gmt":"2018-10-16T02:34:48","slug":"removing-a-database-from-a-replica-in-an-availability-group","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/","title":{"rendered":"Removing a database from a replica in an Availability Group"},"content":{"rendered":"

I recently had a scenario in a two-node Availability Group where multiple large-batch modification queries were executed and created a large redo queue on the replica.\u00a0 The storage on the replica is slower than that on the primary (not a desired scenario, but it is what it is) and the secondary has fallen behind before, but this time it was to the point where it made more sense remove the database from the replica and re-initialize, rather than wait several hours for it to catch up.\u00a0 What I\u2019m about detail is not<\/strong> an ideal solution.\u00a0 In fact, your solution should be architected to avoid this scenario entirely (storage of equal capability for all involved nodes is essential).\u00a0 But, stuff happens (e.g., a secondary database unexpectedly pausing), and the goal was to get the replica synchronized again with no downtime.<\/p>\n

In my demo environment I have two nodes, CAP and BUCKY.\u00a0 CAP is the primary, BUCKY is the replica, and there are two databases, AdventureWorks2012 and WideWorldImporters in my TestLocation AG:<\/p>\n

\"Availability<\/a>
Availability Group (named TestLocation) Configuration<\/figcaption><\/figure>\n

In this case, my WideWorldImporters database is the one that\u2019s behind on the secondary replica, so this is the database we want to remove and then re-initialize.\u00a0 On the secondary<\/em> (BUCKY) we will remove WideWorldImporters from the AG with this TSQL:<\/p>\n

\r\nUSE [master];\r\nGO\r\n\r\nALTER DATABASE [WideWorldImporters]\r\n     SET HADR OFF;\r\nGO\r\n<\/pre>\n
You can also do this in the UI, if you right-click on the database within the AG and select Remove Secondary Database, but I recommend scripting it and then running it (screen shot for reference):<\/p>\n
\"Removing<\/a>
Removing WideWorldImporters from the AG via SSMS<\/figcaption><\/figure>\n
After removing the database, it will still be listed for the AG but it will have a red X next to it (don\u2019t panic).\u00a0 It will also be listed in the list of Databases, but it will have a status of Restoring\u2026<\/p>\n
\"WideWorldImporters<\/a>
WideWorldImporters database removed on the secondary replica<\/figcaption><\/figure>\n
If you check the primary, the WideWorldImporters database there is healthy:<\/p>\n
\"Database<\/a>
Database and AG health on the primary<\/figcaption><\/figure>\n
You can still access WideWorldImporters as it\u2019s part of the AG and using the Listener.\u00a0 The system is still available, but I\u2019m playing without a net<\/strong>.\u00a0 If the primary goes down, I will have not have access to the WideWorldImporters database.\u00a0 In this specific case, this was a risk I was willing to take (again, because the time to restore the database was less than the time it would take the secondary to catch up).\u00a0 Also note that because this database is in an Availability Group by itself, the transaction log will be truncated when it’s backed up.<\/p>\n
At this point, you want to kick off a restore of the most recent full backup of the database on the replica (BUCKY):<\/p>\n
\r\nUSE [master];\r\nGO\r\n\r\nRESTORE DATABASE [WideWorldImporters]\r\n     FROM\u00a0 DISK = N'C:\\Backups\\WWI_Full.bak'\r\n     WITH\u00a0 FILE = 1,\r\n     MOVE N'WWI_Primary' TO N'C:\\Databases\\WideWorldImporters.mdf',\r\n     MOVE N'WWI_UserData' TO N'C:\\Databases\\WideWorldImporters_UserData.ndf',\r\n     MOVE N'WWI_Log' TO N'C:\\Databases\\WideWorldImporters.ldf',\r\n     MOVE N'WWI_InMemory_Data_1' TO N'C:\\Databases\\WideWorldImporters_InMemory_Data_1',\r\n     NORECOVERY,\r\n     REPLACE,\r\n     STATS = 5;\r\n\r\nGO\r\n<\/pre>\n
Depending on how long this takes, at some point I disable <\/strong>the jobs that run differential or log backups on the primary (CAP), and then manually kick off a differential backup on the primary (CAP).<\/p>\n
\r\nUSE [master];\r\nGO\r\n\r\nBACKUP DATABASE [WideWorldImporters]\r\n     TO\u00a0 DISK = N'C:\\Backups\\WWI_Diff.bak'\r\n     WITH\u00a0 DIFFERENTIAL ,\r\n     INIT,\r\n     STATS = 10;\r\nGO\r\n<\/pre>\n
Next, restore the differential on the replica (BUCKY):<\/p>\n
\r\nUSE [master];\r\nGO\r\n\r\nRESTORE DATABASE [WideWorldImporters]\r\n     FROM\u00a0 DISK = N'C:\\Backups\\WWI_Diff.bak'\r\n     WITH\u00a0 FILE = 1,\r\n     NORECOVERY,\r\n     STATS = 5;\r\nGO\r\n<\/pre>\n
Finally, take a log backup on the primary (CAP):<\/p>\n
\r\nUSE [master];\r\nGO\r\n\r\nBACKUP LOG [WideWorldImporters]\r\n     TO\u00a0 DISK = N'C:\\Backups\\WWI_Log.trn'\r\n     WITH NOFORMAT,\r\n     INIT,\r\n     STATS = 10;\r\nGO\r\n<\/pre>\n
And then restore that log backup on the replica (BUCKY):<\/p>\n
\r\nUSE [master];\r\nGO\r\n\r\nRESTORE LOG [WideWorldImporters]\r\n     FROM\u00a0 DISK = N'C:\\Backups\\WWI_Log.trn'\r\n     WITH\u00a0 FILE = 1,\r\n     NORECOVERY,\r\n     STATS = 5;\r\nGO\r\n<\/pre>\n
At this point, the database is re-initialized and ready to be added back to the Availability Group.<\/p>\n
Now, when I ran into this the other day, I also wanted to apply a startup trace flag to the primary replica and restart the instance.\u00a0 I also wanted to make sure that the AG wouldn\u2019t try to failover when the instance restarted, so I temporarily changed the primary to manual failover (executed on CAP, screenshot for reference):<\/p>\n
\r\nUSE [master];\r\nGO\r\n\r\nALTER AVAILABILITY GROUP [TestLocation]\r\n     MODIFY REPLICA ON N'CAP\\ROGERS' WITH (FAILOVER_MODE = MANUAL);\r\nGO\r\n<\/pre>\n
\"Change<\/a>
Change Failover Mode for the AG Temporarily<\/figcaption><\/figure>\n
I restarted the instance, confirmed my trace flag was in play, and then changed the FAILOVER_MODE back to automatic:<\/p>\n
\r\nUSE [master];\r\nGO\r\n\r\nALTER AVAILABILITY GROUP [TestLocation]\r\n     MODIFY REPLICA ON N'CAP\\ROGERS' WITH (FAILOVER_MODE = AUTOMATIC);\r\nGO\r\n<\/pre>\n
The last step is to join the WideWorldImporters database on the replica back to the AG:<\/p>\n
\r\nALTER DATABASE [WideWorldImporters]\r\n     SET HADR AVAILABILITY GROUP = TestLocation;\r\nGO\r\n<\/pre>\n
After joining the database back to the AG, be prepared to wait for the databases to synchronize before things look healthy.\u00a0 Initially I saw this:<\/p>\n
\"Secondary<\/a>
Secondary database joined to AG, but not synchronized<\/figcaption><\/figure>\n
Transactions were still occurring on the primary between the time of the log being applied on the secondary (BUCKY) and the database being joined to the AG from the secondary.\u00a0 You can check the dashboard to confirm this:<\/p>\n
\"Secondary<\/a>
Secondary database added to AG, transactions being replayed on secondary<\/figcaption><\/figure>\n
Once the transactions had been replayed, everything was synchronized and healthy:<\/p>\n
\"Databases<\/a>
Databases synchronized (dashboard on primary)<\/figcaption><\/figure>\n
\"Databases<\/a>
Databases synchronized (connected to secondary)<\/figcaption><\/figure>\n
Once the databases are synchronized, make sure to re-enable <\/strong>the jobs that run differential and log backups on the primary (CAP).\u00a0 In the end, removing a database from a replica in an Availability Group (and then adding it back) is probably not something you will need to do on a regular basis.\u00a0 This is a process worth practicing in a test environment at least once, so you’re comfortable with it should the need arise.<\/p>\n","protected":false},"excerpt":{"rendered":"
I recently had a scenario in a two-node Availability Group where multiple large-batch modification queries were executed and created a large redo queue on the replica.\u00a0 The storage on the replica is slower than that on the primary (not a desired scenario, but it is what it is) and the secondary has fallen behind before, […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[51,40],"tags":[],"yoast_head":"\nRemoving a database from a replica in an Availability Group - Erin Stellato<\/title>\n<meta name=\"description\" content=\"Removing a database from a replica in an Availability Group and then adding it back is an atypical event, but it's possible to do.\" \/>\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\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Removing a database from a replica in an Availability Group - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"Removing a database from a replica in an Availability Group and then adding it back is an atypical event, but it's possible to do.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2018-10-16T14:00:05+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-10-16T02:34:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/10\/AG_initial.png\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/\",\"name\":\"Removing a database from a replica in an Availability Group - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2018-10-16T14:00:05+00:00\",\"dateModified\":\"2018-10-16T02:34:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"Removing a database from a replica in an Availability Group and then adding it back is an atypical event, but it's possible to do.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Removing a database from a replica in an Availability Group\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Removing a database from a replica in an Availability Group - Erin Stellato","description":"Removing a database from a replica in an Availability Group and then adding it back is an atypical event, but it's possible to do.","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\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/","og_locale":"en_US","og_type":"article","og_title":"Removing a database from a replica in an Availability Group - Erin Stellato","og_description":"Removing a database from a replica in an Availability Group and then adding it back is an atypical event, but it's possible to do.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/","og_site_name":"Erin Stellato","article_published_time":"2018-10-16T14:00:05+00:00","article_modified_time":"2018-10-16T02:34:48+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/10\/AG_initial.png"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/","name":"Removing a database from a replica in an Availability Group - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2018-10-16T14:00:05+00:00","dateModified":"2018-10-16T02:34:48+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"Removing a database from a replica in an Availability Group and then adding it back is an atypical event, but it's possible to do.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/removing-a-database-from-a-replica-in-an-availability-group\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Removing a database from a replica in an Availability Group"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/956"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=956"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/956\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=956"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=956"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=956"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}