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.
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.
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.
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.
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.
ALSO READ ... HOW TO BACKUP AN ANALYSIS SERVICES DATABASE USING SSMS
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.
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.
XMLA Script to Synchronize Analysis Services Database
<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...
Geeks who read this article also read…
- How to Backup an Analysis Services Database Using SQL Server Management Studio
- How to Restore an Analysis Services Database Using SQL Server Management Studio
- Automate Backup of Analysis Services Database Using SQL Server Agent Job
- How to Attach Analysis Services Database in SQL Server
- How to Detach Analysis Services Database in SQL Server
- Performance Dashboard Reports in SQL Server 2012
- How to Backup Database in SQL Server
- How to Restore Database in SQL Server
- How to Attach Database Without a Transaction Log File in SQL Server
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…