Question Driven

• Install SQL Server Service Packs and Cumulative updates recommended from http://sqlserverupdates.com http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-before-the-install/ http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-2-after-the-install/
• Keep Long Term Backups at least 1 year (in case of uncaught database corruption)
• Make sure backups are stored in 3 different physical locations (at least 3 different physical buildings)
• Compress Backups (Saves time and space) https://msdn.microsoft.com/en-us/library/ms186865.aspx
• Every Database, Database Options “Page Verify” = CHECKSUM (To help catch corruption early.  Once enabled then any future writes will receive a checksum after written.  Past writes will not have a checksum.)
• Validate Backups and use Checksum option in the backup (Maintenance Plans do not have this option, use scripts from https://ola.hallengren.com/ )
• Create a SQL Agent Job or Maintenance Plan to run DBCC CHECKDB on all databases at least once a week. (As often as you can that your performance requirements will allow) (CHECKDB is slow to run and heavily impacts performance) http://sqlmag.com/blog/does-using-checksum-ensure-successful-backup https://ola.hallengren.com
• RPO/RTO Meeting with Stake Holders http://www.brentozar.com/archive/2014/05/new-high-availability-planning-worksheet/
• Verify Recovery Model per database vs Backup Strategy
  • If Full Recovery Model the Transaction logs must be backed up at least every 30 minutes (More often is better) (every 1-15 minutes is best)
    • Full Once Per Week, Daily Differential, and Every 15 minutes Transaction Log backups are a good starting point (Depends on discussed RPO/RTO with Stake Holders)
  • If Simple Recovery Model then full backups daily
    • Simple Recovery Model has limitations
      • DBCC CHECKDB “repair without data loss” is not possible
      • Piecemeal restore of filegroup is not possible
      • Cannot restore individual pages
      • SQL Server Availability Groups require full recovery model
      • SQL Server Mirroring requires full recovery model
      • SQL Server Log Shipping requires full recovery model
      • https://msdn.microsoft.com/en-us/library/ms189275.aspx
    • Backup System Databases as well (master, msdb and model)
• Make sure backups are stored on different physical hardware than database servers http://www.brentozar.com/archive/2007/10/backing-up-sql-server-my-own-mediocre-practices/
• Set the default data file path, log file path, and backup file path https://msdn.microsoft.com/en-us/library/dd206993.aspx http://thomaslarock.com/2015/02/changing-default-database-file-locations-in-sql-server/
• Exclude MDF and LDF file extensions from Anti-Virus http://blogs.technet.com/b/mdegre/archive/2009/08/26/microsoft-sql-server-and-antivirus.aspx
• Every 6 months Grow data and log files to handle 1 year of growth http://dba.stackexchange.com/questions/23305/sql-server-best-practices-for-growing-database-files
• Change auto growth settings for all database files (never use percentage) 256 MB for data files and 256 MB for Log files is a good starting point.  Set Auto Growth on files to be 1 week of growth for mdf and ldf files. (Take the time to find out how much growth occurs in a week)
• Make sure all databases and agent jobs are owned by sa account (you can run sp_blitz from Brent Ozar to get a list of common problems https://www.brentozar.com/blitz/)
• Make sure only needed people have sysadmin role (Each user in sysadmin role has full permissions to all databases and sql server settings (ie this person can get you fired)) http://www.brentozar.com/blitz/security-sysadmins/
• Monitor Drive Space, Ram Usage, and CPU usage and keep overtime for performance analysis later.
• Index Maintenance(Ola Hallengren http://ola.hallengren.com )
  • Standard edition cannot rebuild indexes online, only can reorganize indexes online (table will be locked until the rebuild is complete)
  • Enterprise Edition can rebuild indexes online
  • When solid state hard drives are used for storing data and log files then index defragmentation is not needed, but updating statistics is needed.
• Statistics Maintenance (Ola Hallengren http://ola.hallengren.com)
• For Each Database – Database Auto Update Statistics true, Auto Create Statistics true, Auto Close false, and Auto Shrink false.  There are exceptions to this general best practice usually related to specific scenarios or very large databases. http://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/ http://sqlmag.com/sql-server/avoiding-autoclose-and-autoshrink-options
• Purge Backup History at most only 1 year is needed “sp_delete_backuphistory @oldest_date = ” (Restores will take too long if older records are not purged on a regular basis, and msdb database will grow out of control)  http://www.sswug.org/articles/memberarticle.aspx?id=68000
• Purge Job History on a regular basis to keep msdb database from growing out of control “sp_purge_jobhistory @oldest_date=”  https://msdn.microsoft.com/en-us/library/ms175044.aspx
• Before upgrading SQL Server Make sure and run SQL Server upgrade advisor before raising compatibility levels on databases on the upgraded SQL Server.
  • (when database is not in use) Once you fix any issues from the upgrade advisor, Backup, Make sure compatibility levels are as high as possible, backup again, rebuild indexes, update statistics, and backup again after complete.
  • Set database to single user before changing the compatibility level and set back to multi user after (Or clear plan cache after compatibility level changed for all databases)
• Cycle Error Logs daily using sql agent job and increase number of logs (long wait times when reviewing the error log is caused by not cycling the error log often enough)http://sqlmag.com/blog/cycling-and-retaining-sql-server-logs http://www.handsonsqlserver.com/how-to-cycle-the-sql-server-error-logs-without-restarting-the-sql-server-service-to-limit-the-size/
• Install sp_WhoIsActive stored procedure (this will help to determine performance issues later) http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/
• Setup the dedicated administrator connection (Dedicated memory and cpu connection) This will help in the event that SQL Server is having performance trouble.  You will be able to diagnose the problem with the dedicated resources. https://msdn.microsoft.com/en-us/library/ms190468.aspx   https://technet.microsoft.com/en-us/library/ms178068%28v=sql.105%29.aspx
• Correctly Size TempDB and enable trace flag 1118 each tempdb file needs to be the same size for each and 1 file per cpu max of 8 files(10GB is a good starting point).  Autogrowth on all tempdb files have to be the same as well. https://technet.microsoft.com/en-us/library/ms345368%28v=sql.105%29.aspx
• Correctly Size Model database and auto growth settings http://sqlmag.com/blog/choosing-default-sizes-your-data-and-log-files
• Change Model database to have your desired default recovery model
• Min and Max memory settings in SQL Server (max 80% – 85% available memory) (Min 30%-50%) SQL Server Standard Max RAM of 64GB get/use as much as possible/available keep at least 2GB for OS. https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/  http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/
  • Make sure there is enough memory for other applications/services that run on the server (It is not recommended to run other applications on a SQL Server, but we don’t live in a perfect world)
• SQL Server Alerts and Configure Database Mail, and operators https://msdn.microsoft.com/en-us/library/hh245116.aspx http://www.snapdba.com/2013/04/enabling-and-configuring-database-mail-in-sql-server-using-t-sql/  http://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/
• Configure SQL Server Agent’s fail safe operator https://msdn.microsoft.com/en-us/library/ms175514.aspx
• Create Default Alerts for severities 16 – 25 and specific alerts for 823, 824 and 825 errors. http://www.brentozar.com/blitz/configure-sql-server-alerts/
• Bios Power Settings (Maximum/High Performance) on database servers (Disable power savings)
  • CPU-Z can be used to see the current CPU speed if slower than expected then power saving mode could be the cause http://www.cpuid.com/softwares/cpu-z.html
  • http://www.mssqltips.com/sqlservertip/3396/windows-server-power-management-effect-on-sql-server/    http://www.brentozar.com/archive/2010/10/sql-server-on-powersaving-cpus-not-so-fast/
• MAXDOP set to number of cores for up to 8 cores, and “cost threshold for parallelism” (25-50 is a good starting point for cost threshold for parallelism with transactional databases) http://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/  http://www.brentozar.com/archive/2014/11/sql-server-cost-threshold-for-parallelism/https://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/
• Setup instant file initialization permissions (“Perform Volume Maintenance Tasks” in Local Security Policy)
  • Note that if your database has Transparent Data Encryption (TDE) enabled, you can’t use instant file initialization.
  • http://www.questiondriven.com/2015/04/27/instant-file-initialization-via-powershell/
• Setup Index Level Fill Factor correctly on clustered indexes (80% for guids, 100% for integers or sequential numbers, these recommendations are good starting points) http://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/   http://sqlmag.com/blog/what-fill-factor-index-fill-factor-and-performance-part-1    http://demo.sqlmag.com/blog/what-best-value-fill-factor-index-fill-factor-and-performance-part-2
• Make sure all log, tempdb, and data files are stored on RAID 1 or RAID 10 drives (What they don’t tell you about RAID 5 is that if there is a single drive failure, RAID 5 is unusable until the drive is replaced.  There is such a performance loss during a single drive failure your production server crawls.)
• SQL Server Storage Best Practices http://sqlmag.com/storage/sql-server-storage-best-practices
• Make sure SQL Server Service and SQL Server Agent run under an Active Directory account
  • Make sure required folders for sql data, sql log, backup folder, and remote backup folders have permissions
• Disable power management on network card https://technet.microsoft.com/en-us/library/ee617165%28v=ws.10%29.aspx
• Team Network Cards if possible http://www.serverwatch.com/server-tutorials/how-to-team-your-network-cards-in-windows-server-2012.html http://davidzi.com/windows-server-2012/windows-server-2012-nic-teaming-so-easy-a-caveman-can-do-it/
• Separate Drives (partitions need to be aligned and 64k block sizes)
  • Partition Alignment Article 1024 KB starting offset, and a 64 KB block size https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx http://www.questiondriven.com/2014/07/24/partition-alignment-for-sql-server-or-exchange/  http://www.midnightdba.com/Jen/2014/04/decree-set-your-partition-offset-and-block-size-make-sql-server-faster/
  • C: drive for OS  (100GB – 128GB)  (This drive does not need to be 64K block size)
  • E: At least 1 drive for SQL Server install
  • F: At least 1 drive for mdf files
  • G: At least 1 drive for Tempdb (1 mdf file per CPU up to 8 pre-grow the files in equal size leaving only a small amount of free space.  If on a SAN, add local SSD drive to the server for tempdb. This saves the SAN performance for your data and log files.  )
  • L: At least 1 drive for log ldf files
  • Z: At least 1 drive for Backups (make sure backups are copied to your three separate physical buildings)

Resources:

http://www.questiondriven.com/2015/04/29/sql-server-general-setup-script/