Tag: PowerShell

Using PowerShell To Set Resource Owners In A Cluster

Following up on my post about Using PowerShell To Add Owners For Cluster Disks here’s another quick post on how PowerShell can help you with your clusters.

In my new cluster build out I needed to quickly set it so that one of the SQL instances could only potentially live on two of the 5 nodes. This could have been quickly done using the GUI however it’s just as fast to do so using PowerShell.

Load up the Windows PowerShell Modules and you’re ready to go.

 

In this example we have a two node cluster made up of SERVER1 and SERVER2 and a single installed SQL instance called INST1.

In PowerShell the following would provide us a list of possible owners of INST1:

Get-ClusterOwnerNode -Group "SQL Server (INST1)"

ClusterObject                          OwnerNodes                                 
————-                                 ———-                                 
SQL SERVER (INST1)           {server1, server2}
         

 

Now to change this so that only SERVER1 can potentially own the SQL instance is a very quick task:

Set-ClusterOwnerNode -Group "SQL Server (INST1)" -Owners SERVER1

 

Sadly you don’t get any feedback that the change has been made, but if you run Get-ClusterOwnerNode again:

Get-ClusterOwnerNode -Group "SQL Server (INST1)"

ClusterObject                          OwnerNodes                                 
————-                                 ———-                                 
SQL SERVER (INST1)           {server1}
       

 

Adding SERVER2 back is as quick as running Set-ClusterOwnerNode again and providing a comma delimited list of servers:

Set-ClusterOwnerNode -Group "SQL Server (INST1)" -Owners SERVER1,SERVER2

ClusterObject                          OwnerNodes                                 
————-                                 ———-                                 
SQL SERVER (INST1)           {server1, server2}
     

 

You have to love the simplicity of working with clusters in PowerShell.

Using PowerShell To Add Owners For Cluster Disks

Here’s a quick post about how great PowerShell can be for your clusters.

I’m in the middle of configuring a few machines in clusters and recently added a new node to a cluster that was already built (but not configured). I needed to have that final node be a possible owner for all 33 presented disks on that cluster.  To do that through the cluster GUI would require a heck of a lot of mouse clicks….PowerShell to the rescue!

The Windows PowerShell Modules (under Administrative Tools) allow you to do all kinds of wonderful things with clusters.

Allowing a single disk to be owned by the extra server is as simple as:

CLUSTER RESOURCE "Cluster Disk 1" /addowner:DBSERVER1

 

 

If I want to do that for 33 disks a quick PowerShell one-liner takes care of it:

$i=1;do {cluster resource `"Cluster Disk $i`" /addowner:DBSERVER1;$i = $i +1} while ($i -lt 33)

 

 

Here’s a version that’s a little more readable:

$DiskNo = 1

DO

    { CLUSTER RESOURCE `"Cluster Disk $DiskNo`" /addowner:DBSERVER1

        $DiskNo = $DiskNo+1

    }

    

WHILE ($DiskNo -lt 33)

 

Quick and easy. Saved my clicky finger lots of work.

 

More PowerShell and cluster fun coming up in the next few weeks.

Set The SQL TCP/IP Port With PowerShell

I’ve previously mentioned how I’m building out a whole bunch of clustered servers. In an effort to standardize all the installation and configuration components I’ve used multiple PowerShell scripts. One of the most challenging to get working was one that enabled the TCP/IP for SQL and set it to listen on Port 1433.

This script is based on the great post by Allen White (blog|twitter) on finding your enabled SQL Server protocols using PowerShell.

 

Is TCP/IP enabled?

For the script to work we have to load the SMO objects with the context of the SQL Server we want to change (if a clustered instance we would use the virtual name)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $SQLName

 

Then we build the path to the TCP/IP protocol in the instance and query the IsEnabled property.

$urn = "ManagedComputer[@Name='$SQLName']/ServerInstance[@Name='$Instance']/ServerProtocol[@Name='Tcp']"

$Tcp = $m.GetSmoObject($urn)

$Enabled = $Tcp.IsEnabled

 

Now to enable TCP/IP if it’s disabled.

IF (!$Enabled)

    {$Tcp.IsEnabled = $true }

 

 

Set the listening port

Now we drill down a little further and set the listening port.

$m.GetSmoObject($urn + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = "1433"

$TCP.alter()

 

 

Now for the fun stuff

What we have there is strictly functional. There’s no error checking or funky components.

First off, we need to run this as admin, so we can check to see if this is an administrator running this in an elevated shell:

$RunningAsAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")

 

We can then check the value of $RunningAsAdmin. If true then continue on our way, otherwise error out and warn the user.

Next we’ll prompt the user for the SQL instance that we’ll be configuring, after all that’s pretty important. The entered value will be placed in the $SQLInstance variable and used in the script.

$SQLInstance = read-host "Enter the SQL instance to configure"

 

We’ll need to split out the server or virtual name from the instance name (if there is one) in order to use the values within the SMO portion (we’ll also make everything upper case).

$SQLInstance = $SQLInstance.ToUpper()

 

if ($SQLInstance -ilike "**") 

    {

    $string = $SQLInstance.Split("")

    $SQLName = $string[0]

    $Instance = $string[1]

    }

else

    {

    $SQLName = $SQLInstance

    $Instance = "MSSQLSERVER"

    }

 

Given that we are making a significant change we should provide the option to cancel the action. We do this with a quick confirmation.

$yes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes",""

$no = New-Object System.Management.Automation.Host.ChoiceDescription "&No",""

$choices = [System.Management.Automation.Host.ChoiceDescription[]]($yes,$no)

 

$captionTCP = "Question!"

$messageTCP = "Enable TCP/IP and set SQL to listen on port 1433 on SQL instance: $SQLInstance ?"

$resultTCP = $Host.UI.PromptForChoice($captionTCP,$messageTCP,$choices,0)

 

We can then check whether yes or no button was pressed and handle the action.

if($resultTCP -eq 0) { #SET TCP STUFF HERE }

    else { Write-Warning "TCP/IP changes cancelled" }

 

Quick and easy

The script only takes a few seconds to run and does exactly what is says on the box. You can copy the script below or download here. Happy playing.

 

#Check the executing user is running in an elevated shell & and an admin

$RunningAsAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")

if ($RunningAsAdmin)

    {

 

 

<######################################

## WORK HERE                         ##

######################################>

 

#Get the SQL instance we'll be working with

$SQLInstance = read-host "Enter the SQL instance to configure"

 

$yes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes",""

$no = New-Object System.Management.Automation.Host.ChoiceDescription "&No",""

$choices = [System.Management.Automation.Host.ChoiceDescription[]]($yes,$no)

 

 

 

#########################################

### ENABLE TCP/IP & LISTENING ON 1433 ###

#########################################

$captionTCP = "Question!"

$messageTCP = "Enable TCP/IP and set SQL to listen on port 1433 on SQL instance: $SQLInstance ?"

$resultTCP = $Host.UI.PromptForChoice($captionTCP,$messageTCP,$choices,0)

#If yes prompt for confirmation then make the changes

if($resultTCP -eq 0)

    {

    Try

        {

 

$SQLInstance = $SQLInstance.ToUpper()

 

if ($SQLInstance -ilike "**") 

    {

    $string = $SQLInstance.Split("")

    $SQLName = $string[0]

    $Instance = $string[1]

    }

else

    {

    $SQLName = $SQLInstance

    $Instance = "MSSQLSERVER"

    }

 

 

$SQLName

$Instance

 

# Load SMO Wmi.ManagedComputer assembly

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

 

Trap {

  $err = $_.Exception

  while ( $err.InnerException )

    {

    $err = $err.InnerException

    write-output $err.Message

    };

    continue

  }

  

# Connect to the instance using SMO

$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $SQLName

 

 

$urn = "ManagedComputer[@Name='$SQLName']/ServerInstance[@Name='$Instance']/ServerProtocol[@Name='Tcp']"

$Tcp = $m.GetSmoObject($urn)

$Enabled = $Tcp.IsEnabled

 

#Enable TCP/IP if not enabled

IF (!$Enabled)

    {$Tcp.IsEnabled = $true }

 

#Set to listen on 1433

$m.GetSmoObject($urn + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = "1433"

$TCP.alter()

 

        "Success: SQL set to listen on TCP/IP port 1433. Please restart the SQL service for changes to take effect."

        }

    Catch { Write-Warning "Unable to enable TCP/IP & set SQL to listen on port 1433" }

     } 

else { Write-Warning "TCP/IP changes cancelled" }

    

    

    }

    

    else { Write-Warning "This script must be executed by an administrator in an elevated shell" }

 

 

 

 

Central Auditing Of SQL Permissions Scripts

Yesterday I gave a presentation for the PASS PowerShell Virtual Chapter on central auditing of SQL permissions with PowerShell and TSQL. For those that attended feel free to download the scripts I used in my presentation. For those that didn’t…well you can still download them but this picture may not apply to you…

Image from http://whosawesome.com/

Presenting For The PASS PowerShell Virtual Chapter

A quick note, on Wednesday at 1PM EST I’ll be giving an online presentation on one of the two topics I submitted for the PASS Summit this year.

 The presentation will be heavy on the demo side of things and I’ll cover the basics of querying SQL Server using PowerShell, running scripts against multiple machines quickly and easily as well as bulk loading data into SQL.

Please check out http://www.powershell.sqlpass.org/ for a link to the livemeeting.

 

Central auditing of SQL permissions with PowerShell & TSQL

Description: As a DBA it can be a challenge to know who has permissions to what SQL instances and what objects. The more instances you have the more complex that task. In this presentation I’ll share a method using PowerShell and TSQL that can be used to capture permissions from all of your SQL instances and load them into a centralized location. We’ll even take it a step further by auditing those permissions so that we can quickly and easily identify any that might have changed.

 

Rock The PASS Summit Vote

The PASS Summit 2011 is fast approaching. It’s being held in October this year and the call for speaker sessions has already closed. In a great turn of events PASS is asking you to take a part in helping to decide on the sessions to be presented. Up until May 20th you can vote on the sessions that you want to see.

I’ve already voted for multiple sessions over at the Session Preferencing page, please ensure that you do so as well. In doing so I hope that you consider voting for two sessions I submitted.

 

PowerShell: Are you checking out my profile? [100 level]
Session Category: Regular Session (75 minutes) 
Session Track: Enterprise Database Administration and Deployment 

PowerShell is a very powerful management tool and you can spend hours writing magical scripts to provide automation for frequently run tasks. Often forgotten is the PowerShell profile, a place you can add your own functions which can provide you lightning fast access to information. 

In this session we’ll talk about the power a profile puts at your fingertips. I will also demo (and share) several PowerShell functions that I use frequently for common tasks like checking database backups and disk space.
I’ll show you my PowerShell profile if you show me yours.

 

I’ve given this presentation at a couple of SQLSaturday events and it’s proven very popular both times. I have a couple of new items in my profile which extends this topic further.

 

Centralized auditing of permissions with SQL Server and PowerShell [100 level]
Session Category: Regular Session (75 minutes) 
Session Track: Enterprise Database Administration and Deployment 

As a DBA it can be a challenge to know who has permissions to what SQL instances and what objects. The more instances you have the more complex that task. In this presentation I’ll share a method using PowerShell and TSQL that can be used to capture permissions from all of your SQL instances and load them into a centralized location. We’ll even take it a step further by auditing those permissions so that we can quickly and easily identify any that might have changed.

I will actually be presenting a shortened version of this on May 18th at 1pm EST for the PASS PowerShell Virtual Chapter. I hope you can attend that and get an idea of how the longer session could help you in auditing your SQL servers.

Handling Periods In Database Names With PowerShell

I came across an interesting problem today. We have a vendor who’s tool, for some unknown reason, creates a database with a period in the name (ie Data.Base). Why on earth anyone would do this I don’t know, but it’s happened. I found this out when my PowerShell script to capture database sizes failed.

It seems that PowerShell was not able to handle that period.

 

Reproducing the problem

Create a new database using SSMS:

CREATE DATABASE [Terrible.DBName]

 

Now open up PowerShell and try to query what’s in that database:

dir SQLSERVER:SQLlocalhostdefaultdatabasesTerrible.DBName


Doing so gives the error:

Get-ChildItem : Cannot retrieve the dynamic parameters for the cmdlet. SQL Server PowerShell provider error: The number of keys specified does not match the number of keys required to address this object. The number of keys required are: Name.
At line:1 char:4
+ dir <<<<  SQLSERVER:SQLlocalhostdefaultdatabasesTerrible.DBName
    + CategoryInfo          : InvalidArgument: (:) [Get-ChildItem], ParameterBindingException
    + FullyQualifiedErrorId : GetDynamicParametersException,Microsoft.PowerShell.Commands.GetChildItem
   Command

I tried encapsulating the path in quotes and escaping the period with a back tick, both to no avail.

dir ‘SQLSERVER:SQLlocalhostdefaultdatabasesTerrible.DBName’
dir SQLSERVER:SQLlocalhostdefaultdatabasesTerrible`.DBName


 

Fixing the problem

Eventually I found the solution. Changing the period to it’s hex value of %2e allowed me to query correctly.

dir SQLSERVER:SQLlocalhostdefaultdatabasesTerrible%2eDBName

 

PeriodInDBName

 

 

Updating scripts

Now that I found the problem and resolution I’ll be going through and changing my scripts to take this into account.

One thing to note, when trying to use –replace to replace the period in a variable you need to place it within square brackets otherwise the entire string gets replaced with %2e.

The wrong way:

$a = “Some.String”
$a = $a -replace ".", "%2e"

$a


PeriodInDBNameWrongString

 

The right way:

$a = "Some.String"

$a = $a -replace "[.]", "%2e"

$a


PeriodInDBNameRightString

Checking Database Space With PowerShell

Have you ever been asked the question “quick, I need to know how much free space there is in the Nodrap database on server Cromulent”?

Ever wished that you could quickly grab that information?

You might well have a TSQL script that will grab that information for you. I’ve been working on pulling that information centrally, grabbing that data for multiple servers becomes a much simpler task when using PowerShell.

 

 

Why PowerShell and not TSQL?

For me the big bonus that PowerShell provides me for grabbing this data is that I can pull it quickly and easily, I can also simply run it for multiple machines and the same script works for versions SQL 2000 and up.

 

 

How big is my database?

Open up your favorite PowerShell ISE (you can even use the one that comes with Windows). If the aren’t already loaded you’ll need to add the SQL snapins

Add-PSSnapin SqlServer* 


 

 

To get a list of databases, their sizes and the space available in each on the local SQL instance.

dir SQLSERVER:SQLlocalhostdefaultdatabases | Select Name, Size, SpaceAvailable | ft -auto


 

CheckDBWPS SizeSpace

 

Very quick and easy. The information here is a little misleading though.

The Size is the size of the database and includes size of the transaction log(s). SpaceAvailable only represents the space available in the data files however, unless we’re looking at SQL 2000 in which case it’s the free space in the data and log files. To make things even more confusing Size is reported in MB and SpaceAvailable in KB.

To get more details we need to look at the transaction log information.

 

 

How big is my transaction log?

To grab this information we need to go deeper. In this case we will focus on the AdventureWorks database.

dir SQLSERVER:SQLlocalhostdefaultdatabasesAdventureWorkslogfiles | 

    Select Name, Size, UsedSpace | ft -auto


 

CheckDBWPS LogSpace

 

 This information is all in KB which helps have it make a little more sense.

 

Doing the math

Now we have the size of the database, the size of the log and the free space in each it’s some quick math which gives us the sizing information.

  • Size = Database Size in MB
  • Size / 1024 (from log) = Log size in MB
  • UsedSpace / 1024 = Log used in MB
  • (Size – UsedSpace) / 1024 = Log free in MB
  • Size – (Size / 1024) (from log) = Data files size in MB
  • SpaceAvailable / 1024 = Space free in data files
  • Size – (SpaceAvailable / 1024) – (Size / 1024) (from log) = Space used in data files
  • Size – ((SpaceAvailable / 1024) – ((Size – UsedSpace) / 1024) = Space used in data files (SQL 2000)

 

Outputting nice results

Running all this and grabbing the data from different areas can lead to messy results. This is where the PowerShell DataTable come to the rescue.

A DataTable is a PowerShell object, much like a SQL table that can hold data for you.

First you create the object, define and add columns and then add rows before finally returning the data.

Here’s a quick example:

$dataTable = New-Object system.Data.DataTable "SomeStuff"

#Specify the column names for the data table

$col1 = New-Object system.Data.DataColumn FunkyText,([string])

$col2 = New-Object system.Data.DataColumn JustANumber,([int])

$col3 = New-Object system.Data.DataColumn TodaysDate,([datetime])

 

#Add the columns to the data table

$dataTable.Columns.Add($col1)

$dataTable.Columns.Add($col2)

$dataTable.Columns.Add($col3)

#Create a new row

$row = $dataTable.NewRow()

#Add data for each column in the row

$row.FunkyText = "Something really funky"

$row.JustANumber = 1395

$row.TodaysDate = ((Get-Date ).ToString("yyyy-MM-dd HH:mm:ss"))

#Add the new row to the datatable

$dataTable.Rows.Add($row)

 

#Output the datatable

$dataTable | out-gridview


 

CheckDBWPS Datatable

 

 

Putting everything together

Running the following script will pull the data and log information for all the databases on the SQL instance specified in the top parameter. It also handles named instances without modification, and is factored to return the correct information for SQL 2000 as well as higher versions

Note: Run DBCC UPDATEUSAGE for SQL 2000 instances to ensure that the data is accurate

Param ($Servername= 'localhost')

  
Function Get-DBSizes ($Servername)

{

$Servername

#Check to see if it's a name instance, if it is the location will be different

if ($Servername -ilike "**") { $BaseLocation = "SQLSERVER:SQL$Servernamedatabases" }

    else { $BaseLocation = "SQLSERVER:SQL$Servernamedefaultdatabases" }

$dblist = dir $BaseLocation -Force | select Name

foreach ($db in $dblist)

{

    $location = $db.Name

    $locationFixed = $location -replace "[.]", "%2e"

    #Grab the database information

    $DBInfo = dir $BaseLocation -Force | Where-Object {$_.Name -eq $location; $_.Refresh()} | 

        select Name, 

                size,

                SpaceAvailable,

                CompatibilityLevel

 

    #Pull the log information

    #Use measure-object to sum up sizes in the event that we might have more than one log file

    $logsize = dir $BaseLocation$locationFixedlogfiles | Where-Object {$_.Name; $_.Refresh()} | Measure-Object -Property Size -Sum 

    $logused = dir $BaseLocation$locationFixedlogfiles | Where-Object {$_.Name; $_.Refresh()} | Measure-Object -Property UsedSpace -Sum 

    $sp = $DBInfo.SpaceAvailable

    $TotalDBSizeMB = $DBInfo.size

    $LogSizeMB = ($logsize.sum / 1024)

    $LogUsedMB = ($logused.Sum / 1024)

    $LogFreeMB = ($LogSizeMB - $LogUsedMB)

    $DataFilesSizeMB = ($TotalDBSizeMB - $LogSizeMB)

    $SpaceAvail = ($dbinfo.SpaceAvailable / 1024)

    
    #Because SQL2000 spaceavailable    includes log space we have to do different calculations depending on version

    #Run DBCC UPDATEUSAGE on your 2000 databases nightly to help ensure this data is accurate

    $Compat = $DBInfo.CompatibilityLevel

    if ($Compat -eq 'Version80') { $DataFileFreeMB = ($SpaceAvail - $LogFreeMB)    }

        else { $DataFileFreeMB = $SpaceAvail }

    

    

    $DataFileUsedMB = ($DataFilesSizeMB - $DataFileFreeMB)

    $DataFilePercentUsed = ($DataFileUsedMB / $DataFilesSizeMB) * 100

    $DataFilePercentFree = 100 - $DataFilePercentUsed 

    $LogPercentUsed = ($LogUsedMB / $LogSizeMB) * 100

    $LogPercentFree = 100 - $LogPercentUsed

    $date = (Get-Date ).ToString("yyyy-MM-dd HH:mm:ss")

    

    #Write the results into the data table

    $row = $dataTable.NewRow()

    $row.ServerName = $Servername

    $row.DatabaseName = $location

    $row.TotalDBSizeMB = $TotalDBSizeMB

    $row.DataFilesSizeMB = $DataFilesSizeMB

    $row.DataFilesUsedMB = $DataFileUsedMB

    $row.DataFilesFreeMB = $DataFileFreeMB

    $row.DataPercentUsed = $DataFilePercentUsed

    $row.DataPercentFree = $DataFilePercentFree

    $row.LogFilesSizeMB = $LogSizeMB

    $row.LogFilesUsedMB = $LogUsedMB

    $row.LogFilesFreeMB = $LogFreeMB

    $row.LogPercentUsed = $LogPercentUsed

    $row.LogPercentFree = $LogPercentFree

    $row.Date = $date

    $dataTable.Rows.Add($row)

    
    #And we are done

}

}

 
 

#Create data table to hold the results

$dataTable = New-Object system.Data.DataTable "Results"

#Specify the column names for the data table

$col1 = New-Object system.Data.DataColumn ServerName,([string])

$col2 = New-Object system.Data.DataColumn DatabaseName,([string])

$col3 = New-Object system.Data.DataColumn TotalDBSizeMB,([int])

$col4 = New-Object system.Data.DataColumn DataFilesSizeMB,([int])

$col5 = New-Object system.Data.DataColumn DataFilesUsedMB,([int])

$col6 = New-Object system.Data.DataColumn DataFilesFreeMB,([int])

$col7 = New-Object system.Data.DataColumn DataPercentUsed,([decimal])

$col8 = New-Object system.Data.DataColumn DataPercentFree,([decimal])

$col9 = New-Object system.Data.DataColumn LogFilesSizeMB,([int])

$col10 = New-Object system.Data.DataColumn LogFilesUsedMB,([int])

$col11 = New-Object system.Data.DataColumn LogFilesFreeMB,([int])

$col12 = New-Object system.Data.DataColumn LogPercentUsed,([decimal])

$col13 = New-Object system.Data.DataColumn LogPercentFree,([decimal])

$col14 = New-Object system.Data.DataColumn Date,([datetime])

#Add the columns to the data table

$dataTable.Columns.Add($col1)

$dataTable.Columns.Add($col2)

$dataTable.Columns.Add($col3)

$dataTable.Columns.Add($col4)

$dataTable.Columns.Add($col5)

$dataTable.Columns.Add($col6)

$dataTable.Columns.Add($col7)

$dataTable.Columns.Add($col8)

$dataTable.Columns.Add($col9)

$dataTable.Columns.Add($col10)

$dataTable.Columns.Add($col11)

$dataTable.Columns.Add($col12)

$dataTable.Columns.Add($col13)

$dataTable.Columns.Add($col14)

 
 

 
#Provide the name of the SQL server that we want to check

 
$WarningPreference = "silentlycontinue"

#Call the function to populate the results

#get-content c:Tempserverlist.txt | % {Get-DBSizes $_  } 

Get-DBSizes $Servername

 

#Output the results

$dataTable | Out-GridView

 

 

Click to embiggen

 

Final note

Download the PS1 file and save it on your machine, from there you can call the script and just pass in the SQL instance to get the results eg:

./Get-DatabaseSize.ps1 localhost
./Get-DatabaseSize.ps1 cromulentawesomesauce

In a follow up post I’ll show how we apply a minor tweak to the script and have the data loaded into a SQL database.

Guest Post On Hey, Scripting Guy! Blog

Recently I was contacted by Aaron Nelson (blog|twitter) who provided me an awesome opportunity to write a guest blog post for the Hey, Scripting Guy! Blog. Naturally I jumped at the chance and the post went live this week as a part of a week of SQL related post in honor of SQLRally which is coming up next week in Orlando.SQLRally

 

My post was on using PowerShell to report on SQL Server backup status. Go have a read and let me know what you think. With the rest of the week being dedicated to SQL and PowerShell don’t forget to keep checking the Hey, Scripting Guy! Blog.

Checking Windows Power Plans With PowerShell

Are you getting the best performance out of your CPUs in Windows 2008? You might think you are however the Power Plan that Windows is using might be hurting you and degrading your CPU performance by as much as 25%

 

A Power Plan?

Power Plans are used by Windows to adjust certain performance settings to improve power management and reduce power and cooling requirements. You might be more familiar with this in a laptop setting where the CPU gets stepped down and screen dims when you are on battery power.

Power Plan settings can be found under the Control Panel –> Hardware and Sound –> Power Options

PowerPlanSettings

 

For a high performing database server you really need to be using the High Performance plan.

I highly recommend going and reading Glenn Berry’s (blog|twitter) most recent post on power management (and then go read the other posts in the SQL Server Hardware Nugget series).

 

Checking Power Plans on your servers

It could be very time consuming to go through all your servers and check that the Power Plan is set appropriately. With this in mind I adapted a script by Ed Wilson (blog|twitter) who posted on his Scripting Guys blog a post which shows how to check the Power Plan settings using PowerShell.

My expanded script is just a function that returns the Power Plan of the computer that is passed in.

Firstly it checks the Windows version of the passed in computer, unless we’re running Windows version 6x or higher the win32_Powerplan WMI provider does not exist and we will get errors.

Assuming a version that has the WMI provider we query that, grab the active plan GUID and then use that to get the actual Power Plan in use. This information is then written to the console window. In order to make sure that it stands out that the incorrect plan is set the font color is changed to red, while a correct plan shows green.

Finally, given that this is a function we can easily grab a list of machines from a text file (or database table) and pass those in using a foreach (%) loop so multiple machines can be queried at once quickly and easily.

 

The PowerShell

# Read more about the importance of the right power plan at http://sqlserverperformance.wordpress.com/2010/12/28/the-importance-of-windows-power-plans-for-server-performance/

 

Function Get-PowerPlan ($Computer)

{

    # Grab the windows version so we know whether to query for the power plan or not

    $winver = gwmi -Class win32_OperatingSystem -ComputerName $Computer

        
        # Version 6x is Win7/2008 powerplan not relevant below that

        if ($winver.Version.substring(0,1) -gt 5) 

        {

            $plan = Get-WmiObject -Class win32_Powerplan -Computername $Computer -Namespace rootcimv2power -Filter "isActive='true'"  

            $regex = [regex]"{(.*?)}$" 

            $planGuid = $regex.Match($plan.instanceID.Tostring()).groups[1].value 

            $PlanType = powercfg -query $planGuid

            
            # Grab just the first record which has the actual plan being used

            # From that record just grab the actual plan type which is enclosed in parenthesis            

            $PlanType = $PlanType[0].Substring($PlanType[0].LastIndexOf("(")+1) -replace ")", ""

           

            # If the plan isn't high performance let's make it stand out

            if ($PlanType -ne "High performance") { Write-Host $Computer":" $PlanType.ToUpper() -foregroundcolor "Red" }

            else {  Write-Host $Computer":" $PlanType -foregroundcolor "Green" }

        }

        else

        {

            # If the Windows version doesn't support power plans just let us know

            Write-Host $Computer": n/a"

        }

}

 

#Based upon a list of machines contained in c:tempserverlist.txt

get-content C:tempserverlist.txt | %  {  Get-PowerPlan $_ ; }

 

Running this against a couple of machines locally I got the following results:

localhost: BALANCED
anony-lptp: High performance

 

Give the script a try, let me know how the results look for you.