Friday, August 22, 2008

Call for inspiration: PowerShell + SQL

I was wondering if anyone out there has any good suggestions for SQL related PowerShell automation that just needs doing?

Any suggestions are welcome - if I can find the time, I might even try to create a solution.

This blog is now listed at

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.

Saturday, August 16, 2008

SMO example

As mentioned before, I spend quite some time creating scripts in PowerShell for managing SQL server. Here is an example of a couple of SMO functions I have found very useful.

In the future, I'll try to post some examples that uses the functions below

We'll start by loading the correct assembly:

[void][reflection.assembly]::Load("Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

Or the more general "LoadWithPartialName" - that also works if you have another SMO version:


Then create a function to establish a SMO connection to an SQL instance. The function below creates a smo connection, with a user defined timeout, and applicationname. Remember that it is always recommended to give meaningful names to any connectionto SQL - it can make troubleshooting much easier.

function New-SMOconnection {
    Param ($server, $applicationName= "PowerShell SMO", [int]$commandTimeout = 5)
    Write-Debug "Function: New-SMOconnection $server $connectionname $commandtimeout"
    if (test-path variable:\conn) {
    } else {
        $conn = new-object('Microsoft.SqlServer.Management.Smo.Server') $server
    $conn.connectioncontext.applicationName = $applicationName
    $conn.connectioncontext.connectTimeout = $commandTimeout

And a small function to remove a SMO connection once you are done with it:

function Remove-SMOconnection {
    Param ($connection)
    Write-Debug "Function: Remove-SMOconnection $connection"
    Remove-Variable -Name $connection -Scope 1

The above functions can be used in the following manner:

1.) Establish a SMO connection, remembering to check for errors:

$SMOconn = New-SMOconnection "MyTestServer\SQLinstance0001" "JBs Secret PowerShell App"
if ($SMOconn.connectioncontext.isopen -eq $false) {# handle error}

You now a an open SMO connection to your SQL server. If you would like to retrieve information about what version/build you SQL server is, you could use the following:


Or retrieve a list of database names:

Foreach ($database in $SMOconn.databases) { $ }

Remember that "Get-Member is you friend!" - you can use Get-Member (or its alias, "gm") to explore the possibilities of SMO:

$SMOconn | gm

Once you are done, remember to close the SMO connection:

Remove-SMOconnection SMOconn

Friday, August 15, 2008


The current Sort-Object cmdlet in PowerShell lacks "random" functionality.
Here is a small function that allows youto sort randomly:

function sort-random {
    process {
        [array]$x = $x + $_
    end {
        $x | sort-object {(new-object Random).next()}


1..100 | Sort-Random
- or -
Get-Process | Sort-Random