SQL Server Post-Install Configurations

What the Installer Doesn’t Do For You

Author

Kevin Hill
July 11, 2025

The SQL Server installer has gotten better: tempdb configuration, MAXDOP, and even max memory can now be configured during setup.

But don’t be fooled: there’s still a post-install checklist that can make or break your environment over time. If you’ve ever inherited a server that “just ran” for years and started getting slower over time you’ve likely seen what happens when this list gets ignored.

These are not in any particular order, but some do require a restart of the server or the SQL Server Engine service to take effect:

  • Required for notifications from SQL Server Agent jobs and alerts.

  • Set up a mail profile and default operator.

  • Enables proactive failure detection and response.

2. Set Up Alerts for High Severity errors 19–25, and 823-825 (corruption)

  • These represent serious errors such as disk issues, memory exhaustion, and corruption.

  • Configure SQL Agent alerts to trigger on these severity levels.

  • Route alerts to the Operator for immediate action.

  • Don’t forget to check the “Enable mail profile” box in the SQL Agent Properties>>Alert System page.

  • Some vulnerability / security tools intentionally send in bad usernames or packets that trigger Severity 20 alerts. You may wind up disabling this one.

3. Enable Backup Compression by Default

  • Saves space and often speeds up backup jobs.

  • sp_configure 'backup compression default', 1

  • Reduces I/O load and backup windows on most systems.

  • No risk to this option.

  • SQL Server 2025 might be enhancing this. Backup compression has been an on/off switch since it was introduced.

4. Create and Schedule Maintenance Jobs

  • Avoid relying on default Maintenance Plans if you can.

  • Key tasks:

    • Full, differential, and log backups (user and system databases)

    • Integrity checks (DBCC CHECKDB)

    • Index and stats maintenance

      • What parameters and how often? Lets argue in the comments!

  • Use Ola Hallengren’s Maintenance Solution for greater flexibility, better logging and more frequent updates. Free tool. Not supported by Microsoft.

5. Configure Error Log Recycling

  • Prevent bloated error log files that slow down viewing or parsing.

  • Set SQL Server to recycle logs weekly (my preference)

  • Increase log retention to 12–30 files for historical troubleshooting. I like 25 so I have 6 months of data.

6. Apply Cumulative Updates and Security Fixes

  • SQL Server isn’t patched after install.

  • Download and apply the latest CU and any critical security updates.

    • Make sure your applications are compatible with the updates you are installing.

  • Document patch level and baseline configuration.

  • Restart after each install. Don’t leave a reboot pending for the next person.

  • Full list of CUs, Security patches and Service Packs

7. Back Up System Databases Immediately

  • Even a fresh install has valuable information (logins, jobs, etc.).

  • Take manual backups of master, model, and msdb.

  • Set the model database parameters to what new databases will typically use (Auto settings, RCSI, etc.)

  • Repeat after significant changes (e.g., login creation, job setup, new databases), in addition to scheduled backups

  • IFI drastically reduces file growth and restore time.

  • Requires “Perform Volume Maintenance Tasks” right for the SQL Server service account.

    • This is an installation option, but it is often overlooked

  • Check via sys.dm_server_services.

  • Requires a SQL Server service to take effect.

9. Set Windows to High Performance Power Mode

  • Prevent CPU throttling that slows SQL Server under load.

  • Switch to High Performance mode via Windows Power Options.

10. Reduce Surface Area

  • Disable unused features: Full-Text Search, SQLCLR, etc.

  • Disable SQL Browser if not using named instances.

  • Use sp_configure and SQL Server Configuration Manager to audit and lock down services.

11. Review Default Permissions and Roles

  • Remove unused logins and review built-in accounts.

  • Disable or rename the ‘sa’ login if not in use.

  • Avoid assigning sysadmin unless absolutely necessary. Check it regularly.

12. Instance level configurations

  • Cost Threshold for Parallelism

    • Defaults to 5, through at least SQL 2022. I prefer 50 for an OLTP system that does some reporting/complex querying

  • Optimize for Ad Hoc Workloads (if you are going to have a lot of Ad Hoc and you are tight on memory)

The Bottom Line:

Finishing the install is just the beginning. These post-install configurations set the foundation for a stable, secure, and high-performing SQL Server. Skip them, and you’ll be firefighting later. Not every single setting applies to every server. Click the links, read the docs, do the research.

New Pocket DBA® clients get the first month FREE!

Book a call, and mention “Newsletter”

SQL tidBITs:

Here’s the T-SQL code to check whether Instant File Initialization (IFI) is enabled, using the built-in sys.dm_server_services DMV (available starting in SQL Server 2016): 

SELECT 
    servicename,
    startup_type_desc,
    status_desc,
    instant_file_initialization_enabled
FROM 
    sys.dm_server_services
WHERE 
    servicename LIKE 'SQL Server%';

Share with your friends that have Errorlogs so big they won’t open!

Reply

or to participate.