SQL Express, Microsoft Internal Database, and SharePoint – Be Careful Upgrading

Anyone that has worked with SharePoint for some time will tell you that setting it up using the Basic or default install is a big no no. Doing so uses only local accounts, prevents you from adding any additional servers to the farm, and because the SharePoint system account is local, it effectively prevents SharePoint from seeing any data on any other systems. Reversing this requires a complete uninstall of SharePoint. It is much better to do a proper farm install, even if the databases are to be stored on the same machine.

There is however one business case where the basic install has historically been the right approach, and it’s very specific. To explain, a little explanation of some different flavours of SQL is required. Most people are familiar with SQL Server Standard Edition and Enterprise Edition. Also available for download is a free edition of SQL Server entitled SQL Server Express, which has a few limitations, most notably a single database size limit of 4 GB (and has none of the BI components). There is (or was) also a lesser known version of SQL Server known as the Windows Internal Database. It can’t be downloaded, but occasionally will show up on a system when a Microsoft product that requires SQL Server is installed into an environment without an available SQL Server.

What’s the difference between the Windows Internal Database (WID) and SQL Express? SQL Express is a little easier to manage (and possible to uninstall, unlike WID). WID is relatively invisible, but the major difference between the two is the size restriction. Microsoft will often change the maximum DB size limit for the WID in the interest of the product that it is backing. This is very much true of SharePoint 2007. Given SharePoint’s focus on storing large content,Microsoft didn’t want people to bump into that 4 GB size restriction too quickly,so for WSS 3.0 (the free version of SharePoint 2007), they removed the limit completely.

This means that if you use WSS 3.0, and you install using the basic configuration option, you have no licensing limits on the amount of content that you can store within SharePoint. It is very important to note that this is ONLY true of WSS 3.0, if you install SharePoint Server  2007 in basic mode, once you hit 4 GB everything stops working. Also, if you decide that maybe you should do a best practice farm install, and you install SQL Express separately from SharePoint, you will run into that 4 GB wall with either WSS or SharePoint server.

This then is the one business case where it is appropriate to use the basic installation. If the feature set of  WSS 3.0 is adequate, and you will want to store more than 4 GB in a single site collection, basic installation is for you.

Thus far I’ve been addressing WSS 3.0 and SharePoint Server 2007. With SharePoint Foundation 2010, and SharePoint Server 2010, Microsoft made some significant changes to the model. Now, they exclusively rely on SQL Express for the basic installation.  While this is great for anyone that’s ever wanted to uninstall an instance of the Windows Internal Database, Express has that 4 GB limit for both editions, which essentially means that they’ve closed the door on the free storage ride.

This also means that if you are in this category of organizations using more than 4 GB of SharePoint storage, you’re going to need to spring for a SQL Server license if you want to upgrade to SharePoint Foundation 2010. You will also need to do an uninstall, and a reinstall to get yourself into a farm configuration. It’s OK, it’s not as scary as it sounds.

The SharePoint 2010 changes mean that there is now no good reason to use basic installation in a production environment. Development or testing, OK, but why not use farm in that scenario as well? It more closely approximates the real world.

UPDATE – 24/08/201

I was listening to Todd Klindt’s Admin Netcast #53 today, and he was addressing some of the above issues. He brought up two very good related points. Firstly, in SQL Express 2008 R2, the storage limit has been increased to 10 GB, so that might help you out. Also, you can use the SQL FileStream Remote Blob storage to help get the size of your content database down.

Advertisements

3 thoughts on “SQL Express, Microsoft Internal Database, and SharePoint – Be Careful Upgrading”

  1. Very good advice, thanks for throwing a flag on this one. I am glad I found your post before upgrading our internal site to SharePoint 2010.

    I would have found out the hard way, as John did, because Microsoft doesn””t always highlight these details (sometimes the answers we really need are only found in the trenches). Thanks again!

  2. Thanks for information.
    Do you have details or information on how much it would cost in license fees, etc for a Sharepoint 2010 solution with not so large enterprise.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.