Administering and Monitoring Analysis Services - SQL Server Analysis Services 2012 Cube Development - page 299

Detaching and attaching databases
You are now familiar with backup and restore as well as synchronization methods
for scaling out SSAS solutions. However, as you learned in the previous section, syn-
chronization is single threaded and must transfer only one file at a time. Fortunately,
you can use the Detach and Attach features along with the Robocopy command
for copying files to ensure the faster delivery of files from the source to target instance.
When you detach a database, SSAS leaves the data folder in its current location and
creates a detach logfile. You can copy the entire data folder to another host or another
instance's data folder and reattach the database. Keep in mind, however, that unlike
synchronization, the Detach and Attach methods do not copy the data for you; you
must come up with your own routine (script) for copying data. Additionally, you can
attach a previously detached database in the read/write mode to only one instance.
For all other instances, you must attach the database in the read-only mode. For most
environments, this is acceptable because large databases rarely use write-back, and
data must be processed on only one instance.
The Detach and Attach operations simply update the server metadata; they don't
have to copy files or change security. Hence, both are very fast.
How to do it...
To detach a database, perform the following steps:
1. Connect to the SSAS database of interest using SSMS, right-click on the data-
base, and choose Detach . This activates the Detach Database dialog.
2. You can specify the password that will be used for encrypting the detached
database. As with the backup command, document the password in a secure
location if you intend to encrypt the database. You can script the Detach com-
mand—it will look similar to the following code snippet:
<Detach xmlns =
"http://schemas.microsoft.com/
analysisservices/2003/engine">
<Object>