Edit

Database ReadWriteModes

  • Article

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

There are often situations when an SQL Server 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 SQL Server Analysis Services dba to easily change the database operating mode.

ReadWriteMode database property

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 mode Restricted 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 operations Any background operations that would modify the database are disabled. This includes lazy processing and proactive caching.

ReadWriteMode Usage

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.

Note

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

See Also

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