Every experienced DBA is familiar with the software equivalent of “Murphy’s Law” and the notion that if something can go wrong, it will. We all know that hardware can and does fail. Now that I think about it, that’s where I first became acquainted with the term “mean time between failures.” As a result, bad things can occasionally happen to good databases and it’s usually at a very inopportune time. That probably explains why, as a group, we tend to be real concerned with having something that resembles a well-designed and tested plan for backing up and restoring databases after a disaster. It’s not terribly surprising that most of us are reasonably familiar with the SQL Server Recovery Models and the implications of each for disaster recovery. Analysis Services ships with SQL Server, which probably explains why we’re administering this type of server in addition to the relational database servers. Like any other database, it can fall victim to hardware failures, corruption, fire, flood, pesky rodents or other issues that make one or more databases inaccessible. The prospect of failure gives us reason to be deeply involved in planning and testing:
There is a relatively new variant of Analysis Services that is integrated with SharePoint 2010 in the PowerPivot Mid-Tier Server that was released with SQL Server 2008 R2. PowerPivot Mid-Tier Server is essentially an instance of Analysis Services, but the architecture is a bit different than the traditional Unified Dimensional Model due to the integration with SharePoint. Unfortunately, most DBAs aren’t intimately familiar with SharePoint Server and the concept of SharePoint Server Farms. SharePoint, like any other type of server, is subject to disaster so it’s a good idea to have a disaster recovery plan in place. In theory, one could use SQL Server Log Shipping or Always On Availability Groups with PowerPivot for SharePoint application databases, however, these configurations have not been tested and are not supported.
The documentation on SharePoint 2010 Disaster Recovery is relatively sparse. One of the more comprehensive documents on SharePoint Server 2010 Disaster Recovery is on Technet (http://technet.microsoft.com/en-us/library/ff628971.aspx). Todd Klindt discusses SharePoint 2010 Disaster Recovery and Randy Williams discussed Step-by-Step SharePoint Disaster Recovery in some detail. As they point out, SharePoint stores configuration, content and administration data in SQL Server databases. So obviously, the first part of any plan for disaster recovery involves regular backups of the Configuration, Content, and Administration databases (and if you aren’t already taking regular backups, you obviously haven’t had the panic attack that we’ve all experienced with a notification that a production server is down). With PowerPivot Mid-Tier server, data for the PowerPivot Service Application is also stored in a SQL Server database.
What the documentation doesn’t really tell you is that with the PowerPivot Mid-Tier, the uploaded PowerPivot workbooks are stored in the Content database, so having a valid backup of that database is a definite must. Having said that, this is what I learned from the pain of experience attempting to test a disaster recovery plan using a cold standby SharePoint 2010 Server. I should note that even though I lost quite a bit of hair and am now completely gray, no databases were harmed in the process of testing this.
In order for any disaster recovery plan to succeed, the cold standby SharePoint 2010 server that the production SharePoint 2010 server will need to reside in the same domain and preferably configured to use the same service accounts. Failing either of those conditions, you could be in for a lot of failures (not to mention as bald and gray as me) when attempting to pull up any Excel Workbooks that have been stored in the PowerPivot Mid-Tier server. After more hours than I care to think about, this is what I came up with:
Your Disaster Recovery test should have been successful.
For purposes of disclosure, I do work for Microsoft.