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

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:
  

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!

Continue reading...