I’ve been playing around for a while now with a function to test if a instance of SQL is “alive” or not, below is the PowerShell function I created to test 1 or more servers at a time,

FUNCTION Test-SQLConnection {

<#
.SYNOPSIS
Test SMO connection to SQL Server to see if its open (works for SQL Server 2005+.)
.DESCRIPTION
Test connections to SQL Server instances either listed in variable or as input to Name
.PARAMETER Servers
The name of the instance(s) you wish to check connections to
.PARAMETER OutputTo
Optional Parameter to choice the output type of the function, default is Object, The other option is String
.EXAMPLE
Test-SQLConnection -Servers DEV-SQL
.EXAMPLE
Test-SQLConnection -Servers DEV-SQL, DEV-SQL2
.NOTES
Created 07/09/15 Stephen Bennett 
.LINK
https://sqlnotesfromtheunderground.wordpress.com/
#>


 [CmdletBinding()]
 Param (
 # List of server(s) to run against
 [Parameter(Mandatory=$true)]
 [string[]]
 $Servers
 ,
 # Define output to either String or Object (Object is default)
 [ValidateSet("String","Object")]
 [string]
 $OutputTo
 
 )
 
BEGIN {}
 
PROCESS {
 
 # Loop though all the servers
 foreach ($s in $servers){
 # Build connection String to test connection based on server name and Windows Authentication
 $connectionString = "Data Source=$s;Integrated Security=true;Initial Catalog=master;Connect Timeout=3;"
 TRY {
 # Try and connect to server
 $sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString
 $sqlConn.Open()
 # Test Parameter -OutputTo If its String run
 IF ($OutputTo -eq "String")
 {
 
 # If connection was made to the server
 IF ($sqlConn.State -eq 'Open')
 {
 # write to output and verbose
 $sqlConn.Close();
 Write-Output $s
 Write-Verbose "Available Server: $s"
 }
 # Test Parameter -OutputTo if its Object or not entered run
 } ELSE {
 # If connection was made to the server
 IF ($sqlConn.State -eq 'Open')
 {
 # write to output and verbose
 $sqlConn.Close();
 $obj = New-Object –typename PSObject
 $obj | Add-Member –membertype NoteProperty –name ComputerName –value ($s)
 Write-Output $obj
 Write-Verbose "Available Server: $s"
 }
 }
 
 }
 
 CATCH {
 
 # If connection failed write to verbose
 Write-verbose "Not available Server: $s"
 }
 }
} 
 
}
Advertisements