Friday, 4 August 2017

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.

1 comment:

meena resma said...

It was so nice article. I was really satisfied by seeing this article. SQL server dba Online Training Bangalore