PowerShell and SQL - SQLPSX

2019 update: SQLPSX was great seven years ago but nobody is actively working on it. If you are interested in using PowerShell with SQL, check out http://dbatools.io this is an active project on GitHub that just released version 1.0

This is part two in my PowerShell and SQL series. If you missed part one, you can use the PowerShell link under labels on the right to get to it. Last week I went over PowerShell Basics, what it is and how to use it. I included some examples to show you how you can use PowerShell to get SQL related event logs and store them in a file. This week I am going to introduce the SQL Server PowerShell Extensions.

You can download the modules for SQLPSX from the codeplex website: http://sqlpsx.codeplex.com/

SQLPSX is a library of modules that can be used to directly talk to SQL server. You can get server and database information, read from and write to a SQL database and even perform most tasks a DBA performs on a regular basis. Now you can do this without using SQLPSX by creating your own connections to a SQL server but I am going to show you why you shouldn't bother reinventing the wheel.

Installation

Once you download the file and run it, the SQLPSX modules will be added to your modules sub-directory of your Windows PowerShell folder (usually located under My Documents). In order to use the modules you have to import them into PowerShell.

NOTE: loaded modules are session based, this means that when you close the PowerShell window and get back in, the modules will not be loaded anymore. You can have them auto-load when PowerShell opens by adding an entry to your PowerShell profile. If you are interested here is an article at How-to-Geek: http://www.howtogeek.com/50236/customizing-your-powershell-profile/

You load the module into powershell using the import-module cmdlet, but wait, do you know what the names of the modules are? After you have installed SQLPSX open up powershell and type this command:

Get-Module -ListAvailable

You will get a result that looks like this:

There are thirteen modules in the SQLPSX installation, you can see the listing on the SQLPSX website under documentation.

Now lets import one of the modules into our PowerShell Session. To do this we use the Import-Module cmdlet followed by the name of the module we want to import.

Import-Module SQLMaint

Next let's see what functions are available in this module. We can do this using the Get-Command cmdlet and passing it the -Module parameter with the name of the module we just imported.

Get-Command -Module SQLMaint

You should now get a result that looks like this:

The best thing to do next is to pick a function that you like and see what it does and what parameters it takes. Let's look at the Get-SqlData function using our buddy the Get-Help cmdlet

Get-Help Get-SqlData

This returns the name of the function, a synopsis, syntax and a description. Notice the last section, Remarks:

Look at that! You can get a more detailed description and even examples. This is pretty cool, right!

Play around by importing the other modules and checking out the functions. I like to use the SQLMaint and  Agent Modules but check out the other ones to see what is available. If you find something cool, post it in the comments!

Don't Reinvent The Wheel

Like I said before you can connect to SQL just fine without using SQLPSX and do everything you can with it but you will end up writing more code. Let's take a look at two examples. The first example will connect to my server and return a result set from a query:

Now lets do the same thing using SQLPSX:

Fourteen lines of code vs five lines of code. Pretty easy decision in my head. I have enough to do during the day without having to remember all that code just to run a query. If you used method one you would probably end up writing your own module and importing it in all your scripts but why bother when the resource is already available. I want to point out that SQLSPX isn't doing anything different, the functions that they give you use the same code that you can hand write, they have just already done all the leg work for you. If you want to see what is going on "behind the scenes" you can open up the SQLServer.psm1 file in your PowerShell modules directory in the SQLServer folder.

Links and References

Download SQLPSX from Codeplex
Learn More About SMO and PowerShell

Sum Up

That's it for this week. This lesson is important because I am going to use the SQLPSX modules in all of my later examples. Download it and play around and check in next week and we will get started on putting this all together to help simplify your life as a DBA

Hasta Luego,
RutherfordTX