thanks to RISHABH

Database File Separation Recommendations

1. Storage is one of the keys to performance. It is recommended that the following database file separation be used (separate disks, separate LUNs), in priority order, where possible:

Priority	Database File	SAN Optimization
1	TempDb data	Write
2	TempDb log	Write
3	Content DB logs	Write
4	Service Apps DB log	Write
5	Search Crawl DB log	Write
6	Content DB data	Read/Write
7	Service Application DB Data	Read/Write
7	Usage and Health data	Read/ Write
8	Search Analytics Database	Read/ Write
9	Search Property Database	Write

2. High Performance Mode
   • Operating System Level Power Plan should be: High Performance
   • Default setting is Balanced – this throttles CPU performance when memory consumption is low
   • Ensure BIOS-level Power Savings mode is disabled
3. Disk Allocation
   • Default disk allocation unit for most drives is 4k
   • SQL uses extents to write data to the disk
     • Each extent is composed of eight 8k pages
     • Each extent is a total of 64k
   • Format drives for 64k allocation unit
     • Will create performance improvements for operations that access bulk disk sectors (restores, database creation, etc)
4. SQL Server Instance Settings
   • Collation
   • Fill Factor
   • Max Degree of Parallelism (MAXDOP)
   • Maximum Memory
   • When preparing the SQL server to host SharePoint databases there are a few recommended practices to consider:
     • Collation: We support any CI collation for the SQL instance (for master, tempdb databases). However we recommend using Latin1_General_CI_AS_KS_WS as the instance default collation (master, tempdb databases). However, we do not support changing the default collation (Latin1_General_CI_AS_KS_WS) for SharePoint databases to any other collations (CI, AS, KS, WS). Any pre-created databases must have this collation before they can be mounted in SharePoint.
       Supportability regarding SQL collation for SharePoint Databases and TempDB
     • Fill Factor: Fill factor is used to determine how much free space is required for an index page. This is necessary in order to keep the index as compact as possible while as the same time preventing performance delays when splitting data to a new page after the current page fills up. For SharePoint, a server-wide setting of 80 is optimal to support growth and minimize fragmentation.
     • MAXDOP: The max degree of parallelism option controls the number of processors that can be used to run a single Microsoft® SQL Server statement using a parallel execution plan. The default value for this configuration is 0 and indicates that all available processors can be used. Setting MAXDOP to 1 is mandatory for SharePoint 2013, otherwise the Configuration Wizard fails to complete successfully.
     • Maximum Memory: Use Dynamic Memory Management to set min and max memory levels for SQL. This is critical for servers that run more than one instance – otherwise one instance can consume all memory leaving none available for the OS and other instances
5. SQL Server Service Account
   • Do not provide this account Domain or Local Admin Privilege
   • Local Security Policy -> User Rights Assignments:
     • Perform Volume Maintenance Tasks
     • Lock Pages in Memory
     • Perform Volume Maintenance Tasks – In SQL Server, data files can be initialized instantaneously. This allows for fast execution of file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously. This can be especially useful on improving SQL server restart times – SQL has to re-provision tempDB on every reboot, so this will improve the length that operation takes.
     • Lock Pages in Memory – This setting should not be treated as an absolute best-practice. There are pros/cons to turning this on, and these risks should be evaluated to confirm alignment with IT goals. If operating system is experiencing memory pressure, SQL will trim memory allocation, which can create throttle SQL performance to give enough memory back to the OS to allow OS-level operations to complete. LPIM prevents SQL from releasing memory back to the OS, so the OS-related operations will be bottlenecked at the expense of SQL transaction performance. This should be tested to confirm the impact in the environment. If there is extensive memory pressure and this setting is turned on, there will be extensive paging to the disk which will also impact performance. Microsoft Support KB: How to enable the “locked pages” feature in SQL Server 2012
6. Configure SQL Aliases
   • Two strategies are available: DNS Alias or SQL Client Alias
     • DNS Alias can only be used if SQL is running on default port
     • SQL Client alias can be used to connect to an instance on a non-default port
     • Use on of the above methods to obfuscate the SQL connection string from SharePoint
     • This removes dependencies to a particular FQDN and allows us to move the databases to a different location if needed (DR solutions or hardware changes)
     • Neither of these strategies is an inherent security mechanism
7. Harden SQL Server
   • Do not use Mixed-Mode Authentication if possible
     • Mixed-mode authentication will be required for Access Services 2013
     • Ensure Windows Password Policies are being enforced on any required SQL logins
   • Configure firewall rules to only accept traffic from known hosts
   • Configure all incoming connections with SSL and Kerberos
   • Encrypt data-at-rest with Transparent Data Encryption
     
     The following blog post describes steps for implementing SSL and TDE
     • http://blogs.technet.com/b/rycampbe/archive/2013/10/14/securing-sharepoint-harden-sql-server-in-sharepoint-environments.aspx
     • http://technet.microsoft.com/en-us/library/ff607733(v=office.15).aspx

       Change SQL Server to a non-default port – This can be considered a relatively light hardening step. As the above blog post mentions, port scanning can easily reveal the correct SQL port. However, this can be implemented successfully following the steps in this article: http://technet.microsoft.com/en-us/library/ff607733(v=office.15).aspx

8. TempDB Configuration
   • Prioritize to fastest available disks for data and log files
     • Target: RAID-10
   • Only one log file is needed
   • Pre-size tempDB to 10% of largest database (20GB for 200GB ContentDB)
   • Avoid auto-growth on tempDB
     • Default auto-growth settings will only grow one tempDB data file at a time – BAD
     • Proactively manage database file sizes or consider Trace Flag 1117 which forces all data files to grow evenly when auto-growth is triggered
9. TempDB Allocation
   • Allocate a tempDB data file for each logical processor available to the server (Max of 8)
10. Model Database
    • SharePoint uses the Model Database for:
      • Default database/log size
      • Recovery model
    • SharePoint does not use this for:
      • Auto-growth configuration
      • Collation
      • Auto-growth settings on the modelDB are ignored by SharePoint databases, so we must configure auto-growth on the SharePoint databases after they are created
11. Auto-growth
    • Treat auto-growth as a protection for unexpected growth in the database
    • Regular capacity planning exercises by the SharePoint Admin team should estimate capacity for each database
    • Databases should be pre-grown by DBA to a size that will support 6-12 months of workload (depending on storage availability)
    • Set auto-growth to fixed values to avoid dynamic growth operations
    • Turn on database file instant initialization to improve growth-operation performance (details on slide 7)
      For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow. It is recommended to monitor file sizes and available space inside the files and grow the files proactively during maintenance windows to an appropriate size. This helps you avoid file fragmentation and moves the file growth to times when it will not interfere with your database’s normal operations. http://technet.microsoft.com/en-us/library/cc263199.aspx#databases – This diagram can help with sizing each database for your environment
12. Index Fragmentation and Statistics

    SharePoint Health Analyzer rules evaluates the health of database indexes and updates index statistics daily for the following databases:

    • Configuration databases
    • Content databases
    • Managed Metadata Service database
    • User Profile Service Application Profile databases
    • User Profile Service Application Social databases
    • Word Automation Services databases
    • App Management Service database
    • Machine Translation Service Database
    • Subscription Settings

      Maintenance plans should be created to defragment indices on all other databases manually. Additionally, these other databases should have AUTO_UPDATE_STATS turned ON

13. Database Maintenance Plans
    • Database Fragmentation
    • DBCC CheckDB

      Database Maintenance plans should be created to run regular database integrity checks, as well as to defragment any indices that need it.

      DBCC CheckDB should be run on a weekly basis

    • The following table describes the recommended resolution for various fragmentation levels:

Fragmentation level	Defragmentation method
Up to 10%	Reorganize (online)
10-75%	Rebuild (online)
75%	Rebuild (offline)

14. Anti-Virus Exclusions

    • Exclude all SQL file extensions from real-time scanning
      • .mdf; .ndf; .ldf; .bak
    • If using SQL clustering, also exclude
      • <$windir>/cluster
      • Witness disk