How to Synchronize Analysis Services Database Using Synchronize Database Wizard in SSMS

Nov 25, 2012


In this article we will take a look at how to Synchronize Analysis Services Database using Synchronize Database Wizard to make two analysis services databases identical by copying the metadata and data between source and the destination SSAS database. This feature is available in SQL Server 2005 and higher versions of SQL Server.

Important Note: - During the synchronization process, the users can continue to query the Destination SSAS database. Once the synchronization is completed the analysis services will automatically switch the users to the newly synchronized data and the metadata. Finally, analysis services will drop the old data from the destination analysis services database.

Best Practice for Synchronizing Analysis Services Database

  • If you have a very large Analysis Services Database i.e., greater than 10 GB then synchronization will be faster if you drop the destination SSAS database and then synchronize the SSAS database between source and destination.
  • Generate XMLA script which can be used synchronize the SSAS database between source and destination.
  • If the synchronization process is taking long time to complete then it is recommended to Use Analysis Services Database Backup and Restore Method or Use SSAS Detach and Attach Method (Detach the SSAS DB, Copy the files to the new server and then attach the SSAS Database).

How to Use Synchronize Database Wizard

1. Open SQL Server Management Studio and connect to the Analysis Services Instance where Destination SSAS Database is hosted.

2. In Object Explorer, right click Databases Folder as shown in the snippet below and then choose Synchronize… from the drop down.

How to Synchronize Analysis Services Database in SQL Server

3. The Synchronize Database Wizard synchronizes two Analysis Services databases located on different servers. The wizard copies changes to data and Meta data from a source analysis server to a destination server. Using this wizard you can also replace the security settings on the destination server with those on the source server. In Synchronize Database Wizard screen click Next to continue with the synchronization.

Synchronize Database Wizard to Synchronize Analysis Services Database

4. In Select Database to Synchronize screen you need to choose the Source Server and the Source SSAS Database from where the data and the Meta data will be replaced on the destination server. Click Next to continue with the Synchronization.

Select Source Database to Synchronize

5. In Specify Locations for Local Partitions Wizard screen, you can change the measure group partitions to different drives if desired. Click Next to continue with the Synchronization Database Wizard.

Specify Locations for Local Partitions

6. In Synchronization Options Wizard screen, you can specify security and compression settings. Different options available for Security roles and members are:-

  • Copy all: - All roles and members will be copied to the destination database. Any existing role and membership data will be replaced.
  • Skip Membership: - Only roles will be copied to the destination database, leaving members intact. This operation can result in addition or deletion of roles on the destination server.
  • Ignore all: - No security information will be copied. Any existing roles and members remain unaffected.

Important Note:- One can select the check box “Use compression when synchronizing databases” to use compression feature while synchronizing analysis services database.

Analysis Database Synchronize Options in Synchronize Database Wizard

7. In Select Synchronization Method wizard screen there are options available to Synchronize the analysis database immediately or to save the Synchronization XMLA scripts for future usage.

Gennerate XMLA Script for Synchronize SSAS Database

8. In Completing the wizard screen you will be able to see a quick summary of all the selections you have done so far. To close the Synchronization Database Wizard click Finish.

Complete Analysis Services Database Synchronization

XMLA Script to Synchronize Analysis Services Database

<Synchronize xmlns:xsi="" xmlns:xsd="" xmlns="">
ConnectionString>Provider=MSOLAP.4;Data Source=SOURCESERVER;ConnectTo=10.0;Integrated Security=SSPI;Initial Catalog=MyTechMantra</ConnectionString>

Reference: Synchronize Analysis Services Databases

Continue Free Learning...

  • Please leave below your valuable feedback for this arSticle.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…

Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."