Category Archives: PowerShell

Post navigation

All SQL Server 2008 installations will automatically have PowerShell. As a SQL Server DBA you will not survive without knowing PowerShell in future. For SQL Server there is a separate component of PowerShel – SQLPS. You start SQLPS in SQL Server or by typing SQLPS in command prompt. SQLPS loads required components which you can work with SQL Server. In the normal PowerShell editor SQL Server related components are not loaded.

Starting SQLPS from SQL Server:

SQLPS

In SQLPS there is a directory structure which you can traverse through that using “cd” which is essentially an alias of Set-Location cmdlet. You can see the directories available in each level by executing “dir” which is another alias for the Get-ChildItem cmdlet.

In the root there are four logical drives or directories as you can see in the above screenshot.

SQL

SQLPolicy

SQLRegistration

DataCollection

The below screenshot shows that I have traversed through the directory structure upto “tables” object in my local server. Look at the prompt carefully.

Now if you execute “dir” you will get a list of tables available in the AdventureWorks database. You can execute T-SQL commands at this point. For example to get a list of the top 10 tables with the highest number of records.

You can manage system services in local or remote computers using PowerShell. PowerShellprovides you set of cmdlets to work with system services. They are;

Get-Service

Stop-Service

Start-Service

Suspend-Service

Resume-Service

Restart-Service

Set-Service

New-Service

Let’s consider a simple scenario – you need to stop the SQLBrowser service running in a remote computer. The below statement above get the SQLBrowser service in remote computer, Susantha-lp and stores in a variable called “$s”

$s = Get-Service -Name SQLBrowser -ComputerName susantha-lp

The next step is to stop the service. There are two different methods available for doing this:

You can use Stop-Service cmdlet or

You can invoke stop() method in “$s” object

The first method of stopping the service can be achieved using the below code:

$s | Stop-Service

The second method is:

$s.Stop()

Both methods are really simple. My preference would be for the second method due to its object oriented approach.Continues…

Reading Event logs with PowerShell

An event log is a windows service that manages event logging in a computer. When this service is started, Windows logs important information about the operation of the system and the applications running on it . The logs available on a system depend on the system’s role and the services installed.

Two general types of log files are used;

Windows log

Application and services log

Event log records events of different categories. namely:

Information

Warning

Error

Critical

Audit success

Audit failures

The GUI Event Viewer is used to view the individual events in an event log. In addition to the GUI tool, PowerShell can be used to query the event log. The following PowerShell cmdlets can be used to manage the event log:

Get-WinEvent

Get-EventLog

Clear-EventLog

Limit-EventLog

Show-EventLog

The below script displays records from the event log which has an “error” state in the Application, System and Security logs.

Using WMI Objects in PowerShell

WMI, Windows Management Instrumentation is set of managed APIs provided by the Windows OS to expose information about local or remote computers.

You can use WMI objects from PowerShell by using the Get-WMIObjectcmdlet use to execute WMI objects as shown below

You can see the list of WMI objects available by executing Get-WMIObject -List as shown below:

More complex WMI queries such as the below example can be executed:

In the above query $QueryText is a variable which contains the WMI query which is similar to a T-SQL style query. This query extracts the logical disk information of C,D and G whereFreeSpace is greater than a certain value and the FileSystem is NTFS.

The below query gives the same result as above. Examine the way it has written, as it is a little different.

In common with other scripting languages, in PowerShell has basic language elements like Variables, Arrays, Functions, Objects, Loops, IF statements, Switch statements, etc. (See here for a full explanation)

PowerShell CmdLets (Commands)

In PowerShell commands are known as CmdLets (pronounced Command Lets). Cmdlets follow the naming convention of Verb-Noun combination:

E.g: Get-Help, Get-Service, Get-Process

PowerShell cmdlets are not case sensitive and are the smallest unit of functionality in PS. You can use either the PowerShell Integrated Script Environment shown below (just type powershell_ise into the search box in the Windows start menu to launch this) or the command line tool to execute Cmdlets.

In the PowerShell ISE. you enter the command at the prompt and the results of the cmdlet will be displayed in the middle panel of the tool. You can use the top panel in the tool to write scripts and also to execute single Cmdlets. The F5 key executes the entire script while F8 key executes only highlighted Cmdlets (Run Selection).

Cmdlets are simple to type, you can use Tab key to auto complete the Cmdlet. E.g: Type Get-Pro then press the Tab key.

Cmdlet parameters

Cmdlet accept which are denoted by using the “-“symbol:

For example, the “-Name” is the parameter instructs PowerShell to display only “winrm” service information. Parameters are also auto completed using the Tab key, so that you don’t have to remember the entire parameter name.

Piping

Piping or pipelining is a method of combining two or more PowerShell Cmdlets to do a single task. PowerShell is a fully objected oriented scripting language as a result Cmdlet returns an object as result. To combine Powershell Cmdlets you can use the symbol, “|”.

The Get-Service cmdlet returns all services (whatever the state) on the local machine. It returns as an object. The “|” or piping passes that object in to next cmdlet (where-object) which essentially does the filtering. The braces “{ }” represents the body of the where-object cmdlet, whichs specifies a condition. “$_.Status” is the current object (“$_.”), property (“Status”) and “-eq” is the logical condition (ie “=”).
Operators in PowerShell user characters and not as symbols as in other languages.

The Where-object cmdlet iterates through all the objects returned from the Get-Service cmdlet and filters out only the objects which are have the status of “Running”.Continues…

A major drawback of PowerShell 1.0 was the lack of a method to execute commands on a remote machine. PowerShell 2.0 addresses this with a new feature named remoting, which is designed to enable command (or script) execution on remote machines. Using PowerShell remoting, commands can be issued either synchronously or asynchronously and even scheduled or throttled.

Before using PowerShell remoting, you will first need the appropriate permissions to connect to the remote machine, then execute PowerShell, and finally execute the desired command or scripts. Additionally, the remote machine will need to have both PowerShell 2.0 and Windows Remote Management (WinRM) installed, and PowerShell will need to be configured for remoting. Note that the commands executed via remoting will be subject to the remote machine’s execution policies, preferences, and profiles.

Powershell Remoting Requirements

Before using PowerShell remoting, both the local and remote computers must have the below:

PowerShell 2.0 or later

.NET Framework 2.0 or later

Windows Remote Management (WinRM) 2.0 (this is part of Windows 7 and Windows Server 2008 R2. For previous versions of Windows, an integrated installation package needs to be downloaded and installed – the PowerShell 2.0 download includes this.).

Configuring Remoting

On Windows Server 2008 R2, both PowerShell and WinRM are installed by default, however for security reasons, both PowerShell remoting and WinRM are initially configured to not allow remote connections. There are several methods to configure remoting:

The simplest method to enable PowerShell remoting is to execute the Enable-PSRemoting cmdlet:PS C:\> enable-pssremoting
Once this is executed, the below tasks are performed by the cmdlet:

Runs the Set-WSManQuickConfig cmdlet, which in turn executes the belows tasks:

Starts up the WinRM service.

Sets the WinRM service startup type on the to Automatic.

Creates a listener to listen for and accept requests on an IP address.

Enables a firewall exception for WS-Management communications.

Enables all the registered PowerShell session configurations to receive instructions from remote computers.

Registers the “Microsoft.PowerShell” session configuration (unless it has already been registered).

Registers the “Microsoft.PowerShell32” session configuration on 64-bit systems (unless it has already been registered).

Removes “Deny Everyone” setting from the security descriptor for all registered session configurations.

Finally, restarts WinRM to make the above changes effective.

Note that the Enable-PSRemoting cmdlet needs to be executed as an Administrator (using the Run As Administrator option).

Using PowerShell Remoting

The power PowerShell remoting is that any the cmdlets/scripts you used in PowerShell 1.0 are available everywhere (provided PowerShell is installed on the server).Continues…

As with most areas of Windows Server 2008 and 2008 R2 , Microsoft is emphasizing PowerShell as an important tool for managing IIS 7 and IIS 7.5. The IIS PowerShell snap-in provides many new cmdlets and enables admins to manage IIS properties in numerous different ways.

Select Windows PowerShell Modules from the Administrative Tools group and the system will load the modules included with Windows Server 2008 , including the WebAdministration module which provides the IIS functionality. You may also import the module manually from the Windows PowerShell prompt using the below command:

Import-Module WebAdministration

Once the IIS PowerShell snap-in is running, you can display all the cmdlets it contains using the below command:

Get-Command –pssnapin WebAdministration

The IIS PowerShell snap-in uses three types of cmdlets:

PowerShell provider cmdlets

Task-oriented cmdlets

Low-level configuration cmdlets

These cmdlet types relate to the three different methods of managing IIS from the PowerShell prompt.

Using the IIS PowerShell Provider

The IIS PowerShell provider creates a hierarchical IIS namespace which admins can navigate similar to a standard directory structure. Type iis: and press Enter at the PowerShell prompt (with the WebAdministration module having been already imported) and the prompt changes to PS IIS:> then typing the dir command displays, but the top level of the IIS namespace (not the file system) as below:

Name
----
AppPools
Sites
SslBindings

After moving to the Sites directory using the cd Sites command, the dir command displays a list of the IIS sites on the server.

The Get-Item cmdlet allows you to show selected sites in the same format. By piping results of the Get-Item cmdlet to the Select-Object cmdlet, you can see all properties of a selected site.Continues…

By default SQL Server 2008 installs PowerShell and two SQL Server PowerShell snap-ins which expose SQL Server functionality from PowerShell. PowerShell Snap-ins are simply .NET assemblies which contain Windows PowerShell providers and/or PowerShell cmdlets that extend the functionality of the shell. When a snap-in is loaded into PowerShell , its cmdlets and providers are registered with the shell.

Starting PowerShell

PowerShell can be invoked and used on SQL Server 2008 server in four ways:

Using the sqlps utility
The sqlps utility is a command line utility for executing PowerShell commands and is installed by default in the \Program Files\Microsoft SQL Server\100\Tools\Binn folder. Since the Binn folder is added to the System PATH variable, the sqlps utility can be launched by selecting Start > Run and then entering sqlps. This should launch the sql utility:

PowerShell can also be launched from within SSMS (SQL Server Management Studio) by right-clicking an object in the Object Explorer, as shown below. SSMS will then launch launch sqlps and set
the location to the object which you right clicked.

PowerShell cmdlets and scripts can also be launched from inside a SQL Server Agent job. A SQL Server Agent job step can be set to type PowerShell and then PowerShell cmdlets and scripts can be used.

The Get-PSSnapin –registered command simply shows the listing of the available snap-ins and the two Add commands registers the snap-ins.To confirm the snap-ins are added to your PowerShell session execute the command:

Get-PSSnapin Sql*

Navigate The SQL Server Object Hierarchy using PowerShell

To see the SQLSERVER drive along with other PowerShell drives which are available in the current environment, execute the cmdlet Get-PSDrive :

Loops are an essential construct in any programming language as they enable a block of code to be repeatedly executed. This can be useful for iterating through items in an object or repeatedly performing an operation.

This tutorial covers the five types of loops that are provided in PowerShell – namely For, ForEach, While, Do-While, and Do-Until.

ForEach Loop

The foreach statement is very useful in PowerShell when you need to loop through an array or loop through items in an object.

ForEach allows the loop to evaluate the number a certain type of objects in an array or parent object and loop through those objects. One thing to bear in mind is that a ForEach loop is that there is a performance penalty for this convenience.

The below example, shows a ForEach Loop operating on a object, the code assigns the object resulting from the Get-process cmdlet to a variable, and then iterates through the process items in the object and displays the process name.