Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Day Zero with PowerShell

By Aaron Nelson, (first published: 2011/05/10)

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.


Start Menu


Next, follow these steps:

  1. Read and accept all the warning messages.
  2. Type the command Set-ExecutionPolicy RemoteSigned hit F5.
  3. Choose Yes when prompted.
  4. 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.

<# 1. Just SQL Service Accounts on your local machine #>            
$BunchaServers = 'localhost'            
Get-WmiObject win32_service -ComputerName $BunchaServers |            
Where {$_.DisplayName -like "SQL*"}|            
Select SystemName, Name,StartName, Started, StartMode |            
Format-Table -AutoSize            
            
            
<# 2. Grab just SQL Service Accounts on multiple machines.
    Make sure to swap out "127.0.0.1" with the name
    of another machine. #>            
$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            
            
            
<# 3. SQL Service Accounts that aren't running.
    Remove the "#" in front of "| Measure-Object"
    to get a count instead of a list. #>            
$BunchaServers = "localhost"            
Get-WmiObject win32_service -ComputerName $BunchaServers |            
Where {$_.DisplayName -like "SQL*"} |            
Where {$_.Started -ne "True"} |            
Select SystemName, Name,StartName, Started, StartMode #| Measure-Object            
            
            
<# 4. SQL Service Accounts that aren't running
    and should be! 
    Make sure to swap out "127.0.0.1" with the name
    of another machine.#>            
$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            
            
            
<# 5. SQL Service Accounts that aren't running
    and should be! 
    Make sure to swap out "127.0.0.1" with the name
    of another machine.#>            
$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!

Resources:

Get_ServiceAccountsInfo.ps1
Total article views: 15464 | Views in the last 30 days: 10
 
Related Articles
BLOG

SQLPSX 2.3 Release

Just in time for PASS Summit 2010, the CodePlex project SQL Server PowerShell Extensions (SQLPSX) ha...

ARTICLE

SQL Server PowerShell Extensions (SQLPSX) Part 2

In part two of his series on Codeplex extensions to Powershell for SQL Server, Chad Miller talks mor...

ARTICLE

SQL Server PowerShell Extensions (SQLPSX) Part 1

Powershell is the new management interface for scripting and working with SQL Server that is integra...

BLOG

Building A PowerShell Module Installer

As part of the 2.3 build of SQLPSX I built an MSI based installer to package all 10 SQLPSX modules.....

BLOG

Powershell default start directory

This post discusses some methods in configuring the Powershell  default start directory  By default...

Tags
powershell    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones