Optimizing SQL Server for SharePoint 2010

Summary:

Recommendations -- not in order -- for optimizing SQL server for SharePoint. Best to simply read chapter 3 of English, Bill, Brian Alderman, and Mark Ferraz. "Optimizing SQL Server for SharePoint 2010." In Microsoft Sharepoint 2010 Administrator's Companion, 95-133. Redmond: Microsoft Press, 2010.

Resources:

1. Hardware and software requirements:  http://technet.microsoft.com/en-us/library/cc288751.aspx
   1. Devote SQL server only to SharePoint
   2. Use 64-bit edition of Microsoft SQL Server 2008 R2
   3. 32 GB Ram (b/c we expect > 60 GB in the content DB and >1200 users: see attached xl file for calculations)
   4. 4 CPU (see attached xl file for calculations)
   5. 64 kb NTFS unit allocation size (can increase performance by 30%... ours is currently set to 4kb on sql server)
   6. Set min and maximum memory of SQL server. Set min = max = (total Physical Memory - OS Memory); this effectively fixes the memory available to SQL. (Does not require a restart)
   7. Change default database files path (e.g., … primary data file, transaction log files). (Important: do this after installing SQL and before installing content databases) These should
      1. Not be on the OS
      2. On a RAID-10 array or
      3. On separate disks
   8. Change "Model DB Settings" (Read this article for database sizing information:Database Types and Descriptions)
      1. Initial size of primary data file (say, 1GB… we will need to manually modify any newly created content DBs...which means we will need a process for enabling new SP Services... this process will need to include specifications for database size, autogrowth values, and ratio of mdf to ldf files).
      2. Initial size of transaction log file (25% of mdf)
      3. Recovery model (set to Full)
      4. Autogrowth (set to 1GB, though this property is not inherited by newly created databases)
   9. Set default collation type for the instance to Latin1_General_CI_AS_KS_WS (This can alternatively be done database-by-database)
2. Configure the domain accounts (As specified here: http://technet.microsoft.com/en-us/library/ee662519.aspx)
   1. sharepoint-sql (SQL Server service account; Domain user account; No permissions necessary… assigned during installation)
   2. sharepoint-setup (Domain user account; Member of the Administrators group on each server on which Setup is run… but NOT on SQL server; SQL Server login on SQL Server; Member of the following SQL Server security roles:
      1. securityadmin fixed server role
      2. dbcreator fixed server role
      3. sysadmin (not necessary… only if this account will be an SQL admin … per Glaser: http://andreasglaser.net/post/2009/11/18/Installing-SharePoint-Server-2010-on-Windows-Server-2008-R2-and-SQL-Server-2008-R2-Part-5-Administrative-and-service-accounts.aspx)
      4. Only if we run Windows PowerShell cmdlets that affect a database, will this account be a member of the db_owner fixed database role for the database
   3. sharepoint-farm (Domain user account; Note: additional permissions are automatically granted on setup!; Also, it's automatically added as SQL Server login on SQL Server with the following roles: dbcreator; securityadmin; db_owner (User Profile Service ultimately requires this to be a member of the local administrator's group on the server that service runs on… but does not need to be and should not be in local administrator's group on the SQL server).
3. Harden SQL Server for SharePoint environments (SharePoint Foundation 2010). Instructions here: http://technet.microsoft.com/en-us/library/ff607733.aspx
   1. Block UDP port 1434
   2. Configure named instances of SQL Server to listen on a nonstandard port (other than TCP port 1433 or UDP port 1434).
   3. For additional security, block TCP port 1433 and reassign the port that is used by the default instance to a different port.
   4. Configure SQL Server client aliases on all front-end Web servers and application servers in the server farm. After you block TCP port 1433 or UDP port 1434, SQL Server client aliases are necessary on all computers that communicate with the computer running SQL Server.
4. Create Content DBs.
   1. Either Deploy by using manually DBA-created databases (SharePoint Foundation 2010) … http://technet.microsoft.com/en-us/library/cc288606.aspx (Advantage: all model database settings – including the autogrowth – are inherited. Must check collation types, though)
   2. Or: Install SharePoint; let this happen automatically.
5. Things to double check:
   1. Collation must be Latin1_General_CI_AS_KS_WS
   2. Recovery model set to FULL for all DBs, except tempdb, which should be "simple"
   3. Transaction log files:
      1. Size: 25% of combined data files' size
      2. Autogrowth: 25-50% of file's initial size
      3. Path: configured at SQL server instance… on RAID-10 array
   4. Primary Data Files:
      1. Initial Size: See spreadsheet
      2. Autogrowth: 25-50% of file's initial size
      3. Path: configured at SQL server instance… on RAID-10 array
   5. Secondary Data Files:
      1. Initial Size: See spreadsheet
      2. Autogrowth: 25-50% of file's initial size
      3. Path: configured at SQL server instance… on RAID-10 array
   6. Check the Tempdb (SP is only as fast as tempdb database)
      1. Fastest I/O subsystem
      2. Simple recovery
      3. RAID-10 disk
      4. Set initial size to something bigger (lean toward bigger)
      5. Set file growth size to a "reasonable size" to prevent db files from growing in small increments.
         1. If initial value is 1-100mb: set to 10mb
         2. If initial value is 100-200mb: set to 20mb
         3. If initial value is 200mb or more: set to 10%

References

Good article: http://technet.microsoft.com/en-us/library/hh292622.aspx