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:

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:

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

Leave a Reply

Your email address will not be published. Required fields are marked *