SharePoint University

I am fairly sure I've seen this question asked before but I can't find the post for some reason.

I am trying to find out what is the DB size limitation on the default database (the embedded SQL server) that WSS 3.0 sets up? I read somewhere that the default database has no size limitation but is not manageable like SQL Express. However, I can't find the post which said that and I had no luck finding concrete evidence so far.

Could anyone please advise me on this?

Here's what I have in my notes regarding WSS databases...  With WSS you have three options for the back-end database provider for your Config and Content databases:

• Windows Internal Database - as I understand it, you have no size limitations when you install using the Windows Internal Database option (it installs seamlessly with WSS if you choose to use it), but you can only administrate it via the command line and it has to be on the same server as your WSS install, so you lose a lot of flexibility with its use.  The command line tools are consistent with the command line tools you get with SQL Server Express or SQL Server 2005, your DBAs just won't be able to use a graphical user interface to manage the databases.
  
• SQL Server Express - With SQL Server 2005 Express (or its predecessor the MSDE), you can set up your database remotely or on your WSS server, but you face limitations (4 GB database size with 2005, 2 GB and performance throttling w/ MSDE).  You can administrate it using a GUI tool, and you can't beat the price, but you will quickly come up against the 4 GB limits.
• SQL Server 2005.  No database limits, GUI admin tool, remote capabilities.  The only limits on your database size are your available disk space and how your DBAs feel about large DBs.  Large DBs take much longer to recover from backups, which is one reason why people like to stay away from them.  But SharePoint (WSS or MOSS) does not have any sizing constraints on database sizing.

Thank you very much for your reply! 

WID - The command line tools might be adequate so it shouldn't be a big cause for concern...yet. Also the DB size being unrestricted is good although for the usage of this, I don't expect it to grow too rapidly provided that the users don't abuse the system.

SQL Express - I am using it right now on my test server and it's good so far but the DB size limit is a bit of a cause for concern

SQL 2005 - This might be impossible at this stage as the system is not in production yet. But when they are 100% sold on it, who knows.

However, could I ask where you got the info on WID? I haven't seen an official MS doc that says there's no size limit so to me it sounds like a case of if you know about, you'll exploit it. Also how difficult or easy is it to migrate from WID to full blown SQL if the possibility arises in the future?

I can't remember exactly where I got the info that was in my notes, but here's a few items I was able to find this morning.

• The WID is also referred to as the SQL 2005 Embedded Edition
• The WID is only used as the database backed for the Basic install of WSS 3.0; when you use the Basic (also referred to as the All-In-One option) for MOSS 2007 SQL Server 2005 Express is installed
• Links:
• http://www.wssdemo.com/Pages/db.aspx
• http://www.microsoft.com/downloads/details.aspx?FamilyID=de6b898b-1e59-4571-a301-d26fb39d6d9f&DisplayLang=en
• http://blogs.msdn.com/joelo/archive/2007/04/18/wss-3-0-server-roles.aspx
• http://www.sharepointblogs.com/echef/archive/2007/08/23/sql-2005-embedded-edition-what-the-heck-is-that.aspx
• http://www.devcow.com/blogs/jdattis/archive/2006/11/17/Windows-SharePoint-Services-v3-RTM-A-Basic-Installation.aspx
• http://www.eggheadcafe.com/software/aspnet/29019401/-using-sql-express-wit.aspx
• http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2601190&SiteID=1

To give you some insight into how I'm finding this stuff, a while back I created a Google Custom Search Engine that I use for the majority of my SharePoint research these days: http://SearchForSharePoint.com

For this issue I searched on "Windows Internal Database" and "SQL Embedded Edition" and found a few relevant nuggets.

I have a question on this? I have recently been told that the max size on the CDB is 100GB. by MS....

Also, If you had a CDB that were say, 500GB what would the actual solution be from your point of view?

Would you split it out into seperate CDBS per site, or would you take and make a Clustered SQL 2005?

Thanks,

Curt

Curt --

You might want to take a look at a post I made in another forum question, its got a lot of good info that relates to your question:

http://www.sharepointu.com/forums/p/2276/6229.aspx#6229

I would however go with MS's advice as to the size of your content database, since it's probably somewhat tailored to your specific environment. 

With a content database over your threshold, whatever that may be, I would definitely look to identify sites that can and should be moved to their own content database, or at least an additional shared content db.  If you have a single site that may go over your threshold, you may have to talk to the site owner to find out what kind of content they're storing in their site; large files like videos are probably better-suited for a crawled and indexed file share than a SharePoint site.

Clustering wouldn't really address this issue for you.  Don't get me wrong, clustering is definitely a good thing to consider if you've got mission critical data in your environment and need to always have those content dbs available. But the bigger issue w/ large content databases is the amount of time it will take to restore that database from a backup, as well as the amount of time it may take to update that database when you roll out updates for SharePoint such as Service Pack 1 or Office 14.

Thanks,

A novel concept!!!

CW

Yep. 

Keep in mind that a SQL instance can contain multiple databases within it, so according to those numbers you're looking at a max of 50-60 content databases (if they are all expected to max out at 200 GB) per SQL instance.  (Just wanted to make sure you weren't reading that as no more than 2 content dbs per MOSS farm)

Also, you'll want to check with your SQL DBA to make sure that any configuration choices or storage planning you do falls within their tolerances for SQL Server usage.  Ultimately (in my opinion), they should have a better idea of what your SQL Server environmant can support and provide you with guidance around that.

John

Does this impact Search features, or is this just a bi-product of a large SQL CDB? In other words, if I have a 500GB CDB what happens with my Search DB's?

Curt

Hmm... not sure I have enough experience with Search to help you with that question...

Here's what I know (and don't know ):

• Your search index (or indices) is not stored in the database; it's stored by default in the file system of your index server.  The index files can grow quite large, so often you'll see that the storage location is moved to a separate file share or SAN location
• You can back up your search index file via the SharePoint Central Admin GUI, but it's not as mission-critical to do as your content dbs.  The reason being that you can always rebuild your index by recrawling your content, as long as you're willing to wait.  But you can't replace your content if its not backed up.
• I don't know how the search database is involved in the index backup process available through the GUI, or really what's stored it in.  I would guess that it has historical data about search queries, among other things, which may be valuable to retain. 
• I don't know how large to expect the search database to get, sorry.

A coworker of mine pointed out this Wikipedia entry on the Windows Internal Database that has a little bit more good info on it:

http://en.wikipedia.org/wiki/Windows_Internal_Database

The one really relevant item to note in the article is that it provides instructions on how to access the database via the SQL Server Management Studio Express GUI admin tool.  I haven't gotten a chance myself to see if this works, but if it does it definitely makes life with WSS and the WID more manageable.

That is a useful tid-bit of info. Pity I was in the office today and couldn't test it. I will test it next week. If you are using the pipes to get to it, I wonder, how flexible is it really and is there any possibility that a patch will can it? Another thing bugging me is whether this will cause it to suddenly resurrect the 2GB DB limit?

Right now I'm having some difficulty transforming data from an SQL source to XML list items to feed to the WSS list, using code I hacked up in Visual Web Developer Express 2008 (I can get data down from our SQL server and I can also push data into the WSS list, independently). If that were to work, it'll just be a short while of testing by a few users before the ball begins rolling. Perhaps this is another thread for the development board.

EDIT: I just tested it and it works. I can see the DBs and what not.