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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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
Thanks for the info. I had this problem too. But thanks to good help and professional forums I was able to get an answer to my question and solve my problem.
If you have problems with writing, it can help you https://papersowl.com/apa-paper-writing-service because it is a quality APA paper writing service.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply