March 11, 2020 at 11:29 pm
Hi,
I have a mix of SQL Server 2012 and 2016 instances. Databases on those instances belong to various availability groups. We a doing a migration to another datacentre. Using availability groups to do the switchover was discarded due to some hardware/network limitations before I joined the company. We are planning to use custom made log shipping to do the switchover.
I was hoping to switch the databases to read-only just before the switchover, but got this message:
ALTER DATABASE [MyDB] SET READ_ONLY WITH NO_WAIT
GO
Msg 1468, Level 16, State 1, Line 5
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.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.
What is the best way to prevent writing to a database on the primary replica without removing it from an Availability Group?
Thanks.
March 12, 2020 at 11:32 am
Close off the connections or restrict access to the database would be the best guess I have. Probably you just need to plan on tearing down & rebuilding the AG on the other side after the migration.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2020 at 12:03 pm
is your reason for switching to read only mode just to preserve the original database?
at that point the AG becomes useless anyway - so break the AG.. if you can validate the log shipping is all good then not only do you have the original database, but also the new database (and hopefully backups + all the tlog backups from logshipping)
theen put your new AG on the new server - iff all does wrong then fail back and rebuild the AG on the old server
MVDBA
January 31, 2021 at 11:31 am
This was removed by the editor as SPAM
September 28, 2021 at 2:09 pm
This was removed by the editor as SPAM
March 29, 2023 at 12:19 pm
This was removed by the editor as SPAM
April 7, 2023 at 12:09 pm
This was removed by the editor as SPAM
April 21, 2023 at 5:29 am
This was removed by the editor as SPAM
April 25, 2023 at 6:18 pm
This was removed by the editor as SPAM
December 20, 2023 at 2:40 pm
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply