Powershell – Install or Uninstall Windows Server 2012 R2 feature

Check existing features

Check available and installed features with the following cmdlet:


To check it in a remote computer, type:

Get-WindowsFeature –ComputerName MYSERVERNAME

Windows Server 2012 R2 features available
These are the available features (different releases may vary output)
Powershell - Install Windows Server 2012 Feature_s001
Install feature
To install .NET Framework 3.5 feature type:

Install-WindowsFeature NET-Framework-Core

To install .NET Framework 3.5 feature defining the corresponding source type:

Install-WindowsFeature NET-Framework-Core –Source D:\sources\sxs\

Doing it on a remote server installing all depending features:

Install-WindowsFeature NET-Framework-Core –ComputerName MYSERVERNAME -IncludeAllSubFeature

If the feature to install requires restart you can specify to do it automatically with the –Restart parameter:

Install-WindowsFeature NET-Framework-Core –ComputerName MYSERVERNAME –IncludeAllSubFeature -Restart

Note: Install-WindowsFeature alias is Add-WindowsFeature
Remote installation on multiple servers
To install a component on more than one server, ejecute:

$multiservers = (‘server01’,’server02’,’server03’)
ForEach ($server in $multiservers) {
Install-WindowsFeature NET-Framework-Core –ComputerName $server –IncludeAllSubFeature –Restart

Uninstall feature
To uninstall a feature on a remote computer and doing a restart if needed, type:

Uninstall-WindowsFeature –Name NET-Framework-Core –ComputerName MYSERVERNAME -Restart

Remove-Item alias:\show
Note: Uninstall-WindowsFeature alias is Remove-WindowsFeature

In Depth

Tested With
PowerShell 4.0

Categories: Powershell | Leave a comment

Powershell – Add user to local administrators group

Add domain user/group

To see current server name:


To connect to current server Adminstrators group, type:

$srvgroup = [ADSI]("WinNT://"+$env:COMPUTERNAME+"/administrators, group")

To check server group connected you can query for is name:


To add an Active Directory user or group (example AD name is MYAD), type:


To check if new users or group were added correctly you can query group members:

$srvgroup.Invoke("Members") | foreach {$_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null)}


In Depth

Tested With
PowerShell 4.0

Categories: Powershell | Leave a comment

Powershell – Working with files and folders

Cmdlets resume

These are the key file system cmdlets:

Cmdlet Name Alias Cmd Command UNIX sh Command Description
Clear-Item cli N/A N/A Clears content of a file
Copy-Item cpi copy cp Copies file or folder
Get-Content gc type cp Gets the content of a file
Get-Location gl cd pwd Gets the current directory
Move-Item mi move mv Moves file or folder
New-Item ni, md N/A N/A Creates file or folder
Remove-Item ri del, rd rm, rmdir Deletes file or folder
Rename-Item rni, rn ren mv Renames file or folder
Set-Content sc N/A N/A Sets file content
Set-Item si N/A N/A Sets file content
Set-Location sl cd, chdir cd, chdir Sets current directory

Copy files or folders
To copy a file or a folder to a new location, type:

Copy-Item c:\scripts\test.txt c:\test

To copy every text files, type:

Copy-Item c:\scripts\*.txt c:\test

To copy a folder to a new location with all its content, type:

Copy-Item c:\scripts c:\test -recurse

Note: Copy-Item alias is cpi, cp or copy
Create new file or folder
To create a new directory (named Poweshell), type:

New-Item c:\scripts\Windows PowerShell –type directory

To create a new text file, type:

New-Item c:\scripts\Windows\PowerShell\new_file.txt –type file

To create a new text file replacing an existing one with the same name and adding some text, type:

New-Item c:\scripts\Windows\PowerShell\new_file.txt -type file -force -value "This is text added to the file"

Note: New-Item alias is ni
Delete file or folder
To delete a file, type:

Remove-Item c:\scripts\Windows\PowerShell\new_file.txt

To remove all files of a folder, type:

Remove-Item c:\scripts\Windows\PowerShell\*

To remove all folder files except those with wav extension and bypassing the confirmation prompt (with the recurse parameter), type

Remove-Item c:\scripts\Windows\PowerShell\* -recurse –exclude *.wav

To remove all files with wav and mp3 extension from a folder and bypassing the confirmation prompt (with the recurse parameter), type

Remove-Item c:\scripts\Windows\PowerShell\* -recurse –include .wav,.mp3

To delete all text files except those who have the string vamue test in file name, type:

Remove-Item c:\scripts\Windows\PowerShell\* –include *.txt –exclude *test*

To simulate the execution of the delete cmdlet without really implementing it use the whatif parameter and for every file\folder you will receive a notification :

Remove-Item c:\scripts\Windows\PowerShell\* –include *.txt –exclude *test* -whatif

In order to remove an alias (named show), type:

Remove-Item alias:\show

Note: To switch to a powershell drive (in this case the environment variables) use this command:

Set-Location env:\

Move file or folder
To move a file, type:

Move-Item c:\scripts\Windows\PowerShell\new_file.txt c:\test

To move all text files and force overwrite if a file exists with the same name in destiny, type:

Move-Item c:\scripts\Windows\PowerShell\*.txt c:\test -force

For a single file you’re able to move and rename it with these parameters:

Move-Item c:\scripts\Windows\PowerShell\new_file.txt c:\test\renamed_file.txt

Note: Move-Item alias is mi, mv, move
Rename file or folder
To rename a file, type:

Rename-Item c:\scripts\Windows\PowerShell\new_file.txt new_name.txt

Note: Rename-Item alias is rni or ren
Replicate dir command
To get the similar of a dir command, type:


To list all files and folder in c: subfolders and all files (including hidden and system items), type:

Get-ChildItem –force c:\ -recurse

You can with the same cmdlet work with environment variables like:

Get-ChildItem env:
Get-ChildItem HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall

You can also work with exclude and include parameters

Get-ChildItem c:\scripts\*.* -include *.txt,*.log
Get-ChildItem c:\scripts\*.* -exclude *.txt,*.log

Using the Sort_Object parameter:

Get-ChildItem c:\scripts\*.* | Sort-Object length –descending

Note: Get-ChildItem alias is gci, ls or dir
Retrieve specific item
To retrieve a specific item such as file, folder, registry ket… use Get-Item cmdlet
To obtain properties of an item (first example a file, second a registry key), type:

$(Get-Item c:\scripts).lastaccesstime
$(Get-Item hkcu:\software).subkeycount

Note: To see properties of an item use Get-Member:

Get-Item hkcu:\software | Get-Member

Note: Get-Item alias is gu
Verify existence of file or folder
You can check if a file or files (or folders) exists. It will return a Boolean with the result.

Test-Path c:\scripts\test.txt

You can do it with wildcards to check if any file exists:

Test-Path c:\scripts\*.wav

You can do work with different items like registry keys:

Test-Path HKCU:\Software\Microsoft\Windows\CurrentVersion


In Depth

Tested With
PowerShell 4.0

Categories: Powershell | Leave a comment

Powershell – Working with devices, partitions and volumes

Windows storage

Disks, partitions and volumes have the following relation:

Let’s focus on every one of them on the following sections.

To list all operating system visible disks type:


You’ll get something like:

Note: If you run Get-Disk without Admin rights you will receive the following error: “Access to a CIM resource was not available to the client”.
If you’re already Admin remember to run powershell console as Administrator or you will receive the same error.
To restrict query results we can pipe the output of Get-Disk to the Where-Object cmdlet.
To get all USB disks you have to type:

Get-Disk | Where-Object –FilterScript [$_.Bustype –Eq “USB”}

Note: ? is alias for Where-Object. So you can type the same command this hay:

Get-Disk | ? –FilterScript [$_.Bustype –Eq “USB”}

To list all system disks type:

Get-Disk | ? IsSystem –eq $True

To clear all partition and volumes from a disk (disk numb 3), type (you will lose all data and the operation can’t be undone):

Clear-Disk 3 -RemoveData

Note: If the disk has a OEM partition in it it’s also necessary to specify the –RemoveOEM parameter

To initialize a disk (disk numb 3) in order to create partitions and volume type:

Initialize-Disk 3

Note: All disks are initialized as GPT (GUID Partition Table) unless you specify differently. To initialize as MBR (Master Boot Record), type:

Initialize-Disk 3 –PartitionStyle MBR

To change disk properties you have to use Set-Disk.
In this case piping the result of the Get-Disk cmdlet and changing all objects (disks) to an online state. Just type:

Get-Disk | ? IsOffline | Set-Disk –IsOffline:$False

To change read-only disk property type:

Get-Disk | ? IsReadOnly | Set-Disk –IsReadOnly:$False

A Disk object can have one or more logical regions called partitions.
To list all partition on all disks, type:


To list all partition of a specific disk (disk numb 3), type:

Get-Partition –DiskNumber 3

To create a new partition (on disk numb 3), type:

New-Partition –DiskNumber 3 –UseMaximumSize -AssignDriveLetter

To list all volumes of a drive letter (drive letter C), type:

Get-Volume –DriveLetter C

To format a volume (drive letter C) with NTFS, type:

Format-Volume –DriveLetter C

The following cmdlet gives to the current session the following drives:
– Windows logical drives, including mapped drives and network shares
– Drives exposed by powershell providers, HKLM, HKCU, Registry drives…
– Drives created with New-PSDrive


Note: Get-PSDrive alias is gdr

Cmdlet used to map a drive to a path.
In this example we create a drive X mapped to folder C:\Scripts:

New-PSDrive -name X -psprovider FileSystem -root c:\scripts

Note: Get-PSDrive alias is ndr or you can use mount

Storage Settings
To see storage setting like disk policy (previously SAN policy), type:


To change a storage setting like new disk policy, type:

Set-StorageSetting –NewDiskPolicy OfflineShared

These are the existing disk policy options:

Policy settings Description
OfflineAll All new disks are left offline by default
OfflineInternal All disks on busses that are detected as internal are left offline as default
OfflineShared All Disks on sharable busses, such as iSCSI, FC, or SAS are left offline by default
OnlineAll All disks are automatically brought online

WMI remains in most cases the primary automation technology for system administration, so Get-WmiObject cmdlet is essential in order to do routine management tasks. And unlike most cmdlets, Get-WmiObject can be run against remote computers.
To obtain information about volumes, type:

Get-WmiObject win32_logicaldisk

To obtain same information remotely but only for drive type 3, type:

Get-WmiObject –query “select * from win32_logicaldisk where drivetype=’3’” –computername “MYSERVERNAME”

The same output data on table format:

gwmi diskpartition –filter “drivetype=’3’” –computername “MYSERVERNAME” | format-table

…most commonly used
To physical disks information use class: win32_logicaldisk class
To see disk partitions information use: win32_diskpartition
To see map of physical disk and partition use class: win32_logicadisktopartition
To see volumes use: win32_volume
To check drives mapped to a computer use: win32_mappedlogicaldisk

In Depth

Tested With
PowerShell 4.0

Categories: Powershell, WMI | Leave a comment

Powershell – Pipeline

Pipeline concept

Pipeline concept, enables the output of one cmdlet to be piped as input to another cmdlet.

Other Shell languages use the pipe concept, but PowerShell is different in that you pipe objects and not their text-representations.

In Powershell pipeline structured .NET objects are passed between stages in the pipeline instead of typically unstructured text. Using objects eliminates the need to explicitly parse text output to extract data.

 Powershell - Pipeline_s001

Powershell uses the pipeline to send a stream of objects, thereby encapsulating all the information and methods within the object in an easily an accessible way.



The current pipeline object; used in script blocks, filters, the process clause of functions, where-object, foreach-object and switch

Commonly used on a pipeline with corresponding cmdlet member property, method or event. Examples: $_.length, $_.name or $_.tostring

Note: To see cmdlet members (in this case for Get-Process cmdlet) type:

Get-Process | Get-Member


Why using?


Generate easier and more readable solutions

You can replace this code

$a = Get-ChildItem *.txt

foreach ($file in $a)


if ($file.length -gt 100)


Write-Host $file.name



With this one

Get-ChildItem *.txt | where {$_.length -gt 100} | Format-Table name


Create complex and useful scripting solutions…

A simple example:

Get-Process ‘

| Where-Object {$_.company -Notlike '*Microsoft*'} ‘

| Format-Table ProcessName, Company

A even simpler example:

Get-Process | Out-Host -Paging


In Depth

Tested With
PowerShell 2.0

Categories: Powershell | Leave a comment

Powershell – Help, Get-Help

PowerShell help
The existing cmdlet to get help regarding Windows Powershell concepts and language is Get-Help.
To display information about Get-Help cmdlet, type:


To display information about all existing Powershell topics (cmdlets providers, functions and scripts), type:

Get-Help *

To display information about all Powershell functions, type:

Get-Help -Category Function

To display basic information about a specific Powershell cmdlet, type (in this example Get-Host cmdlet):

Get-Help -Name Get-Host

Note: The parameter name –name can be omitted as it’s by default the first input parameter for Get-Help cmdlet. The last command could by typed Get-Help Get-Host
Specific or detailed help
To display complete information about a specific Powershell cmdlet (example Get-Host), type:

Get-Help -Full Get-Host

To display basic information but with parameters description and examples about a specific Powershell cmdlet (example Get-Host), type:

Get-Help -Detailed Get-Host

To display detailed information about a specific Powershell cmdlet parameter or parameters (example all parameters of Get-Process), type:

Get-Help Get-Process -Parameter *

To display basic information with examples about a specific Powershell cmdlet (example all parameters of Get-Process), type:

Get-Help Get-Process -Examples

On-line help with Get-Help
To display online, very detailed information about a specific Powershell cmdlet, type (example all parameters of Get-Process):

Get-Help Get-Process -Online

This online help can be used with cmdlets, functions and scripts.
In Depth / References


Tested with

PowerShell 2.0


Categories: Powershell | Leave a comment

SQL Statistics – Importance, how it works, explained

Statistics importance means one thing, PERFORMANCE. Let’s review shortly how this relation occurs.

1== You execute a query

2== The SQL query is passed to the SQL Server query optimizer

3== If an execution plan is already created for this query, it’s used, if not, a new one has to be created

4== Create a new execution plan means deciding if using a index seek, index scan or table scan for a logical search or using hash, merge and nested loop for a inner join or …

5== A number of factors influence when creating a new query execution plan, one of the most important is cardinality estimations. Others are number of CPUs, amount of RAM for example

6== Data distribution statistics on tables and indexes is from where cardinality estimates are obtained. If there were no statistics the optimizer will be forced to simply scan the table or index

7== Bottom line, with good, accurate statistics, query optimizer can get accurate cardinality estimates and create a good execution plan for a query. Final result, brilliant performance J

Note: Cardinality estimations/data distribution also have a big impact on parallelism, join-order and even memory allocation for query requests


Statistics real data

A single set of statistics gathers two different measures about the data, his density and his distribution.



Density shows how many unique values there are within a given column, or set of columns.

To calculate density we can use the following formula:

Density = 1 / Number of distinct values for column(s)

High density values (low distinct values used repeatedly through the column (s) – like a true/false column) will be of less use to the optimizer because it might not be the most efficient way of getting at your data. For example, for a table with high number of records a where clause that uses a bit column will lead the optimizer to use a full table scan instead of an index seek because it’s more efficient.


Data distribution

Data distribution does a statistical analysis of the kind of data a column has.

The statistical analysis is represented as/generates a histogram that’s a visual representation of the distribution of the data.

The histogram defines intervals of data (bins) and then represents data distribution into those intervals. This way you get a fairly good density estimation of data quickly and using few resources.

SQL Server histogram consists of 200 bins (interals of data) represented as 200 rows in a table.

The rows represent the way the data is distributed within the column by showing a pieces of data describing that distribution:

RANGE_HI_KEY Upper bound value of a histogram step i.e. the highest value in the step. The first step is the lowest value for the column in the table.
RANGE_ROWS Number of rows from the sample that fall within a histogram step, excluding the upper bound. By definition, this must be zero in the first sample.
EQ_ROWS Number of rows from the sample that are equal in value to the upper bound of the histogram step
DISTINCT_RANGE_ROWS Number of distinct values within a histogram step, excluding the upper bound.
AVG_RANGE_ROWS Average number of duplicate values within a histogram step, excluding the upper bound.
This is defined as:


How – sample or full scan

As we mention earlier statistics holds information of data distribution in tables and indexes.

Statistics data can be created of two different ways, by sampled or through a full scan.

The sampled approach makes the generation and updates of statistics very fast, but can cause them to be inaccurate.

Sample mechanism randomly reads data through the table and then does calculations to arrive at the values for the statistics. This is accurate enough for most data set, but if you prefer you can make much more accurate statistics by manually updating or creating the statistics with a full scan.


When – automatically or manually

Statistics can be created or updated automatically or manually.



You can create statistics in SQL Server 2008 in a number of different ways, as described below:

        – The optimizer automatically creates single-column nonfiltered statistics as needed as a side effect of optimizing SELECT, INSERT, UPDATE, DELETE, and MERGE statements if AUTO_CREATE_STATISTICS is enabled, which is the default setting

        – There are two basic statements in SQL Server 2008 that explicitly generate the statistical information described above:

                + CREATE INDEX generates the declared index in the first place, and it also creates one set of statistics for the column combinations constituting the index keys (but not other included columns)

                + CREATE STATISTICS only generates the statistics for a given column or combination of columns.

Note: If the CREATE INDEX defines a predicate, the corresponding statistics are created with the same predicate.

In addition, there are several other ways to create statistics or indexes. Ultimately, though, each issues one of the above two commands

        – Use sp_createstatsto create statistics for all eligible columns (all except XML columns) for all user tables in the current database. A new statistics object will not be created for columns that already have a statistics object.

        – Use dbcc dbreindex to rebuild one or more indexes for a table in the specified database.

        – In SQL Server Management Studio, expand the folder under a Table object, right click the Statistics folder, and choose New Statistics.

        – Use the Database Engine Tuning Advisor to create indexes.

Note: The default statement when rebuilding an index generates new statistics automatically. This can be changed with the use of a parameter. When reorganizing indexes no statistics updates are made.


Update automatically

Statistics are updated automatically by default, and these are the three thresholds that automatically cause an update (the next time they are needed – to be more specific this process marks them as invalidated):

        – The table size has gone from 0 to >0 rows

        – The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then

        – The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered

The default behavior presented above has some variations with filtered indexes and filtered statistics. When applying traceflag 2371 in SQL Server 2008R2 SP1 and in SQL Server 2012 you are able to set dynamic value instead of the fixed 20%, very helpful for larger databases in order to force a more frequent statistics update.


Update manually

You can also update statistics manually this way:

        – Running sp_updatestats that uses a cursor to walk through all database statistics. The process based on changes to column modification counters (colmodctrs) to update the statistics. Previous SQL Server 2008 if the rowmodctr flag was > 0

        – Running UPDATE STATISTICS and so you can target individual statistics by providing his name. With this process you can specify the full scan option


Where are statistics stored

Statistics are stored in database.

That’s the reason why a backup/restore preserves statistics but bear in mind that this doesn’t guarantee the creation and use of a similar execution plan. The reason is that you surely have other factors that are not exactly the same, such as cpu number, memory, SQL version…


Check existing statistics

To displays the current distribution statistics for the specified target on the specified table:

DBCC SHOW_STATISTICS ( table_name | view_name , target )

[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]

< option > :: =


The output generated:

        – Header: which contains meta-data about the set of statistics

        – Density: Which shows the density values for the column or columns that define the set of statistics

        – Histogram: The table that defines the histogram laid out above

In Depth

Categories: SQL performance, SQL statistics | Leave a comment

Powershell – Console

Open PowerShell console
It depends of version installed (and OS) so I post a few options:

Powershell - Console_s002

== Starts > All Programs (for PS2: Accessories > Windows PowerShell) and select Windows PowerShell
== Start > Run and type powershell
== On Search box (Start > Search) type powershell and then select Windows Powershell below program’s group
Check PowerShell version
You probably have more than one version installed so before continuing check which one you opened by executing the following cmdlet:


The above method is more reliable because it shows the engine version, nevertheless, if you want the current host version execute:


Customize PowerShell console home directory
Let’s create a home directory and underneath it, inside one or more folders, organize our own PowerShell scripts (PSS).
The same home directory can be used to deploy other PSS such as application deployments, audit scripts…
== Create an accessible directory on the file system (in my case C:\PSScripts)
== Create a PowerShell desktop shortcut or pin PowerShell to start menu
== Right click on the shortcut and open Properties
== On Start text box (Shortcut tab) type the following:


Powershell - Console_s003

== Click OK and you’re ready to go
Customize PowerShell console size color and text
We’ll post about PowerShell profiles but for now let’s customize the console for first use (It’ll be the default to everybody):
== Right click on the PowerShell shortcut and open Properties or click on the console window upper-left-corner control box and select Properties
== On Layout tab adjust Windows size area. New values are:

Width: 150
Height: 80

== Check if Screen buffer size width is equal to windows size area width
== On Font tab select (default values):

Size: 8x12

== On Colors tab select:

. for screen text
Red: 1
Green: 36
Blue: 86
. for screen background
Red: 255
Green: 255
Blue: 255
. for pop-up text
Red: 0
Green: 128
Blue: 128
. for pop-up background
Red: 238
Green: 237
Blue: 240

Note: Just in case you want to go back these are colors tabs default values:

. for screen text
Red: 238
Green: 237
Blue: 240
. for screen background
Red: 1
Green: 36
Blue: 86
. for pop-up text
Red: 0
Green: 128
Blue: 128
. for pop-up background
Red: 255
Green: 255
Blue: 255

PowerShell console editing features
Major editing features are:

Keys Editing Operation
Left and right arrows Moves the cursor left and right through the current command line
Ctrl+Left and right arrow Moves the cursor left and right through the current command line, one word at a time
Up and down arrows Move up and down through the list of recently typed commands
Page up and down Jumps to the first and last command in the history buffer
Insert Switches between insert and overstrike text-entry modes
Delete Deletes the character at the current cursor position
Backspace Deletes the character immediately preceding the current cursor position
Home Jumps to the beginning of the command line
End Jumps to the end of the command line
F7 Shows history buffer. Use the up and down arrows to select a command, and press Enter to execute it
Tab Auto-completes command-line sequences. Use the Shift+Tab sequence to move backward through a list of potential matches

In Depth / References


Tested with

PowerShell 2.0


Categories: Powershell | Leave a comment

Powershell – Importance, how it works

Key elements
== It’s a command-line shell and scripting language designed from scratch to be an optimal environment for modern Windows operating system
== It’s task based
== Built on top of the .NET Framework common language runtime (CLR) and the .NET Framework
== PowerShell commands are object based not text based, they accept and return .NET Framework objects
== All shell commands use the same command parser
== PowerShell has syntax features and keywords that are very similar to those used in the C# programming language, because Windows PowerShell is based on the .NET Framework
== PowerShell provides an extensible architecture that allows new functionality to be added to the shell in the form of cmdlets, providers, type extensions, format metadata…
== PowerShell commands are referred to as Cmdlets
== Cmdlets are instances of .NET Framework classes, they are not stand-alone executables
== Cmdlets are record-oriented because they process a single object at a time
== Cmdlets are written in verb-noun format to show specific functions to performed against an object
== You can define aliases for commands to customize the environment to how you like to work
In Depth / References


Tested with

PowerShell 4.0
PowerShell 2.0


Categories: Powershell | Leave a comment

ORA SQL – Change Oracle SQL Developer Java Development Kit (jdk) executable file

ORA SQL – Change Oracle SQL Developer Java Development Kit (jdk) executable file_s001When you start SQL Developer for the first time you are prompted to select the Java Development Kit executable file.

On subsequent uses of SQL Developer it will run the selected jdk. If you want to change the specific jdk follow these steps:

1== Go to SQL Developer installation folder and open the file .\sqldeveloper\bin\sqldeveloper.conf

2== Replace SetJavaHome entry by setting a new jdk file path. Something like:

SetJavaHome C:\Program Files\Java\jdk1.7.0_21

3== Save file and restart SQL Developer

Tested With
Oracle Sql Developer x64
Java Developer Kit 1.7.0 21

Categories: SQL Developer | Leave a comment

Blog at WordPress.com.