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!
