Going in Production - Expert Cube Development with SSAS Multidimensional Models - page 311

Copying databases between servers
There are several scenarios where we may need to copy an Analysis Services database
from one server to a different server. Here are a couple of examples:

• We have several frontend servers directly accessible by users in the DMZ

area of the network and one backend server inside the internal network.
When the cube is processed on the internal server, it needs to be duplicated
to the frontend servers so it can be queried by our users.

• We have two powerful Analysis Services servers and several cubes. Half of

the cubes are processed on one server and half are processed on the other.
When both servers have finished processing, the databases are synchronized
between the two servers so they both have the same data and can share the
query load.

Using Analysis Services, we have three options to synchronize data between servers:

• Use the Synchronize XMLA command. This is the preferred method in most

situations. When executed, the Synchronize command tells one Analysis
Services instance to synchronize a database with a database on another
instance. During the synchronization process, Analysis Services checks
for any differences between the version of the database on the destination
database and the version on the source server. Updated objects are copied
over the network from the source server to the destination server until the
destination is identical to the source.

• The XMLA needed for a Synchronize command can be generated in SQL

Management Studio by right-clicking on the Databases node of an instance
in the Object Explorer pane and running the Synchronize Database
wizard at the end, choosing to generate a script rather than running the
synchronization immediately. This XMLA command can be run from an
Execute Analysis Services DDL Task in Integration Services.

• The synchronization operation might be slow in situations where there

is a lot of data in a cube or where there are a lot of differences between the
source and the destination. However, the major advantage of using this
approach is that users can continue to query the destination server while
synchronization takes place, although when synchronization has completed,
there will be a (usually short) period where new users cannot connect and
new queries cannot be run, while the old version of the database is replaced
by the new version. Note that synchronization cannot be used at the same
time as lazy aggregation building is taking place because there is a risk of
database corruption.