Database ReadWriteModes

 

Applies To: SQL Server 2016

There are often situations when an Analysis Services database administrator (dba) wants to change a read/write database to a read-only database, or vice versa. These situations are often driven by business needs, such as sharing the same database folder among several servers for scaling out a solution and improving performance. For these situations, the ReadWriteMode database property enables the Analysis Services dba to easily change the database operating mode.

The ReadWriteMode database property specifies whether the database is in read/write mode or in read-only mode. These are the only two possible values of the property. When the database is in read-only mode, no changes or updates can be applied to the database. However, when the database is in read/write mode, changes and updates can occur. The ReadWriteMode database property is defined as a read-only property; it can only be set through an Attach command.

When a database is in read-only mode, certain restrictions are in place that affect the ordinary set of allowed operations to the database. See the following table for the restricted operations.

ReadOnly modeRestricted operations
XML/A commands

 

Note: An error is raised when you execute any one of these commands.
Create

 Alter

 Delete

 Process

 MergePartitions

 DesignAggregations

 CommitTransaction

 Restore

 Synchronize

 Insert

 Update

 Drop

 

Note: Cell writeback is allowed in databases set to read-only; however, the changes cannot be committed.
MDX statements

 

Note: An error is raised when you execute any one of these statements.
COMMIT TRAN

 CREATE SESSION CUBE

 ALTER CUBE

 ALTER DIMENSION

 CREATE DIMENSION MEMBER

 DROP DIMENSION MEMBER

 ALTER DIMENSION

 

Note: Excel users cannot use the grouping feature in Pivot tables, because that feature is internally implemented by using CREATE SESSION CUBE commands.
DMX statements

 

Note: An error is raised when you execute any one of these statements.
CREATE [SESSION] MINING STRUCTURE

 ALTER MINING STRUCTURE

 DROP MINING STRUCTURE

 CREATE [SESSION] MINING MODEL

 DROP MINING MODEL

 IMPORT

 SELECT INTO

 INSERT

 UPDATE

 DELETE
Background operationsAny background operations that would modify the database are disabled. This includes lazy processing and proactive caching.

The ReadWriteMode database property is to be used as part of an Attach database command. The Attach command allows the database property to be set to either ReadWrite or ReadOnly. The ReadWriteMode database property value cannot be updated directly because the property is defined as read-only. Databases are created with the ReadWriteMode property set to ReadWrite. A database cannot be created in read-only mode.

To switch the ReadWriteMode database property between ReadWrite and ReadOnly, you must issue a sequence of Detach/Attach commands.

All database operations, with the exception of Attach, keep the ReadWriteMode database property in its current state. For example, operations like Alter, Backup, Restore, and Synchronize preserve the ReadWriteMode value.

System_CAPS_ICON_note.jpg Note


Local cubes can be created from a read-only database.

Detach
Attach and Detach Analysis Services Databases
Move an Analysis Services Database
Detach Element
Attach Element

Community Additions

ADD
Show: