SharePoint 2016: Exploring SharePoint Databases

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.