Lets say a database was removed from AG for maintenance purpose. The primary node would have the w/r copy of it and secondary would have it in restoring state. What would be the best way to put it back in the AG again after maintenance? From primary --> add to AG and then join?
February 12, 2021 at 10:25 pm
Since you are on 2014 - the only method will be to backup/restore - apply tlog backups - and join. If you were on 2016 or higher you could set it to automatic seeding and then it would just be a matter of adding the database back to the AG and it would (should) automatically synchronize and join.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 17, 2021 at 2:51 pm
Thank you.
so the process would be as below then?
Remove the db from AG.
Delete the db that is in restoring state from secondary.
Add the db from primary into AG which would back up and restore to secondary
February 17, 2021 at 3:21 pm
You don't have to delete the database on the secondary - you can recover the database and then restore.
You can have SQL Server perform the backup/restore for you...or you can do each step yourself.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 19, 2021 at 3:18 pm
Thank you. I will need to create a test lab to try it. One more question. After step 3 'restore with recovery' is performed we would have two copies of DB, one in each node. So, after performing backup on primary (db and tlog) do we overwrite the database in secondary when restoring? If so, should it be done in no-recovery mode for both db and t-log?
Also, when backup is taken from primary for both db and t-log would a copy-only backup work as well?
February 19, 2021 at 4:17 pm
Definitely test the process...
After bring the database online on the secondary - yes, you will have both databases available, but since it has been removed from the AG this won't cause any issues. When you restore - restore over the existing database using REPLACE and NORECOVERY. It has to be done in NORECOVERY so it can be added to the AG (joined).
COPY_ONLY backups work just fine, as long as the process you are using to restore does not include a differential backup. I have rebuilt AG's on very large databases using a full backup, a differential - and the latest transaction log backups. This allows for copying that large backup file across the network and restoring before a migration/cutover.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 21, 2021 at 5:19 am
Lets say a database was removed from AG for maintenance purpose. The primary node would have the w/r copy of it and secondary would have it in restoring state. What would be the best way to put it back in the AG again after maintenance? From primary --> add to AG and then join?
What maintenance activity you are talking about? I was able to remove and add the secondary database in 2014 without any issue by 'Join to Availability Group'
February 24, 2021 at 3:40 pm
In 2014 environment, when I tried that once it didn't work. In that case, the secondary database showed in "restoring state". I ended up getting rid of the secondary and then added the db (backup and restore through ssms) to AG. So, wanted to verify if that is what needs to be done. Jeffrey suggests that a regular join won't work in 2014 and has a process outlined above.
When testing, I removed the db from AG. The db showed as "not synchronized" in the secondary. But I was able to add back to AG with join only (ssms skipped the verification process).
Perhaps, when it is in "restoring" state (say the db gets kicked out of AG for some reason) then the join wouldn't work?
If you remove a database from the AG - and then that database back before a new transaction log backup has been run, then SQL Server can synchronize that database and it will be added.
As soon as a new log backup is done - you cannot synchronize, which then leads to having to perform a new backup/restore process including all transaction logs from that backup to current point in time.
From 2016 forward - we now have automatic seeding which gets around the above limitation.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 24, 2021 at 9:10 pm
Thank you! That clarifies a lot. It must have been that there was a new log backup taken by the backup software last time it didn't work for me. Great to know.
February 27, 2021 at 1:52 pm
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply