Test Connection to a SQL Instance

This is one of those somewhat simple functions that I end up using quite a bit, similarly to the Test-ADUser one from my last post. I find it particularly useful for parameter validation in my other SQL-related functions. It is nice to be able to offload the connectivity tests right into the parameter declaration in the function without having to clutter up the main function.

I have been slowly moving my collection of scripts into more modular functions to make my code more re-usable, and so far it seems to be working out well. It is much easier to update one function in a module, then to try and track down every location I used that functionality and update it as well.

As part of this effort, I have also come up with a function that loads the SQLPS module if it is available, and throws an error and halts execution if it is not. That function looks like this:

FUNCTION Import-SQLPS {
<#
.SYNOPSIS 
    Imports the SQLPS module with error checking
.DESCRIPTION
	Imports the SQLPS module if it is not already loaded, with a basic try-catch-throw to avoid executing the rest of a script as well as avoiding changing the path to SQLSERVER:\
.EXAMPLE
    PS C:\> Import-SQLPS
.NOTES
    Author      : Ryan DeVries
    Last Updated: 2015/06/18
    Version     : 1
.INPUTS
    [string]
#>
    [CmdletBinding()]
    Param()
 
    begin {
        Write-Verbose "Detected parameter set $($PSCmdlet.ParameterSetName)"
        $scriptstring = "Starting $($MyInvocation.MyCommand)"
        Write-Verbose $scriptstring
    }
 
    process {
        if (!(Get-Module -Name sqlps)){ 
            try { 
                Write-Verbose "Trying to import SQLPS module"
                Push-Location
                Import-Module -Name sqlps -DisableNameChecking -ErrorAction Stop
                Pop-Location 
            } 
            catch { throw $_.Exception.GetBaseException().Message } 
        }
        else { Write-Verbose "SQLPS module already loaded" }
    }
    
    end { Write-Verbose "Ending $($MyInvocation.Mycommand)" }
}

Pretty straight forward, just checks if the module is already loaded, then tries to load it if not.  It also pushes and pops your current working directory so that you don’t get your prompt changed annoyingly to PS SQLSERVER:\.  If it fails to load the module, it throws a terminating error with the reason for the failure.

Now for the Test-SQLConnection function:

FUNCTION Test-SqlConnection {
<#
.SYNOPSIS 
    Test connection to SQL Instance
.DESCRIPTION
	Test connection to SQL Instance
.PARAMETER  Instance
	The name of the instance you wish to check connections to
.EXAMPLE
    PS C:\> Test-SQLConnection -Instance DEV-MSSQL
.NOTES
    Author      : Ryan DeVries
    Last Updated: 2015/06/01
    Version     : 1
.INPUTS
    [string]
.OUTPUTS
    [boolean]
#>
    [CmdletBinding()]
    Param(
	[Parameter(Position=0,Mandatory,ValueFromPipeline,ValueFromPipelineByPropertyName,HelpMessage="The name of the instance")]
        [ValidateNotNullorEmpty()]
        [string]$Instance
    )
 
    begin {
        Import-SQLPS
        Write-Verbose "Detected parameter set $($PSCmdlet.ParameterSetName)"
        $scriptstring = "Starting $($MyInvocation.MyCommand)"
        foreach ($param in $PSBoundParameters.GetEnumerator()){ $scriptstring += " -$($param.key) $($param.value)"}
        Write-Verbose $scriptstring
    }
 
    process {
        $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $instance
        $srv.ConnectionContext.ConnectTimeout = 5
        try {
            Write-Verbose "Trying to connect to $instance"
            # Try and connect to server
            $srv.ConnectionContext.Connect()
            Write-Verbose "Connection successful! Disconnecting from $instance"
            $srv.ConnectionContext.Disconnect()
            return $true
        }
        catch { 
            Write-Verbose "Could not connect to $instance"
            return $false 
        }
    }
    
    end { Write-Verbose "Ending $($MyInvocation.Mycommand)" }
}

Also pretty straight forward.  Takes an instance name as the parameter, then tries to connect.  If it fails, the error is caught and the function returns false, if it is successful, the function disconnects the connection and returns true.  I based the testing part on one of Chrissy LeMaire’s SMO recipes here.

Typically, I use this function in parameter validation, like so: [ValidateScript({Test-SqlConnection -Instance $_})]

I’ve started putting all my SQL functions into a module, which you can find here: GitHub