Check for SQL Server Connectivity

My company is revamping its domain structure and tightening up network security. I am one of the guinea pigs and therefore have been put into the new domain and OU groups. Obviously, we are experiencing some teething problems. As a result I can't access things, namely SQL Server instances that I used to be able to connect to from my work station.

I needed to provide the network team a list of the instances I could no longer access and I certainly wasn't going to try to connect to each one manually. We have a very large estate and it would have taken me ages.

As is the case more and more lately, I turned to PowerShell. The script below does the following:

1. Connects to our asset management database, Landesk, to get a list of the VM names that host SQL Server instances. 
2. Loops through and attempts to execute a simple query on each
3. Captures when there is a "network-related or instance specific" error
• Alternative 1: Print only the server name that cannot be accessed
• Alternative 2: Also print the successful server names

My life is made easy in that very few SQL Servers in the estate use named instances. But I am also working under the assumption that if I can't access the default instance I won't be able to access any named instances on the same host VM.

An important part of this PowerShell script is to set the ErrorActionPreference global parameter to SilentlyContinue. That way you will get a clean list of server names and the script won't abort on an error.

I've added a filter to the If statement, so that it will only report when a "network specific" error is returned. This could easily be changed to any other error string you need to catch.

I have also created a parameter to capture the results of the query. I do not want to see the results of successful query executions. It would only make the server list difficult to read.

Finally, the only difference to the two variations I described above is the Else statement. If all you want is a list of inaccessible servers, remove the Else statement.

cls
$ErrorActionPreference = "SilentlyContinue"
<############################################################
For the list of servers either:
Use Landesk or other CMDB by modifying the where clause in 
the below query
or
Modify the list of the $Servers parameter. Enter either 1 or
more server names

Comment out the one you don't need
############################################################>

<#### LANDESK QUERY ####>
#$Servers = (Invoke-Sqlcmd -ServerInstance LanDeskSQLServer01 -Database Landesk -Query "select DeviceName DeviceName from dbo.Computer where DeviceName like '%sql%'" -ErrorVariable ProcessError).DeviceName 
<#### LANDESK QUERY ####>

<#### MANUAL SERVER LIST ####>
[string[]]$Servers = ("SQLServer01","SQLServer02")
<#### MANUAL SERVER LIST ####>
foreach ($Server in $Servers)
{ 
    $TestConnectivity = Invoke-Sqlcmd -ServerInstance $Server -Database master -Query "select @@version as version" -ErrorVariable ProcessError
    If ($ProcessError -like "*A network-related*") 
    {
         Write-Warning -Message $Server
    }
    Else
    {
        Write-Verbose $Server -Verbose
    }        
}

$ErrorActionPreference = "Stop"