The Login Failed… Wait, What?!?
There are at least two cool things you learn when doing upgrades to SharePoint 2010 from 2007 via database attach:
- You can test your SharePoint 2007 databases for upgradeability _before_ you actually run the upgrade
- You can designate one or more servers to run the actual database upgrades for quicker performance
Item #1 is a boon to any poor SharePoint administrator who has had a bad experience with an aborted upgrade. Simply put, SharePoint will not only notify you of missing Features, etc., but will also let you know in advance if you have an item that will cause the DB Attach upgrade to fail.
Item #2 is a true bonus. While your SharePoint box is humming along happily against its existing databases, you can harness the power of a secondary SQL server (or more servers) in order to upgrade content databases without affecting the performance of your original SQL server.
So, I took a DB backup and attached it to a secondary SQL box with the intention of running the Test-SPContentDatabase PowerShell command. The database restored faithfully with no errors – next, I made sure that the permissions were good to go on the restored database. So far, so good.
Next, I built a web application to be my “temporary” web application (I could always move the Content DB later to the web application of my choosing). As part of the web application creation process, SharePoint dutifully built a Content Database which I named “WSS_DeleteMe”, because I will eventually get rid of it and replace it with my upgraded database.
And away we go – I ran the command to test the database:
Test-SPContentDatabase –name DBtobeUpgraded -webapplication http://mydestinationwebapp
My PowerShell console replied:
Test-SPContentDatabase : Cannot open database “DBtobeUpgraded” requested by the login. The login failed…
I ran through the troubleshooting steps:
- Did I open my SharePoint 2010 Management Shell as Administrator? Yep – the title bar reads “Administrator: SharePoint 2010 Management Shell”.
- Did I set all of the permissions on the freshly moved Content DB? Yep – all good there
It took me a moment to realize what I had missed – You see, PowerShell is not always the best at communicating what issues it is having with your commands, but it was trying to tell me something.
I went to Central Administration looking for answers:
Ah. there it is – I had neglected to tell SharePoint which server was hosting the DB to be upgraded. You see, I had forgotten to specify the –ServerInstance switch in PowerShell to let SharePoint know what database server actually would be doing the heavy lifting.
While I could have changed the default database server, this was only as a last resort – if someone decided to build a new web application while I was doing my test, the database would be created on the wrong DB server – not good.
I re-ran the Test-SPContentDatabase check again, and got the expected output, dutifully saved to a text file for future reference (add ” > TestDatabase.log” to the command). Time to run the Mount-SPContentDatabase PowerShell command (with the -DatabaseServer switch) and get this show on the road!