I have recently done some work with PowerShell and SQL. That must be a common requirement, Right? OK, let’s talk about that…

During my time working with PowerShell and SQL I came across a few gotchas so I am going to share some with you during this post while discussing database connection testing.

During this work I encountered some failures which appear to be a result of the Database connectivity becoming unavailable. Now although I was capturing this error it was frustrating, so I wrote a function to test connectivity before the connection and query was attempted. That way I could keep trying repeatedly for however many times I wished till the connection succeeded or I chose to end the operation.

 

So how do we test the connection?

The first thing we need is a SQL connection string. So to make this more generic I am going to build the string using variables.

The connection string is built from:

  • Datasource
  • Database Name
  • Security Type
  • UserID
  • Password

So I create a variable for each of these and populated them with the required data

$DataSource = “IP_Address,Port”
$DBName = “Database_Name”
$ISecurity = “False”
$ID = “DB connection account”
$Password = “Password”

It should be fairly obvious what most of these are but ‘ISecurity’ probably not so…

This is the variable I am using for specifying the integrated security option. Integrated security enables you to use the account under which you are running the code, rather than specifying the credentials. If that were the case, UserID and Password would not be required.

From this information we can now build the connection string and store in the $ConnectionString variable.

$ConnectionString = “Data Source=$DataSource;Initial Catalog=$DBName;Integrated Security=$ISecurity;User ID=$ID;Password=$Password”

And now the code required to test the connection:

    # connect to the specified database then close the connection.
    # If an error occurs, output a failure message.

       # Create a SQL connection object
        $dbConnection = New-Object System.Data.SqlClient.SqlConnection

        # Bind the Connection string to the connection
        $dbConnection.ConnectionString = $ConnectionString              

        try {

              # Try to make the DB Connection and record the success if no error
               $dbConnection.Open()

              # Store the result in the Results Variable
               $Result = “Connection was Successful”           

                     # Close the connection
                      $dbConnection.Close();

        } # Close the try block

        # Catch and output a notification if the connection fails.
         catch {

                     # Store the result in the Results Variable
                       $Result = “Connection Failed”

                } # Close the Catch block       

     Finally {

            # close the database connection
              $dbConnection.Close()
              $result

        } # Close the finally block

Gotcha

OK, straight forward right? Yeah pretty much many similar examples out there in the wild

However, this highlighted that there was an intermittent slow connection that would cause the connection to time out.

ConnectionTimeout.

Turns out you can also add a connection timeout to the connection string.
*Note – I think the default timeout is 30 seconds.

*There is actually another timeout setting which I came across (and needed to use to deal with long running queries). I may cover that in another post as that’s part of the code to run the SQL query

 

$DataSource = “IP_Address,Port”
$DBName = “Database_Name”
$ISecurity = “False”
$ID = “DB connection account”
$Password = “Password”
$ConnectionTimeout = 60

$ConnectionString = “Data Source=$DataSource;Initial Catalog=$DBName;Integrated Security=$ISecurity;Connection Timeout=$ConnectionTimeout;User ID=$ID;Password=$Password”

I have added a new variable for this setting and used that in the connection string. Setting this to 60 would set the timeout to 60 seconds. You can actually set this to 0 (‘NO TIMEOUT’ ) but that is undesirable in most cases.

Once tested successfully it can be wrapped into a function.
So that’s what I did…

Right, that was fun…