Introduction
This post introduces the various types of databases within a
SharePoint farm and explores each type and instance.
Types of SharePoint Databases
The databases that are created for use
of SharePoint 2016 are categorized as one of three types:
- Configuration
- Content
- Service Application
The configuration database is created
when you install the first instance of SharePoint and create a new farm. There
is only one configuration database and it is essentially your “farm” from a SQL
Server perspective. When you attach servers to your farm you select the
configuration database accordingly. The Central Administration content database
may also be considered a configuration database as the content it stores deals
with the configuration of the farm. More details about the configuration
database are explained in the next section.
Not to sound smart but the content
databases store all of the content within the SharePoint sites. Each Web
Application that is created on the farm may have one or more content databases
associated. This is where all of the pages, list items, documents, etc. are
physically stored. The smallest block of content in a content database is a
site collection. Therefore, a content database stores one or more site
collections. A site collection can only “live” in one content database.
It is a good idea to have multiple
content databases to help partition the SharePoint sites and provide
flexibility in backup and restoring. It is much easier to maintain a 40GB
database than a 400GB database for example. This book will explain more details
and functions that may be performed with content databases.
Finally, the DBA’s nightmare, are the
service application databases. For each service application in SharePoint, one
or more databases are created to maintain the settings and configurations of the
particular service application.
Previously in SharePoint 2007 (MOSS
2007), several of these services (Search, User Profiles, Business Data Catalog,
and Excel Services) were combined into the Shared Service Provider (SSP)
service of SharePoint. Usually this meant only one SSP database although you
could have more than one SSP and thus multiple databases.
SharePoint 2010 changed the
architecture of the SSP and split out each service into its own service
application. This provided more flexibility and scalability; however, it also
created more databases.
The same architecture model exists in
SharePoint 2016 and there are several new service applications - which again
means more databases. So that’s why I say these are the DBA’s nightmares because
now there can be 10-20 databases just for the service applications (Enterprise
search creates and uses four itself).
Configuration Database
The configuration database, as explained
previously, is essentially the “farm” from a SQL Server perspective. This
database is generally small and should maintain with less than 1GB of space.
The configuration database stores data
about the following:
- All of the other SharePoint databases
- IIS Web Sites
- Site Templates
- Specific Farm Settings - Quotas, Blocked File Types
- Trusted Solutions
- Web Applications
- Web Part Packages
This database is very read intensive,
however, as modifications and deployments are executed, the transaction logs
can get bloated. Therefore if you keep the recovery model in the default Full
state, it is recommended to backup the transaction log regularly for truncation
purposes. Otherwise, it is recommended to switch the recovery model of the
configuration database to Simple.
Central Administration Content Database
The Central Administration (Central
Admin) Content Database is used to store the content of the Central Admin web
application which is used to administer and configure farm wide systems and
services. This database also is generally small and should maintain at less
than 1GB of disk space (just like the configuration database).
The recovery
model defaults to Full it is fine to keep it that way. There shouldn’t be too
much read/writes as it is usually just the SharePoint Administrator(s) who is/are
making modifications and generally content such as documents is not uploaded to
Central Admin like a normal content database. However, it could increase in size if
PowerPivot is installed and deployed within the SharePoint farm. All of the
Excel worksheets and Power Pivot data files used in the Power Pivot Management
Dashboard are stored within the Central Admin content database.
The default database name is
SharePoint_AdminContent_<guid>. DBA’s tend to hate the GUID in the database
name because a) it is not “clean” and b)
backup programs tend to have issues with the GUID involved. Therefore it is
recommended to create the Central Admin site via PowerShell as explained in Chapter 2 of my DBA Guide.
Content Databases
The Content Databases store all of the
content for all of the site collections on the farm within a given web
application. This includes list items, documents, web part settings, user
information, and other site related configurations.
Each web application must have at
least one content database but may have multiple. A site collection can only
live in one content database. While the recommended max size is 200GB (although
up to 1TB is supported), I personally like smaller 40-50GB content databases.
It is easier to backup and restore smaller databases as well as copying the
backup files around to different servers.
 |
| A web application may have multiple content databases which house multiple site collections |
Service Application Databases
The service application databases are created when their
respective service applications are created. If not all service applications
are needed or created, then obviously the databases are not needed or created.
The details of each service application database are explained in my DBA guide book.
Conclusion
There are many databases involved in a SharePoint farm. Understanding each database and how to manage them is essential for SQL Server administrators. My DBA guide attempts to enlighten those that manage SQL Server but do not understand SharePoint.
