This is a very common question across the board. Microsoft’s documentation recommends a maximum of 200GB per content database. This isn’t a limitation, just a recommendation. They are recommending this level due to performance and maintenance considerations. When your database gets much larger than that, there are some additional steps you need to take to optimize SQL’s performance. Check out MS’s white paper on Managing Multi-Terabyte Content Databases with Microsoft SharePoint 2010. This paper explains what’s needed to optimize databases between 200GB and 4TB, and then databases even larger than that!
RBS externalizes the documents SharePoint stores in SQL. Read more about RBS. This does allow for smaller content databases, however you’re still using up a large amount of space, somewhere. Microsoft’s recommendations as mentioned above should be considered including the space used in RBS.
SQL Express is a great solution for development environments, and even staging if budgets are tight and the full SharePoint Server experience isn’t required. I wouldn’t recommend it for a production environment at all. Maintenance is a lot more difficult and performance will be significantly limited with SQL Express. You cannot schedule maintenance tasks like backups. You’ll have to manage all of that outside of SQL using scripts or third-party tools. Also, the Express edition has a database size limit of 10GB, and will only use 1GB of RAM. SQL wants as much RAM as you can give it, 1GB for production will slow things down considerably.
This can go two ways, though you’ll quickly see which is preferred. As noted above, the size of the database doesn’t doesn’t matter, too much, since you can work around maintenance tasks and performance issues. I prefer and recommend to load a site collection into its own content database, instead of managing multiple sites in one database. Not only does this keep the database sizes small, it also helps with recovery.
For instance, lets say you have a site collection for each of your departments, and the HR department does something that warrants a restore. If all of your sites were in one databases, you’d be forced to restore all of the site collections within that same database (or restore it to another farm, then backup that one site collection and move it over). Instead, with each site collection in its own databases, you can restore that one database and be back to work.
It’s not too late if you already have several site collections in your database. Check out the following article on Moving site collections between databases.
- Database Types and Descriptions: http://technet.microsoft.com/en-us/library/cc678868.aspx
- Databases that support SharePoint 2010 http://go.microsoft.com/fwlink/p/?LinkId=187969 (pdf)
David, this post is awesome. Your “Should I run multiple site collections in a single database?” advice is spot one and something people rarely consider (SLA’s with restoring).