Thursday, November 06, 2008

SharePoint MOSS 2007 - Moving the farm's SQL databases

I just tried moving SQL databases for one of my MOSS test farms form one SQL server to a new one...and failed horribly in my first attempt. I tried to use the SharePoint Products and Technologies Wizard to disconnect then reconnect to the same configuration database I had copied to a new SQL server. I got the following error message:

An exception of type System.ArgumentException was thrown. Additional exception information: Error during encryption or decryption. System error code 997

which I then found this article which gives you the workaround of ... creating a new configuration database and basically kissing the old one goodbye -.-;

This probably happend in my case because SharePoint 2007 was uninstalled and reinstalled on that box several times. The only way I could move my databases for this farm was to recreate the web applications by hand, disconnect (and then delete) the content databases created by default, then I attached the old content database and fixed any site collection settings (like url mappings) so the sites would work again.

After that little disaster, I did some research and found instructions for moving a farm's SQL databases:

Hopefully that will make the next farm migration this weekend go smoother.

--- UPDATE NOV 11, 2008 ---

I've completed the database migration as of last night with limited success. I was moving 2 farms from the one database server to a new database server. Everything seemed to go just fine, and the "stsadmin -o renameserver ..." command completed successfully. When I brought the farms back up, i was able to login to the central administrator just fine, and I only checked one content database on each farm to make sure it pointed to the new database server, which it did.

At that point I did the restore operation of the SSP's for each farm, which took several tries due to permissions issues ( ).

When that was done, I stopped the old DB server and suddenly both of my farms explodes into a slew of errors. Looking at the application log showed errors about the w3p service not being able to contact the old SQL server...

Oh yes, I was pissed.

I booted up the old SQL server did did some digging in the central administrator on both farms to see what happened. Checking the "Servers in Farm" page showed -both- SQL Severs as members of the farm, but it showed the old SQL server as hosting the configuration database. The default database setting also reported the old server name. I then went through and checked all the web application content database settings to find that they all pointed the old database server -except- for two of my web applications, which had two content databases listed: both had the same name, but one pointed to the old server, the other to the new one. Madness...

So, then I went about trying to fix the farm. I knew my databases were good on both servers, so I just needed to get the farm to use the new SQL I tried the "stsadm -o renameserver ..." command, only to get an error saying that the target SQL Server is already in the farm. I shrugged at that, went back into the Central Administrator and tried to delete the new SQL Server from the farm, only to get a SharePoint error that the SQL Server is being used by a SharePoint object. And the best part, the error message said something like "Here are the objects using the connection you are trying to delete: " and then nothing...just white space o.o It was easy enough to troubleshoot since I had already seen the content databases using the new SQL Server, but my god you just gotta love SharePoint error messages .. they're horrible!

After deleting the offending content databases I was able to remove the new SQL Server from the farm. I then ran through the steps of shutting down the farm, ran the "stsadm -o renameserver ..." command successfully, and started the farm again. From there, I double checked everything in the central admin, and all the settings were correct! The farm now pointed to the new SQL Server for all its databases, even the central admin. I shutdown the old SQL Server and hit all my web applications and they came up just fine.

The only thing I did not migrate over were the SSP's. And to be honest, at this point I am thinking that the restore operation may have somehow hosed something up in my farms. But in my case, I didnt really need to restore the SSP's because we dont use SharedServices or Microsoft Search at all in the web applications we host.

Since I do need to be able to run content deployment jobs, I eventually had to create a default SSP for the farm. If you dont have a default SSP configured, you'll find that your content deployment jobs fail with an error message saying something vague like "Content Deployment Failed. Cannot run remote job.", so you have to at least configure a default SSP on both farms to get that working again.

No comments: