SQL Server Setup and Maintenance Tips

Server Maintenance
- Windows Updates

Run windows updates manually and frequently. Turning on automatic updates is not advised, based on the history of windows updates breaking certain products.  Before installing updates, check the internet for reports of issues it could cause with your applications.

- Virus Scanners

Exclude MDF, NDF, LDF, BAK, TRN and BCP file extensions and make sure schedules scans don;t coincide with other scheduled tasks.

- Backups

Perform a full system backup periodically and before and after installations are run.

- Defragmentation

SANs and SSDs are less likely to get fragmented in normal operation. If a drive is used only for database files, it is less likely to need defragmentation.  Drives that hold BAK files will need frequent defragmentation.

SQL Server Setup Best Practices
- Plan the data storage scheme before installing SQL

Disk 1:  OS, page file, SQL Server binaries
Disk 2 or LUN 1: MDF files (Except tempdb)
Disk 3 or LUN 2: LDF files
Disk 4 or LUN 3: tempdb files
Disk 5 or LUN 4: BAK files

- Check for Physical File Fragmentation before creating a database or log file
- Pre-size MDF and LDF files
- Do not set auto-growth of MDF or LDF files to a percentage.  Set it to a value in MB

Revisit periodically and reset initial size if necessary

- Plan disk sizes so that you can keep free drive space above 15%
- Turn on Instant File Initialization in SQL
- Turn on Auto Statistics

SQL Server Maintenance Best Practice for a Typical GP Instance

- Use Full backup model
- Weekly (or more frequent) Full database backup, transported off site.
- Nightly incremental database backup
- Periodic log backups throughout the day

Use backup compression

- What should you back up?

master
msdb
DYNAMICS
All company databases
NEVER back up tempdb
Model only needs to be backed up when making changes to master (such as when upgrading or patching SQL Server)

- When backing up data, the tasks should be run in this order

1. Check DB
2. Reorganize indexes
3. Update Statistics
4. Backup Database

- When backup up logs, maintenance tasks are not necessary

SQL Server Manual Period Maintenance Tasks
(These tasks could be scheduled, but their results should be checked before allowing users back into the system)

- Index Maintenance

Add missing indexes
Remove unused and duplicate indexes
Monitor indexes for excessive fragmentation

- Msdb Maintenance

Periodically delete backup, restore, job, and maintenance plan history
sp_delete_backuphistory [oldest date]
Use log file viewer for others

- Shrinking

Always rebuild indexes after shrinking.  If you need to avoid growing the file with a rebuild after shrinking, then at least re-organize indexes.  Shrinking leaves indexes nearly 100% fragmented by design.
Never shrink tempdb
Shrinking should be a rare occurrence if the growth settings are tuned properly

- Monitor Drive free space

Keep more than 15% free

- Monitor SQL Server logs and Windows event logs for errors and warnings

Did this help you?  If so, please leave a comment!