Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

How to synchronize two SSAS Servers

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (10)   |   Related Tips: > Analysis Services Administration
Problem

Unfortunately SQL Server Analysis Services (SSAS) does not support differential backups and creating full backups for the servers might take too much time.  In this tip we are going to show how to synchronize two SSAS servers in order to have a failover server in case something goes wrong with one of the servers.

Solution

Starting with SSAS for SQL 2005, Microsoft introduced the synchronize feature. In this tip we will synchronize the Adventureworks database using SQL Server 2012.

Requirements

We are going to use Adventureworks database for SQL Server 2012 and the Adventureworks multidimentional project for this tip.  You can download them here: http://msftdbprodsamples.codeplex.com/releases/view/55330

We are also using SQL Server 2012 Enterprise edition.

For this example, there are two SQL Servers, one named SSAS and the other named DEV. On the SSAS server we have the Adventureworks database and on the DEV server there is no Adventureworks database, but this will be created when we synchronize the servers.

SSAS Servers

You also need to make sure that there is a common domain user account for both servers. In this tip, we added a user to the BIAdmin group.

Users and groups

Finally, we add the BIAdmin group as a SSAS administrator. To do this within SQL Server Management Studio right click on the server and choose the Security tab and add the BIAdmin group as shown below.

SSAS Security

Synchronization Setup

  1. To start, open SSMS and connect to SSAS using a domain account common between the two SSAS servers.
  2. Once connected, go to the Databases folder and right click on it and select the Synchronize option.

    Synchronization

  3. Select the Source Server and Database as shown below.

    Run command

  4. In the Synchronization options you can
    • Copy all - this will copy all of the data roles and members. If you do not have a destination database already a copy all will help.
    • Skip membership - this let's you copy the roles, but not the members.  If you already have users you may be careful with the skip members option because some roles may be overwritten.
    • Ignore all - let's you avoid copying the roles and users. 

      GCI command

  5. The first time that I ran the synchronization program I received the following error messages:
  6. SSAS parts

  7. In order to solve this problem, I needed to restart the SSAS Service with a domain account with privileges on both SSAS servers. Open SQL Server Configuration Manager (SSCM) to make this change:

  8. Open Configuration Manager

  9. In the SSCM click on SQL Server Services and double click on SQL Server Analysis Services. In the Account Name, change this account to a domain account and restart the service.

  10. ssas service account

  11. You should now be able to start the synchronization.

    sync message

  12. At the end of the synchronization a successful message is displayed.
  13. New SSAS Database created

Checking Synchronization

  1. On the destination server the Adventureworks database should now be created.

  2. Run command

  3. Let's verify if changes are synchronized. Let's remove one partition on the source SSAS server:

  4. delete partition

  5. Repeat the synchronization steps again and verify that this partition has been deleted on the destination SSAS Server. If everything is OK, the partition that was deleted on the source should be deleted at the destination as well.

    multidimensional database

In this tip we learned how to synchronize two SSAS servers. We can use this method as a backup, as a failover alternative or as a migration method.

Next Steps


Last Update: 4/15/2013


About the author
MSSQLTips author Daniel Calbimonte
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, February 12, 2014 - 7:30:14 AM - vivek Read The Tip

Hi Daniel,

It would be great if you could post step by step process of removing the synchronization between source and destination database ?


Friday, February 07, 2014 - 7:22:37 AM - vivek Read The Tip

Hi Daniel,

Could you post step by step process of removing the synchronization between source and destination database ?

 


Monday, January 13, 2014 - 3:23:35 PM - Daniel Read The Tip

http://technet.microsoft.com/en-us/library/ff929186.aspx


Monday, January 13, 2014 - 12:12:08 PM - Stephanie Read The Tip

Hi, do you have an example on how to setup the synchronization as a SQL Server schedule job?   I know you can save the setting as XML file but not sure how do I execute it from SQL Server Agent. 

Thank you!


Tuesday, November 26, 2013 - 8:52:04 AM - avni Read The Tip

Hi Daniel

 

Thanks for the quick information on Synchronizing instances!

I wanted to know about the errors specified in the point 5. What are the chain of events? it says an existing connection was forcibly closed by the system. Was it because of Synchronization the Analysis services was stopped or because of the loss in connection the Sync failed?

Even so, why did the services had to stop?

 

Thanks & regards

Avni


Thursday, June 20, 2013 - 6:49:59 PM - OLAWALE Read The Tip

Hi Daniel,


Is it possible that i sychronize 2008r2 server  and 2012?


Thanks

 


Wednesday, May 15, 2013 - 10:26:37 AM - Daniel Calbimonte Read The Tip

Yes, the feature is available in SQL Server 2005, 2008, 2008R2 and SQL 2012


Wednesday, May 15, 2013 - 7:06:28 AM - Prashant Thakwani Read The Tip

Mohamed,

Yes, it is same in SQL Server 2008.

 

 

 


Tuesday, May 07, 2013 - 12:30:41 PM - mohamed Read The Tip

can we do  the same work with SQL Server 2008 ? 


Tuesday, May 07, 2013 - 12:27:54 PM - mohamed Read The Tip

nice tips, it was so obvious to understand ,

thanks you so much 




 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.