In this first post in my PowerShell series I am going to go over some PowerShell basics and in the next posts I will get into how to use it with SQL Server.
What is PowerShell?
PowerShell is a task automation framework. This means it is a way to perform tasks on a server (add a login, add a database, etc) and more importantly it is a way to automate them. PowerShell is both a command line and a scripting language. You can use the PowerShell CLI to perform tasks one at a time or you can write a script that will perform multiple tasks sequentially. You can even use Task Scheduler or and Agent job to kick off a PowerShell Script at a set time. Pretty Cool, Right?Microsoft has learned from it's past mistakes (vbscript) so PowerShell is also secure. It uses a concept called Execution Policy to determine what scripts can run. At the strictest setting PowerShell will not run any scripts, only commands entered at the CLI. The next level down requires that all scripts be digitally signed either using a third-party certificate or a self-signed certificate. Here are the four Execution Policies
- Restricted - No scripts can run. Only commands from the CLI
- All Signed - All scripts must be digitally signed
- RemoteSigned - Local unsigned scripts can run but any remote scripts must be digitally signed
- Unrestricted - All scripts can be run. No digital signatures required
Working with PowerShell
PowerShell is installed by default on Windows 7 and later but it isn't in your Start Menu. To find it, use the 'Search programs and files' bar in the Start Menu, just search for powershell.exe for the CLI and powershell_ise.exe for the integrated scripting environment.
Cmdlets
The basic commands used when working in PowerShell are called cmdlets. Cmdlets take the form [verb]-[noun]. Examples:- Get-Help - can be used with any other cmdlet to get information (like -help in cmd)
- Get-Command - Returns a list of all cmdlets, functions and aliases available
- Out-File - can be used to reroute output from the screen to a file
- Get-SQLData - takes a server, db, and a query as parameters and returns the result
Most cmdlets take one or more parameters. You can use Get-Help to show you what parameters are available and which ones are required. Here is how I would check the Application Event Logs on my local machine and how I would check it on a remote machine (make sure you have the necessary permissions on the remote machine)
- Get-Eventlog -logname 'appliation'
- Get-Eventlog -logname 'application' -computername 'MyServer'
Pipes
You can string cmdlets together using the | operator (it's called a pipe) to take the output from one cmdlet and feed it to the next. This allows you to string cmdlets together to do what you want to do. Here we are taking the Get-EventLog cmdlet and writing the output to a file- Get-EventLog -logname 'application' | Out-File 'C:\temp\applog.txt'
Scripts
After working with cmdlets for a while you will find the need to combine a series of cmdlets together. The is where a script comes in. PowerShell scripts use the .ps1 extension. You can create it in your favorite text editor or use the built in Microsoft Scripting Environment, PowerShell ISE. The ISE in PowerShell v3 includes intellisense so I would definitely suggest upgrading if you haven't already. You can get it here:Functions
Once you have written a few scripts you will notice that some scripts have the same blocks of code. Instead of rewriting it every time you can create a function. A function is like a mini-CmdLet, you can create it to take input parameters and specify default ones if they aren't given. Let's take our previous example and turn it into a function that takes the computer name as a parameter.
function get-MyEventlog($computername) {
Get-EventLog -logname 'application' -computername "$computername" | Out-File 'C:\temp\applog.txt'
}
get-MyEventlog("MyServer")
To create a function you first define it with the word 'function' then you give it a name and then specify input parameters if needed. Notice the entire function is enclosed in curly brackets, everything between the curly brackets is part of the function. Then once the function is written we can call it to get the Event Logs written to file. Now imagine that we fancied up the function so it just pulled Event Logs where the source was MSSQLSERVER.
function get-MyEventlog($computername) {
Get-EventLog -logname 'application' -computername "$computername" |
where-object { $_.source = 'MSSQLSERVER' } |
Out-File 'C:\temp\applog.txt'
}
get-MyEventlog("MyServer")
Now you can start to see some of the power of PowerShell. You could have it iterate through a series of computer names, get the SQL Event Logs and write them to a file. You could schedule the script to run nightly and dump the file to your desktop so that when you log in in the morning you can check the logs for all your servers from the previous day. You could even have the script write to a database and use SSRS to deliver a daily error report to your entire DBA team.
Modules
The next level after functions is modules. You can create a module of various useful functions and make it available to multiple scripts. Module creation is a bit outside the scope of this PowerShell intro but if you are interested here is a link to a MSDN article:Some Common Gotchas
Here is a quick list of some common gotchas I found when I started working with PowerShell
- Assignment vs Comparison Operators - In PowerShell '=' is an assignment operator, you do not use it to compare to values. '$var = 1' sets the value of the variable $var to 1, it does not check to see if $var is equal to one. To compare values you use -eq for equal so: '$var -eq 1' will check to see if the value contained in the variable $var is equal to one. Here is a link to an article that discusses the comparison operators and goes into some conditional logic:
http://www.powershellpro.com/powershell-tutorial-introduction/powershell-tutorial-conditional-logic/ - Double vs Single Quotes - In PowerShell variable interpolation (or variable substitution) only happens if the variable is enclosed in double quotes. If the variable is enclosed in single quotes Powershell treats it as a string literal. For example look at this code:
$myvar = "RutherfordTX"
'$myvar'
"$myvar"
The first call (the one in single quotes) will return the string $myvar while the second one (double quotes) will return the value RutherfordTX
Links and References
PowerShell v3 downloadModule Creation
Comparison Operators and Conditional Logic
Sum Up
Wow, that was a lot more than I thought I was going to write. I have quite a few ideas for the next couple of posts so expect me to get pretty regular for a while. Also I will be presenting at our local SQL User group, CACTUSS, here in Austin on October 15th and 16th on this topic so if you are in Austin drop on by.
Hasta Luego,
RutherfordTX
No comments:
Post a Comment