Art of the DBA Rotating Header Image

Am I Alive? SQL Connectivity Checks with #Powershell

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.

 

10 Comments

  1. Shawn says:

    Nice one! You might add one more extension, since you are grabbing the created date for tempdb just go ahead and calculate the uptime in days or hours. That way you don’t have to sit there and remember, “What is today’s date?”.

  2. Shane says:

    Great work Mike! I have many uses for this.

    PS: Top section block needs a correction on the last line:
    From: Test-Connection -Instances ‘localhost’
    To: Test-SQLConnection -Instances ‘localhost’

    1. Mike Fal says:

      Thanks Shane! Corrected.

  3. […] Am I Alive? SQL Connectivity Checks with #Powershell – Mike Fal (Blog|Twitter) […]

  4. Ranger says:

    Mike
    hi , great script I am working thru it to gain more PS knowledge.
    Could you post the script that would make use of a txt file storing all the names of the SQL servers you want to check ?
    Is that possible ?

    Many thanks
    ranger..

    1. Mike Fal says:

      It’s absolutely possible. What you can do is use the Get-Content cmdlet to read your text file, put it in to a collection, and then use that instead of the CMS. So it would like something like $servers=Get-Content yourserverlist.txt. I’ll be honest, I’m not a big fan of that approach because it isn’t dynamic. This is why I prefer CMS (or AD, if you have your organizational units set up) to perform these kinds of checks.

  5. Dustin Jones says:

    thanks for sharing

  6. Peter Shore says:

    I am curious, why not query the SQL Server start time from sys.dm_os_sys_info instead of TempDB creation time?

    1. Mike Fal says:

      The main reason is that it works. :) That dmv is not available in some of the earlier versions of SQL Server I’ve worked with. If using sys.dm_os_sys_info works for you, no reason not to use it.

  7. Ja says:

    I have tried your connection test and Invoke-Sqlcmd2 on my CMS server, but I have noticed an issue. If the server is unreachable the connectiontimeout doesn’t appear to work. It will sit there for ~30 seconds. If It can’t connect in 3-5 seconds I just want it to fail. Am I missing something?

    The error in your connection check is:
    Test-SQLConn : Cannot connect to (servername).
    At (filename)
    1:88 char:9
    + Test-SQLConn $Server
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Test-SQ
    LConn

    The error in Invoke-Sqlcmd2 is:
    Exception calling “Open” with “0” argument(s): “A network-related or
    instance-specific error occurred while establishing a connection to SQL Server. The
    server was not found or was not accessible. Verify that the instance name is correct
    and that SQL Server is configured to allow remote connections. (provider: Named Pipes
    Provider, error: 40 – Could not open a connection to SQL Server)”

Leave a Reply to (SFTW) SQL Server Links 30/01/15 - John Sansom Cancel reply

Your email address will not be published. Required fields are marked *