This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
Today, we got a service request that our customer reported that they need to change to readonly a database in Azure SQL Managed Instance using the TSQL: ALTER DATABASE xxx SET READ_ONLY WITH NO_WAIT. Currently, this option is not supported and we have many other options to have a database in readonly, like ReadScale for Business Critical, Replication, etc.. However, our customer needs to use the same Azure SQL Managed Instance.
When our customer executes the following TSQL ALTER DATABASE XXX SET READ_ONLY WITH NO_WAIT they got the following error message: Msg 5008, Level 16, State 11, Line 3 This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again. Msg 5069, Level 16, State 1, Line 3 ALTER DATABASE statement failed.
We an example and workaround, we might have two options:
- Implement a trigger server DDL - Lesson Learned #309: Create a DDL Trigger on Database in Azure SQL - Microsoft Community Hub
- And for tables we could use, as an example, the following script:
If any user tries to delete, insert or update any data they are going to have the following error message: Table in Readonly
Msg 3609, Level 16, State 1, Line 8 The transaction ended in the trigger. The batch has been aborted.
Enjoy!