Monday, February 26, 2007

Upgrading Sharepoint v3.0 SSEE to SQL 2005 Standard

I've been working hard the past few months to prepare for our move from Lotus Notes to Exchange. One of the key tasks of that project is finding a new home for the hundreds of the Lotus Notes databases/apps we currently use.

Many of the simple Notes apps can be redesigned quickly and effectively using Microsoft SharePoint. This new version is so good, it's moved from test server to production server overnight!

Unfortunately, I loaded up SharePoint v3 as a stand-alone server which means it installed the SQL Server Express Edition, aka Windows Internal Database, for its database server. SSEE is nice in that it's free and has no size limitations. However, you can't easily back it up (no SQL agent) and with the use of SharePoint growing so rapidly at my company I decided I'd better upgrade this to SQL Server 2005 as soon as possible.

The searched all over the net for solutions, but couldn't find anyone who had written about moving from SSEE to SQL Server 2005. The best instructions I could find were for Sharepoint 2.0 on moving from WMSDE to SQL 2005

I tried these instructions thinking that they'd be close enough to get the job done. I didn't realize that a key step would get me stuck at a point of no return. The section "Reconnecting to the Configuration Database and restarting the Virtual Servers" expects that you can connect back to the configuration database using Sharepoint Central Admin, but you can't at that point. It has been moved to a different sql server instance and it won't be found. Perhaps there's a way to change that by some hack, but I wasn't about to start messing around.

After panicking for a few moments, and kicking myself for not taking backups of the data other than the mdf and ldf files, I finally did get this to work.

First I uninstalled Sharepoint entirely, and then uninstalling Windows Internal Database using these instructions from Jemm's Sharepoint Blog

Next, I reinstalled Sharepoint v3 and chose the Advanced options and setup the server as a server farm. I went through the wizard and connected the server to my SQL 2005 Standard default instance.

When I got to the screen asking about the configuration database, I thought I might be able to connect it to the previously setup database from SSEE. Using the instructions in the WMSDE to SQL 2005 technote I had gotten the SSEE databases attached to my SQL 2005 Standard edition, but the wizard wouldn't let me connect to the existing database, so I had to create a new one.

I finished the install and then started to configure the Sharepoint server using the SharePoint Central Administrator. I got to the point where you create a content database and at that point I just specified the existing one. SharePoint didn't complain and all the sites and data were in tact.

Thankfully that was all I needed to do. To sum up, the processed I followed was this:

  1. Move the SharePoint database files out of the SSEE directory and attach them to the SQL Server 2005 Standard Edition instance.
  2. Uninstall SharePoint and SSEE
  3. Reinstall SharePoint, specifying Advanced - Web Fronted server instead of stand alone server
  4. Finish install, creating a new configuration database
  5. Specify your old content database(s) using SharePoint Central Administration.