Nucleus Datakonsult Checklist for SQL Server implementations

Home

About me and Nucleus
Partners

Articles, tips etc.
My blog
Links
Microsoft articles
Forums

Courses and training




Microsoft Most Valuable Professional


Skor
R

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, backup files
  • Configuration for tempdb (where to put it, number of files, size for each file)


Instance
  • Instant File Initialization, implement and verify
  • Lock Pages In Memory
  • Max Server Memory Setting
  • Cost Threshold for parallelism, increase to 25 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 neccesary for 2016, depending on database compatibility level and other settings.


Database
  • 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-load.
  • 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).


Tempdb
  • Set tempdb initial size to reasonable size.
  • Multiple tempdb data files, reasonable initial size and autogrow on all.
  • Turn on trace flag 1118?
  • Turn on trace flag 1117?


SQL Server Agent
  • Implement mainentence routines. I typically use Ols 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 scipt.
  • Possibly implement Master and Target server environment for Agent (MSX-TSX).
  • Implement routine to trim mail history tables in msdb.


Health check, spend more time initially and re-visit every few months
  • Glenn Berry's diag scripts.
  • Brent Ozar's sp_blitz.
  • Go through the errorlog files, using my utility.


Again: work in progress. If you have things and you want to contribute, then just send me an email. Let me know if you want to mentioned in below "Acknowledgements" (I will default to "yes"). I reserve my right to disagree wheather a sugestion should go on this list. :)


Acknowledgements
Thanks for providing feedback and suggestions for this article:
Donald Duck, Mickey Mouse (placeholder names for now :) ). Much appreciated!