Todd Klindt's Office 365 Admin Blog

Quick Launch

All Site Content

Todd Klindt's home page > Todd Klindt's Office 365 Admin Blog > Posts > Installing SQL Express 2008 R2, even upgrading SQL Express 2005

May 05

Installing SQL Express 2008 R2, even upgrading SQL Express 2005

by Todd O. Klindt

on 5/5/2010 4:15 PM

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

12 Comment(s)

Comments

Question

Todd,

Your blog is highly informative. I am having an issue where my SharePoint 2007 content DB is right at the 4GB limit. If I do this upgrade will my databases be able to expand to 10GB? I am fairly new to SharePoint admin, so forgive me if this is a dumb question. I only need to do this to get by till I can implement a full blown SharePoint 2010 setup. I plan to use a full SQL install for that. Any tips on migrating from 2007 to 2010? I have a couple of sites I would like to move.

Thanks,

Bill

on 6/8/2010 9:34 AM

Re: Question

If you upgrade your SQL Express instance to 2008 R2 your databases can grow to 4 GB, so this will work for you.

Upgrading 2007 to 2010 is a pretty big topic. Way too much to put here. My first piece of advice is to buy our new book, "Professional SharePoint 2010 Administration" linked on the front page of this blog. It has a whole chapter on upgrade and outlines your options, as well as some techniques you can use to minimize downtime and increase your success rate.

tk

Todd O. Klindt

on 6/12/2010 9:56 PM

Express 2008 R2 database size

Think that post above should have read :

SQL Express instance to 2008 R2 your databases can grow to 10 GB (upgraded from previous limit of 4GB)

on 7/21/2010 12:35 AM

Migrate from SQL Svr Express to SQL Server 08 in SP 2010?

Todd,
I saw your earlier entry about how to upgrade from SSE to SQL Server Std or Ent in MOSS 2007, but can you tell me the steps I should follow to do this in SharePoint 2010? (Yes, I started with the dreaded Basic install and now want to move to a better environment). I don't have a lot of content, but don't really want to start over configuring everything either. Thanks in advance!

on 9/29/2010 10:12 AM

Re: Migrate from SQL Svr Express to SQL Server 08 in SP 2010?

I haven't tried to upgrade the Express version of SQL 2008 using the same method, but it should work.

In all honesty if you did a Basic Install the correct answer is to back up your content and reinstall it correctly. There are a handful of reasons Basic installs are bad and they will haunt you. Better to take the lump now than down the road.

tk

Todd O. Klindt

on 10/5/2010 9:44 PM

Question SQL 2005 to SQl 2008 R2 and sharepoint wss 3.0

Hello Todd,

First of all I just want to say that you did a great job writing this blog. And as Bill said, it`s highly informative.

I have a question: do I need to stop any SP processes while doing upgrade from sql 2005 express to sql 2008 r2 express? Or I just follow your steps from this blog and that`s it (no hidden steps between listed in this blog). I have installed wss 3.0.
WSS and SQL are running on the same server.

I`m doing it this upgrade for the first time and don`t want to make any mistake.

Thanks in advance,

Milan

on 11/28/2012 4:49 PM

Re: Question SQL 2005 to SQl 2008 R2 and sharepoint wss 3.0

You could stop SharePoint if you wanted to. You don't have to though. Of course SP will be very upset while SQL is not available during the upgrade, but it works itself out of that funk when SQL comes back. At the very worst you'll just need to reboot the SharePoint box.

tk

Todd O. Klindt

on 11/30/2012 11:29 AM

Remove SQL2005 Tools first?

1. I don't understand - if the iinstall offers to upgrade my SQL2005 Express on a 64bit SBS2008 server; and there's documentation to support an upgrade - why you say that an upgrade to SQLServer 2008 Express Edition is not possible;
2. I ran the upgrade and I got no errors, but when I try to follow your guide to install the Management Tools, I get an error saying I must uninstall the SQL Server 2005 Express Tools!

What have I done wrong?
Jerome

on 12/2/2012 5:43 AM

Re: Remove SQL2005 Tools first?

Hi Jerome,
1. I don't see where I said any upgrade is not possible. Please quote it here so I know what we're talking about.

2. Did the database engine upgrade correctly? How are you determining that? I'm not sure why the tools aren't upgrading. Have you tried following its advice and uninstalling the SQL 2005 Express tools? That would be a good place to start.

tk

Todd O. Klindt

on 12/2/2012 2:00 PM

Re: Remove SQL2005 Tools first

Hello Todd,
I guess I was interpreting it that way from your second paragraph "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".
I did not realise SQL Express 2005 was 32bit only. As it is 64bit hardware I downloaded and installed the 64bit version of SQL 2008 Express. I took the 'Upgrade' option and it all seemed to run swimmingly. But when I went to install the Management Tools it said I'd have to remove SQL2005 Tools first. I cannot see this as an installed program so can't see how to remove this.
I'm wondering if I should have installed the 32bit version of SQL2008 Express. If so, do I first uninstall the SQL2008 64 bit? and if the upgrade to SQL2008 32 bit works, how do I get the system up to a 64bit version of SQL2008, since that's what you recommend?
Thanks
Jerome

on 12/2/2012 6:03 PM

1 - 10