Share via

Switch an Analysis Services database between ReadOnly and ReadWrite modes

  • Article

There are often situations when a Analysis Services database administrator (dba) wants to change the read/write mode of a tabular or multidimensional database. These situations are often driven by business needs, such as sharing the database among a pool of Analysis Services servers for a better user experience.

A database mode can be switched in many ways. This document explains the following common scenarios:

  • Interactively using SQL Server Management Studio

  • Programmatically using AMO

  • By script using XMLA

Procedures

To switch the read/write mode of a database interactively using Management Studio

  1. Locate the database to be switched in the left or right pane of Management Studio.

  2. Right-click the database and select Properties. Find the database folder and note the location. An empty database storage location indicates that the database folder is located in the server data folder.

    Important

    As soon as the database is detached, Management Studio can no longer help you obtain the database location.

  3. Right-click the database and select Detach...

  4. Assign a password to the database to be detached, and then click OK to execute the detach command.

  5. Locate the Databases folder in the left or right pane of Management Studio.

  6. Right-click the Databases folder and select Attach...

  7. In the folder text box, type the original location of the database folder. Alternatively, you can use the browse button (...) to locate the database folder.

  8. Select the read/write mode for the database.

  9. Type the password that was used in step 3 and click OK to execute the attach command.

To switch the read/write mode to a database programmatically using AMO

  1. In your C# application, adapt the following sample code and complete the indicated tasks.

private void SwitchReadWrite(Server server, string dbName,

ReadWriteMode dbReadWriteMode)

{

if (server.Databases.ContainsName(dbName))

{

Database db;

string databaseLocation;

db = server.Databases[dbName];

databaseLocation = db.DbStorageLocation;

if (databaseLocation == null)

{

string dataDir = server.ServerProperties["DataDir"].Value;

String[] possibleFolders = Directory.GetDirectories(dataDir, string.Concat(dbName,"*"), SearchOption.TopDirectoryOnly);

if (possibleFolders.Length > 1)

{

List<String> sortedFolders = new List<string>(possibleFolders.Length);

sortedFolders.AddRange(possibleFolders);

sortedFolders.Sort();

databaseLocation = sortedFolders[sortedFolders.Count - 1];

}

else

{

databaseLocation = possibleFolders[0];

}

}

db.Detach();

server.Attach(databaseLocation, dbReadWriteMode);

}

}

  1. In your C# application, invoke SwitchReadWrite() with the necessary parameters.

  2. Compile and execute your code to move the database.

To switch the read/write mode to a database by script using XMLA

  1. Locate the database to be switched in the left or right pane of Management Studio.

  2. Right-click the database and select Properties. Find the database folder and note the location. An empty database storage location indicates that the database folder is located in the server data folder.

    Important

    As soon as the database is detached, Management Studio can no longer help you obtain the database location.

  3. Open a new XMLA tab in Management Studio.

  4. Copy the following script template for XMLA:

<Detach xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>%dbName%</DatabaseID>

<Password>%password%</Password>

</Object>

</Detach>

  1. Replace %dbName% with the name of the database and %password% with the password. The % characters are part of the template and must be removed.

  2. Execute the XMLA command.

  3. Copy the following script template for XMLA in a new XMLA tab

<Attach xmlns="https://schemas.microsoft.com/analysisservices/2003 /engine ">

<Folder>%dbFolder%</Folder>

<ReadWriteMode xmlns="https://schemas.microsoft.com/analysisservices/2008/engine/100">%ReadOnlyMode%</ReadWriteMode>

</Attach>

  1. Replace %dbFolder% with the complete UNC path of the database folder, %ReadOnlyMode% with the corresponding value ReadOnly or ReadWrite, and %password% with the password. The % characters are part of the template and must be removed.

  2. Execute the XMLA command.

See Also

Attach*
Microsoft.AnalysisServices.Database.Detach*
Attach and Detach Analysis Services Databases
Database Storage Location
Database ReadWriteModes
Attach Element
Detach Element
ReadWriteMode Element
DbStorageLocation Element