SQL Best Practices for SharePoint Farm

It is important to set Latin1_General_CI_AS_KS_WS collation for both SharePoint 2010 and SharePoint 2013 databases – Case insensitive, Accent sensitive, Kana sensitive, Width sensitive.

SharePoint uses the Latin1_General_CI_AS_KS_WS collation because the Latin1_General_CI_AS_KS_WS collation most closely matches the Microsoft Windows NTFS file system collation – http://support.microsoft.com/kb/2008668

You can validate Server Collation from General tab of SQL Server instance properties using SQL Server Management Studio

2) You must set max degree of parallelism (MAXDOP) to 1 for instances of SQL Server that host SharePoint databases to make sure that a single SQL Server process serves each request.

Important:

Setting the max degree of parallelism to any other number can cause a less optimal query plan to be used that will decrease SharePoint Server 2013 performance.

Also is it possible to configure the MaxDOP by a SQL query:

USE AdventureWorks2012 ;
GO 
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO

Reference: http://technet.microsoft.com/en-us/library/hh292622(v=office.15).aspx

3) You must configure the user accounts before install and build the SharePoint farm:

SP_Admin – to install SharePoint

SP_Farm – for Farm Administrator

Both need to have dbcreator and securityadmin rights on SQL Server instance.

Reference: http://technet.microsoft.com/en-us/library/cc263445.aspx

Other best practices to SQL is to configure an Alias in SharePoint farm, this would help in case you need to change your SQL server, and your SQL Databases would be always up to date listed in SharePoint. But you have to remember that you configured an Alias by the cliconfg command. (see post https://sharepointrescue.wordpress.com/2015/10/20/create-a-sql-alias/ to know how to do it)

In addition to that, is it very recommended to change the initial size of the files of the mdf files, as well as the increments for the databases. If you leave the default sizes, if the database grows a lot, it could generate defragmention.

I’m sure that would be more recommendations, so I’ll try to keep updated this post 🙂

Advertisement