Tuesday, March 15, 2005

From the Boiler Room - CorasWorks: Understanding the Configuration Database vs the Content Database in a WSS or SPS Environment

CorasWorks: Understanding the Configuration Database vs the Content Database in a WSS or SPS Environment

Courtesy of CorasWorks:

Understanding the Configuration Database vs. the Content Database in a Windows SharePoint Services or SPS Environment

With the move to Windows Sharepoint Services and Sharepoint Portal Server 2003 all data has moved into the SQL server instead of the file system and database. This has made maintenance simpler but the new configuration requires some understanding.

There are 2 logical types of data storage in Windows SharePoint Services & SPS. One is the Configuration database and the other is the Content Database. For those of you with some knowledge of IIS & SharePoint terminology, this comparative review will provide a clearer understanding of how these databases should function in the SharePoint environment.

The Configuration Database

Typically, Windows SharePoint Services & SharePoint Portal Server do not access the configuration database for every user request, but reserve the configuration database for comparatively infrequent administrative tasks such as, extending virtual servers, adding physical servers, and adding web parts.

There should only be one Configuration Database per Windows SharePoint Services environment installation shared by all Virtual Servers. It should store all the global configuration information for your Sharepoint or Windows SharePoint Services implementation and maintain the current set of metadata describing all resources available in the server farm. This allows the configuration database to define the boundaries of a server farm, including all of the metadata necessary to reconstruct a server farm. This includes information such as physical server identification (web server and database servers), globally installed Site templates and content database and installed web part information. (For the database table structure see below.)

The configuration database for Sharepoint Portal Server is a Windows SharePoint Services configuration database configured with SharePoint Portal Server-specific extensions.

Name and Descriptions

AntiVirusVendors- Antivirus software vendors

CustomTemplates- Templates that appear in the global templates list (template picker)

Databases-Content databases and associated settings

Globals-Settings that apply to all sites on the physical server within a Windows SharePoint Services topology

InstalledWebPartPackages-Web Part Packages that are available to the virtual server

Servers-Physical computers in the Windows SharePoint Services topology

Services-Services such as database, Web, and mail services that are included in the Windows SharePoint Services topology

Sites-Sites in the Windows SharePoint Services topology

VirtualServers-Virtual servers and their associated settings

WebPartPackages-Web Part Packages that are installed in a topology

The configuration database for Sharepoint Portal Server is a Windows SharePoint Services configuration database configured with SharePoint Portal Server-specific extensions. Since there is only one configuration database and it is not used in every query its location relative to the servers returning the content is less critical than that of the content database.

The Sharepoint Services Administrative Virtual server Application Pool is the one reading from and writing to the configuration database.

Backup Strategy

Changes occur when web parts / virtual servers / templates / databases are added at a global level. Daily back-up is generally sufficient.

The Content Database

All web part page configuration information, list item data, documents in document and Picture libraries for any given virtual server are contained in the content database.

Close physical proximity to the web servers providing the content for the virtual server is required since this database is used for EVERY page returned to the user.

The application pools assigned to the virtual servers use this database. See below for the database table information.

Name and Descriptions

Categories-Associations among documents and their assigned areas

ComMd-Document discussions

Deps-Document dependencies

DiskWarningDate-Date and time of last warning about disk quota

Docs-Documents and document folders

DocVersions-Version history of documents from document libraries

EventCache-Cache for alerts

EventLog-Log for alerts

HT_Cache-Cache of documents transformed for viewing in a Web browser

HT_Settings-Settings for HTML viewing

ImmedSubscriptions-Alerts that are set to be sent as soon as the changes occur

Links-Links to be recalculated during a site recalculation

Lists-Lists in the site

NavNodes-Nodes that are displayed in the navigation bars

Personalization-Personalization done to Web Parts

SchedSubscriptions-Alerts that are set to be sent on a daily or weekly basis

SiteGroupMembership-Members of cross-site groups

SiteGroups-Cross-site groups

Sites-Sites in the content database

TimerLock-Locking scheme that determines which server locks which database

UserData-List data

UserInfo-Info about each user added to the site

WebCat-Links between Web sites and areas

WebGroupMembership-List of members of the site groups

WebGroups-Site groups (such as Administrator, Web Designer) for the site

WebMembers-Members of a site

WebParts-Web Parts available for the sites. A view is a Web Part

Webs-Subsites associated with a site

WelcomeNames-Default documents for the site

Every Virtual server has at least one physical content database. Every single page requires access to the database. There is one logical content database per virtual server but this could actually be multiple physical databases.

For example, if you wanted to restrict the file size of the database due to back-up concerns or disk space restrictions you can create a second database to assign to a virtual server. This could be on the same physical server as the first content database or another completely different physical server.

Then you would take the first database off-line (meaning that no new sites could be created there but current sites could be accessed) and leave the second to hold new sites.

Backup strategy

Since this database contains every single page & list item it is advisable to back up more frequently than the configuration database. This could be achieved using SQL backup set to run more frequently or there are other options out there in the market. For example Per list item backup solutions available, this allows a single list item to be restored. This presents a great advantage in mission critical list and site usage.

Avepoint-http://www.avepoint.com/website/feature_docave_2.5.html is one example.

Note from the field - Implications of Database storage for External Cache effectiveness.

In our experience there is little benefit to external caching in front of a Windows SharePoint Services or SPS infrastructure due to the fact that the last modified date on web pages is always the time the page was requested – making caching almost useless. This is because the page is composed on the fly from the database contents.

Only those files served from the _layouts folder (ows.css & shared graphics files for example) can be cached since they have a reliable modified date. In fact we only saw around 1% of content being delivered from the cache on our ISA server rather than the almost 99% being directly delivered from the Windows SharePoint Services & SPS server infrastructure.