Made some “improvements” to Test-SQLConnection, instead of trying to connect with the Windows auth, (which can fail if your login doesn’t have access to the box, But the box is still up!) I changed it to a SQL login and made sure it would fail by putting in a silly username and password. this way no matter what authentication mode you use or if you have access or not, It will show you if a SQL instance is online.

I also got bored of not knowing how far into my list of servers the test had got, so I added Write-Process loading bar to it.

<#

.FUNCTION

Test-SQLConnection

.SYNOPSIS

Test SMO connection to SQL Server to see if its open (works for SQL Server 2005+.)
Needs to run on PowerShell 3 +

.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-Connection -Servers DEV-SQL

.EXAMPLE

Test-Connection -Servers DEV-SQL, DEV-SQL2

.EXAMPLE

$importservers = get-content C:\Temp\test.txt
Test-SQLConnection -Servers $importservers

.Release

1.0 2014/02/11 Stephen First Release
2.0 2014/02/14 Stephen changed login to fail (looking for login failed) and connection logic in CATCH instead of TRY. Added Write-Process

#>

FUNCTION Test-SQLConnection {
   [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
	{
    	# Set up info for Write-Progress
		$progressCount = $servers.count
		$i = 1
		# Loop though all the servers
		foreach ($s in $servers)
		{
	        # Set up info for Write-Progress
	        $percent = ($i/$progressCount)*100
	        Write-Progress -Activity 'Testing SQL Connection' -Status "$percent Complete" -PercentComplete $percent ;
	        ## iterate i +1 for each box checked
	        $i ++

	        # Build connection String to test connection based on server name and Windows Authentication
	        $connectionString = "Data Source=$s;Integrated Security=false;;User ID=MadeUpAccount;Password=NeverGonnaWork 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
	        }

	        CATCH [Exception]
	        {
	            IF ($_.Exception.Message -eq "Login failed for user 'MadeUpAccount'.")
	            {
	                IF ($OutputTo -eq "String")
	                {
	                    Write-Output $s
	                    Write-Verbose "Available Server: $s"
	                }
	                ELSE
	                {
	                	# write to output and verbose
	                    $obj = New-Object –typename PSObject
	                    $obj | Add-Member –membertype NoteProperty –name ComputerName –value ($s)
	                    Write-Output $obj
	                    Write-Verbose "Available Server: $s"
	                }
	            }
	            ELSE
	            {
	    			Write-Verbose "No Connection Available to Server : $s"
	            }
	        }
		}
	}
}

Advertisements