Welcome to PowerShell Week at SQL University.  For regulars at SQLvariant, SQL University is something Jorge Segarra organized to get industry experts together to create learning courses around SQL Server.  These learning courses are in one week segments that focus on a specific area. 

When I began blogging about PowerShell there were some complications based on your operating system. There were so many people blogging about these issues that I didn’t address it initially.  Since this is an introductory course I decided to go ahead and spin up a brand new Windows 7 VM so that I can walk you through these small but vital details.  If you don’t have Windows 7 this would be a great time to upgrade.  If you can’t upgrade then download PowerShell for you operating system from this link.  Now, onto getting started:

Disclaimer:

PowerShell opens in a protected state as a security measure.  To enjoy the full power of PowerShell (and run scripts you downloaded off of the internet) you need to run without these safeguards. In today’s lesson we are simply launching PowerShell and priming it for the scripts and work we will interact with in the next several lessons.

To get started with PowerShell just click the Start button and type in ISE to locate the Integrated Script Editor in your program listing.  Right click and launch PowerShell in Run as Adminsitrator mode:

Enter Get-ExecutionPolicy as a command. If you haven’t used PowerShell yet you will most likely see that the Execution Policy is set to “Restricted”.  You’ll need to run the command Set-ExecutionPolicy RemoteSigned to be able to run the scripts and follow along with the lesson.  You’ll also get a popup that you’ll have to say yes to. 

Your output should look very non-informative like this:

Now that we have that set let’s go to SQLPSX.CodePlex.com and and download the SQLPSX project.  It’s currently in version 2.2.1 and consists of 9 modules containing 133 advanced functions, only 2 cmdlets and 7 scripts.  It’s for working with ADO.NET, SMO, SQL Agent, RMO, SSIS, SQL script files and using the PowerShell ISE as a SQL query tool:

NOTE:  Update at bottom with workaround:

During the installation we will run it without modifying the profile (just yet).

Once you’ve installed it successfully you should now have a WindowsPowerShell directory under your My Documents if you didn’t have one already (which is really important):

After it’s installed we should be able to open up the ISE again and run this command :

Get-Module -ListAvailable

And finally we’re going to get to the step where we load something up.  Run import-module SQLServer to load up the main SQL Server module.  After you run that command it won’t give you any output telling you that it successfully loaded so you can run this command write-host -foregroundcolor DarkGreen “Modules Loaded: $(get-module)” to have it tell you what Modules it currently has loaded.

Alright that pretty much wraps it up for today.  I know we didn’t do a lot with PowerShell and SQL Server yet but this gets some important ground work out of the way so that hopefully everyone is able to script along for the rest of the week.  (If you’re dying to read ahead have a look at this post: Basic Querying From PowerShell)

If you run into any issues please leave a comment and I’ll be glad to lend any help that I can.  See all you back here tomorrow for the next post in this series Post 1.

UPDATE:

There seems to be an issue with loading the SQLServer Module as I had described.  I have found this to be a work-around:  Go to \\~\My Documents\WindowsPowerShell\Modules\SQLIse\Modules\ and copy everything except for the SQLISE folder, then go to the \\~\My Documents\WindowsPowerShell\Modules\ folder and paste all of those items.  At this point you should now be able to run the command and get it to load up properly. 

I will post more when I found out other alternatives.