Friday, November 20, 2009

SharePoint 2007 content database and its maximum size

Hi all,

The SharePoint product and technology recommends 100 GB of content database per site collection as the best practice for WSS v3 and MOSS 2007 deployments. In one of SharePoint conference Joel Oleson recommended 150 GB for a content database per site collection

Q. What happens if our content database is more than 100 GB or 150 GB?
A. Well, SharePoint 2007 and Content Database are built on SQL server & has tight cohesion with SQL tables. If content database goes beyond 150 GB, this is likely to happen:

  • SharePoint 2007 database team chose an approach where they use few tables and not using many small tables. Now, using this approach the whole SharePoint Database deisgn is prone to table-locks if content database exceeds 100-150 GB limit. Its to be noted that its NOT A LIMIT but its a recommendation.

    Moreover, SharePoint 2007 database schema has large tables (Lists, Site-Collections with huge tables) and the performance were affected by SQL Server locks. Microsoft also recommend 2000+ items for the lists

    This is commonly referred to as the “100GB content database size limitation".

    NOTE: in SharePoint 2010, 100 GB recommendation for the current database is not a problem any longer. Its due to the major architecture changes are done to the database. Please refer this article to get more information about SharePoint 2010 database changes. This time they have moved the content from single table to several tables. Hence, we can store million of items in the lists and can a huge collaborative project in just one site collection.

  • May hit the Performance SharePoint-sites.

    For an instance, we have a site-collection which contains 300 sub-sites. If the content database exceeds 150 GB or more, and users are doing some kind of insertion/update etc then that particular site collection table will be locked and subsequently other sub-sites.
If have a very large collaboration SharePoint (like 350 GB), its always to split into different site collections. Each site collection has its own content database. Therefore, in this particular case we can split 2 site collections with each site collection with its separate content database.

Microsoft has a tool for splitting site collection and move across content database: "SharePoint Administration Toolkit".

I have read some blogs where people have deployed their productions server with content database around 350 GB and growing (VSS-based backup solution in place).

NOTE:
Also, I have seen blogs where some people suggest never touch SharePoint database and they recommend to use SharePoint API (Object Model) to do any kind of updates. Its quite a valid argument but at times we need to modify the SharePoint database if a new solution or some feature get messsed up but developer MUST NOT delete any row or table if he/she does not know the usage of that table.
For an instance, look my blog, where I had developed a timer job solution and forgot to deactivate the feature, changed the scope of feature directly and upgraded the solution. My entire SharePoint environment got screwed up.
Apparently, SharePoint UI cant help . I have to modify the Database (SharePoint Configuration database ).

In fact, Sql server can go to terabyte level, but SharePoint 2007 database schema has limitations. Practically speaking, the recommendation is based primarily on two significant factors:

  1. Service Level Agreement (SLA) requirements for a given organization may dictate that backup operations for the SharePoint databases must be executable in a limited amount of time. The size of the content databases will have a direct impact on how long it takes to execute that backup.


  2. The storage subsystem must be robust enough to handle the disk I/O requirements of the SharePoint solution that it serves.


There is useful article by Joel Oleson about How large for a single SharePoint content database?

Cheers,
--aaroh

References:
1) Inspecting SharePoint Database
2) How big is SharePoint Database
3) Split large collaboration site
4) Understanding SharePoint database
5) Limitation of ECM solutions

1 comment:

Anonymous said...

How much will be the max size of the database of the built-in sql-2005 database? 3 GB?

How to display Sharegate administrative dynamic reports via Power BI on SharePoint Online

After working in the SharePoint domain for a couple of years as SharePoint consultant , I am fortunate enough to help customers from man...