Checklist for SQL Server implementations

Overview
The purpose of this article is to serve as a checklist for whenever I encounter an existing SQL Server instance or when I'm to implement a new SQL Server instance. There are a bunch of things that I want to check out, verify etc. Most of these aren't particulary difficult, but the trick is to remember to do them. Hence this article, a cheat-sheet. It is not meant to be exhaustive. There will always be more stuff to put in here, or things that you and I prefer to do differently. And, this will always be a work in-progress state.

Before and during installation. Or check and document for existing installations.

• No powersave in bios or Windows (High Performance)
• What components to install
• Collation
• Security mode
• Password for "sa"
• Service accounts
• Default folders for database files and backup files
• Configuration for tempdb (where to put it, number of files, size for each file)
  

• Instant File Initialization, implement and verify
• Lock Pages In Memory
• Max Server Memory Setting
• Cost Threshold for parallelism, increase to 50 or whatever is your preference.
• Check for unexpected weird backups, such that will break the chain of scheduled backups and consequently ruin the expected restore scenarios.
• Optimize performance for ad-hoc workloads, set to true
• Install my sp_dbinfo, sp_tableinfo and sp_indexinfo procedures.
• Verify no auto-shrink and no scheduled shrink jobs.
• Verify no auto-close database settings.
• Verify page verify is checksum. If not, set to checksum.
• Verify reasonable size for ldf files (sp_dbinfo).
• Local Books Online (SQL Server Documentation). Update it.
• Turn on trace flag 2371?
• Turn on trace flag 4199? Might not be necessary as of 2016, depending on database compatibility level and other settings.
  

• Database file location
• Database file autogrow not percentage
• Database file autogrow increments reasonable (not too small, not too large)
• Verify proper recovery model for each database. Simple if we aren't supposed to take log backup. Else full, or possibly bulk-logged.
• Verify no auto-shrink and no scheduled shrink jobs.
• Verify no auto-close database settings.
• Verify page verify is checksum. If not, set to checksum.
• Verify reasonable size for ldf files (sp_dbinfo).
• Not too many Virtual Log Files (max ca 200).
  

• Set tempdb initial size to reasonable size.
• Multiple tempdb data files, reasonable initial size and autogrow on all.
• Turn on trace flag 1118? (Dummy option as of 2016)
• Turn on trace flag 1117? (Dummy option as of 2016)
  

• Implement maintenance routines. I typically use Ola Hallengren's solution.
• Increase job history to 200 rows per job and 50000 rows total.
• Inplement Database Mail. Test and verify. Configure Agent to use the profile, restart Agent service. Create test job that only send mail on execution and verify the mail config.
• Install my MailAfterJob procedure.
• Implement SQL Server Agent Event Alerts, as per my script.
• Possibly implement Master and Target server environment for Agent (MSX-TSX).
• Implement routine to trim mail history tables in msdb.

• Glenn Berry's diag scripts.
• Brent Ozar's sp_blitz.
• Go through the errorlog files, using my utility.