Last week the SQL Express team announced that the 2008 R2 version of SQL Express would no longer have the stingy 4 GB database limit that SQL Express had in its 2005 and 2008 iterations. The 2008 R2 was getting a promotion to 10 GB. Glory be! For us SharePoint folks, this is great news. SQL Express is a great way to try SharePoint, without paying big bucks for SQL Server Standard or Enterprise. If you, against my pleading, did a Basic Install of MOSS 2007, SharePoint Foundation 2010 or SharePoint Server 2010 you get an instance of SQL Express also. The 4 GB limit with earlier versions of SQL Express was quite a pain, but fortunately even if you did the dreaded Basic Install of MOSS 2007 you can take advantage of SQL Express 2008 R2's 10 GB limit. In this blog post I'm going to walk through upgrading SQL Express 2005 to SQL Express 2008 R2.
Your first step, regardless if this is a new install or an upgrade is to download SQL Express 2008 R2. You can download just the database engine, but I recommend grabbing the download that includes the Management Tools. If this is a fresh install, and you're on 64 bit hardware, I recommend using the 64 bit version. If you're on 32 bit hardware, or if you're upgrading from SQL Express 2005 you'll need the 32 bit version. SQL Express 2005 and 2008 was 32 bit only.
If you're upgrading I highly recommend running a backup first. In my tests the upgrade always went smooth, but that's hardly a guarantee. If you're using SQL Express with SharePoint you can do a Farm backup in Central Administration to back up all your databases. Go ahead, do it now, I'll wait.
Okay, now that that is done and you've downloaded the install, go ahead and run it. Since we're going version to version, we don't need to do anything tricky like using the SKUUPGRADE switch. When you start the installer, choose the upgrade option:
When it comes to the upgrade screen, pick the OFFICESERVERS instance. That's the instance SharePoint creates when it does a Basic Install.
If you're not upgrading a Basic Install your instance name will be different. If you didn't install SQL to a named instance the instance name will be MSSQLSERVER.
After that click Next a bunch of times and let the upgrade happen. If everything goes well, you'll be greeted with this cheerful message:
At this point your database engine has been upgraded, but you don't have the fancy Management Studio. From the splash screen choose New Installation or Feature. On the Installation Type screen choose " New installation or add shared features." On the next screen clear all the boxes except the one next to "Management Tools – Basic." SQL might try to install another database engine instance too, don't fall for that.
Click next a bunch of times and you should be finished. After the installation is finished you'll have a link the SQL Management Studio under All Programs:
If you did a Basic Install you'll need to connect to the OFFICESERVERS instance.
When you hit Connect you'll be connected to the SQL Instance that has all your SharePoint databases. Databases that can now grow to be 10 GB, all for free. J