Thursday 15 January 2015

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


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.



No comments: