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

Click here to read “How to Synchronize Analysis Services Database Using Synchronize Database Wizard in SSMS” article from the beginning.

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

• [do_widget_area gridlove_default_sidebar]
• [do_widget_area gridlove_default_sticky_sidebar]
  • [do_widget id="mks_ads_widget-22"]
• [do_widget_area gridlove_footer_sidebar_1]
  • [do_widget id="text-3"]
  • [do_widget id="mks_social_widget-3"]
• [do_widget_area gridlove_footer_sidebar_2]
  • [do_widget id="text-4"]
• [do_widget_area gridlove_footer_sidebar_3]
  • [do_widget id="nav_menu-1"]
• [do_widget_area gridlove_footer_sidebar_4]
  • [do_widget id="text-5"]
• [do_widget_area gridlove_header_sidebar]
  • [do_widget id="mks_social_widget-1"]
  • [do_widget id="gridlove_posts_widget-3"]
• [do_widget_area gridlove_sidebar_1]
  • [do_widget id="mks_social_widget-4"]
• [do_widget_area gridlove_sidebar_2]
• [do_widget_area gridlove_sidebar_3]
• [do_widget_area gridlove_sidebar_4]
• [do_widget_area gridlove_sidebar_6]
• [do_widget_area gridlove_sidebar_7]
• [do_widget_area widgets_for_shortcodes]
  • [do_widget id="arpw-widget-2"]
  • [do_widget id="text-6"]
  • [do_widget id="mks_ads_widget-11"]
  • [do_widget id="mks_ads_widget-13"]
  • [do_widget id="mks_ads_widget-15"]
• [do_widget_area wp_inactive_widgets]
  • [do_widget id="custom_html-2"]
  • [do_widget id="gridlove_posts_widget-2"]
  • [do_widget id="gridlove_posts_widget-7"]
  • [do_widget id="gridlove_posts_widget-6"]
  • [do_widget id="custom_html-3"]

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

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

Recommendation: Perform Database Consistency Checker DBCC CHECK For Analysis Services Database in SQL Server 2016 for Tabular and Multidimensional Databases after successfully Synchronizing Database

XMLA Script to Synchronize Analysis Services Database

<Synchronize xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Source>
<ConnectionString>Provider=MSOLAP.4;Data Source=SOURCESERVER;ConnectTo=10.0;Integrated Security=SSPI;Initial Catalog=MyTechMantra</ConnectionString>
<Object>
<DatabaseID>MyTechMantra</DatabaseID>
</Object>
</Source>
<SynchronizeSecurity>CopyAll</SynchronizeSecurity>
<ApplyCompression>true</ApplyCompression>
</Synchronize>

Reference

• Synchronize Analysis Services Databases

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