SQL 2012 Quick pointers for SharePoint 2013

Jasjit ChopraNo Comments

  1. Use dedicated SQL Instances for SharePoint 2013
  2. Always use SQL Server Alias for SharePoint 2013 SQL Server instance
  3. Default collation setting while installing SQL 2012: Latin1_General_CI_AS_KS_WS
  4. Site collection resides in only one database however a database can have many site collections
  5. Maximum size for a DB recommended by MS is 200GB, however it can support up to 16TB
  6. Since SharePoint manages the schema on its own never attempt to modify a SharePoint database from SQL
  7. The I/O profile that you design for your storage will change overtime depending on actual SharePoint usage
  8. The SharePoint Config database is what comprises the actual SharePoint farm configuration information like IIS Settings, Farm solutions, Topology info, etc. This database is read intensive and can range between 0-5GB size
  9. Never user Simple recovery mode for production SharePoint SQL databases, always use Full recovery mode
  10. Tempdb best practices
    1. Number of tempdb files should be 1/4th or 1/2 the number of processor cores on SQL server – This is a practice that needs real data from your existing system to see if tempdb is creating a bottleneck and whether or not increasing the number of db files will increase performance. Refer to this and this article for more details.
    2. Tempdb size should be roughly 10% of total content DBs
    3. Autogrowth setting should be in MB and not a percentage
    4. Tempdb should use Simple Recovery Model
    5. Tempdb should be placed on different drive than content databases file
  11. Model DB best practices
    1. Work with SharePoint Admin to identify default initialize size for model db and its log file
    2. Set the autogrowth in MB and not in percentage (There is a caveat – Databases created by SharePoint does not copy autogrowth settings from Model DB)
  12. Set the maximum memory for SQL instance appropriately leaving at least 4GB for the OS
  13. Set the default file locations on appropriate drive, ideally this is always other than c drive
  14. Set Max Degree of parallelism to 1 for SQL instance
  15. For the SQL Server OS set the NTFS allocation size to 64K for MDF partitions, leaving it at 4K will reduce the performance by as much as 30%
  16. Always use high availability either via cluster or always on for Production farms
Website
Jasjit Chopra is a MS Cloud Architect with strong background in SharePoint, Office 365 and Azure. As a SharePoint expert he has worked with many multinational clients including HP, Avanade, Accenture, Unistar Nuclear Energy, Warner Music Group, Inventiv Health and iHeartMedia. Jasjit holds a Masters of Business Administration in Technology and Management from CERAM, Sophia Antipolis, France. Having worked for different clients across the United States Jasjit has gained insight knowledge on Business Processes for the State Government and Manufacturing, Nuclear, Pharmaceutical and Music industries.

Categorized in: SharePoint 2013, SQL 2012

Leave a Reply