June 4, 2022 at 6:07 am
Hi,
I have a large database within an 2016 AOAG that needs to be refreshed quite in a test environment from a copy in production.
In order to minimise restore time due to tight maintenance windows, i was looking at an option to restore a copy of the database alongside the existing database in a non recovery state.
Once in a position to refresh the database , https://omegle.website/ i was hoping i could drop the original database, bring online the copy , rename the db and join to the AOAG saving restore time on the maintenance window.
Points i have learnt https://ome-tv.org/ ...
I understand you cannot rename a database in NORECOVERY state
I understand you cannot rename a database whilst in AOAG
I understand you cannot reattach a database in a NORECOVERY state
Has anyone found themselves in a similar position with a large database, where they have thought of side loading a database copy to the AOAG in parallel and then replacing the original
database with a renamed copy thus saving time on the day, restoring across all nodes ?
Just wondered if anyone had experienced this and had a valid approach that may work for me ?
Thanks
James
https://cpstest.uno/
June 5, 2022 at 6:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 5, 2022 at 2:28 pm
I don't know of any way to do what you are asking - nor would I even try doing that. I would look at ways to improve the performance of the backups and restores so that something like this isn't needed.
I have a process that refreshes a copy of live to a 'test' system once a week. The database is more than 6TB and restores in less than an hour with the secondary taking no more than 1 to 2 hours to be available through automatic seeding.
Take a look at the backup/restore parameters BUFFERCOUNT and MAXTRANSFERSIZE. Modifying these from the default values can improve your backups as well as your restores. Before setting these - the process would take up to 6 hours to restore that database.
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
June 10, 2022 at 11:24 am
What you suggest could work right up to the point when you join the AG.
You will have to drop the old DB from the AG before you do the rename, and add the new DB into the AG. Then you need to do the normal process of reinitialising the DB on the AG secondaries.
If you somehow managed to rename the DB without dropping the old one from the AG, the AG would notice the change in DB number and the LRSN. This would prevent the secondary from being updated, and if you are unlucky it might stall the entire AG.
This situation is a good case for keeping up with SQL version updates. For SQL 2017 and above, things become a bit easier as you have auto-synchronisation. For SQL 2017+, drop the old DB from the AG and restore your DB replacing the old one. Then drop the secondary DBs and add the primary back into the AG. The auto-sync will then refresh the secondaries.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply