January 27th, 2015:

Inevitably, there will be the call from a developer or admin saying “I can’t connect to the MORDOR SQL Server!  Everything is terrible!  All is darkness and shadows!  HELP!”.  After explaining that one does not simply connect to MORDOR, I fire up SQL Server Management Studio and attempt to log in.  It’s not particularly onerous, but it takes time and can’t be particularly automated for more proactive connectivity checks.

The connectivity check, at the heart of it, isn’t really that complex.  Usually it’s just a case of connecting and running a simple select statement.  I’ve done this proactively in the past using a central management server and linked servers for all the instance in my environment.  While this works, it’s difficult to manage, automate, and isn’t very mobile.  Me being me, I turn to Powershell for a better way.

Test-SQLConnection

The function here is pretty simple: Loop through a collection of instance names and try to query the server name and TempDB creation time.  If I get a result set, then my connection test is successful.  If it errors out, then no connection.  Essentially, the test is whether or not my return set has a StartupTime (TempDB creation date) or not.  So now I can pass single instance or collection and test them all with the one function.

function Test-SQLConnection{
    param([parameter(mandatory=$true)][string[]] $Instances)

    $return = @()
    foreach($InstanceName in $Instances){
        $row = New-Object –TypeName PSObject –Prop @{'InstanceName'=$InstanceName;'StartupTime'=$null}
        try{
            $check=Invoke-Sqlcmd -ServerInstance $InstanceName -Database TempDB -Query "SELECT @@SERVERNAME as Name,Create_Date FROM sys.databases WHERE name = 'TempDB'" -ErrorAction Stop -ConnectionTimeout 3
            $row.InstanceName = $check.Name
            $row.StartupTime = $check.Create_Date
        }
        catch{
            #do nothing on the catch
        }
        finally{
            $return += $row
        }
    }
    return $return
}

Test-SQLConnection -Instances 'localhost'

Why do I return the TempDB creation date?  Well, a boolean value is easy enough to return, but I figure I could use more info to work with.  After all, if data comes back, then I know the connectivity is working.  So let’s do a little more.  By returning the TempDB creation date, I can find out if my instance restarted recently (or if it’s been up for a really long time).  This gives me a little more information to work with.

Extending the functionality

While calling this function adhoc is easy enough, the reason to put it in to Powershell is not so we can just have another way to test our connections.  Automation is the key here.  So what if we took this and combined it with another technique, getting a server collection from CMS?

$CMS='SHION'
$servers=@((dir "SQLSERVER:\SQLRegistration\Central Management Server Group\$CMS").Name)

$servers+=$cms
Test-SQLConnection -Instances $servers

Using this, we can quickly test the connection of all the servers in our CMS.  We can also check to see if any have started up recently, perhaps warning us of a restart of our SQL Services.  From here, it’s not a tremendous leap to load it into a database table for regular health checks.

Another tool for the toolbox

What’s great about these functions is that once I write it, I have a new tool I can use.  Initially I wrote this function so I can verify which servers in my active directory are actually SQL Servers (assuming the default port, of course).  As we can see here, though, the function can be used for a variety of purposes.