SQL Server 2012 for SharePoint 2013 checklist

 Checklist for SQL Server 2012 for SharePoint 2013

1. Use multiple: SQL Aliases (separate 1 for search).
2. Dedicate SQL Server for SharePoint.
3. Set max degree of parallelism (MAXDOP) to 1 for instances of SQL (SP will do this when SP is installed).  Number of processes for each SQL statement.
4. Mixed mode authentication - Don't install SQL 2012 for SP in mixed mode auth unless you have good reason (the only reason I have heard of is from Todd Klindt's podcast that mentions Access Services needs to use the SA account.  If you have other database that need SQL permission access consider moving them to a dedicate SQL instance.
5. SQL Server 2012 AlwaysOn Availability Groups are a new high availability and disaster recovery solution that are an alternative to database mirroring and log shipping solutions. AlwaysOn Availability Groups support a set of primary read-write databases and up to four sets of secondary databases that can be set as read-only.
6. Memory: You can set the max memory each SQL instance can use.  If the machine is dedicate to only provide SQL for SharePoint, the max setting is total memory minus 4GB for the OS.  See image 3.
7. Model DB: Increase initial size and autogrowth settings - fix growth sizes.  I would start  with 100MB for the mdf and 20MB for the ldf for initial sizes.  Autogrowth on content db's I start with 50MB growth for the mdf and 25MB for the log file (ldf).  See image1 below.
8. Model DB: Don't modify DB Collation after install.
9. Model DB: Use Full recovery model on the Model system database - Simple prevents large log files. 
10. Avoid giant ldf log files ... (don't use DCC_Shrink to resize ldf files with switching to the simple recovery model, it breaks the LSN/log backup chain).  ldf growth is far more resource intensive than mdf files growth, the problem I see with Content db growth is the IT pro lets the ldf get out of control, then backs up and shrinks the database.  Usage causes the ldf to autogrow periodically and the farm goses back to needing the process repeated with heavy growth issues.  Key is to ensure the lfd has a decent initial size (you can work this out between full backup cycles), the ldf for content db's should rarely need to autogrow and when it does make it a fixed amount.
11. TempDB: Having multiple mdf tempdb files speeds up SQL performance.  The tempdb is a system db that has resource available to all users.  And from the expert
12. TempDB: Increase it's initial size  & autogrowth to MB as opposed to percent (see image2).
13. TempDB: Simple recovery model for TempDB is correct.
14. TempDB: The default is 1, you need more than this depending on how many CPU cores are on your database server.  1 option is set the number of TempDB's to the same as the number of CPUI cores (1-to-1).  Some folks recommend the number of tempDB's should be 1 less than the number of cpu cores, other folks go for 1 TempDB per 2 CPU cores.   I start with 4 and tune in performance testing or once it is running.  Saying that I normally have 16 cores.  I can't see performance gains from my testing after 4 TempDB's but as a rule I'd start with 1 TempDB for each 2 Cores and tune from there. 
15. TempDB: Move the tempdb.mdf files and the TempDB .ldf file to their own fast as possible drive.
16. Content DBs: CA or PS when creating a new content db won't take all the model settings, it does take initial sizes but not the autogrowth settings.
17. Content DBs: Workaround- create the db outside of PS, get the SP collation right "Latin1_General_CI_AS_KS_WS" collation.
18. If your SQL db uses spinning disk split the mdf and ldf files onto separate disks.  Order the db files as follows: tempdb must be on the fastest disk, content db log files next and content db's next.
19. Change the default backup location to a separate disk (pretty obvious but it is the default setting). 
20. Set the default for the database instance file locations  - set the default location where new mdf, ldf and backups will go on disk (per your fasteset disk calcs).
21. Set the default for the database instance backup compression - I'd go with compression for all backups.
22. mdf and ldf should be on separate drives for 2 reasons : IOPS speed (provided this is spinning disk) & DR (you don't want to loose both).
23. OS: NTFS allocation unit, by default on Windows 2008 this is 4096 byte (4kb), generally much faster to have it set to 64kb allocation unit size.  e.g. cmd>Chkdsk c: 
24. Use RAID 10 where possible.
25. Windows firewall - if using it you will need to open the incoming SQL port i.e. 1433
26. Avoid huge transaction logs and size them appropriately.  Pref don’t use simple recovery model. Ldf content is not removed every 60 seconds when it is written to the mdf files. Backup – get last full backup and last differential to get you to the lasted backup version.  Or get the current ldf, restore the last full backup and play the current ldf through the db.  To slim the ldf down, after a successful full backup, can backup the transaction with "truncate the transaction log" (it zeros all transaction before the checkpoint made by the transacion log backup or (sic. delete the log file) to get it back to a reasonable size.  Hint: BACKUP LOG databasename TO devicename
27. Watch the size of the Content Databases, they take time to recover.  Max up to 4TB, try stick to around 200GB (exception will be for blob storage).  This makes backup and restore quick however AlwaysOn also changes the scenario.
28. Format the Drives with 64K NTFS Allocation Units.
29. Antivirus software must exclude LDF/MDF/NDF files.
30. Don't shrink database log files by switching the recovery model to Simple.
31. Ensure you are within the latency recommendation for SP to SQL (< 20ms).

Image 1. Change the model database initial size and autogrowth settings.

Tip: AutoGrowth of SharePoint 2013 Content databases - Changing the initial size of the model db will affect the content db's - nice, issues is that the autogrowth settings in the model db are not pushed to the content databases created through SharePoint (either CA or PowerShell).

Image 2. Change the TempDB to have multiple mdf files.

Image 3. Setting Memory on SQL Server instances.

SharePoint Sizing Starting point notes:
SP_Config database - "Transaction log files. We recommend that you back up the transaction log for the configuration database regularly to force truncation." Technet.   The Full recovery model is the default, switch this to Simple.  If you need Full, your ldfs be busy.  Suggest ldf at least 1000MB per day growth, can be a lot more.

Suggested Search database sizing.  If the Search databases are in the Full Recovery mode you also need to set the ldf sizing.

Database	mdf	mdf growth	ldf	ldf grow
SP_Search_Admin	100 MB	10 MB	100 MB	50 MB
SP_Search_CrawlStore	100 MB	50 MB	300 MB	100 MB
SP_Search_AnalyticsReportingStore	100 MB	50 MB	25 MB	25 MB
SP_Search_LinksStore	100 MB	50 MB	25 MB	25 MB

Update 23 Jan 2014:  Todd Klindt has a good set of blog posts on SQL 2012 for SharePoint 2013.

How do ldf files work with mdf in SQL Server:
Content goes into .ldf file temporarily, checkpoint occurs every minute and moves from .ldf to mdf.  If the "Full recovery model" is used the content in the the ldf file is retained. Hence large trans logs but recovery is better.  If a simple recovery model is used, the ldf data is dumped.


Keith Tuomi provide the code to automatically change the autogrowth sizing.

01	$Server="SQLSRV2012"

02	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

03	$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Server

04	$databases = $SMOserver.Databases;

05	foreach ($DB in $databases | where{$_.Name -like '*Content*'}) {

06	#Set Log File growth

07	foreach ($DBLF in $DB.logfiles) {

08	$DBLF.set_GrowthType("KB");

09	$DBLF.set_Growth("51200"); #50mb

10	$DBLF.Alter();

11

}

12	#set File Growth

13	$DBFG = $DB.FileGroups;

14	foreach ($DBF in $DBFG.Files) {

15	$DBF.set_GrowthType("KB");

16	$DBF.set_Growth("102400"); #100mb

17	$DBF.Alter();

18

}

19

}

SQL Licencing:
There are numerous licensing models available to SQL server on the different versions and I find them extremely complex. For large SQL using the Enterprise Edition of SQL 2012, per-core licensing at the hardware (hypervisor) level is an option. The SQL instances can be tied to specific hardware. Affinity rules do need to be setup to prevent vMotion moving the VM to another hardware host.  In a HA situation using AOAG in a passive situation, the secondary SQL servers et al. will require licencing.

SQL Installation:  I slipstream and automatically install SQL Server 2012.  The checklist below lists the SQL features you can install.  Determine your needs makes creating the config.ini files used by the install much easier to do.  The example below is used to create both my primary SQL Server and the secondary (AOAG) server, they are identical.  The choices are pretty standard, you may want to move the Reporting Services features to another server or remove if you are not using them.

Feature	Install
Database Engine Services	Y
SQL Server Replication	Y
Full-Text Search	N
Data Quality Service	N
Analysis Services	N
Reporting Services: Native	N
Reporting Services: SharePoint	Y
Reporting Services Add in for SharePoint	Y
Data Quality Client	N
SQL Server Data Tools	N
Integration Services	Y
Client Tools Connectivity	Y
Client Tools Backward Compatibility	Y
Client Tools SDK	N
Documentation Components	N
Management Tools Basic	Y
Management Tools Complete	Y
SQL Client Connectivity SDK	N
Master Data Services	N
Distributed Replay Controller	N
Distributed Replay Client	N

More Info:
http://yalla.itgroove.net/2013/03/sql-server-powershell-sharepoint-set-autogrowth-on-content-dbs/
http://blog.cloudshare.com/2013/08/28/how-to-use-the-same-autogrowth-value-for-sharepoint-content-databases/
http://technet.microsoft.com/en-us/library/hh292622.aspx
http://channel9.msdn.com/Series/Tuning-SQL-Server-2012-for-SharePoint-2013/Tuning-SQL-Server-2012-for-SharePoint-2013-03-Server-Settings-for-SQL-Server (Excellent)
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/
http://www.brentozar.com/blitz/blitz-result-percent-growth-use/
http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-before-the-install/
http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
http://www.toddklindt.com/default.aspx
SharePoint database types and description

Thanks to:
@allanSQLIS - Allan Mitchell - great sitting next to a SQL expert.

Steve Goodyear has a blog post on Farm Install and build guide.  I haven't used it but it is a good post to check you are ready for your install and you have done the big steps.

SQL Hardening:

From http://blogs.technet.com/b/rycampbe/archive/2013/10/14/securing-sharepoint-harden-sql-server-in-sharepoint-environments.aspx
Hardening SQL Server is done in a 3 phased approach:

1. Encryption at Rest (Encrypt the data sitting on the hard drives)
2. Encrypt Connections (Encrypt the data in flight on the network between servers)
3. Server Isolation (Configure SQL Server's firewall to ignore requests from unauthorized servers)

Transparent Data Encryption (TDE) can be used to encrypt any SharePoint database.  This will encrypt the mdf and ldf files, this ensures that even is the hard disk storage is comprimised, the mdf and ldf cannot be used to restore the databases using the SQL restore tools.  There are a lot of ramifications to using TDE so review the decision to use TDE carefulkly before implementing.
 

File Upload Size Limits

Problem: Need to store files in excess of 2GB in SharePoint.
Initial Hypothesis:

• 50MB is the default upload limit set by SharePoint OOTB.  You can change this on the farm as described here by Dave Coleman up to 2GB or 2047MB. 
• A common misconception is that by using RBS and not you content database to store the blob you can overcome this 2GB limit.  We it's partly true...  The maximum file size for a file in SQL Server is 2GB however, the next restraint is SharePoint 2010 Server Object Model and this has a hard limit of 2GB for an upload so moving to RBS won't overcome the problem.
• I believe SharePoint limit's the upload to 2GB due to IIS's worker process w3wp.exe, to upload a file you need to use all the IIS available memory to upload the full stream.  Each w3wp.exe worker process runs well with 2-4GB of memory, this is not a boundary just a good idea (on x64), therefore this makes sense to me that the SP2010 team have limited any file upload to 2GB.
• Also be aware that increasing you upload file size to 2 GB has performance ramifications so it a user uploads a file and there is no memory available no new requests can be handled until the memory is available again.

Resolution:  Store large files outside SharePoint and surface them in SharePoint.  I believe there is another solution available using a Telerik Silverlight upload control but I haven't tried it.
More Info:
http://blah.winsmarts.com/2010-3-Large_File_Upload_in_SharePoint_2010.aspx

Update 24/07/2013: SharePoint 2013 has the same hard limit of 2 GB for the maximum upload size.  Technet states 50 MB is the default limit for SP2013, the default from an OOTB install is 250MB which is the same value you get with SharePoint Online/Office 365.

SP2010 Coding Tips

Retrieving a list using the Serve-side Object Model
*******************************************
Problem:  In MOSS we could use CAML queries or the SharePoint API (Server side object model) to retrieve list data.  In SP2010 there are various methods for getting a list however, the Server-side Object Model has been improved/enhanceced.

SPSite site = new SPSite("http://demo.dev");
SPWeb web = site.OpenWeb();
SPList list = web.Lists["Customers"];

Hypothesis:
If the list doesn't exist you received a NullReferenceException.  A try catch block is usually wrapped around the list retrieval however, SP2010 has an improved method TryGetList().

Resolution:
SPList list = web.TryGetLists["Customers"];  // if list doesn't exist list will be null.
Other improvements to the API include: TryGetFieldByStaticName,

Disposing Objects
***************
Problem: SPSite and SPWeb objects are not automatically disposed.
Resolution:  Use either using statements or try catch finally statements to dispose of SPWeb and SPSite objects.  Check all code using the SPDisposal tool.

More Info:
Reading this post jogged my thoughts on the 2 topics posted above.  This is useful for SP developers to know.

Best Practice Sharepoint 2010 coding tips

1.> SharePoint Dispose Checker Tool - Run the SP Dispose Checker Tool on all custom code you write.
SPSite & SPWeb object has a big load and we need to ensure dispose is always called. 2 easiest ways to ensure Site and web objects are always disposed are shown below:
SPSite site;
try
{
site = new SPSite("http://demo1"); // Create the SPSite object
...
}
finally
{
if (site != null) // Check if the SPSite object exists
{
site.Dispose(); // Clean up the SPSite object as it has not be disposed of
}
}
Alternatively I prefer to use using statements
using (SPSite site = new SPSite(http://demo1/))
{
.... // SPSite.Dispose method will be called always
}
Run the SPDisposeCheck tool on all code before deploying outside of you development environment, this can be automated into VS 2010.
2.> Have at least 3 environments i.e. don't send code from the developers machine straight into production. Sandboxed solutions alleviates this risk to some degree but use a UAT, pre-prod, QA environment. Your deployment environment must mimic production as closely as possible i.e. ensure there is a separate SQL DB, all versions of software are identical, load balancing is setup. Have documented change control. Try perform changes through scripts not manual changes if possible. Web.config changes need to be replicated to all servers on the farm so doing the change manually is not the best option. Change the web.config via code to ensure it is done through the configuration database so they are changed on all web.config's in the farm.
3.> Error handling, catch errors as specifically as possible, die gracefully and appropriately, log the errors, check the production error logs periodically.
4.> Deploy code using wsp solutions. Ensure code is not in debug mode when compiled, it runs slower and potentially can crash/holdup your production environment. Implement CAS, deploy to bins if possible and apply CAS security policies to custom coding. Perform peer code reviews, it ensures coding standards are being followed, developers learn from each other, bugs are often picked up pre-testing and it increases team members knowledge that reduces maintenance costs.
5.> Develop using source control no matter how smal the dev project is. Preferably TFS 2010 but VSS 2005 is even fine, failing this use CVS, IMB/rationals ClearCase for source control. Also have bug tracking with TFS the integration is excellent between the bugs and the source control. I.e. use TFS if possible.
6.> SharePoint projects are often very good candidates to SCRUM or other agile methodologies. Use them if it's appropriate. Traditional formal SDLC / waterfall approaches tend to work well on the larger SharePoint projects.
7.> Use the developer dashboard.
8.> Unit testing - don't test SharePoints API, test your custom code. In MOSS Type Mock isolator was a great product for SharePoint I presume this is still the way to go. Andrew Woodward is a good blogger on SP unit testing.

SQL Server for SharePoint 2010 notes

• SharePoint Server 2010 needs 64-bit SQL Server 2008 SP 1 CU2 (Cumulative Update) or 64-bit SQL Server 2005 SP3 CU3.
• Determine you storage requirements
• SQL is I/O intensive, to improve this get fast disks and use multiple disks & disk controllers.
• Use a SAN if possible, the physical hardware with multiple disks that are RAID 10 (Stripped & mirrored) preferable with C:\ drive for programs, d:\ for data and e:\ for logs.
• Don't virtualise SQL Server unless you are a virtualisation expert and can get extremely high IOPS.
• Search db's can also be broken into their own drive/disks.
• Build the database with hardware redundancy (NIC, controllers, RAID).
• Use SQL Server 2008 R2 Enterprise edition if possible.
• Index's will be about 25% of the size of your database storage.
• 8 GB is the minimum amount of RAM for a DB in production, 16 GB is more comfortable and for large farms or big site collections 64GB is a good guideline. SQL Enterprise Edition (EE) can support up to 2 Terabytes of RAM.
• Don't install any other software on the database as you want maximum I/Oa nd the DB server/s should be locked down.
• SQL guideline is 4 SP2010 server for each SQL machine.
• Use multiple Data files for Content databases (Distribute data across disks, faster backup and restore). Try keep data files roughly similar in size & usage.
• Large files can be store in Remote Blob storage (RBS), saves db and can be cheaper on disk space. Cheaper storage can be used to hold large blob data such as cloud storage. Blobs can be stored locally using the files stream using all versions of SQL, for remote storage EE is needed (Not sure what this means).
• Pre-grow data & log files, faster than doing it on the fly when the system is over utilised.
• Try keep 25% of db space free for growth in peak times.
• Monitor SQL Servers including hardware counters.
• SQL Database Mirroring is greatly improved.
• Use backup compression on SQL 2008 (2005 not supported) is backup size is an issue. I/O is improved for the backup process.
• SQL 2008 offers improved clustering.
• Mirroring or Clustering is a good resilience option.  Review for HA.
• Use Windows authentication not mixed mode authentication.
• Use throttling if SQL is under load.
• Transparent Data Encryption is supported in SQL 2008 EE & SP2010, there are costs but security is much better.
• Failover clustering is still available is you use Standard or EE of SQL 2008.
• Clustering and mirroring are good options for High Availability (HA) select appropriately for your network and knowledge.
• Install SQL Server using a domain account.  The windows service account needs no permissions but is needed for advance SQL features as opposed to using built in accounts or local accounts.
• I tend to use IP adrs for point to SQL Server, the netbios name also works and can be easier in the event of a SQL Server disaster.  For really good availability use a SQL Alias, it takes more setup time but if you loose your SQL box you will be glad you did it as you can switch over to another SQL box quickly.
• Mirroring is a good option for HA.  Backups can be performed in various ways ensure you select the appropriate backup strategy. 
• Max Degree of Parallelism (MAXDOP) should be set to 1. This can be found on the SQL Server instance properties > Advanced > Parallelism > Max Degree of Parallelism. Or run the T-SQL SELECT value FROM sys.configurations WHERE name = 'max degree of parallelism' SP2013 tries to reset MAXDOP during installation. 
• AUTO_UPDATE_STATISTICS & AUTO_CREATE_STATISTICS should be disabled in SP2010.  More Info.
• Use the default SQL Collation (Latin1_General_CI_AS_KS_WS), a good reason why SharePoint farms should have their own SQL Server instance.
• Full backups should clear down the transaction log, if the transaction log is not cleaned up, perform it manually after you have checked the SQL backup of the db is valid.
• Incremental backups are cumulative i.e. they go back to the last Fullback up not the last incremental backup.
• Don't let transaction logs grow continuously, perform full backups periodically followed by taking a transaction log backup that truncates the log to remove/zero unused transactions.
• SQL 2008 Developer Edition is the equivalent of SQL 2008 Enterprise Edition.
• SQL Server 2008 R2 is the best option if you can choose.
• To determine SQL edition in SQL Management Studio run

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

• SQL Server 2008 = 10.0.1600.22 needs cumulative update 2 for SP1. Update - 12/10/2010 or SP2
• SQL Server 2008 SP1  = 10.0.2531.0 needs cumulative update 2 for SP1.
• SQL Server 2008 SP1 + CU2 = 10.0.2714.0
• SQL Server 2008 + SP2 = 10.0.4000.0 Update - 12/10/2010
• SQL Server 2008 R2  =  10.50.1600.1 Update - 12/10/2010
• SQL Server 2008 R2 SP1 = 10.50.2500.0 Updated - 26/07/2011

I also telnet from each of my SharePoint servers to SLQ Server before I install to ensure networking is working and that SQL Server is available.

SharePoint DB's create:
SPF2010: 1) Configuration database (DB), 2) CA, Content DB's (multiple for site collections, 3) 1 content db stores 1 or more site collections data, Best Practice is to limit content db's to 200GB), 4) Usage and Health Data Collection database (farm health & usage info), 5) Business Data Connectivity database, Application Registry database(BDC in MOSS used for historic reason) & 6) Subscription Settings database.
SPS2010 Std Ed: 1) Secure Store database (stores & masps credentials), 2) State database (State info used by forms server, info path & visio services), 3) Web Analytics Staging database, 4) Web Analytics Reporting database, 5) Search service application Administration database, 6) Search service application Crawl database, 7) Search service application Property database, 8) User Profile service application Profile database, 9) User Profile service application Synchronization database, 10) User Profile service application Social Tagging database, 11) Managed Metadata database, 12) Word Automation Services database
SPS2010 Ent Ed: 1) PerformancePoint service application database, 2) Project Server 2010 databases, 3) Published database, 4) Archive database, 5) Reporting database, 6) ...
More info:
http://technet.microsoft.com/en-us/library/cc990273.aspx
Determine the SQL Server version installed
SQL Version no's
SQL DB info for SP 2010 - db's created
Updated 15 Dec 2010 - Database Maintenance for SharePoint 2010 by Matt Ranlett, Brendon Schwartz
Updated 11 May 2011 - Nice simple article on the SP2010 database's by Bert Jan van der Steeg
Updated 24 May 2011 - SQL Server mirror is either Synconous (hot standby for HA) or Asynconous (for DR).  Mirroring requires Enterprise edition and standard edition support is limited.  Clustering is normally done in the same server room whereas Mirroring is done on a remote site, the distance is dictated by the speed of the connection.
Update 11 Aug 2011 - Set the appropriate recovery model for your SP2010 databases.
Updated 28 May 2012 - SQL Best Practices for SharePoint 2010
Update 13 August 2013 - Best Practices for SQL Server in a SharePoint 2013 Farm - In SP2013 still ensure MAXDOP is set to 1.  Note:  During the SPInstall SQL will make this change if it has permissions to do this.