[SP2010] Database Changes

Rate this item
(0 votes)

SharePoint 2010 database model has been changed significantly in the way of distributing data across several databases that makes horizontal distribution of SharePoint 2010 solutions very feasible.

There are three areas of changes - architecutre changes, new databases and new tables.

Architecture Changes

SharePoint 2007 stored all content in large tables (for example lists, site collects end etc were represented as a huge table), and the performance were affected by SQL Server locks, especially in the large collaborative projects, when a lot of people use the same content and SQL Server locked the table, so no one can work till person generated the lock release the content (SQL Server 2005 suffered from this very much, because its internal behavior tends to lock table when 2000+ items are selected). I posted several tips, explaining such behavior (see tip #29)

Thus, you were limited with 2000 items in List and should use several Site Collections in case of large collaborative community.

SharePoint 2010 changes solved those issue - moving content from the singe tables into different tables. Such changes allows you to store million items in the lists (yep, we are step close to ECM requirements I blogged earlier) and have very intensive collaborative project in one Site Collection.

100 GB "recommendation" on the Content Database size is not an issue any longer.

New Databases

SharePoint Shared Services became deprecated in SharePoint 2010, and instead we have new Services architecture, where all services are independent from each other. Such architecture affected the way information and configuration are stored and each services has it's own database.

Previously, with SharePoint 2007 we had at least 6 databases for each installation

  1. Central Administration - [SharePoint_AdminContent<GUID>]
  2. Configuration - [SharePoint_Config]
  3. SSP - 3 databases for SSP settings, MySites, and Search
  4. Web Application - custom database

With SharePoint 2010 model we still have Central Administration and other databases, but instead of point 3 we end up with the separate database for each Service, for example [AccessServices], [MetadataServices], [WebAnalyticsReporting] and etc. So, we can easity have 15 databases only for the simple SharePoint 2010 solution.

[new] User Profiles use 3 databases: [Profiles] - for the actual profile content, [Syncronization] to keep configurations of the extenal data and [Social Tagging] to store tags and noted created by users

SharePoint Configuration Database Changes

There are several changes of the tables in the SharePoint Configuration database. The following tables have been added:

  1.  AllFileFragments
  2. AllListAux
  3. AllListPlus
  4. AllListUniqueFields
  5. AllLookupRelationships
  6. AllWebParts (Renamed from WebParts)
  7. CustomActions
  8. Resources
  9. SharedAccessRequest
  10. SiteDeletion
  11. SolutionResourceUsageDaily
  12. SolutionResourceUsageDailyOriginal
  13. SolutionResourceUsageLog
  14. SolutionResourceUsageLogWindowed
  15. Solutions
  16. WebsPlus

Removed Tables:

  1. Categories
  2. Image0x
  3. WebCat


Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.