Lesson Learned #331: ALTER DATABASE XXX SET READ_ONLY WITH NO_WAIT
Published Feb 17 2023 02:50 AM 2,184 Views

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!

Version history
Last update:
‎Feb 17 2023 02:50 AM
Updated by: