The other day we did an install of SSAS on a server without loading the database engine. Everything went fine, the end user started accessing the service and then we started talking about how we should make certain we are taking backups of the objects that are being created. Now, how would you think to create a backup of a database in SSAS? Sure, do a right-click and select backup. OK, not a problem.
What’s that? Oh, you want to properly configure and schedule the backups, just like you do for the other database instances? Yeah, me too. Did you notice anything missing when you tried to schedule that backup using SSMS? Did you? No?
Well I sure did. In fact, I suddenly felt as if I were in a remake of The Crying Game, as I was totally not expecting the fact that there was not a SQL Agent under the covers for me to use to schedule my backups!
No sir. SSIS, SSRS, SSAS, but no SQL Agent? So, either Microsoft feels that backing up your cubes is not all that important, or that the use of the built-in Task Scheduler is good enough. Honestly, I can’t help but feel that someone dropped the ball here, I cannot imagine that someone made the decision to simply leave this service out of the final product. Would it be so hard to include the SQL Agent here? If I decide to scale out, and I build an ETL process on top of a cube and decide to use this server, how would I schedule the ETL piece anyway? Wouldn’t I want that to be as easy as a right-click somewhere? And don’t tell me to simply use an existing agent on an existing server somewhere else, because in my opinion I simply should not have to do that. Besides, if that agent goes down then I lose the backups being run on two boxes, not just one.
And yes, I know that you do not need to do backups of your analysis databases and cubes as frequently as regular database backups, but that is no excuse for not giving me the functionality of SQL Agent. I really want to call someone an Egghead here, but this doesn’t seem to have his markings. No, this is more like a Louie the Lilac type of caper, someone who sells you something that smells too good to be true.
You can use SQL Agent on another box to issue the backup commands to Analysis Services.
Backup Strategies with SQL Server 2005 Analysis Services
http://technet.microsoft.com/en-us/library/cc917611.aspx
thanks for the link Cindy. i still don’t feel that is an acceptable solution, because then I am relying on one agent to do the dumps for two instances, and i would prefer to keep everything local. if Denny Cherry can build a stand-alone agent for SQL Express, then Microsoft could give us the option to install SQL Agent when doing an install of SSAS without the db engine selected.
Great point. So you can do the approach Cindy uses, you can also use a 3rd party backup program to take a VSS snapshot of all of the files used in SSAS. Or you can rely on your definitions like we do. Basically we backup our cube definitions and disaster recovery plans call for restore of the relational sources but redeploy/process of the cubes. Will it take a bit longer? Perhaps but it meets our SLAs for the reports just fine.
I agree that a scheduler is an important missing piece. I also miss the ability to easily see what is slowing you down, what is causing your performance issues and react as it happens like you can in the DB engine. Yes you can run profiler, and you can make some config changes but the level of functionality brought on by the DMVs (or heck even SP_Who2 and KILL) is sorely lacking.
and i am not only talking about dumps, but what about the ETL process? if i scale out to an additional server, how would the ETL pieces be schduled?
You could do what Cindy says, or just use Task Scheduler in Windows somehow? Call the dtsx from cmd line.
Wondering, is Profiler installed or not when you don’t install the engine? Can you profile SSAS?
SQL Server Agent uses msdb to store the jobs. what more would you expect?
where exactly are you going to ETL your data to without a database?
while it seems clever to have SSAS installed without the database components, I’m not sure it’s particularly useful.
brandon,
good points all around, i was confusing the roles and functions of the services. however, i still think msft could provide a scaled down version of the agent with perhaps a sql express backend.