I was ready to submit my first PowerShell article to SQLServerCentral when I got Steve Jones’s email reminding me that I should focus on explaining scripts to PowerShell beginners. However, beginners need PowerShell configured before they can follow along with any PowerShell article. I will be using SQLPSX in future articles here.
A configuration refresher is something that many experienced PowerShell users can benefit from as well, because even if you are set up locally you’ll still need to repeat these steps each time you use PowerShell on a new server. It’s much easier than installing a SQL Server instance, but not so automated that you can just double-click a single MSI and be up and running. Follow along with this setup today and you’ll be ready to rock and roll when you tackle our follow-up post on managing your data file free space.
There are 5 steps to establishing your minimal SQL + PowerShell setup:
- Run the Set-ExecutionPolicy Command
- Install SSMS 2008 or 2008 R2 (most of you probably have this already)
- Install SQLPSX (Recommended)
- Create a WindowsPowerShell Directory in your My Documents Folder (If you ignored my advice and did not install SQLPSX)
- Install .Net 3.5.1 and the ISE feature (Only if you’re on a Windows 2008 R2 server).
These steps assume you’re running Windows 7 or Windows 2008 R2. If that isn’t the case you are on your own for figuring out anything else your environment might need.
Run the Set-ExecutionPolicy Command
Hit Start and type the letters “ISE” in the search box. If you have a choice, opt for the non-(x86) PowerShell ISE. Right-Click the program icon and choose to Run As Administrator.
Next, follow these steps:
- Read and accept all the warning messages.
- Type the command Set-ExecutionPolicy RemoteSigned hit F5.
- Choose Yes when prompted.
- Close the ISE.
Install SSMS 2008 or 2008 R2
You must have SSMS 2008 or SSMS 2008 R2 installed. If not all bets are off. There is an urban myth that you only need to install one portion of this feature pack. That has never worked for me. If you want to try it and have success with it please let me know what you do.
Install SQLPSX
Install SQLPSX. SQLPSX is an open source utility with around 150 functions for working with PowerShell and SQLServer. Over 1,000 Database Professionals keep up with the SQLPSX project and new features are added frequently. It isn’t a hard and fast requirement, but I consider it the most important thing you could possibly do to have greater success with your PowerShell endeavors. (Other important things: Attending one of my sessions and investing time to read and practice tips you find in articles like these.)
Create a WindowsPowerShell Directory
Life is much easier if you have a directory in your My Documents folder named WindowsPowerShell. Ok ‘technically’ you don’t have to but… a) At some point I’m going to tell you to load something into your profile at which point you’ll need this directory and I will certainly neglect to point that out. b) SQLPSX creates this directory for you because its use is just that common. So if you installed SQLPSX just check off this step as done and move on to…
Install .Net 3.5.1 and the ISE feature
This only applies to users running on Windows Server 2008 R2. Since you need .Net 3.5.1 installed for SSMS 2008+ we’ll just assume that it’s there and tell you to go to Add Features and scroll all the way to the bottom until you’ve seen the longest name in the list. Windows PowerShell 2.0 Integrated Scripting Environment. Install that. If .Net 3.5.1 isn’t installed scroll all the way to the top of the list and install it first.
Now you are ready to begin using PowerShell. And now that you are setup you can do all of your work in the ISE. If you run into a problem in that little blue window and ask me to help, my first question will be “What happens when you run that same thing in the ISE”.
Finally some code!
Now that you have all that in place I’ll leave you with some pieces of code. Every once in a while I’ve seen SQL Agent doesn’t come back to life after a patch or a Service Pack. In the examples below the code blocks will do the following:
1. Just tell you all of the services on your local machine that start with the name “SQL”.
2. Like the first piece of code but as you can see, you can modify it to run against multiple machines.
3. Tell you the services that starts with the name “SQL” and aren’t running.
4. The fourth piece of code will tell you if a service starts with the name “SQL” and is marked for “Automatic Startup” that isn’t running.
5. In the final block of code it will actually start those services for you.
This code is also available in the Resources section below for download.
$BunchaServers = 'localhost' Get-WmiObject win32_service -ComputerName $BunchaServers | Where {$_.DisplayName -like "SQL*"}| Select SystemName, Name,StartName, Started, StartMode | Format-Table -AutoSize $BunchaServers = "localhost", "127.0.0.1" Get-WmiObject win32_service -ComputerName $BunchaServers | Where {$_.DisplayName -like "SQL*"} | Select SystemName, Name,StartName, Started, StartMode | Format-Table -AutoSize $BunchaServers = "localhost" Get-WmiObject win32_service -ComputerName $BunchaServers | Where {$_.DisplayName -like "SQL*"} | Where {$_.Started -ne "True"} | Select SystemName, Name,StartName, Started, StartMode #| Measure-Object $BunchaServers = "localhost", "127.0.0.1" Get-WmiObject win32_service -ComputerName $BunchaServers | Where {$_.DisplayName -like "SQL*"} | Where {$_.StartMode -eq "Auto" -and $_.Started -ne "True"} | Select SystemName, Name,StartName, Started, StartMode | Format-Table -AutoSize $BunchaServers = "localhost", "127.0.0.1" Get-WmiObject win32_service -ComputerName $BunchaServers | Where {$_.DisplayName -like "SQL*"} | Where {$_.StartMode -eq "Auto" -and $_.Started -ne "True"} | Select SystemName, Name,StartName, Started, StartMode | Start-Service
And with that you should now be ready to run some SQL + PowerShell!
