{"id":1826,"date":"2015-11-11T01:12:37","date_gmt":"2015-11-11T01:12:37","guid":{"rendered":"http:\/\/www.obieta.com\/?p=1826"},"modified":"2015-11-11T01:12:37","modified_gmt":"2015-11-11T01:12:37","slug":"sql-server-2012-for-sharepoint-2013-checklist","status":"publish","type":"post","link":"https:\/\/obieta.com\/?p=1826","title":{"rendered":"SQL Server 2012 for SharePoint 2013 checklist"},"content":{"rendered":"

\u00a0<\/strong><\/h3>\n
    \n
  1. Use multiple: SQL Aliases (separate 1 for search).<\/li>\n
  2. Dedicate SQL Server for SharePoint.<\/li>\n
  3. Set max degree of parallelism (MAXDOP) to 1 for instances of SQL (SP will do this when SP is installed).\u00a0 Number of processes for each SQL statement.<\/li>\n
  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.\u00a0 If you have other database that need SQL permission access\u00a0consider moving them to a dedicate SQL instance.<\/li>\n
  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.<\/li>\n
  6. Memory: You can set the max memory each SQL instance can use.\u00a0 If the machine is dedicate to only provide SQL for SharePoint, the max setting is total memory minus 4GB for the OS.\u00a0 See image 3.<\/li>\n
  7. Model DB: Increase initial size and autogrowth settings – fix growth sizes.\u00a0 I would start\u00a0 with 100MB for the mdf and 20MB for the ldf for initial sizes.\u00a0 Autogrowth on content db’s I start with 50MB growth for the mdf and 25MB for the log file (ldf).\u00a0 See image1 below.<\/li>\n
  8. Model DB: Don’t modify DB Collation after install.<\/li>\n
  9. Model DB: Use Full recovery model on the\u00a0Model system database\u00a0– Simple prevents large log files.<\/li>\n
  10. Avoid giant ldf log files … (don’t use DCC_Shrink\u00a0to resize\u00a0ldf files\u00a0with switching to the simple recovery model, it breaks the LSN\/log backup\u00a0chain).\u00a0 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.\u00a0 Usage causes the ldf to autogrow periodically and the farm goses back to needing the process repeated with heavy growth issues.\u00a0 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.<\/li>\n
  11. TempDB:\u00a0Having multiple mdf tempdb files speeds up SQL performance.\u00a0 The tempdb is a system db\u00a0that has\u00a0resource available to all users.\u00a0 And from the expert<\/a><\/li>\n
  12. TempDB: Increase it’s initial size\u00a0 & autogrowth to MB as opposed to percent (see image2).<\/li>\n
  13. TempDB: Simple recovery model for TempDB is correct.<\/li>\n
  14. TempDB: The default is 1, you need more than this depending on how many CPU cores are on your database server.\u00a0 1 option is set the number of TempDB’s to the same as the number of CPUI cores (1-to-1).\u00a0\u00a0Some 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.\u00a0\u00a0 I start with 4 and tune in performance testing or once it is running.\u00a0 Saying that I normally have 16 cores.\u00a0\u00a0I 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\u00a0from there.<\/li>\n
  15. TempDB: Move the tempdb.mdf files and the TempDB .ldf file\u00a0to their own fast as possible drive.<\/li>\n
  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.<\/li>\n
  17. Content DBs: Workaround- create the db outside of PS, get the SP collation right “Latin1_General_CI_AS_KS_WS” collation.<\/li>\n
  18. If your SQL db uses spinning disk split the mdf and ldf files onto separate disks.\u00a0 Order the db files as follows: tempdb must be on the fastest disk, content db log files next and content db’s next.<\/li>\n
  19. Change the default backup location to a separate disk (pretty obvious but it is the default setting).<\/li>\n
  20. Set the default for the\u00a0database instance file locations\u00a0 – set the default location where new mdf, ldf and backups will go on disk (per your fasteset disk calcs).<\/li>\n
  21. Set the default for the database instance backup compression – I’d go with compression for all backups.<\/li>\n
  22. mdf and ldf should be on separate drives for 2 reasons : IOPS speed (provided this is spinning disk)\u00a0& DR (you don’t want to loose both).<\/li>\n
  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.\u00a0 e.g. cmd>Chkdsk c:<\/li>\n
  24. Use RAID 10 where possible.<\/li>\n
  25. Windows firewall – if using it you will need to open the incoming SQL port i.e. 1433<\/li>\n
  26. Avoid huge transaction logs and size them appropriately<\/a>.\u00a0 Pref don\u2019t use simple recovery model. Ldf content is not removed every 60 seconds when it is written to the mdf files.\u00a0Backup \u2013 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\u00a0or (sic. delete the log file) to get it back to a reasonable size.\u00a0 Hint<\/strong><\/em>: BACKUP LOG databasename<\/em> TO devicename<\/em><\/li>\n
  27. Watch the size of the Content Databases, they take time to recover.\u00a0 Max up to\u00a04TB, try stick to around 200GB (exception will be for blob storage).\u00a0 This makes backup and restore quick however AlwaysOn also changes the scenario.<\/li>\n
  28. Format the Drives<\/a> with 64K NTFS Allocation Units.<\/li>\n
  29. Antivirus software must exclude LDF\/MDF\/NDF files.<\/li>\n
  30. Don’t shrink database log files by switching the recovery model to Simple.<\/li>\n
  31. Ensure you are within the latency recommendation for SP to SQL (< 20ms).<\/li>\n<\/ol>\n\n\n\n\n
    <\/td>\n<\/tr>\n
    Image 1. Change the model database initial size and autogrowth settings.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Tip<\/em><\/strong>: 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).<\/p>\n\n\n\n\n
    <\/td>\n<\/tr>\n
    Image 2. Change the TempDB to have multiple mdf files.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n
    <\/td>\n<\/tr>\n
    Image 3. Setting Memory on SQL Server instances.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    SharePoint Sizing Starting point notes:<\/strong> SP_Config database – “Transaction log files<\/strong>. We recommend that you back up the transaction log for the configuration database regularly to force truncation.” Technet.\u00a0\u00a0\u00a0The Full recovery model is the default, switch this to Simple.\u00a0 If you need Full, your ldfs be busy.\u00a0 Suggest ldf at least 1000MB per day growth, can be a lot more.
    \nSuggested Search database sizing<\/a>.\u00a0 If the Search databases are in the Full Recovery mode you also need to set the ldf sizing.<\/p>\n\n\n\n\n\n\n\n
    Database<\/strong><\/td>\nmdf<\/strong><\/td>\nmdf growth<\/strong><\/td>\nldf<\/strong><\/td>\nldf grow<\/strong><\/td>\n<\/tr>\n
    SP_Search_Admin<\/td>\n100 MB<\/td>\n10 MB<\/td>\n100 MB<\/td>\n50 MB<\/td>\n<\/tr>\n
    SP_Search_CrawlStore<\/td>\n100 MB<\/td>\n50 MB<\/td>\n300 MB<\/td>\n100 MB<\/td>\n<\/tr>\n
    SP_Search_AnalyticsReportingStore<\/td>\n100 MB<\/td>\n50 MB<\/td>\n25 MB<\/td>\n25 MB<\/td>\n<\/tr>\n
    SP_Search_LinksStore<\/td>\n100 MB<\/td>\n50 MB<\/td>\n25 MB<\/td>\n25 MB<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Update 23 Jan 2014:<\/strong>\u00a0 Todd Klindt has a good set of blog posts on SQL 2012 for SharePoint 2013<\/a>. How do ldf files work with mdf in SQL Server<\/em><\/strong>: Content goes into .ldf file temporarily, checkpoint occurs every minute and moves from .ldf to mdf.\u00a0 If the “Full recovery model” is used the content\u00a0in the the ldf file is retained. Hence large trans logs but recovery is better.\u00a0 If a simple recovery model is used, the ldf data is dumped.
    \nKeith Tuomi provide the     code<\/a> to automatically change the autogrowth sizing.<\/strong><\/p>\n\n\n\n
    01<\/td>\n$Server=”SQLSRV2012″<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    02<\/td>\n[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    03<\/td>\n$SMOserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $Server<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    04<\/td>\n$databases = $SMOserver.Databases;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    05<\/td>\nforeach ($DB in $databases | where{$_.Name -like ‘*Content*’}) {<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    06<\/td>\n#Set Log File growth<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    07<\/td>\nforeach ($DBLF in $DB.logfiles) {<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    08<\/td>\n$DBLF.set_GrowthType(“KB”);<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    09<\/td>\n$DBLF.set_Growth(“51200”); #50mb<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    10<\/td>\n$DBLF.Alter();<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    11<\/td>\n}<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    12<\/td>\n#set File Growth<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    13<\/td>\n$DBFG = $DB.FileGroups;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    14<\/td>\nforeach ($DBF in $DBFG.Files) {<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    15<\/td>\n$DBF.set_GrowthType(“KB”);<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    16<\/td>\n$DBF.set_Growth(“102400”); #100mb<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    17<\/td>\n$DBF.Alter();<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    18<\/td>\n}<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
    19<\/td>\n}<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    SQL Licencing:<\/strong> There are numerous licensing models available to SQL server on the different versions and I find them extremely complex.\u00a0For large SQL using the\u00a0Enterprise Edition of SQL 2012,\u00a0per-core licensing at the hardware (hypervisor) level is an option.\u00a0The SQL instances can be tied to specific hardware. Affinity rules\u00a0do need to be\u00a0setup to prevent\u00a0vMotion\u00a0moving the VM to another hardware host.\u00a0 In a HA situation using AOAG in a passive situation, the secondary SQL servers et al.\u00a0will require licencing. SQL Installation<\/strong>:\u00a0 I slipstream and automatically install SQL Server 2012.\u00a0 The checklist below lists the SQL features you can install.\u00a0 Determine your needs makes creating the config.ini files used by the install much easier to do.\u00a0 The example below is used to create both my primary SQL Server and the secondary (AOAG) server, they are identical.\u00a0 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.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \u00a0Feature<\/strong><\/td>\n\u00a0Install<\/strong><\/td>\n<\/tr>\n
    Database Engine Services<\/td>\nY<\/td>\n<\/tr>\n
    SQL Server Replication<\/td>\nY<\/td>\n<\/tr>\n
    Full-Text Search<\/td>\nN<\/td>\n<\/tr>\n
    Data Quality Service<\/td>\nN<\/td>\n<\/tr>\n
    Analysis Services<\/td>\nN<\/td>\n<\/tr>\n
    Reporting Services: Native<\/td>\nN<\/td>\n<\/tr>\n
    Reporting Services: SharePoint<\/td>\nY<\/td>\n<\/tr>\n
    Reporting Services Add in for SharePoint<\/td>\nY<\/td>\n<\/tr>\n
    Data Quality Client<\/td>\nN<\/td>\n<\/tr>\n
    SQL Server Data Tools<\/td>\nN<\/td>\n<\/tr>\n
    Integration Services<\/td>\nY<\/td>\n<\/tr>\n
    Client Tools Connectivity<\/td>\nY<\/td>\n<\/tr>\n
    Client Tools Backward Compatibility<\/td>\nY<\/td>\n<\/tr>\n
    Client Tools SDK<\/td>\nN<\/td>\n<\/tr>\n
    Documentation Components<\/td>\nN<\/td>\n<\/tr>\n
    Management Tools Basic<\/td>\nY<\/td>\n<\/tr>\n
    Management Tools Complete<\/td>\nY<\/td>\n<\/tr>\n
    SQL Client Connectivity SDK<\/td>\nN<\/td>\n<\/tr>\n
    Master Data Services<\/td>\nN<\/td>\n<\/tr>\n
    Distributed Replay Controller<\/td>\nN<\/td>\n<\/tr>\n
    Distributed Replay Client<\/td>\nN<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    More Info<\/strong>: http:\/\/yalla.itgroove.net\/2013\/03\/sql-server-powershell-sharepoint-set-autogrowth-on-content-dbs\/<\/a> http:\/\/blog.cloudshare.com\/2013\/08\/28\/how-to-use-the-same-autogrowth-value-for-sharepoint-content-databases\/<\/a> http:\/\/technet.microsoft.com\/en-us\/library\/hh292622.aspx<\/a> 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<\/a>\u00a0(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\/<\/a> http:\/\/www.brentozar.com\/blitz\/blitz-result-percent-growth-use\/<\/a> http:\/\/www.brentozar.com\/archive\/2008\/03\/sql-server-2005-setup-checklist-part-1-before-the-install\/<\/a> http:\/\/www.sqlskills.com\/blogs\/kimberly\/8-steps-to-better-transaction-log-throughput\/<\/a> http:\/\/www.toddklindt.com\/default.aspx<\/a> SharePoint database types and description<\/a>
    \nThanks to<\/strong>: @<\/span>allanSQLIS – Allan Mitchell – great sitting next to a SQL\u00a0expert.
    \nSteve Goodyear has a blog post on     Farm Install and build guide<\/a>.\u00a0 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.
    \nSQL Hardening:<\/strong> From     http:\/\/blogs.technet.com\/b\/rycampbe\/archive\/2013\/10\/14\/securing-sharepoint-harden-sql-server-in-sharepoint-environments.aspx<\/a><\/strong> Hardening SQL Server is done in a 3 phased approach:<\/p>\n

      \n
    1. Encryption at Rest (Encrypt the data sitting on the hard drives)<\/li>\n
    2. Encrypt Connections (Encrypt the data in flight on the network between servers)<\/li>\n
    3. Server Isolation (Configure SQL Server’s firewall to ignore requests from unauthorized servers)<\/li>\n<\/ol>\n

      Transparent Data Encryption (TDE) can be used to encrypt any SharePoint database.\u00a0 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.\u00a0 There are a lot of ramifications to using TDE so review the decision to use TDE carefulkly before implementing.<\/p>\n

      Lots of good blogs are gone so I am Reblogging \u00a0it from:\u00a0 http:\/\/blog.sharepointsite.co.uk\/2013\/10\/sql-server-2012-for-sharepoint-2013.html<\/p>\n","protected":false},"excerpt":{"rendered":"

      \u00a0 Use multiple: SQL Aliases (separate 1 for search). Dedicate SQL Server for SharePoint. Set max degree of parallelism (MAXDOP) to 1 for instances of SQL (SP will do this when SP is installed).\u00a0 Number of processes for each SQL statement. Mixed mode authentication – Don’t install SQL 2012 for SP in mixed mode auth […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/posts\/1826"}],"collection":[{"href":"https:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1826"}],"version-history":[{"count":0,"href":"https:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/posts\/1826\/revisions"}],"wp:attachment":[{"href":"https:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1826"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1826"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1826"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}