It is time for another edition of Powershell Tool Time, an ongoing series about writing Powershell tools to assist with managing SQL Server. The past two articles have covered some basics about Powershell functions and how we can make them more useful through our use of parameters. Now how do we go about making these functions available to us for reuse? This article will cover how we can use the Powershell environment to create a simple toolbox where our functions will reside and load every time we need it.
Dot Marks the Source
Up to this point, if we wanted to load our functions into our session we’d have to run the function as a block of Powershell code. Running the code places the function into memory, letting us call the function as long as the session remains open. For example, let’s take a function I’ve blogged about, Test-SQLConnection:
function Test-SQLConnection{
param([parameter(mandatory=$true)][string[]] $Instances)
$return = @()
foreach($InstanceName in $Instances){
$row = New-Object –TypeName PSObject –Prop @{'InstanceName'=$InstanceName;'StartupTime'=$null}
try{
$check=Invoke-Sqlcmd -ServerInstance $InstanceName -Database TempDB -Query "SELECT @@SERVERNAME as Name,Create_Date FROM sys.databases WHERE name = 'TempDB'" -ErrorAction Stop -ConnectionTimeout 3
$row.InstanceName = $check.Name
$row.StartupTime = $check.Create_Date
}
catch{
#do nothing on the catch
}
finally{
$return += $row
}
}
return $return
}
If I wanted to reuse this, I’d have to run this entire block of code in my Powershell session, but after that I can call the function by name:
This is cumbersome, though. We are not saving ourselves a lot of time if we had to go through this process every time we wanted to use our functions. Fortunately, we can get help from some functionality that has been around for a while in scripting languages: dot sourcing.
Dot sourcing is the act of executing a Powershell script to load any functions contained within that script into our session. The reason it is a called ‘dot sourcing’ is because we run a script by using the period character (the ‘dot’) before a script path:
. .\Test-SqlConnection.ps1
This is not really different from running the block of code, except that now our code is contained within a Powershell script and we just run that file to save ourselves a heap of typing, copying, and pasting.
Consolidating Our Tools
Now we just need to build our tools script. I have combined the three tool functions that have been part of this series into a single file. I will then take this script and place it locally into C:\Scripts for easy reference. When I next open up a Powershell session, I’ll load these functions by dot sourcing the script. After the script runs, we can see what functions are available by using the Function Provider within Powershell:
Pretty nice, right? By saving our tools into their own file, we can give ourselves easy access to our tool set with one simple call to run the script file. Once called, we have immediate access to the functions we’ve written. The additional benefit is if we write a new function, we just have to add it to our tool file, dot source the file again, and it will always be there.
Keeping It Simple
Code reuse within Powershell is a deep topic, with many levels for writing functions and grouping them together. However, the great thing about Powershell is that there are simple methods that can be used effectively as well. Over the course of this series, I will show how we can be more elegant and build out richer, more complex tool sets, but we need our foundation to start with.
There are other gaps to cover first, though. Chief among them is every data professional’s favorite task: documentation. One of Powershell’s strengths is its rich help system, which provides detailed information about all the core cmdlets. This help system is supported by a foundation that lets scripters easily write their own help information. For our next article, we will dive into these components and start writing help information for our tools.
Article Resources
Further resources that might help you.
GitHub Repository: https://github.com/MikeFal/PowershellToolbox-SSC
Test-SqlConnection: https://github.com/MikeFal/PowershellToolbox-SSC/blob/master/Test-SqlConnection.ps1
SSC-SQLToolBox: https://github.com/MikeFal/PowershellToolbox-SSC/blob/master/SSC-SQLToolBox.ps1
