SQL Server Configuration for SharePoint
There’s been a lot of videos pop up lately (or maybe I just haven’t seen them before, which is more than likely!) about how to configure SQL Server for SharePoint.
After watching a couple of them it does seem there isn’t too much extra to do, for people that set up SQL and follow the normal build best practices. The extra steps I have found are noted below:
SharePoint should have a standalone instance of SQL Server
MAXDOP should be set to 1
SharePoint will run queries with using ( WITH MAXDOP x) for queries that can use parallelism.
Note: When installing SharePoint 2013 the install will try and set the instance to MAXDOP 1 and if the account doesn’t access to change the setting will fail until it’s been set!
Set the instance collation to Latin1_general_CI_AS_KS_WS
Set SQL authentication to Windows.
Note: Access Services in SharePoint 2013 uses SQL logins so needs to be run in Mixed mode.
Set Fill Factor to 80% for the SharePoint Databases.
Note: I’ve also seen other posts and SharePoint folk saying this isn’t true!
Optimizations and Best Practises:
Use SQL Aliases and or DNS A Records. To simplify databases migrations.
Note: Don’t use DNS CNAMEs as there are issues with Kerberos.
Use Naming standards for the databases names (Remove the GUIDS!!!)
Collection of resources:
Microsoft Virtual Academy – Tuning SQL Server 2012 for SharePoint 2013 Jump Start
Optimizing SQL Server for SharePoint
Optimizing SQL Server 2012 for SharePoint 2013