Best practices for SQL Server 2008 in a SharePoint Server 2010 farm

Applies to: SharePoint Server 2010

This article describes best practices for configuring and maintaining Microsoft SQL Server 2008 in a Microsoft SharePoint Server 2010 environment. These practices are ordered based on the sequence in which they would apply, from installing and configuring SQL Server 2008, to deploying SharePoint Server, and then maintaining the farm.

This article is one of a series of Best Practice articles for SharePoint Server. For more articles in the series, see Best practices (SharePoint Server 2010). For additional information and resources about Best Practices for SharePoint Server 2010, see the Best Practices Resource Center (https://go.microsoft.com/fwlink/p/?LinkID=220280).

1. Use a dedicated server for SQL Server 2008

To ensure optimal performance for farm operations, we recommend that you install SQL Server 2008 on a dedicated server that is not running any other farm roles and is not hosting databases for any other application. The only exception is if you are deploying SharePoint Server 2010 on a stand-alone server, which is not recommended for large-scale production environments.

2. Configure specific SQL Server 2008 settings before you deploy SharePoint Server 2010

To ensure consistent behavior and performance, configure the following options and settings before you deploy SharePoint Server 2010.

• Do not enable auto-create statistics on a SQL Server that is supporting SharePoint Server.

• Set max degree of parallelism (MAXDOP) to 1 for SQL Server instances that host SharePoint Server 2010 databases to ensure that each request is served by a single SQL Server process.

  Important

  In a SharePoint Server environment, any other setting will result in a sub-optimal query plan being selected for execution, which will decrease overall performance.

• To improve ease of maintenance, and make it easier to relocate the database if it is required in the future, create DNS aliases that point to the IP address for all instances of SQL Server.

For more information, see Set SQL Server options.

3. Harden the database server before you deploy SharePoint Server 2010

We recommend that you plan for, and harden the database server before you deploy SharePoint Server 2010. This includes securing the database server role for SharePoint Server and SQL Server. For more information, see:

• Plan security hardening (SharePoint Server 2010)

• Harden SQL Server for SharePoint environments (SharePoint Server 2010)

4. Configure database servers for performance and availability

As is the case with web servers and application servers, the configuration for database servers affects how well SharePoint Server 2010 performs. Certain databases require specific co-location with or separation from other databases. For more information, see:

• Capacity management and sizing overview for SharePoint Server 2010

• Storage and SQL Server capacity planning and configuration (SharePoint Server 2010)

For highly available databases that use mirroring, consult the best practice guidance in Database Mirroring Best Practices and Performance Considerations (https://go.microsoft.com/fwlink/p/?LinkID=185119).

5. Design storage for optimal throughput and manageability

We recommend that you separate, and prioritize your data among the disks on the database server. Ideally, you should place the tempdb database, content databases, usage database, search databases, and SQL Server 2008 transaction logs on separate physical hard disks. The following list provides some best practices and recommendations for prioritizing and managing data and logs. For more information, see Configure databases.

• For collaboration or update-intensive sites, use the following ranking for storage distribution:

  1. tempdb data files and transaction logs on the fastest disks

  2. Content database transaction log files

  3. Search databases, except for the Search administration database

  4. Content database data files

• In a heavily read-oriented portal site, prioritize data and search over transaction logs as follows:

  1. tempdb data files and transaction logs on the fastest disks

  2. Content database data files

  3. Search databases, except for the Search administration database

  4. Content database transaction log files

• Testing and user data shows that overall farm performance can be significantly impeded by insufficient disk I/O for tempdb. To avoid this issue, allocate dedicated disks for tempdb.

• For best performance, place the tempdb on a RAID 10 array. The number of tempdb data files should equal the number of core CPUs, and the tempdb data files should be set at an equal size.

• Separate database data and transaction log files across different disks. If files must share disks because the files are too small to warrant a whole disk or stripe, or you have a shortage of disk space, put files that have different usage patterns on the same disk to minimize concurrent access requests.

• Use multiple data files for heavy-use content databases, each on their own disk

• To improve manageability, limit content database size to 50 GB

Proper configuration of I/O subsystems is very important to the optimal performance and operation of SQL Server systems. For more information, see Storage Top 10 Best Practices

6. Proactively manage the growth of data and log files

We recommend that you proactively manage the growth of data and log files by considering the following recommendations:

• When possible, pre-grow all data files and log files to their expected final size, or periodically increase these at set periods, for example, every month or every six months, or before rollout of a new storage-intensive site such as during file migrations.

• We recommend that you enable database auto-growth as a protective measure to ensure that you do not run out of space in data and log files. Consider the following:

  Important

  You must factor in the performance and operations issues associated with using auto-growth. For more information, see Considerations for the "autogrow" and "autoshrink" settings in SQL Server (https://go.microsoft.com/fwlink/p/?LinkID=117750)

  • Do not rely on the default settings for auto-growth, use the guidance provided in Set SQL Server options.

  • Set autogrow values as a percentage instead of fixed number of megabytes. The bigger the database, the bigger the growth increment should be.

    Consider for example, a scenario where content is gradually increased, say at 100MB increments, with autogrowth set at 10MB. Then suddenly a very large quantity of data storage is required for a new document management site, perhaps with initial size of 50 GB; we would want this to grow at 500 MB increments not 10MB increments.

  • For a managed production system, you must consider auto-growth to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis using the autogrow option.

• Maintain a level of at least 25 percent available space across disks to accommodate growth and peak usage patterns. If you are managing growth by adding disks to a RAID array or allocating more storage, monitor disk size closely to avoid running out of space.

7. Continuously monitor SQL Server storage and performance

We recommend that you continuously monitor SQL Server storage and performance to ensure that production database server is adequately handling the load put on it. Additionally, continuous monitoring enables you to establish benchmarks that you can use for resource planning.

Take a holistic view of resource monitoring; do not limit monitoring to resources that are specific to SQL Server. It is equally important to track the following resource components of a server that is running SQL Server: CPU, memory, cache/hit ratio, and the I/O subsystem.

When one or more of the components of the database server seems slow or overburdened, analyze the appropriate strategy based on the current and projected workload. For more information, see:

• Monitoring and Tuning for Performance (https://go.microsoft.com/fwlink/p/?LinkID=131173)

• Troubleshoot performance problems in SQL Server 2008 (https://go.microsoft.com/fwlink/p/?LinkID=168448).

• Performance Monitoring Getting Started Guide

8. Use backup compression to speed up backups and reduce file sizes

Backup compression can speed up any SharePoint backup, and is available in SQL Server 2008 Enterprise Edition or SQL Server 2008 R2 Standard edition. By setting the compression option in your backup script, or by configuring the server that is running SQL Server to compress by default, you can significantly reduce the size of your database backups and shipped logs. For more information, see Backup Compression (SQL Server) (https://go.microsoft.com/fwlink/p/?LinkId=129381) and Data Compression: Strategy, Capacity Planning and Best Practices (https://go.microsoft.com/fwlink/p/?LinkId=223674).

Acknowledgements

The SharePoint Server 2010 Content Publishing team thanks the following contributors to this article:

• Stephen Dillon, Senior Consultant

• Gus Apostal, Senior Program Manager, SQL Server