SSAS Synchronization

People tend to say that synchronization is failure concept in SSAS but fortunately(or unfortunately I have been working on a system which does a sync 3 times a day(SQL 2005,SP2,25 GB database)

We did face a lot issues but it was a great learning!


Some of the issues that we faced are

1. Sync fails when many users are using the Reporting instance(Sync happens from Processing instance to Repoting instance). We had raised a call with MS and we were told that during the final stages of the sync, SSAS needs a write lock over the reporting instance database(obviously as this is going to be replaced by the Processing instance database - in crude terms) and it cancels all the queries which are running on the Reporting instance.

But a query on distinct count measure group might not get cancelled and hence causing the failure.I was also told that SP3 would improve the situation(not solve) but have not tried it yet.

We don't have much issues as 2 of our sync happens when there are no users and the 3rd one happens with minimal users on the system.

2. Sync fails when an on access virus scan runs on your server. To solve this issue, you need to exclude the SSAS data folders from the virus scan.

3. The risk is that, if sync fails, it MIGHT corrupt your Reporting instance which means the system is unavailable. What happens when SSAS instance corrupts itself is more interesting.

When the SSAS database gets corrupted, you wont be able to do any operations on the SSAS server including creating/deleting databases. The following method helped me solve these issues

You could rename or move the data folder(located at Program Files\Microsoft SQL Server\MSSQL.x\OLAP\Data) and create a new empty folder called “Data” after stopping the SSAS service. You should be able to connect and create new databases once you start the service. This method assumes that you don’t need other existing databases on the server.

If you would like to preserve the existing databases, then you could delete the ‘YourDatabase.N.DB’ folder and ‘YourDatabase.N.DB.xml’ files in the Data folder(after stopping the service) which should solve your issue. (‘YourDatabase’ is the name of the database which you were trying to restore. It would be better if you could backup the existing data folder before trying this!).

Lets hope we don't have such issues in SSAS 2008!