27

Managing and Optimizing SharePoint SQL Server Databases

When you install SharePoint Server 2010 Enterprise Edition, you will start with several SQL Server databases. Most of the databases will be suffixed with a system-generated GUID. These names can be changed, but that is a book within itself. This lesson provides an overview of the different databases, a brief explanation of what is stored in most of the databases, and provides a few techniques that can be used to optimize each database type.

OVERVIEW OF SHAREPOINT 2010 DATABASES

As mentioned earlier, when SharePoint is installed several databases are created. Each database contains data that is specific to a certain area of SharePoint 2010. In some cases there will be more than one of the same type of database, but it will be suffixed with a different GUID. Figure 27-1 lists the databases that will be created after a default farm install.

The database model in SharePoint 2010 has been modified significantly. Instead of storing all content in a few large tables, the data is stored in different tables. This change allows several people to work on large projects without creating contention on the SQL Server.

SharePoint 2010 also introduced a few new databases. Whenever you configure a new service, a new database is created. For example, when you configure the Secure Store Service and PerformancePoint Service Application, two corresponding databases are created, as shown in Figure 27-2.

FIGURE 27-1

FIGURE 27-2

In addition, User Profiles requires three databases: Synchronization, Profiles, and Social Tagging, as shown in Figure 27-3.

FIGURE 27-3

This is not an exhaustive list of all the changes, but it provides you with some insight into how Microsoft has changed the structure to accommodate large-scale deployments of SharePoint 2010.

TYPES OF CONTENT IN THE SHAREPOINT 2010 DATABASES

SharePoint 2010 databases contain five basic types of information: Logging, Configuration, Content, Service Application, and Search. The Search type could be grouped with the Service Application content, however, we separated it because it serves a different purpose.

Each database serves a specific purpose; therefore they each store data that is specific to that purpose. Careful consideration should be taken when designing your server architecture and disk layout for these databases. A few tips are given in the next section in regard to these topics. This section focuses on the data that is stored inside of these databases.

The first content type is Logging. The only database under this category is the WSS_UsageApplication database or the Usage and Health Data Collection Database. This is the only database whose schema can be modified and its data queried. It stores health monitoring and usage that can be used for reporting. The data in this database is temporary and is removed based on schedules that are configurable in the Central Administration console. In the list of databases it is prefixed with WSS_UsageApplication.

The next type is Configuration. Two databases fall into this category: the Configuration database, whose name will appear as SharePoint_Config on the SQL Server, and the Central Administration database, whose name will appear as SharePoint_AdminContent. The Configuration database stores information about SharePoint databases such as Web Part packages, web application and farm settings, web applications, and websites. These are read-intensive databases because most of the configuration information is needed as regularly as the site is accessed. This database also coordinates all deployment administration, which includes managing load-balancing and directing appropriate database requests. For example, when a web server receives a request, a call is made to the Configuration database to determine which content database contains the data for the requested site. The Central Administration database stores all site content. When you upload documents to your document libraries, that content is stored in this database. In addition, Web Part properties and Central Administration user names and site collections rights are also stored in this database. Finally, if you install PowerPivot, the workbooks and the corresponding data are stored in this database.

In addition to Configuration databases are Content databases. These databases contain data specific to site collections. Note that a content database can be associated to more than one site collection. In addition to the site collection, content databases contain Office Web Application caches. Each content database is suffixed with a GUID. Your installation of SharePoint may have several content databases, and they will all be prefixed with WSS_Content, as shown in Figure 27-4.

FIGURE 27-4

The next type is the Service Application content. As mentioned earlier, each service application produces a new database and each database will contain information about that service application. For example, in the Business Data Connectivity Database, which is prefixed with Bdc_Service_DB_, data regarding external content types and related objects are stored. When you create a connection to an external data source, the metadata about that connection is stored in this database. There is also a PerformancePoint database that stores temporary objects, persisted filters, and user comments. This is all information specific and pertinent to the content that is relevant to your scorecards and dashboards. The State database is another service application database. It stores temporary state information for Visio Services and chart Web Parts.

The user experience is a major part of SharePoint 2010. Therefore, Microsoft scaled the storing of user information to three databases. The Profile database, User Profile Service Application_ProfileDB_, stores information about each user and their associated information. The Synchronization database, User Profile Service Application_SyncDB_, contains staged data that has been synchronized with a directory service like Active Directory. Lastly, the Social database, User Profile Service Application_SocialDB_, stores all the notes and tags created by users.

The final type is Search content data. There are three Search service application databases similar to the User Profile databases. There is an Administrative, Crawl, and Property database. The Administrative database, Search_Services_Application_DB, is accessed for every user administrator and user action. It stores the access control list (ACL) and the search service application configuration data. The Crawl database stores the crawl history and the state of the crawled data. For example, it stores information about items and hosts that are pending processing or already indexed. The final search service application database is the Property database. This database stores properties, queues, and history that is associated with the crawl data. The Crawl and Property databases work together to assist in SharePoint Search capabilities.

This is not an exhaustive list of all the databases that may be part of your SharePoint configuration. However, this section includes most databases that are part of your default installation and a few that are required to finalize your Business Intelligence deployment.

OPTIMIZATION TECHNIQUES FOR EACH CONTENT TYPE

As with any SQL Server, certain configurations must be done on the databases to ensure that they are optimized. In addition, steps must be taken to guarantee that the data is available in the event of a catastrophe or some type of unforeseen failure. This section provides a few techniques that will assist you in improving the performance of your SharePoint SQL Server deployment and a few methods that will assist in making sure that your data is available at all times.

We start the discussion with the configuration databases. These databases are very important to the SharePoint deployment because they contain information that is accessed more frequently than most of the data. They both are read-intensive databases. As a result, you must take precautions to ensure that the data retrieval is fast and that the databases are always online. Generally, the two configuration databases are small. However, if you are using PowerPivot, the Central Administration database may grow over time. Coupling this with the fact that there is only one of these types of databases per farm requires that you implement a strategy that allows the database to scale up. The database recovery models for these databases will be different. For the configuration database, if you do not plan to use Database Mirroring, we recommend that you change the recovery model to Simple. This will help limit the growth of the transaction log. For the Central Administration configuration database you should set the recovery model to Full. Because you may store user data (PowerPivot) in the database, there may be a need to restore to a point in time. By setting the recovery model to Full, you can back up the transaction log and quickly restore to a specific date and time if needed.

The databases that contain the content data can be both read- and write-intensive. The nature of the IO will depend on the purpose of the site. If you are using it for document management, it will be read-intensive. However, if you are using it for collaboration, it will be write-intensive. Regardless, we recommend that you plan for both. Therefore, the databases should be able to grow as large as needed while at the same time support as many users as needed. Capacity planning and SQL Server configuration is a little beyond the scope of the book. However, if you anticipate that your content databases are going to grow in excess of a terabyte, you should plan for at least 32 gigabytes of memory. In addition, you should ensure that you create multiple database files for your content databases and distribute those files across separate dedicated disks.

The Logging database will be a very active database in regard to IO. Therefore, we recommend that the database is placed on its own set of dedicated disks. Also, just as with aforementioned databases, you should have plans in place that will allow this database to scale up. This is because it will be the only database of its type in the farm and it will grow larger. The size of the database will depend solely on how the retention factor is configured and the number of items that have been enabled for logging. There are certainly other factors, but these will contribute most of the data. Because this database can easily be re-created in the event of a failure, the recovery model should be set to Simple.

In regard to the Service Application databases, it is really challenging to provide specific methods and techniques because they all store different types and amounts of data. They also have very different IO patterns, thus making the challenge of defining optimizations more difficult. The fact is that when architecting your database design strategy for this content type, you should take a holistic view of all the service applications you are going to configure and devise a scheme that accommodates the database that will require the most read- and write-intensive scenarios. For example, let's assume that you configure the Secure Store Service and PerformancePoint Services. The Secure Store Service database is read- and write-intensive, whereas the PerformancePoint database's read and write patterns are variable. As a result, you could decide to locate the two databases on separate disks, which would help limit the contention on the disk. On the other hand, you could configure the disks in such a manner that they can handle both read-and write-intensive operations via RAID configuration. Either approach should yield a highly performing set of databases.

In regard to your backup strategy, we recommend that you specify the recovery models based on the content that is stored within the database. For example, if you have configured PerformancePoint, some user annotations are persisted and may increase as the number of users increase. Therefore, we recommend that you set the recovery model to Full to ensure the availability of the data. On the other hand, this strategy could be changed for those service applications that contain data that is not changed very often. Additionally, the backup and optimization techniques that have been outlined for the service application content type apply to the search content type also.

TRY IT

In this Try It you create a spreadsheet that will be used to architect your SharePoint SQL Server database configurations.

Lesson Requirements

Create spreadsheet in which you will define three characteristics about each SharePoint database. These characteristics will help the database administrator properly configure the database. The characteristics are Read/Write Intensive, Stores User Data, and Size. You will need to download the Lesson27_Workbook from www.wrox.com.

Hints

• Databases that are accessed frequently are read-intensive.
• Databases that receive a lot of user data are write-intensive.

Step-by-Step

1. Open the Lesson27_Workbook.xlxs.
2. On the row that contains SharePoint_Config as the database name, click in the column labeled Read/Write Intensive. A drop-down arrow will appear, as shown in Figure 27-5.
3. Click the drop-down arrow and select Read.
4. Click in the column labeled Stores User Date and a drop-down arrow will appear.
5. Click the drop-down arrow and select No.
6. Click in the column labeled Size and a drop-down arrow will appear.
7. Click the drop-down arrow and select Small.
8. Repeat the steps for the WSS_Content database and select Both from the Read/Write Column, Yes from the Stores User Data column, and Large from the Size column.
9. Repeat the steps for the PerformancePoint Service Application database, selecting Both from the Read/Write Column, Yes from the Stores User Data column, and Small from the Size column.
10. Repeat the steps for the WSS_Logging database, selecting Write from the Read/Write Column, No from the Stores User Data column, and Large from the Size column.
11. Repeat the steps for the SharePoint_Admin_Content database, selecting Write from the Both Column, Yes from the Stores User Data column, and Small from the Size column.

    Your spreadsheet should resemble Figure 27-5.

    

    FIGURE 27-5