First published on TECHNET on May 19, 2011
Here's a ??SQL Server PFE war-story to give one example of one of the kind of work we might do.
Most of the customers I support run SharePoint services, and they use a variety of methods to provide disaster recovery capabilities. One method involves SQL Server's Database Mirroring capability to maintain a copy of the data at a remote location. And 'remote location' often means there's a slow ??WAN (low-bandwidth) connection between the primary site and the secondary (mirror) site.
Well, an I.T. manager at one customer was concerned about how to test the disaster recovery plan. I'll mention that one reason mirroring is being used is because the slow WAN link made it impractical to regularly copy huge backup files. Simply relying on the mirroring monitor was not acceptable, because he wanted a full test, meaning he wanted test users to be able to see and interact with the remote instance of SharePoint. ?SharePoint was already installed on the remote site with only a default content database, but switching the mirror's primary and secondary roles in SQL Server wasn't good enough because that would require a few minutes of down-time for the production users?? while mounting and testing the content databases on the remote site, and this was a 24/7 production system.
This almost sounds like a scenario where you could create a Database Snapshot of the mirror, and run your test based on the snapshot. That would work for any test where it's okay for the data to be read-only, and my customer was okay with a read-only test. Unfortunately, SharePoint can't mount a content database without writing to it. So... here's the basic solution I came up with:
-
Mirror the SharePoint content database to a remote site.
-
Restore a full backup of the content database to a test server on the primary site (so you don't have to copy a huge backup file over the WAN).
-
Delete all the tables from the restored database.
-
Make a full backup of the table-less database. This is a shell SharePoint content database with all the users, stored procedures, etc, and it's backup file is tiny.
-
Copy the shell backup to the remote site and restore it.
-
Create a Database Snapshot of the mirror.
-
Create an SSIS package to copy the tables from the snapshot to the shell database, making it a fully functional, read-write test database.
-
Connect the remote-site instance of SharePoint to the test database, and let the test users confirm that everything works.
This solution works well, but beware of a couple of things that might trip you up:
-
Different SharePoint content databases may be associated with different application pools, which means they have different database users. A shell with the wrong users will cause failures. Just make sure your shell database has all the users necessary to support the app pools for all the content databases you're going to test.
-
An SSIS Data Flow task should work for every table in a SharePoint content database, but we had a failure in one system on the AllDocStreams table. We got around this problem by using an Execute SQL task with a SELECT -- INTO statement instead of a Data Flow task for that one table.
When I described this solution to a colleague, they said that while it might work, it's not a configuration supported by Microsoft. After thinking about that awhile, I disagree. Even though you may not see this solution fully described elsewhere in Microsoft documentation, each component of the solution is supported, therefore the entire solution is.
Pretty fun, huh? It's hard to believe I get paid for playing around like this...
