Sunday, August 17, 2008

SQL configuration through SMO

If you administer a large number of SQL servers, you might for some reason, have a server that doesn't quite align to your standards.
In order to avoid this I find it very nice to be able to extract various configuration settings from my servers.
Building on my post from yesterday, I'll post at small script I've been expanding on for some time (the script below is a rather basic version). The script uses SMO to query an SQL server/instance for various configuration settings.
The script requires the SMO functions from my post yesterday to be loaded.

function GetSQLconfig {
    Param ([string]$serverinstance)
    if ($serverinstance -match "\\") {
        $server = $serverinstance.split('\')[0]
        $instance = $serverinstance.split('\')[1]
    } else {
        $server = $serverinstance
        $instance = "MSSQLSERVER"
    $SMOconn = New-SMOconnection $serverinstance
    if ($SMOconn.connectioncontext.isopen -eq $false) {"Failed to establish a SMO connection to: $server\$instance"}
    $port = 0
    $obj = New-Object Object
    $obj | Add-Member Noteproperty Build -value $SMOconn.information.VersionString
    $obj | Add-Member Noteproperty Edition -value $SMOconn.Information.Edition
    $obj | Add-Member Noteproperty Platform -value $SMOconn.Information.Platform
    $obj | Add-Member Noteproperty Collation -value $SMOconn.Information.Collation
    $obj | Add-Member Noteproperty Productlevel -value $SMOconn.Information.Productlevel
    $obj | Add-Member Noteproperty IsClustered -value $SMOconn.Information.IsClustered
    $obj | Add-Member Noteproperty FulltextInstalled -value $SMOconn.Information.IsFullTextInstalled
    $obj | Add-Member Noteproperty LoginMode -value $SMOconn.Settings.LoginMode    
    $obj | Add-Member Noteproperty Auditlevel -value $SMOconn.Settings.Auditlevel
    $obj | Add-Member Noteproperty NumberOfLogFiles -value $SMOconn.Settings.NumberOfLogFiles
    $obj | Add-Member Noteproperty MaxServermemory -value $SMOconn.Configuration.MaxServerMemory.RunValue
    if ($SMOconn.information.VersionString.split(".")[0] -gt 8) {
        $mc = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $server
        $port = $mc.ServerInstances["$instance"].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpPort"].value
    } else {
        $DMOconn = New-Object -comobject "SQLDMO.SQLServer"
        $DMOconn.loginsecure = $true
        $DMOconn.logintimeout = 3
        if ($DMOconn.versionstring -ne $NULL) {
            $port = $DMOconn.Registry.tcpport
        } else {
            $port = -2
    if ($port -lt 1) { $port = -2 }
    $obj | Add-Member Noteproperty Port -value $port
    Remove-SMOconnection "SMOconn"

Usage: GetSQLconfig MyServer\MyInstance

The above function can easily be wrapped in a loop over several servers:

function MultiGetSQLconfig {
    param ($servers)
    Foreach ($server in $MyServers) {
        GetSQLconfig $server

$myServers = "Server1", "Server2", "Server3", "Server4"
$result = multiGetSQLconfig $myServers

The output isn't that good looking, but since the output is just another object, we can use all the normal PowerShell object cmdlets to format the output:

$result | Sort-Object Build | Format-Table -autosize

The above script has been testet against SQL2000, SQL2005 and SQL2008. But remember that in order to access SQL2008, the machine running PowerShell needs a new (SQL2008 compatible) version of SMO.

No comments: