Backing Up SSAS Databases

As we get more involved with the BI stack of tools we find ourselves learning some of the nuances of supporting all of these new environments. One such example is SSAS. When people build and deploy cubes to an instance of SSAS they (eventually) get around to asking about restores and recovery. And since the first two letters of SSAS stand for ‘SQL Server’, my team is naturally asked to ensure that there is a proper recovery strategy in place.

SSAS Backups

Backing up an SSAS database is quite straightforward. Inside of SSMS you could right-click on the database name and select ‘Back Up…’, which then opens up a new window that looks nothing like the database backup window you are used to seeing in SSMS. Beyond that, two additional things should jump out at you. First, the extension defaults to *.abf. Second, there is an information icon at the bottom of the window that states:

The dialog will backup metadata for ROLAP partitions, metadata and aggregations for HOLAP partitions, metadata, source data and aggregations for MOLAP partitions. This is because backup is not applied on the source data contained in the relational database.

Huh?

ROLAP, MOLAP, HOLAP

Your recovery strategy is dependent upon the storage mode chosen when the cube is deployed. You can also alter those settings through SSMS by right-clicking on the cube (not the database) and go to ‘Properties’. You should see a choice for ‘Proactive Caching’. Select that and brace yourself for even more confusion. Low-latency MOLAP? Real-time ROLAP? There are seven options for you to examine, which one do you really want?

This is where I like to take a step backwards and think about the ultimate goal. In this case, the ultimate goal is recovery. So what do I need to do in order to ensure that we can recover a cube? If the cube is using ROLAP and/or HOLAP, then we need to ensure we have a backup of the source data if there is a need to restore the cube to a specific point in time. Should disaster strike on ROLAP/HOLAP cubes and you do not have backups of the source data, then the cubes can only be rebuilt using existing source data.

MOLAP, however, already contains the source data in the *.abf backup file, so you would not need to ensure that you have an additional backup of the source data. You would be able to restore to any point in time for which a previous *.abf file exists.

For more details on backup strategies for SSAS, check out Backup Strategies with SQL Server 2005 Analysis Services and Managing Backing Up and Restoring (Analysis Services).

Scheduling SSAS Backups

You can create an XMLA script for backing up your SSAS databases and use that script to schedule a job inside of SQL Agent to do your SSAS database backups. Two things immediately come to mind: (1) you may need to do a backup of the SSAS database here and coordinate with the relational database backup of the source data on another server, (2) if you have deployed SSAS to a servers without the database engine then SQL Agent does not exist and you will need to find a different means to schedule your SSAS backups, and (3) someone can come along and change the ROLAP/HOLAP/MOLAP setting without your knowledge and be putting recovery at risk.

OK, that was three things. But each warrants mentioning. Especially that last part.

If you want to ensure that you can recover, no matter what, then look to build recovery solutions that perform backups of the SSAS databases as well as the source data databases, and have those backups coordinated to happen within a narrow window of opportunity. This way if someone switches from MOLAP to ROLAP without notifying anyone then you will have a better chance of recovering their cubes in a timely manner.

The easy option is to load everything onto one box, but that is not always possible or desirable. Once your architecture is in place, outline your recovery plan, get your scripts and schedules in place, and then practice recovering the cubes a few times and make certain everyone is comfortable with the process.