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## DBMSWrite-Host "Testing instance $($instance.toUpper()) [DBMS Level]" -fore "yellow"Write-Host "Collation: " -noNewLineWrite-Host "$($smo.collation)" -fore "green"Write-Host "Authentication: " -noNewLineWrite-Host "$($smo.loginMode)" -fore $(if ($smo.loginMode -eq "Integrated") {"green"} else {"red"})Write-Host "Login audit: " -noNewLineWrite-Host "$($smo.auditLevel)" -fore $(if ($smo.auditLevel -eq "All") {"green"} else {"red"})Write-Host "Max number of logfiles: " -noNewLineWrite-Host "$($smo.numberOfLogFiles)" -fore $(if ($smo.numberOfLogFiles -gt 30) {"green"} elseif ($smo.numberOfLogFiles -gt 0) {"yellow"} else {"red"})Write-Host "Server RAM available: " -noNewLineWrite-Host "$memSrv MB" -fore "green"Write-Host "SQL memory Min: " -noNewLineWrite-Host "$memMin" -fore $(if ($memMin -eq 0) {"yellow"} else {"green"})Write-Host "SQL memory Max: " -noNewLineWrite-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" }}## DATABASESWrite-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" }}