Quick – What’s the maximum recommended size for a Content Database? (Hey, no cheating and looking it up online!)
If you’ve been reading about Microsoft’s SQL DB sizing best practices for SharePoint, you will notice a pretty stock set of recommendations:
| SharePoint Version | Content Database Size |
| WSS 3.0 or MOSS 2007 | No more than 100GB |
| MSF 2010 or MSS 2010 | No more than 200GB |
Table 1 – SharePoint Versions and Max Content DB Size Recommendations
These numbers are really intended as prescriptive guidance rather than mandatory limits. I have seen (but certainly don’t recommend) organizations have production SharePoint Content Databases in the several hundreds of Gigabytes (GB). In reality, your organization may need/want to consider a number smaller than 100/200GB.
Two important considerations for database size are Backup Time and Service Level Agreements (SLAs). If you have a SQL DB Admin, go ask him or her what is the maximum database size they would be comfortable restoring. Odds are, the DBA will either have a self-imposed limit based on experience or a SLA mandate from management.
What happens if YOU are the DBA/SharePoint Admin in a smaller shop – what then? Well, when you evaluate the maximum DB size you’d be interested in maintaining, the real questions that need to be asked are:
How much data are you comfortable restoring at one time?
- When was the last time you attempted a backup from your media (Disk/Tape/SAN)? Do you have an established metric of how many GB you can restore per hour?
- Having this number defined gives you a de facto guide as to how long a restore will take (#HRs/GB x #GB to be restored = Duration in Hours). So if you can comfortably restore, say, 20GB per hour, a 100GB content DB may take you 5 hours to restore – which leads us to the next question
How much time will your business allow for a restoration to take place?
- Time is Money. Don’t believe me? Go ask your manager about the last time there was a server outage. If your business can withstand a 5 hour restoration window, then you are probably OK, but that does not necessarily mean that the business will be happy about waiting.
- On the other hand, if you are in a business where your SharePoint farm directly affects the ability of your business to make money, odds are that the restoration window must be shorter.
So you start your installation with a Portal site and you put it into its own Site Collection (maybe even its own Content DB). Next, you perhaps build another series of Site Collections for each department in your organization, and you put these into their own Content Database(s).
But what about the collaborative/project sites? Do these get put under a particular department?When setting up a SharePoint installation, it is important to remember that there will be sites that are project-, task-, or document-based; this is true for Microsoft SharePoint Framework 2010, Microsoft SharePoint Server 2010, and Microsoft Project Server 2010 sites alike (this also is true for 2007 versions).
A Need For Site Collections
Collaborative/project sites are generally time boxed. A site such as this may only exist for a few days/weeks/months and then need to be archived. Each of these archives must be full-fidelity, meaning that they can be restored individually as a complete, stand-alone entity (happens all the time in lawsuits).
A fairly simple way to approach this is to ensure that these sites are created as Site Collections (referred to as a Site in the Object Model), not as Sites (a Web in the Object Model). The backup of a site simply will not do, because it cannot be backed up as a completely standalone unit. These Site Collections can be created manually, but are most often created automatically (this can be configured to happen in SharePoint and Project Server alike).
Site Collections are stored in Content Databases. In the SharePoint 2010 example shown below (Central Administration –> Manage Content Databases), we can see that there is currently one content database for the http://titan URL and one for collaborative sites (these appear under the http://titan/sites/ URL).
Illustration 1 – Original Content Databases, one for Collaborative and one for Portal Content
A closer examination of this image screen reveals that this administrator has limited the WSS_Content database to only having a maximum of two Site Collections in this database. The other database, Collaborative_WSS_Content_1, is intended for multiple Site Collections – the problem here is that this database (by default) can contain 15,000 Site Collections.
Doable? Most certainly. Best Practice? No, I think not. Remember our 100GB/200GB limits? Even if you could comfortably and quickly restore these databases, do the math:
100 GB / 15000 Site Collections = .0067GB per Site Collection (6.7MB per Site Collection)
That’s not gonna fly. Even at the 9,000 Site Collection “warning” number, the number works out to be a whole 11 Megabytes per Site Collection – I think my watch has more storage than that.
What’s a good admin to do? Scale Out!
You could take it upon yourself as the admin to make sure that you build up new Content Databases as you near a fixed limit of Site Collections – and there’s a PowerShell script for that. And you can move Site Collections that grow beyond what you had originally envisioned – that will happen anyway (there’s also a PowerShell script for that).
Let’s think about this a little more.
In our case, the admin chose instead to simply limit the portal database (the poorly named WSS_Content database) to maintaining two Site Collections. All collaborative sites will be created in the Collaborative_WSS_Content_1 Content Database. So far, so good, but this DB will eventually top out at whatever the chosen # of Site Collections/DB is.
SharePoint has a built-in (but often overlooked) process to fill out databases in a “round-robin” fashion. There’s no special configuration required to make this happen. This occurs on a per Web Application basis, so some companies actually put their collaborative/project sites under its own URL.
In our case, the intrepid SharePoint Admin simply creates a couple of new Content DBs, and choses the maximum number of sites (remember, this really means Site Collections) to be 100. This works out to about 200MB per site for a 20GB Content Database (Hey, maybe this should be the number set as a Site Collection Quota for this Web App!):
Illustration 2 – Content Databases added for Collaborative Site Collections
Looks like we might be onto something here. As new Site Collections are added to SharePoint/Project Server, they are evenly distributed among the databases (nope, a 9th collaborative site hasn’t been built yet):
Illustration 3 – Content Databases scaling out with new Site Collections
At some point, the admin may decide to add a new Content DB to the mix, perhaps worried about overall growth. If new Content DBs are added, SharePoint will fill in those DBs first before adding new Site Collections to the other Content DBs:
Illustration 4 – New Content DBs
Distributing the content over multiple Content DBs lessens the footprint of any database corruption that might occur and also will shorten the restore time required to put any one Content DB back in service.
