Auditing SQL Server - The Basics

I've audited a lot of SQL Servers in my current role and have developed templates and scripts to assist me. So what do I check?

Audit Summary

• Date & Time
• Auditor (usually me!)
• Location
• Server name
• IP Address
• Physical or Virtual ?
• Virtual Platform
• Clustered ?

CPU

• Processor Type
• Clockspeed
• 32/64 bit

Windows

• OS Version
• OS Edition
• Service Pack

Questions this raises -
   Is the server patched? 
   Is the OS and Service Pack supported? 
   Is the edition appropriate? *  
   * Ever found a 32bit OS installed on a 64bit server? I have...
   Is the SQL edition appropriate i.e are we using it's features?

Memory

• Physical Size
• Swap file Configuration - Location, Min Size, Max Size, Recommended & Current Size *

* Ideally not in use at all on a live server.

Networking

For Each NIC -

• Use
• IP Address Subnet
• Max Speed
• Full Duplex? (Occasionally old servers prevail)

Other Settings

• Power Saving - Yes, I've found this incorrectly configured on live servers!
• Antivirus - Are SQL file types and executables excluded from scanning activity?
• Are any Roles installed?
• Are any Features installed?
• Other Applications - Does SQL have to share the server?
• Are any scheduled tasks present?

System Settings

• No errors / misidentified hardware in device manager?
• Is the server optimised for background services?

SQL Configuration

Basics

• Instance Name
• SQL Version
• SQL Edition
• SQL Service Pack
• SQL Build
• Collation

Service Account

• What account does the SQL Service run under?
• Does it have the correct Group Policy settings?

Processor Cores

• How many are there?
• How many are configured for SQL?
• Cost Threshold of Parallelism?
• Max degree of Parallelism
• Affinity
• Affinity I/O

Memory Configuration

• Physical Available to Server
• SQL Allocation - Dynamic / Fixed
• SQL Minimum Memory
• SQL Maximum Memory

Networking 

• Which protocols are enabled?
• Are they used / needed?
• What is the binding order?

SQL High Availability 

Are these present ? Are they monitored?

• Clustering
• Mirroring
• Replication
• Log Shipping

Review Logs -

• SQL Server Logs
• SQL Agent Logs
• Windows Event Logs

Drive Configuration , Look for -

• Alignment (if pre Windows 2008)
• Cluster Size
• NTFS Compression

Drive Capacity

• Disk Total
• Disk Free
• Data Allocated
• Data Used 
• Data Free
• Logs Allocated
• Logs Used
• Logs Free

File Locations

• Data
• Logs

Data & Logs should be on different volumes for performance.

Database Configuration

• Database Compatibility Mode - Does it match the server? Should it?
• Data & Log Allocated
• Data & Log Used
• Data & Log Free
• Data & Log Growth intervals
• Autoshrink Enabled?

Database Maintenance

Backups

• Last Successful Full Backup 
• Last Successful Log Backup 
• Backup Locations

Look in the backup path to ensure they are present.

Are they archived away from the server too?

Data, Logs & their respective backups should be on different volumes for safety.

Other Maintenance 

How often are these tasks performed?

• Index Maintenance
• Integrity Checks
• Manage History tables

What period do these tables cover?

• Backup History
• Agent History
• Maintenance Plan History

Don't allow them to get unnecessarily large.