How to synchronize two SSAS Servers

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.

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.

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.

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.

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.
   
3. Select the Source Server and Database as shown below.
   
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.
     
5. The first time that I ran the synchronization program I received the following error messages:</li

   

6. 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:

7. 

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

9. 

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

    

11. At the end of the synchronization a successful message is displayed.</li

    

Checking Synchronization

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

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

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

   

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

• You can synchronize any multidimensional database or automate the task using a SQL Agent Job. For more information read the following links:
  • http://msdn.microsoft.com/en-us/library/ms174928.aspx
  • http://aniruddhathengadi.blogspot.com/2011/09/error-while-synchronizing-ssas-database.html
  • http://msdn.microsoft.com/en-us/library/ff929186.aspx

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

• MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018