Hello experts,
TL;DR: I'd like advice on how to tweak my AOAG restore script to avoid the remove/offline/drop errors I run into on the secondaries.
I've been learning how to work with AOAG for a couple of years. I have a pretty good grasp of how to add, remove, and restore databases manually.
But when I try to script the restores, I always seem to run into odd wrinkles that cause the process to take longer than expected. Below I have listed a schematic version of what I script. It leaves out several parts including where I try to capture existing permissions to reapply after the restore, and using a couple of cursors (I know, I know) to loop through the script in order to restore a whole set of databases in an AG.
-- 1. Primary Node: Remove the databases from AG.
ALTER AVAILABILITY GROUP [myag01] REMOVE DATABASE [MyDB];
-- 2. Secondary Node: Set secondary database offline.
ALTER DATABASE [MyDB]
SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- 3. Secondary Node: Drop secondary databases.
DROP DATABASE [MyDB];
-- 4. Primary Node: Restore database.
RESTORE DATABASE...
-- 5. Primary Node: Back up database and log to shared folder.
BACKUP DATABASE...
BACKUP LOG...
-- 6. Primary Node: Add database back to AG.
ALTER AVAILABILITY GROUP [myag01] ADD DATABASE [MyDB];
I got the general approach from this SQL tip:
But when I try to run the part of the script that sets the databases offline, I get this error on the secondary:
ALTER DATABASE failed because a lock could not be placed on database ...
or
Msg 1468, Level 16, State 3, Line 81
The operation cannot be performed on database "MyDB" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Similarly, when I refresh the database list in SSMS, I see either "(Not Synchronized)" or "(Restoring...)" and can't figure out any consistent reason why the database ends up in one state or the other.
Basically, at this point the "script" reverts to semi-manual commands in SSMS and even manually setting databases offline and manually dropping the secondary databases in order to proceed.
Thanks for any help - I feel like I am missing something obvious.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
I think the problem is going to be in the details that you have left out - but I do have some comments.
There is no reason to drop the database on the secondary if you are going to be adding it back to the system - the restore will overwrite the existing database files, and can be faster because those files already exist and don't need to be recreated.
Since you are on 2016 you can utilize automatic seeding. With automatic seeding there is no need to restore the database on the secondary. Restore the database to the primary - and add that database to the AG with automatic seeding and SQL Server will build out the database on the secondary for you.
The process could be as simple as:
SQL Server will then synchronize everything...worse case, you have to drop the database on the secondary and let SQL Server rebuild it for you. Shouldn't be necessary though...unless you are restoring a different database but naming it the same as a previously existing database and I cannot think of any good reason for that type of process.
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
May 21, 2021 at 3:58 pm
Hi Jeffrey,
Wow, thanks! I will try out your suggestion - if it works, it would definitely be a lot easier not to try to drop the databases or restore anything to the secondary.
Thanks again!
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply