Sunday, December 13, 2009

SQL: Poor man's audit

Most DBAs have had to examine unknown database/instances in order to evaluate their configuration.

I recently had to examine a few SQL servers for a client in order to offer advice on improvement - mainly in the area of "best practice configuration". This is a (simplified) version of a script I wrote in order to do a quick survey of misconfigurations. Please excuse the simple script - it is more of an exercise in SMO than in SQL or PowerShell.

## **************************************************************
## * Script Name: SQLpoormansaudit.ps1
## * Version: 1.0
## * Developed by:Jakob Bindslet
## * Contact: jakob@bindslet.dk
## * ----------------------------------------------------------
## * Usage: *
## * SQLpoormansaudit.ps1 "Server\InstanceName"
## * ----------------------------------------------------------
## * Description:
## * Script to perform an very quick audit of SQL
## * 2005+ instance.*
## * Tests for collation mismatch between DB & DBMS, allocated
## * memory, AutoClose/shrink, membership of fixed server roles
## * and other stuff
## * Can easily be extended to check for other parameters
## **************************************************************

Param ($instance)
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$smo = new-object('Microsoft.SqlServer.Management.Smo.Server') $instance
$memSrv = $smo.information.physicalMemory
$memMin = $smo.configuration.minServerMemory.runValue
$memMax = $smo.configuration.maxServerMemory.runValue
$version = $smo.versionmajor
$cpuSrv = $smo.information.Processors
$maxParallel = $smo.configuration.maxDegreeOfParallelism.runvalue

## DBMS
Write-Host "Testing instance $($instance.toUpper()) [DBMS Level]" -fore "yellow"
Write-Host "Collation:                  " -noNewLine
Write-Host "$($smo.collation)" -fore "green"
Write-Host "Authentication:             " -noNewLine
Write-Host "$($smo.loginMode)" -fore $(if ($smo.loginMode -eq "Integrated") {"green"} else {"red"})
Write-Host "Login audit:                " -noNewLine
Write-Host "$($smo.auditLevel)" -fore $(if ($smo.auditLevel -eq "All") {"green"} else {"red"})
Write-Host "Max number of logfiles:     " -noNewLine
Write-Host "$($smo.numberOfLogFiles)" -fore $(if ($smo.numberOfLogFiles -gt 30) {"green"} elseif ($smo.numberOfLogFiles -gt 0) {"yellow"} else {"red"})
Write-Host "Server RAM available:       " -noNewLine
Write-Host "$memSrv MB" -fore "green"
Write-Host "SQL memory Min:             " -noNewLine
Write-Host "$memMin" -fore $(if ($memMin -eq 0) {"yellow"} else {"green"})
Write-Host "SQL memory Max:             " -noNewLine
Write-Host "$memMax ($([math]::round($memMax/$memSrv*100,1))% of server total)" -fore $(if ($memMax -gt 2000000) {"red"} else {"green"})
foreach ($role in $smo.roles) {
    if ($($role.enumserverrolemembers().count) -gt 1) {
        Write-Host "# of $($role.name)s: `t    " -noNewLine
        Write-Host "$($role.enumserverrolemembers().count)" -fore "yellow"
    }
}

## DATABASES
Write-Host "`nTesting Databases:" -fore "yellow"
$databases = $smo.databases | where {$_.id -gt 4}
Foreach ($db in $databases) {
    Write-Host "`nDatabase: " -noNewLine
    Write-Host "$($db.name.toUpper())" -fore "green"
    if ($db.autoClose -ne $false) {
        Write-Host "Auto Close:                 " -noNewLine; Write-Host $db.autoClose -fore "red"
        }
    if ($db.autoShrink -ne $false) {
        Write-Host "Auto Shrink:                " -noNewLine; Write-Host $db.autoShrink -fore "red"
    }
    if ($db.autoCreateStatisticsEnabled -ne $true) {
        Write-Host "Auto Create Statistics:     " -noNewLine; Write-Host $db.autoCreateStatisticsEnabled -fore "red"
    }
    if ($db.autoUpdateStatisticsEnabled -ne $true) {
        Write-Host "Auto Update Statistics:     " -noNewLine; Write-Host $db.autoUpdateStatisticsEnabled -fore "red"
    }
    if ($db.status -ne "Normal") {
        Write-Host "Status:                     " -noNewLine; Write-Host $db.status -fore "red"
    }
    if ($db.owner -ne "sa") {
        Write-Host "Owner:                      " -noNewLine; Write-Host $db.owner -fore "red"
    }
    if ($db.pageVerify -ne "Checksum") {
        Write-Host "Pageverify:                 " -noNewLine; Write-Host $db.pageVerify -fore "red"
    }    
    if ($db.collation -ne $smo.collation) {
        Write-Host "Collation:                  " -noNewLine; Write-Host $db.collation -fore "red"
    }    
    if ($($db.CompatibilityLevel.toString().replace('Version','')) -ne $version * 10) {
        Write-Host "Compatibility Level         " -noNewLine; Write-Host $db.CompatibilityLevel -fore "red"
    }    
    if (((get-date) - $db.LastBackupDate).days -gt 1) {
        Write-Host "Last full backup:           " -noNewLine; Write-Host $db.LastBackupDate -fore "red"
    }
}

No comments: