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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
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
One thought on “Test Connection to a SQL Instance”