Tuesday, January 5, 2016

SQL Best Practices for SharePoint

Select Latin1_General_CI_AS_KS_WS Collation for Database Engine 

Remember to set Latin1_General_CI_AS_KS_WS collation,  when you install DB server for your Sharepoint 2010 and Sharepoint 2013 infra.

CI - Case-insensitive
AS - Accent-sensitive
KS - Kana-sensitive
WS - Width-sensitive

If SQL is already installed, or you are planning to make use of an existing SQL Cluster, please be sure to check collation by opening Properties of DB instance from SQL Management Studio.

MS KB here for reference -: https://support.microsoft.com/en-us/kb/2008668

Change Default collection to Latin1_General_CI_AS_KS_WS , if SQL Server was installed.
1) Stop all SQL Services
2)  Open CMD
3) CD "Program Files\Microsoft SQL Server\<SQL Server Folder for the version installed>\MSSQL\Binn"
4) sqlservr -m -T4022 -T3659 -s"<SQLInstanceName>" -q"Latin1_General_CI_AS_KS_WS"    If only once instance is running, then -s is not required and command will be like the below one
    sqlservr -m -T4022 -T3659 -q"Latin1_General_CI_AS_KS_WS "

5) Once Done, Start SQL services and check Instance Properties to confirm the change.
Maximum Degree of Parallelism in SQL  

In SharePoint 2010, setting Maximum Degree of Parallelism option was recommended to be set as 1 , but is was optional.
But, in case of SharePoint 2013, it is required to set this to 1 otherwise the configuration wizard will fail.

No comments: