SQL Server for SharePoint–Best Practices

SQL Server has many configuration settings to allow it to be tailored and optimised for specific applications that depend on the databases being hosted. SharePoint is no different to many other applications in that it has particular configuration settings that are recommended, and in some cases, mandatory.

SQL Server Installation

Dedicated Server

In testing and production SharePoint environments the SQL Server database engine should reside on a server that does not have SharePoint installed.

Dedicated Instance

SharePoint creates multiple databases and depending how the databases are created they can have unpleasant names that contain GUIDs. If there are multiple SharePoint farms using the same Databases Instance then there will be no way to identify the databases in use for each instance.

The performance of SharePoint will often be impacted or impact other applications if they share the same database instance.

Collation

The instance should be installed with the following collation: Latin1_General_CI_AS_KS_WS

MAXDOP = 1

The maximum degree of parallelism must be set to 1 - SharePoint will not install if this value is set to any other value.

Auto-Create Statistics

Do not enable auto-create statistics on the SQL Server Instance as the SharePoint application will manage the statistics

Model DB

As SharePoint will create multiple databases from the application level, or from PowerShell, the Model DB should be configured to have preferred defaults. SharePoint will alter many of the settings so not every database created will have the default settings.

Suggested settings:

• MDF Size - 200MB
• LDF Size - 50MB
• MDF Growth - 100MB
• LDF Growth - 50MB
• Recovery model - full

Connection from SharePoint Servers to SQL Server

Use SQL Aliases and DNS entries

At a minimum aliases should be created for the following SQL Server Instances:

• Farm - Contains all farm databases and, by default, the databases for all SharePoint Service Applications
• Search - Contains the databases required for search
• Content - Contains the content databases
• MySites - Contains the MySite content databases

SQL Server instances do not need to be created for all of these but it can be useful to have the aliases in place should the farm need to be scaled to use multiple SQL Instances.

If each SQL Instance has a DNS entry then moving a SQL Instance becomes much easier as a single DNS entry will be changed and once each SharePoint Server has had its IP cache flushed, it will be pointing at the correct location.

Management

Pro-Actively Size Databases

The purpose of a database created for SharePoint will determine the characteristics of the I/O and growth of the database. If the database is expected to grow fast then change the size of the database and the auto growth in a controlled manner to prevent any degradation to the performance through auto growth growing the database files.

Data Files

Multiple data files are allowed but they should all be in the primary file group. It is not supported to create multiple file groups in any of the SharePoint databases

Backup the Databases

Ensure that a full backup of the databases is carried out on a regular basis so that the transaction log is truncated and does not grow unexpectedly.