When installing SQL, it is too easy to click next and accept all the default settings. Kevin Boles has preached for years that SQL Server defaults suck! Joseph D’Antoni has also addressed bad default settings in Building Perfect SQL Servers Every Time with his best practices installation script. As a SQL DBA it is best to configure our servers on purpose and not by default. There are a few things can be changed during the installation but the bulk of setup steps occur after the install is completed.
Based on this good advice and best practices, I have continued to refine a setup script and master checklist that I apply to every new SQL server instance. Anytime I do something over and over a few times, I script it and document it. Running this script is the first thing I do after a SQL install and before any production data touches the instance. It isn’t dynamic enough to automatically run on every type and version of server, but it is a pretty quick process that helps keep things organized and consistent across all servers. In a nut shell, the script modifies default settings, optimizes the environment, creates SQL jobs and regular maintenance schedules with email alerting.
Benefits:
- Every server has a familiar configuration baseline
- Initial security is consistent at deployment
- Servers are configured more predictable for easier troubleshooting
- Standardized monitoring, alerting and reporting systems
This is briefly what my setup checklist looks like. Most of these topics and settings are covered in the video links above.
Setup Checklist
- During SQL Install
- Only install necessary features
- Assign domain account to sysadmin role
- Set SQL services to run under proper domain accounts
- Assign proper database, log and tempdb drive locations
- Modify default settings
- Set model db to simple mode and adjust auto growth settings
- Set min/max memory
- Set MAXDOP and Cost Threshold for Parallelism
- Set proper # of tempdb files
- Create startup sproc for trace flags
- Enable remote admin connection
- Enable backup compression
- Modify environment (all pre-scripted)
- Create service accounts and assign roles
- Disable SA account
- Setup DBmail
- Setup operator and email alerts for severity 16-25 and event id 823-825
- Install third party scripts
- Ola Halengreen’s backup and maintenance scrips
- Adam Mechanic’s WhoIsActive script
- Setup SQL Jobs (pre-scripted with schedules and email alerting)
- Jobs for full user database backups
- Job for full system database backups
- Job for index maintenance
- Job for integrity checks
- Job for system log cleanup
- Extra
- Install latest SP
- Enable Instant File Initialization
- Add server to custom monitoring solution
- Ensure server power management is set to High Performance
- Ensure anti-virus software is SQL aware