SQL Server Installation Checklist

SQL Server Installation Checklist

(Settings that improve SQL Server performance)
Here are some of our absolute best SQL setup tips.

Warning: You won’t find anything theoretical here. This is strictly a server setup checklist. The checklist below works in 99% of cases. So unless there’s something very specific about your SQL Server, you should follow these items to get the highest performance and reliability out of it.

Hardware Checklist

1. Verify hardware compatibility against SQL Server’s prerequisites.
2. Verify the BIOS is running the latest version.
3. Verify BIOS power management is disabled or set to OS control.
4. Install the latest drivers.

• Networking
• HBA
• Virtualization
• Storage

Operating System

• Start with a freshly installed operating system.

Windows Checklist

1. Set the Windows clock’s time zone.
2. Set the Windows Power Plan to “High Performance”.
3. Change the Windows page file location and size.

Set both the minimum and maximum to 8192MB.

Put the file on your fastest drive (on Amazon AWS, put it on the ephemeral drive, if one is available).

If the page file is set on any other drives, delete those.

1. Install all Windows Updates.
2. Change how Windows Updates are delivered:

“Check for updates but let me choose whether to download and install them.” On your most critical servers, set this so nothing gets installed without your knowledge.

Check the box for “Give me recommended updates the same way I receive important updates.”

Check the box for “Give me updates for other Microsoft products when I update Windows.”

1. Install Windows Features/Roles. Remove them if they aren’t needed. (Not needed on an Amazon or Azure setup.)

Install Multipath I/O if needed (not needed on Amazon or Azure).

Install Advanced Storage features if needed (not needed on Amazon or Azure).

Install .NET 3.5 (needed even in SQL 2016, so that database mail works!)

1. Format all the storage where SQL files will live using a 64 KB allocation unit size.

When the storage volume is an on-premises SAN volume, don’t use “Perform a Quick Format.”

When a volume is larger than 2 TB, format it as GPT (not MBR). We format everything as GPT.

1. Set Visualization Optimization to “Optimized for Performance.”
2. Disable any services that aren’t needed.
3. Configure antivirus and exclusions.
4. Activate the Windows OS license.
5. Disable Server Manager from launching automatically (ideally do this for all users, not just yourself).

Optional steps: I do these to make server management easier down the line

1. Set up the Recycle Bin to confirm before deleting (“Display delete confirmation dialog”).
2. Run CrystalDiskMark on each disk and save the results to a document. Name the document <servername_yyyy_mm_dd>. Make sure performance is close to what you should be getting. This is a sanity check — it’s good to know how fast your disks actually perform.
3. Pin ‘cmd.exe’ to the taskbar.

Set it to run as “Run as Administrator.”

Open the cmd window, right-click the top of the window, go to ‘Properties’ -> ‘Layout’, and change “Height” under “Screen Buffer Size” to “9999.”

1. Pin Windows PowerShell ISE to the taskbar, and set it to run as “Run as Administrator.”

SQL Server installation best-practices checklist (SQL setup checklist)

1. Check the SQL Server installation media (Enterprise, Standard, Developer, Express, etc.).
2. Install SQL using the SQL Server Installation Center.
3. Configuration items during SQL install.
4. Install the latest SQL Service Pack.

First, find the latest SQL Service Pack and Cumulative Updates. Don’t install it right away. Only install it if the update came out 30-60 days ago. Then Google the Service Pack name to see if there are any known issues. If there are none — install it.

1. Install the latest SQL Cumulative Update.
2. Download and install the latest SSMS. I try to avoid installing SSMS on the server itself, but to this day I’ll still install it locally when it’s a critical server — and won’t when it isn’t. Why do this on critical servers? Because when something goes wrong, I may need to be local on the box to troubleshoot it (this happens a lot on Amazon AWS), especially when there are networking or routing issues.

SQL instance changes (post-installation checklist)

1. secpol.msc – set “Lock Pages in Memory” and “Perform Volume Maintenance Tasks.”

Add the account running the SQL Engine service to get these two security rights.

1. SQL Configuration Manager

Disable any services you won’t need. If you did the SQL install correctly, there shouldn’t be much to do here. But if the server was built from an Amazon AWS AMI, you’ll have ALL SQL Server features. That’s not good. But it’s even worse to leave them all running. So disable them.

Turn on the TCP/IP protocol.

1. When fixing the hostname on Amazon AWS.
2. SQL instance changes.

Add trace flags (here’s a script that sets up trace flags):

3226 – stop logging successful backup messages to the SQL Error log.

1222 – capture deadlock information in the SQL Error log.

1. When running SQL below SQL 2016, I also add:

1118 – use only full extents.

1117 – grow all files in a filegroup together.

2371 – controls when AUTO_UPDATE_STATISTICS kicks in for large tables.

1. Set max memory.
2. System database changes.
3. SQL Agent changes.
4. ERRORLOG configuration.
5. Change the default directory.
6. Tempdb setup.
7. Create a DBAToolsdb.
8. Create maintenance jobs.
9. Add security.
10. Set up installation email.
11. Set up installation alerts.
12. Deploy open-source stored procedures.

Monitoring Setup

Below are the scripts we deploy to every SQL Server. This checklist is already huge, so this deserves its own dedicated post

1. SQL Agent restart notification – send an email whenever SQL Agent restarts.
2. SQL Engine restart notification – send an email whenever the SQL Engine service restarts.
3. HDD space monitoring – this is configurable disk-space monitoring that emails Warning and Critical alerts once a threshold is reached.
4. DDL event monitoring. If any table, stored procedure, view, or object changes, I keep a log of everything I can capture.
5. Sp_whoisactive monitoring – run the best stored procedure for understanding what’s happening on the server every few minutes, save it to a table, purge irrelevant and old data, so I can go back and answer a question about why the server was slow at 3pm last Tuesday.
6. SQL Agent job change monitoring – capture every job and job-step change and log it to a history table.
7. SQL Config Change Track and Notify – capture as much as I can about SQL instance configuration changes and send an email once a day, so I know whether I’m dealing with server settings someone forgot to mention to me.
8. Cache single-use plan cleanup – if single-use plans are a problem, this runs and clears out unnecessary plans from the cache, freeing up more RAM for the rest of the server. If your server doesn’t have that problem, this won’t cause any harm.
9. DB and table growth monitoring – regularly log database sizes and table sizes, so after a while I can easily predict what the growth patterns will look like.

That’s it. Your SQL Server is now in better shape than 90% of the SQL Servers out there. And it’ll run on its own, reliably, for a lot longer.