Lesson Learned #331: ALTER DATABASE XXX SET READ_ONLY WITH NO_WAIT

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:

 

SET NOCOUNT ON; DECLARE @Schema sysname, @Table sysname; DECLARE @SQL AS VARCHAR(4000) DECLARE @Name as varchar(4000) DECLARE tcursor CURSOR FOR SELECT SCHEMA_NAME(schema_id), Name from sys.tables ORDER BY 1,2 OPEN tcursor FETCH NEXT FROM tcursor INTO @Schema, @Table WHILE @@FETCH_STATUS = 0 BEGIN SET @Name = convert(varchar(4000),newid()) SET @SQL = 'CREATE TRIGGER [' + @Name + ']' SET @SQL = @SQL + ' ON [' + @Schema + '].[' + @Table + ']' SET @SQL = @SQL + ' for update,insert,delete' SET @SQL = @SQL + ' AS ' SET @SQL = @SQL + ' PRINT '' Table in Readonly''' SET @SQL = @SQL + ' rollback' PRINT @SQL FETCH NEXT FROM tcursor INTO @Schema, @Table END CLOSE tcursor; DEALLOCATE tcursor;

 

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.