Last week, I attended the SQLSaturday 2014 in Paris and participated in a session on SQL Server optimization for Sharepoint by Serge Luca. This session tried to list the best pratices and recommendations for Database Administrators in order to increase the SharePoint performance. This blog post is based on this session and is meant as a sequel to my previous post on Improving your SharePoint performance using SQL Server settings (part 1).
SQL Server instance
It is highly recommended to use a dedicated SQL Server instance for a SharePoint farm and to set LATIN1_GENERAL_CI_AS_KS_WS as the instance collation.
Setup Account permissions
You should give the Setup Account the following permissions in your SQL Server instance:
- securityadmin server role
- dbcreator server role
- dbo_owner for databases used by the Setup Account
It is recommended to use Alias DNS to connect to the SQL Server instance with your SharePoint server. It simplifies the maintenance and makes it easier to move SharePoint databases to another server.
When you plan to allocate your SharePoint databases accross different databases, you might wonder how to maximize the performance of your system.
This is a possible disk organization (from faster to lower):
- Tempdb data and transaction log files
- Content database transaction log files
- Search database data files (except Admin database)
- Content database data files
You should use several datafiles for Content and Search databases, as follows:
- distribute equally-sized data files accross separate disks
- the number of data files should be lower than the number of processors
Multiple data files are not supported for other SharePoint databases.
Content databases size
You should avoid databases bigger than 200 GB. Databases bigger than 4 TB are not supported by Microsoft.
SharePoint is quite abstract for SQL Server DBAs because it requires specific configurations.
As a result, you cannot guess the answer: you have to learn on the subject.