Planning for Disaster Recovery for PowerPivot Mid-Tier Server

  • Article

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:

  1. Regular backups of the database(s) to minimize data loss.
  2. Storage of backups in a secure on site or off site location
  3. Periodically testing the backup and recovery strategies before a real failure occurs.
  4. Assessment of the recovery plan using a cold standby server

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 (https://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:

  1. Setup Primary PowerPivot Mid-Tier server
    1. Install SharePoint
    2. Install PowerPivot (New or Existing Farm). Which one you do depends on whether or not you ran the Configuration Wizard after installing SharePoint. If you ran the Configuration Wizard after installing SharePoint, you'll want to select the "Existing Farm" option. If you did not run the Configuration Wizard after installing SharePoint, you'll want to select the "New Farm" option.
  2. Test the PowerPivot Site
  3. Setup Cold Standby PowerPivot Mid-Tier server using same setup strategy that was used for the Primary
  4. Test the PowerPivot site on the Cold Standby
  5. Create a Test PowerPivot workbook and upload to the mid-tier server
  6. Verify that Data Refresh works
  7. From SSMS on the Primary server, backup the SharePoint_Content_.... Database
  8. Copy the SharePoint_Content_...bak file to the Cold Standby
  9. From SSMS on the Cold Standby, restore the SharePoint_Content_.... database from the .bak file
  10. From SharePoint Central Admin on the Cold Standby, select "Manage content databases"
  11. From the Central Administration > Manage Content Databases page, click on the existing SharePoint_Content_... database. That will open the Central Administration > Manage Content Database Settings page
  12. Check the "Remove Content Database" checkbox
  13. That will cause a "Message from webpage" with the following and rather ominous warning:
        "Warning: There are sites stored in this content database. If you
         remove the database, those sites will no longer be accessible to
         users. Click OK to remove the database, or Cancel to leave the
         database connected."
  14. Click OK
  15. Click OK
  16. From the Central Administration > Manage Content Databases page, click "Add a content database"
  17. Set the Database Name field to the name of the database that was restored in step 9.
  18. Click OK
  19. Test the PowerPivot site on the Cold Standby server. At this point, any Excel PowerPivot Workbooks that were stored in the Primary Server should be visible and accessible on the Cold Standby server.
  20. Schedule a Data Refresh to run "Also refresh as soon as possible"
  21. Verify that the Data Refresh was successful.

Your Disaster Recovery test should have been successful.

 

For purposes of disclosure, I do work for Microsoft.