Synchronize Analysis Services Databases
The Synchronize Database Wizard makes two Analysis Services databases equivalent by copying the data and metadata a database on a source server to a database on a destination 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.
Use the Synchronize Database to accomplish any of the following tasks:
Deploy a database from a staging server onto a production server.
Synchronize a database on a production server with the changes made to the data and metadata in a database on a staging server.
Generate XMLA 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.
Running the Wizard
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.
To use the wizard, you must be an Analysis Services administrator (that is, a member of the Server role) to perform this operation.
To run the Synchronize Database Wizard
In SQL Server Management Studio, connect to the instance of Analysis Services that will run the destination database.
In Object Explorer, right-click the Databases folder, then click Synchronize.
In the Synchronize Database Wizard dialog box, type the name of the source server and source database in the appropriate fields.
Synchronization will occur for source and destination databases that have the same name. If the destination server already has a database that shares the same name as the source database, the destination database will be updated with the metadata and data of the source. If the database does not exist, it will be created on the destination server. Click Next.
Optionally, change location for local partition and click Next.
If a set of partitions are installed on 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.
Choose the security synchronization method you want and click Next. By default, the wizard compresses all data and metadata during synchronization.
Click Finish to synchronize. After verifying the options on the Completing the Wizard page, click Finish again.
See Also
Reference
Concepts
Deploy Model Solutions Using XMLA