August 25, 2015 at 11:04 pm
I'm an experienced operational DBA but an SSAS dunce so I have some questions to ask.
1. Is it important to take SSAS backups as a general rule?
I've read that they can mostly be reconstituted from SSDT projects, on the assumption you have those projects, and can bear the downtime to recalculate everything. I would read this as, "You probably don't have that, so yes it's important," but would like confirmation.
2. Are server-level backups taken by infrastructure considered appropriate as SSAS backups?
I read the SQL 2005 whitepaper which says that a simple copy of the entire SSAS directory would do - with the caveats it doesn't cover remote partitions (which I have yet to investigate how common these are, and this it take a while to work out how to analyse it across a few hundred instances to find if they have any). But on the assumption that infrastructure covers all disks, how recoverable is SSAS from these on average?
I'd be very nervous about directly attaching MDF/LDF from that kind of "backup". I'm not sure how this translates in the SSAS world.
3. Do you backup your SSAS encryption keys as well?
I found _very_ little information on the web about it, though it does seem possible to do, it's almost as if nobody does it. I understand that in most cases it's attached to the user/AD credentials, but if something "happens"... is this a possible/likely scenario that should be catered for?
4. How do you execute your SSAS backups?
I have read about using PowerShell, agent jobs, and SSIS packages to do it. Using XMLA though seems to be per-database/cube, and so do you iterate the instance first to work out what's there? Or does the XMLA take * parameter for the database/cubes to backup? Do the 2012 PS cmdlets operate on older instances like 2008? Or do you use ASO?
If you're only backing up one instance and so don't have much variability, that's ok.
5. What's your starting point for frequency?
While it may "depend" based on cube reprocessing, etc, if you don't know, what would you start with? Daily? Daily unless it exceeds a certain size? If you took weekly backups is it "likely" that you will have to recalculate, and so that it's something usually best done daily?
6. What about PowerPivot?
I read that this is a special case and that the PS/ASO stuff won't work on it, and that it stores its data in normal relational databases which are the ones which need to be backed up. But if you were iterating your SSAS instances and backing things up; do you need to know which ones are in PowerPivot mode in order to skip them; or are they not connectable as normal SSAS instances; or are they connectable but show no databases/cubes inside to backup?
I've searched through a lot of websites and "Pro" SSAS books and while they mention basic backups I haven't seen any that go to this kind of detail on each point. So best to pick the brains of people who maintain it daily.
Thanks.
August 26, 2015 at 7:42 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply