Home Forums Data Warehousing Analysis Services Asking for input from SSAS backups and restores RE: Asking for input from SSAS backups and restores

  • August 26, 2015 at 7:42 am

    #1822473

    Hi Cody. Lots of good questions. Here goes...

    1. Backups. I like backups. Let's say something goes woefully wrong with your data warehouse that feeds SSAS - it would be nice to restore both DBs so they are in sync again in the minimum amount of time (particularly if a full process takes a while). The backup stores not only the data but also the configuration of your SSAS database. Granted, you should have source control for that, and yeah maybe you can reprocess in full to get it all back, but the question really becomes why *not* back it up?

    2. Server backups. I agree with you, even if those are in place I tend to like to have a true SQL Server backup as well. I've done this as an extra precaution, particularly when it also becomes handy to be able to get to a Prod backup quickly without having to request a restore from the infrastructure team (particularly for restoring to a local environment or Dev). I've not performed a restore from an SSAS backup that wasn't taken by SQL Server directly, so I can't speak to reliability.

    3. SSAS keys. You're right - you hear all the time about backing up the SSRS encryption keys but not so much about SSAS. Your sense for wanting to back up any keys like this is a good one.

    4. Execution of backups. I don't have much feedback on this one - since I'm a developer not an administrator, I haven't managed a large-scale environment with tons of SSAS DBs. (Though that sure sounds like fun!) Personally I've used SSIS and agent jobs but I'm usually only focused on one DB that is under development.

    5. Frequency. The systems I build tend to pull data into a relational data warehouse first, then to SSAS. The frequency for SSAS then follows the frequency of the underlying DW. This usually starts out at daily in my world, then adjusts from there. I tend to like to grab a backup before ETL runs for the night.

    6. Power Pivot. Here you must be talking about Power Pivot for SharePoint. Definitely a different animal with the SP databases & one I haven't worked with in a while now. Hopefully someone else who is maintaining this type of environment has some input for you here.