Wayne Hall

Ahhh, my first Midsharpblog post. Upgrading WMSDE to SQL Server...

You'd think I'd have done this by now, but behold, I have not.  Some basic history:  At our company of ~300 folks and 3 intranets (one of which is Sharepoint Portal Server 2001 and one of which is a roll-out of Windows Sharepoint Services 2003) after a merger, we've decided to go SPS 2003.  That's still some time aways, and in the meantime, our WSS site has gotten so popular that I finally got backups running on it a couple months ago AND decided to upgrade the DB from WMSDE to SQL Server Standard.  That's one really really cool thing about Microsoft Enterprise Licensing -- because our anniversary date is 10 months away, I can install SQL Server on this thing and only pay if it's still in production come our anniversary date.  :)

This particular box is a Dell 2450, dual 733Mhz cpu, Windows Server 2003 Standard, 512MB RAM, 2x18 RAID1 OS/swap/program files and 2x33GB RAID1 data.  It's currently at about 1.8GB of data (the backup is 1.6GB) and everyone claims that's it's FAST FAST FAST, especially when compared to SPS 2001, which is running on a quad-cpu box with 4GB RAM (see my many posts a couple years back on the newsgroups about performance tuning for 2001).  Side note: I simply can't wait to get on a production box with ~2 or 3GB RAM; am expecting even better performance, but since we'll be using SPS and My Sites, I'm expecting that I'll need the extra horsepower.  I'll blog later on and let you know how it is.

Anyway, it's time to upgrade.  So I went a-searchin and the first reference I found was Microsoft's guide
http://www.microsoft.com/resources/documentation/wss/2/all/adminguide/en-us/stsf17.mspx

Then I googled for “sharepoint wmsde sql server upgrade search“ since one of my goals with the upgrade is to give a search engine.  Ahh, good old collutions FAQ link about SP3 not working

NOTIFY USERS

1. I've already emailed users letting them know that this upgrade is taking place and that it will be down. 
2. Hopefully this is already part of your procedure on production systems... surely you don't upgrade systems while people use them?

BACKUP YOUR EXISTING SYSTEM

1. First thing it says is to stop WMSDE and copy the database files.  However....
2. I'm one to be pretty careful, so I also did a full WSS Backup in addition to this.  The backup I did is actually a .cmd batch script that I run as a scheduled task.
   C:\>type wss-backup.cmd
   @echo off
   "c:\Program Files\Common Files\Microsoft Shared\web server extensions\60\BIN\stsadm" -o backup -url -filename c:\backups\wss-backup.dat -overwrite
   
3. For me and my data (again, about 1.8GB in the WMSDE Database), this takes about 10 minutes.
4. Then I did what it said to do (always be more careful than the next guy).  However, I didn't quite know how to “stop the WMSDE database“ so I assumed it was MSSQL$SHAREPOINT service:
   
   
   
5. With the service stopped, I copied the entire Microsoft SQL Directory somewhere else.  I'm sure I could have just copied the MSSQL$SHAREPOINT/Data directory, but since I've not tested this yet, I'll just copy the whole darn thing, thank you very much.
6. In addition to these backups, I also have Backup Exec backups of the box for each daily stsadm-based backup as well as SQL-based backup of the database.
7. For the purpose of this upgrade, I also stopped the Backup Exec Remote Agent because I didn't want a backup to start while I was copying things around.

RUN INSTALL PROGRAM

1. The instructions say to run the SQL Server 2000 SP2 Setup program.  I don't think I have that (unless the default installation is SP2).  I get my stuff from Microsoft's Volume licensing.  I just checked the download I used, and it was named EN_SQL2000_SP2.exe, so I'll assume I have that for now.  I know I need to go to SP3a just for slammer and basic sanity anyway.
2. We have all of our installation disks on the network, so I'm actually running setup from there.
3. I right-clicked “Autorun.exe“ and opened it.
4. I clicked on SQL Server 2000 Components
5. I clicked on Install Database Server
6. The standard “SP2 and below“... “SQL Server 2000 SP2 and below is not supported by this version of windows“ message you always get on Windows Server 2003.... popped up.  I clicked Continue.
7. Time Passes...
8. Then I went through the typical screens until I got a screen that I had to change the default.  Take note -- if you're following this procedure, you need to select to UPGRADE instead of install a new instance:
   
   
   
9. Then it asked for the CD-key.  No, I'm not posting a screenshot of our key d8=
10. Then it asked for an Instance name.  Thank goodness it didn't really give me a choice here (you know, like SPS has those four databases... at this time I didn't know that WSS really only had one that mattered or that would be upgraded).
    
    
    
11. Then it let me “Select one of the following to change your installation“.  Again, there's some comfort in knowing that I only have one choice.
    
    
    
12. Wow, it really wants me to confirm.  The next pop-up had a check-box asking “Do you want to upgrade your existing installation to Standard Edition“ and the default was to not be checked.  I checked it and continued:
    
    
    
13. Then it asked for licensing mode -- Per Seat or Per Processor.  You'll need to check with your Microsoft Rep if you have questions on how to license this.  For our licensing, everything we do is per processor.
14. Then it asked if I wanted to install additional components.  Well, I don't know.  It'd be nice if it let me know what they'd be.  But I clicked yes.
15. Hmmm, I picked Management tools cuz I want all that Enterprise Manager and query tools and such.  I don't know if I want the Development tools, so I left that unchecked.  I can always install them later, right?  (I have no idea)
    
    
    
16. Then it started copying and doing all it's install stuff.  Installed MDAC, SQL, etc.  Wow, I had forgotten this installation GUI -- this really reminds me of Windows 3.1 days where it has temperature bars for folders/files, disk, and installation progress.
17. Ok, it said it finished. 
18. Ah, maybe a problem.  I just re-checked the directions at the link above and it said to select “Full-text Search“ if I want to enable that.  Darnit.
19. I'm now re-running autorun.exe just to see if I had installed it.
20. aha!  Now I see it.  Full-Text Search is a Sub-Component listed under “Server Components“  AND, it's not checked.  So I'm going to check it and install.
21. Time Passes...
22. OK, it said it finished.
23. Another double-check of the directions and it says Sharepoint sites should work as usual.  Well, it didn't tell me to start up the database again!  I really wish people would go *through* the directions they post so people don't make mistakes.
24. I started up the Backup Exec Remote Agent Service.  No errors.
25. I started up the MSSQL$SHAREPOINT service.  No errors.
26. I checked the Event Viewer real quick -- only errors were from Windows Sharepoint Services being unable to connect to STS_Config.  Interesting -- surely the instructions should have told me to stop Sharepoint, but there isn't the typical Sharepoint service other than the Timer and I guess W3.
27. I tried running the SQL Service Service Manager and maybe point it to itself... but it wasn't working.  Not sure what I'm supposed to do here.  Reboot?
28. Just to test, I went to one of the WSS sites.  Hmmm, site is taking a while to load, but it does say it's loading.  It gave me an “error on page“ in the bottom left corner, but the page loaded nice and neat.
29. Hmmm, clicking around a little bit -- the system seems slower than before.  I wonder if SQL Server 2000 is meatier than WMSDE.  I did point SQL Server to 2 processors, so maybe it's using more CPU and WSS/ISS is taking a hit....
30. I'm going to scale SQL Server back to 1 processor, and I'm remembering that I only have 512MB in that box, so surely some more RAM would help a lot.
31. Interesting note also -- I tracked down the error on the page and it was “permission denied“ on line 209.  If I reload the page, the error goes away.  (link goes to a specific view of a list).  If I click “New Item“ and let it load, then click the “Back“ button, I don't get the error.
32. WOW. I forgot to install Service Pack 3a.  Interesting that everything's working.  So, it's time to do that.  I got SP3a install available, then ran x86/setup/setupsql.exe across the network.  During the install, it asked for SQL auth or windows, I chose Windows.  Also, I didn't mind sending MS reports of crashes.
33. Whew, time really passes on this sp3a install.  It didn't take this long to install SQL Server itself!  (Kind of reminds me of installing Solaris 8 with all the recommended patch clusters - I remember the Solaris install taking 3-4 hours, and then the patch cluster took 12-14 hours to install.)
34. Also, I never really did know if I was supposed to install the SP3a for Analysis Services or not -- I don't think I use Analysis services.

In general, I think I'm done.  I've clicked around here and there and nothing seems to not be working.  A little slow, but some RAM should fix that.  Also, I generally like to reboot after such major work -- not only does everything start fresh, but I want to verify that a reboot is successful...

Oh wait... need to enable searching.  Hmm, where is that?

Good thing I have my handy-dandy full wall-length SPS and WSS administration roadmap that MindSharp made available...

ENABLE FULL-TEXT SEARCH

1. Go to Sharepoint Central Administration (on the system itself, go to Start > Administrative Tools > Sharepoint Central Administration)
2. Under the “Component Configuration“ grouping, select ““Configure full-text search“
3. Check the box that says “Enable full-text search and index component“.
4. Click OK.  It should think about it for a little bit, and then enable.
5. Hmm, does that mean it's already all set up for searching and indexing?  It's not clear from the GUI that it'll start an index or what.  Also, what will my 512MB do with SQL, sharepoint and now full-text indexing enabled?  Will it grind to a halt?
6. So, now all I need to know is -- when will it index content?  I just tried searching for something and it didn't find anything (not surprised).
7. One item I found was http://support.microsoft.com/?kbid=837367 which is specific to SBS (Small Business Server) 2003, but I'm thinking it's suitable to my situation as well.
8. Woohoo-man.  I just went back to the server (via terminal services) to register the SHAREPOINT instance with SQL Server Enterprise Manager, and the interface is c r a w l i n g....  I checked Task Manager, and mssdmn.exe is taking up it's fair share of cpu, so I'm guessing that's the full text indexer or something.
9. I just followed the “register SHAREPOINT instance with Enterprise Manager portion of the KB article, and finally it tells me to reboot the server.
10. Rebooting.... [rubbing hands together expectantly...] this will be great - SQL server (I'm already ordering more RAM), full-text search, the ability to connect to the sharepoint database remotely, etc....
11. Reboot came up fine.  Checked event viewer logs and sharepoint site.

Whew!  Finished... Hope this detailed blog helps someone else out in the future.

posted on Tuesday, March 08, 2005 1:10 AM