SQL Server Performance, DBA Best Practices & Enterprise Data Solutions | MyTechMantra
FacebookXPinterestEmailGoogle+LinkedInWhatsApp
DBASQL ServerSQL Server Analysis Services

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

Chetna Bhalla
Add comment
4 min read

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.

Recommendation: 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

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.

Gennerate XMLA Script for Synchronize SSAS Database

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.

Complete Analysis Services Database Synchronization

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

Trending Analysis Services Articles and Tips

1 2

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

View all posts

Add comment

You may also like

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.