Using PowerShell for SQL Server Admin

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:

PowerShell for SQL Server

SQLPS

PowerShell for SQL Server

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.

PowerShell for SQL Server

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

  1. SQL
  2. SQLPolicy
  3. SQLRegistration
  4. 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.

PowerShell for SQL Server

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.

gci | sort-object -Property RowCount -Desc | select-object schema,name -first 10 | format-table

Continues…

Managing System Services using PowerShell

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:

  1. You can use Stop-Service cmdlet or
  2. 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…

PowerShell Tutorial – WMI Objects

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-WMIObject cmdlet use to execute WMI objects as shown below

PowerShell WMI Objects

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

PowerShell WMI Objects

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

PowerShell WMI Objects

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.

Get-WmiObject -Class
Win32_LogicalDisk | Where-Object {

 $_.DeviceID -eq "C:"
-or $_.DeviceID -eq "D:" -or $_.DeviceID -eq "G:"-and
$_.FreeSpace -gt 104857600 -and $_.FileSystem -eq "NTFS"} |
Format-Table

Personally I prefer the first version as it is more similar to T-SQL. Continues…

PowerShell Tutorial – CmdLets

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.

PowerShell Tutorial

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).

PowerShell Tutorial

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:

PowerShell Tutorial

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, “|”.

PowerShell Tutorial

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…