Special SQL Server configuration and considerations for SharePoint 2010

Article
11/20/2012

[Revised & Updated on 02/20/2014]

Introduction

This article will help SQL DBAs new to SharePoint 2010 understand what is required to deploy SharePoint 2010 from the SQL perspective using best practices. It also describes the activities required to support the SharePoint farm SQL server to be in a healthy state.

SharePoint databases types and data recovery consideration

This section describes the databases that are installed for Microsoft SharePoint Server 2010. The database names listed in the table below are automatically created when you run the SharePoint Products Configuration Wizard. You do not have to use these naming conventions. You can either specify database names when you create them, or change the database names after they have been created (for some of them).

** 02/20/2014 - User Profile, User Profile Sync and User Profile Social databases support SQL backup\restore as per the update MSDN article here.

1- Configuration\Admin DB Backup and recovery. The configuration and central admin databases are backed up when you perform a SharePoint farm configuration and content backup, and some configuration settings from the database are exported and stored as XML files. When a farm is restored, the configuration database is not restored. Instead, the saved configuration settings are imported. The configuration and central admin database can be successfully backed up and restored by using SQL Server or other tools if the SharePoint farm is first taken offline.

Design considerations

1- Use a dedicated server for SQL Server 2008\2012

2- Use physical servers for SQL Server 2008\2012. (Recommended)

3- Place the tempdb database, content databases, usage database, search databases, and SQL Server transaction logs on separate physical hard disks.

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

tempdb data files and transaction logs on the fastest disks
Content database transaction log files
Search databases, except for the Search administration database
Content database data files

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

tempdb data files and transaction logs on the fastest disks
Content database data files
Search databases, except for the Search administration database
Content database transaction log files

6- To avoid performance issue, allocate dedicated disks for tempdb(s).

7- 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.

8- 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.

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

10- 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.

11- 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.

12- You cannot use a backup made from one version to restore to another version. To do this, you must use the upgrade process. You cannot restore to a farm with a lower update level than the update level of the farm that you backed up. The destination farm must have the same or newer update level.

Configurations

1- Disable auto-create statistics & [Update:02/20/2014] auto-update statistics on a SQL Server that is supporting SharePoint Server.

Explanation why we don't leave auto-create\auto-update stats on by default:

SharePoint has its own stored procs that will take care of both the creation and update of statistics to the best of SharePoint query needs:

So SharePoint should take care of it all, if you look at one of the content dbs list of stored procs you willsee below “proc_UpdateStatistics” & “proc_UpdateStatisticsNVP”

If you go through them you will see SharePoint will actually drop stats that has been created automatically if any and will create its own. It also updates the stats. SharePoint will call these procs on demand.

Here is why from the SQL point of view (For SQL folks):

Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application[SharePoint
in our case]. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

Taken from: https://technet.microsoft.com/en-us/library/ms187348(v=sql.100).aspx

2- 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.

3- To improve ease of maintenance, and make it easier to relocate the database if it is required in the future, configure SQL Server connection aliases for each database server in your farm. (Recommended)

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

5- 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

6- For many situations, the default server-wide fill factor level of 0 (fill each page to 100% full) is optimal. However, for SharePoint 2010, a server-wide setting of 80 is optimal to support growth and minimize fragmentation.

7- The DBA must configure surface area settings so that local and remote connections use TCP/IP or named pipes.

8- All of the databases required by SharePoint Foundation use the Latin1_General_CI_AS_KS_WS collation.

9- All of the SharePoint Foundation databases require that the farm Setup user account is assigned to them as the database owner (dbo).

SQL Maintenance

Routine database maintenance is essential for the smooth operation of Microsoft SharePoint 2010 databases.

The recommended maintenance tasks for SharePoint 2010 databases include the following:

1- Check database integrity.

2- Defragment indexes by reorganizing them or rebuilding them.

3- Set the fill factor for a server.

Maintenance considerations

1- You cannot run DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS. However, you can run DBCC_CHECKDB WITH REPAIR_FAST and REPAIR_REBUILD because those commands update the indexes only of the associated database.

2- In SharePoint 2010, a table that often becomes fragmented is AllDocs, which contains document libraries, their associated documents and lists and list items, and their respective metadata. Create a maintenance plan to rebuild or reorganize those indexes.

3- The following databases do not have an automated maintenance mechanism in place. These databases do not typically have much fragmentation. Monitor these databases for fragmentation, and rebuild the indexes in these databases when fragmentation exceeds 30%.

Search Administration Database
Secure Store Database
State Service Database
Profile Sync Database
Usage Database
Managed Metadata Database
Business Connectivity Services Database
PerformancePoint Services Database

4- Using the DROP INDEX and CREATE INDEX commands is not supported on SharePoint 2010 databases.

5- When an index is being rebuilt offline, a shared table lock is put on the table to prevent all operations except SELECT. SharePoint 2010 databases use clustered indexes specifically. When a clustered index is being rebuilt offline, an exclusive table lock is put on the table to prevent users from accessing it.

6- Because shrinking causes index fragmentation, do not shrink database files regularly. Instead, shrink database files only in response to large quantities of unused space that appear as a result of operations that significantly impact the relative amount of used space in a database. If at all possible, avoid shrinking a database.

However, if you must shrink a database, use the following guidelines

Do not auto-shrink databases or configure a maintenance plan that programmatically shrinks your databases.
Shrink a database only when users or administrators remove 50% or more of the content and you do not expect to reuse the unused space.
Shrink only content databases. Users and administrators do not delete enough data from the configuration database, Central Administration content database, and various service application databases to contain significant free space.
Shrinking databases is an extremely resource-intensive operation. Therefore, if you absolutely must shrink a database, carefully consider when you schedule the shrink operation.
After you shrink a database, the indexes in that database are fragmented. Use ALTER INDEX… REORGANIZE to address the fragmentation. If you are not configured to allow instant file initialization, shrink the database to a target size that accommodates the size required for the near-term growth that you expect. For more information, see Database File Initialization.

7- Using the TRUNCATEONLY option is not supported for SharePoint 2010 content databases.

8- Using the EMPTYFILE option is not supported for SharePoint 2010 database files.

9- We recommend that you delete Maintenance Plan text reports.