• Article

Synchronizing Analysis Services Databases

The Synchronize Database Wizard synchronizes two Microsoft SQL Server Analysis Services databases by copying the data and metadata from a database on a source server to a database on a destination server. You can also use this wizard to deploy a database from a staging server onto a production server, or to synchronize a database on a production server with the changes made to the data and metadata in a database on a staging server.

While the wizard synchronizes the data between the two databases, users can continue to query the destination database. After synchronization finishes,, Analysis Services automatically switches the users to the newly copied data and metadata, and drops the old data from the destination database.

Note

The Synchronize Database Wizard can also generate a script that can be run in the future to synchronize the databases.

You can use the Synchronize Database Wizard, or the generated synchronization script, together with Microsoft Windows Failover Clustering and Network Load Balancing (NLB) to increase the availability and scalability of your Analysis Services databases and cube. For more information about Windows Clustering and NLB, see Clustering Services under Technology Centers on the Microsoft Windows Server 2003 Web site.

Running the Synchronize Database Wizard

Besides letting you select the server and database from which data and metadata will be copied, the Synchronize Database Wizard also lets you change the location for a set of local partitions or a set of remote partitions. For example, if a set of partitions are installed on the Drive C of the source server, the wizard lets you copy this set of partitions to a different location on the destination server. If you do not change the default locations, the wizard deploys the measure group partitions within each cube on the source server to the same locations on the destination server. Similarly, if the source server uses remote partitions, the same remote partitions will be used on the destination server.

The Synchronize Database Wizard gives you the following synchronization options:

  • Security Synchronization Method   By default, the wizard synchronizes all data and metadata, other than membership in existing security groups. You can also copy all security settings or ignore all security settings when synchronizing the data and metadata.

  • Compress While Synchronizing   By default, the wizard compresses all data and metadata during synchronization.

For information about how to run the Synchronize Database Wizard, see the following procedure.

To run the Synchronize Database Wizard

  1. In SQL Server Management Studio, connect to an instance of Analysis Services.

  2. In Object Explorer, right-click the database to be synchronized, and then click Synchronize.

    Note

    The database selected in Object Explorer becomes the destination database. You use the Synchronize Database Wizard to select the source database.

For more information about subsequent steps in the Synchronize Database Wizard, see How to: Synchronize an Analysis Services Database.