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

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.

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 SSAS Database Using Database Wizard in SSMS

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

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

Step 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.

Step 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.

Trending Analysis Services Articles and Tips

• How to Detach SSAS Database in SQL Server
• How to Attach SSAS Database in SQL Server
• How to Synchronize Analysis Services Database Using Synchronize Database Wizard in SSMS
• How to Restore Analysis Services Database in SQL Server Using SQL Server Management Studio
• How to Automate Backup of Analysis Services Database Using SQL Server Agent Job
• How to Backup Analysis Services Database in SQL Server Using SQL Server Management Studio
• Database Consistency Checker DBCC CHECK For Analysis Services Database in SQL Server 2016 for Tabular and Multidimensional Databases

Step 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.

Click the below Number Buttons to continue reading the rest of the article.