Head start to PowerShell 5.0 Experimental Preview

Yes! Windows Management Framework 5.0 Preview Experimental – July 2014 has been available and full of goodies for IT Pro(s) and DevOp(s). I’m hoping you all realized PowerShell is your number ONE tool for automation productivity in your Infrastructure environment(s). Please, take the time to learn it and don’t be left behind.

You all heard Microsoft initiative to have everyone start migrating from Windows Server 2003 to Windows Server 2012 R2 for either your ‘On Premise’ and/or ‘MS Cloud’ environment(s). Definitely, PowerShell can provide the help in completing these tasks.

In order to use **PowerShell Version 5.0 Experimental, you are required to use Windows Server 2012 R2 and/or Windows 8.1. Also, prior to installing the preview, you will need to install a hotfix. This is all documented in the download link. This why is important to read the download instruction before proceeding with the installing the preview.

This new preview provide you with lots of enhancements:

  1. DCS (Desired State Configuration) – Bugs fixes and new modules.
  2. OneGet – simplify how you discover and install software packages.
  3. PowerShellGet – new way to discover, install, and update PowerShell Modules.
  4. Network Switched cmdlets.
  5. PowerShell ISE – DCS authoring improvements
  6. Language enhancements – Develop classes. (You need to check this one out!)

**NOTE:  This is a Preview and it doesn’t mean that all you see will be included in the RTM version. 

Read the Preview Documentation for more information.


Important Resources

Below are the necessary resource links to get you started with the latest materials about PowerShell Version 5.0: (Good reading material)

  1. PowerShell DSC Resource Kit Wave 5 Arrives: http://blogs.msdn.com/b/powershell/archive/2014/07/17/powershell-dsc-resource-kit-wave-5-arrives.aspx
  2. TechNet Gallery – resources for IT professionals – Gallery listing of DCS: http://gallery.technet.microsoft.com/site/search?f%5B0%5D.Type=Tag&f%5B0%5D.Value=DSC%20Resource%20Kit%20Wave-5&f%5B0%5D.Text=DSC%20Resource%20Kit%20Wave-5
  3. DSC Resource Kit (All Modules): http://gallery.technet.microsoft.com/DSC-Resource-Kit-All-c449312d
  4. Holiday Gift – Desired State Configuration (DSC) Resource Kit Wave-1: http://blogs.msdn.com/b/powershell/archive/2013/12/26/holiday-gift-desired-state-configuration-dsc-resource-kit-wave-1.aspx
  5. OneGet (v5.0 April Preview): http://blogs.msdn.com/b/powershell/archive/2014/04/04/windows-management-framework-v5-preview.aspx
  6. Getting Started with DSC: http://technet.microsoft.com/en-us/library/dn249912.aspx
  7. PowerShellGet (v5.0 May Preview): http://blogs.msdn.com/b/powershell/archive/2014/05/14/windows-management-framework-5-0-preview-may-2014-is-now-available.aspx
  8. Recent PowerShell Version 5.0 and DCS articles at the PowerShell Team Blog site: http://blogs.msdn.com/b/powershell/ \
  9. Also, recent DCS Articles at PowerShell.org site: http://www.powershellmagazine.com/tag/dsc/
  10. Check out the Microsoft Virtual Academy: http://www.microsoftvirtualacademy.com/Studies/SearchResult.aspx?q=PowerShell


Getting to know the MS Cloud technology

Yes! Start learning about MS Cloud, better known as Microsoft Azure. If you got a MSDN subscription then you got to use your free Azure credit which ranges between $150 to $200. There are no excuses. Just try it!

Now, you have the ability to build Windows Server(s) and/or Windows 8.1 desktop. YES!! A virtual machine Windows 8.1 Client. So, you won’t stay behind and keep up with today technologies.

Check out the Microsoft Cloud OS blog site: http://blogs.technet.com/b/privatecloud/

PowerShell is mention All Over the Cloud!!


PowerShell Extracting SQL Server Data into Excel

I recently helped someone with providing a solution using PowerShell to extract data from SQL Server into an Excel file. We all know that  we could use SSIS to provide the means to do this but there are some situations you may want to use scripting instead. So, found one script I did back in 2009 that will do such a thing. The funny thing is, when I looked at it, I realized that for the Excel part I had a  lot of unnecessary extra code and it could be improved greatly. So, here’s the updated version.

This *script will do the following steps:
1. Connect to SQL Server and get the SQL Server data.
2. Build the Excel file with columns heading and data.
3. Save the Excel file and Close/Terminate Excel process.

*Note: This script is PowerShell Version 2.0 compatible.

Getting you SQL Data

This script uses the ‘System.Data.SqlClient.SqlConnection’ which you can run on any machines without SQL Server installed. In this example the connection string is a trusted ‘Windows Authentication’. Then, I’m using the Here-String @”..”@ to insert the T-SQL script I want to execute against SQL Server. Keep in mind, this connection string can be change to use SQL Server Authentication.

## ---------- Working with SQL Server ---------- ##

## - Get SQL Server Table data:
$SQLServer = 'SQLServer01\MSSQLInstance01';
$Database = 'Database1';
$SqlQuery = @'
Select top 10
from Database1.dbo.Table1
where [Field4] = 'X001'

## - Connect to SQL Server using non-SMO class 'System.Data':
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = `
"Server = $SQLServer; Database = $Database; Integrated Security = True";

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.CommandText = $SqlQuery;
$SqlCmd.Connection = $SqlConnection;

## - Extract and build the SQL data object '$DataSetTable':
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;
$DataSet = New-Object System.Data.DataSet;
$DataSetTable = $DataSet.Tables["Table"];

Buidling the Excel file

This block of code will generate the Excel file consuming the SQL data object ‘$DataSetTable’. The result of the SQL DataSet will be use to automatically create the columns heading and data rows. This is the heart of the script where the magic happen.

## ---------- Working with Excel ---------- ##

## - Create an Excel Application instance:
$xlsObj = New-Object -ComObject Excel.Application;

## - Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0;
$xlsWb = $xlsobj.Workbooks.Add();
$xlsSh = $xlsWb.Worksheets.item(1);

## - Build the Excel column heading:
[Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;

## - Build column header:
[Int] $RowHeader = 1;
foreach ($ColH in $getColumnNames)
$xlsSh.Cells.item(1, $RowHeader).font.bold = $true;
$xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName;

## - Adding the data start in row 2 column 1:
[Int] $rowData = 2;
[Int] $colData = 1;

foreach ($rec in $DataSetTable.Rows)
foreach ($Coln in $getColumnNames)
## - Next line convert cell to be text only:
$xlsSh.Cells.NumberFormat = "@";

## - Populating columns:
$xlsSh.Cells.Item($rowData, $colData) = `
$rowData++; $ColData = 1;

## - Adjusting columns in the Excel sheet:
$xlsRng = $xlsSH.usedRange;

Saving and Terminating Excel

Now that I’ve build the Excel sheet, I need to save the file, quit and terminate Excel. And, Yes! It’s needed to terminate/kill the Excel process because this process will remain active even if when exiting/closing the PowerShell session.

## ---------- Saving file and Terminating Excel Application ---------- ##

## - Saving Excel file - if the file exist do delete then save
$xlsFile = `

if (Test-Path $xlsFile)
Remove-Item $xlsFile

## Quit Excel and Terminate Excel Application process:
$xlsObj.Quit(); (Get-Process Excel*) | foreach ($_) { $_.kill() };

## - End of Script - ##

SQL Data to Excel file
SQL Data to Excel file

Additional Note

One thing to understand, this process will work with small datasets. So, the more data you extract the slower it may take to build the Excel file. This is why is important to test the script(s) and look at other best possible solution. Maybe it’s better to use SSIS (SQL
Server Integration Services) but it doesn’t hurt try other technologies.

More Discover PowerShell – How about Help with PowerShell Variables?

Finally got this last function working in order and created a new module: “DiscoverPowershell” with all three Show-Help* functions:

1. Show-HelpPowerShellCommand – Meant to select one module at the time and then multiple commands.
2. Show-HelpAboutPowerShellTopic - Multi-select can be applied.
3. Show-HelpPowerShellObject (New) – Multi-select can be applied.
Check out the first two functions on my previous blog.

In the module I tweak is just a little bit but the functionality stay the same. Basically, you can select multiple Item(s) in the Out-Gridview and display the results.

Here’s the link to download and install the module folder “DiscoverPowerShell“: https://onedrive.live.com/redir?resid=7FD7082276C66197!30947&authkey=!AKkr99vUvdqDKCw&ithint=file%2c.zip

*Note: Module requirements: PowerShell V4 (or greater) on Windows 7, Windows 8.1 and Windows 2012 R2.

Here’s the third function: Show-HelpPowerShellObject

function Show-HelpPowerShellObject
Function to list all PowerShell Variable objects in your current session.

This function will display in the 'Out-Gridview' a list of all PowerShell Variable objects in your
session. Press the Crtl-Key and select the PowerShell variable you want to Display information.

.PARAMETER No Parameter(s) required.


Param ()

[Array] $selItem = $null; [Array] $myObj = $null;
While ($selItem -eq $null)
$selItem = Get-Variable `
| Select name, @{ Label = 'objectType'; Expression = { $_.GetType(); } }, value `
| Sort-Object Name | Out-GridView -PassThru -Title "Select the PSVariable Object";
If ($selItem -eq $null) { break };
[Array] $myObj = $null;
ForEach ($myObj in $selItem.Name)
((get-variable $myObj).Value) | get-member `
| Out-GridView -Title ('Displaying Selected PSObject - $' + "$myObj");
If ($myObj -eq $null) { break };
$selItem = $null;
Copy/Paste code
Copy/Paste code
Multi-select items
Multi-select items
Selected items displayed and back to list
Selected items displayed and back to list

It’s all about having fun with PowerShell!!

FLPSUG Last meeting May 15th on “PowerShell Azure SQL Database”

FLPSUG Lync meeting Thursday May 15 2014 – Session: “PowerShell Working w/Microsoft Azure SQL Database” Speaker Maximo Trinidad (MVP) and Co-hosting Adnan Cartwright (MVP).

In this presentation I will be covering how to subscribe to Azure, setup PowerShell to connect to your subscription, use scripting to create a SQL Database Server and then use SMO with PowerShell push data to your SQL Azure tables. All this using PowerShell scripting plus showing some editor, scripting techniques, and tips to avoid issues when working and setting Azure with PowerShell. (live demo with Windows Azure). Thanks to Adnan for assisting me in this meeting.

Here’s the Powerpoint presentation, demo scripts, and link to the recorded video. The video is the full and unedited meeting: https://onedrive.live.com/redir?resid=7FD7082276C66197!31206&authkey=!AIbxWUhyiUth7Dg&ithint=file%2c.zip

Video link: http://youtu.be/9-fMxXY7DcQ

Co-host Adnan Cartwright link: http://www.fisg.us/


Get a hold of PowerShell (WMF) v5.0 Preview

Windows Management Framework 5.0 May 2014

Again, this version is only available for Windows 8.1 and Windows Server 2012 R2.

On PowerShell  v5.0 Preview just released on May 14th.  This version has no problem with the Azure PowerShell cmdlets installation.  Check out what’s hot in version 5.0 Prewview:  DSC (Desired State Configuration), OneGet and PowerShellGet modules.

Blog: http://blogs.msdn.com/b/powershell/archive/2014/05/14/windows-management-framework-5-0-preview-may-2014-is-now-available.aspx

Download at: http://www.microsoft.com/en-us/download/details.aspx?id=42936


Also, Check out the new PowerShell “Script Browser & Script Analyzer 1.2” for ISE and Windows:

This new ISE Add-On will create two shortcuts: one to open the Script Browser in ISE and the other as an individual Windows application.

Script Browser Icons
Script Browser Icons
Script Browser Windows Application
Script Browser Windows Application
Script Browser in ISE
Script Browser in ISE

Go and get them!

Quick Rundown – Microsoft Azure SQL Database Server and PowerShell

Azure SQL Database 
1. Web and Business editions are no longer available. Now there’s Basic, Standard, and Enterprise editions. (New)
2. There is a limit of 6 SQL Database Servers and up to 150 databases per subscription.
3. Create database from 1 GB up to 500GB of storage.
4. Database Throughput Unit(DTU) Service performance levels available: (New)

  • DTU Service Level:
    1  – Basic
    5 - S1
    25 - S2
    100 - P1
    200 - P2
    800 - P3


For more information about Azure SQL Database Throughout Units Service, check out Scott Kline and Tobias Ternstrom on this link: http://channel9.msdn.com/Series/Windows-Azure-Storage-SQL-Database-Tutorials/Scott-Klein-Video-02

Quick tips when testing Azure SQL Database Server:
1. There’s no need to specify a storageaccount.
2. Start with “Basic” or “Standard” Service level.
3. Start with  1GB in size for testing.
4. Current Azure Portal version will create random database server names.

Observations on the “Preview” Azure Portal on Azure SQL Datbases:
1. It show the ability to group databases.
2. You can provide a SQL Database Server name (not random).

Upcoming Azure Portal (Preview)

Assign a Group and Name your Database Server
Assign a Group and Name your Database Server

Windows Management Framework 5.0 May 2014

On PowerShell  v5.0 Preview just released on May 14th.  This version has no problem with the Azure PowerShell cmdlets installation.

Blog: http://blogs.msdn.com/b/powershell/archive/2014/05/14/windows-management-framework-5-0-preview-may-2014-is-now-available.aspx

Download at: http://www.microsoft.com/en-us/download/details.aspx?id=42936

Azure PowerShell cmdlets updated

Azure PowerShell cmdlet was updated to version 0.8.2 on 05/12/2014 with 390 commands.AzurePosh_082_05152014



It’s all about discovering and exploring with PowerShell

Nothing like discovering and exploring what’s already at your fingertip. All you need to get started with PowerShell is already loaded in your system. There are plenty help documentation to keep you busy for awhile.

It’s time to get serious with PowerShell.

Checkout this year TechEd PowerShell session with Don Jones and Jeffrey Snover:

Now, using two known commands: Out-Gridview and Get-help we can search for help information in a flash.  Here I’m providing two custom PowerShell functions that might help you in exploring and learning about PowerShell:

1. Show-HelpPowerShellCommand - Select the module and then which command(s) to display thedocumentation.
2. Show-HelpAboutPowerShellTopic - Select the topic(s) to display the documentation.

Just copy/paste the code into a script file then load them in either PowerShell console or ISE.  To execute the command just type the function name: Show-HelpAboutPowerShellTopic or Show-HelpPowerShellCommand.

function Show-HelpAboutPowerShellTopic
<# .SYNOPSIS Function to list all PowerShell About_* topic from the selected list. .DESCRIPTION This function will display in the 'Out-Gridview' a list of all PowerShell About_* topics installed in the system. Then, you can select one or multiples topic(s) available. Press the Crtl-Key and select the command(s) you want to get the help documentation. .PARAMETER No Parameter(s) required. .EXAMPLE Show-HelpAboutPowerShellTopics #>

Param ()

[Array] $Global:selAbout = $null;
While ($Global:selAbout -eq $null)
[Array] $Global:selAbout = $null;
$Global:selAbout = ((Get-Help About_* | Select-Object Name) `
| Out-GridView -PassThru -Title "Listing all PowerShell About_* topics");

if ($Global:selAbout -eq $null) { break };
foreach ($item in $Global:selAbout) { Get-Help $item.Name -ShowWindow };
$Global:selAbout = $null;

Check the image samples of Show-HelpAboutPowerShellTopic:

Loading Show-HelpAboutPowerShellTopics
Loading Show-HelpAboutPowerShellTopics
Selecting multiple topics
Selecting multiple topics
Viewing results and back to topics listing
Viewing results and back to topics listing
function Show-HelpPowerShellCommand
<# .SYNOPSIS Function to list all cmdlets from the selected module(s). .DESCRIPTION This function will display in the 'Out-Gridview' a list of all PowerShell modules installed in the system. Then, you can select one of the modules to list all commands available in another 'Out-Gridview' window. Press the Crtl-Key and select the command(s) you want to get the help documentation. .PARAMETER No Parameter(s) required. .EXAMPLE Show-HelpPowerShellCommand #>

	Param ()
	[Array] $selItem = $null; [Array] $selCmdlet = $null;
	While ($selItem -eq $null)
		$selItem = get-module -ListAvailable | Select-Object -Unique name, version, path `
		| Sort-Object Name | Out-GridView -PassThru;
		If ($selItem -eq $null) { break };
		[Array] $selCmdlet = $null;
		$selCmdlet = (Get-Command -Module $selItem.Name) | Sort-Object Name `
		| Out-GridView -PassThru -Title "Module: $($selItem.Name) Total cmdlets: $(((Get-Command -Module $selItem.Name) | Sort-Object Name).count)";
		$selItem = $null;
		ForEach ($cmd in $selCmdlet) { Get-Help $cmd.Name -ShowWindow; };
		If ($selCmdlet -eq $null) { break };
Loading Show-HelpPowerShellCommand
Loading Show-HelpPowerShellComman
Select one module first
Select one module first
Select cmdlet(s)
Select cmdlet(s)
View help and exit list
View help and exit list

Please notice that these functions will stay active until you click on ‘Cancel’ in either of the select Topics or Module list.

I’m hoping this will make it fun to use.

Latest Azure PowerShell version 0.8.0 has a preview module included

In my previous blog I mention the latest version has two module.  Well, I was wrong!  It’s important that you take the time to read the documentation and pay attention to what’s trending in the social network (such as twitter).  I did notice someone tweet about the new Azure module included in this last released: the “AzureResourceManager“.  And here’s where the fun begin.

There are three modules:

  1. Azure
  2. AzureProfile
  3. AzureResourceManager (Preview)

When executing the “Get-Module -ListAvailable” command you will notice that only 2 will show up: Azure and AzureProfile.


This new module is a “PREVIEW“, and the documentation states “The Azure and Azure Resource Management modules are not designed to be used in the same Windows PowerShell session. To make it easy to switch between them, we have added a new cmdlet, Switch-AzureMode.”  Here’s the link to the documentation: http://msdn.microsoft.com/en-us/library/jj554330.aspx

This means that in order to use the new commands from the “AzureResourceManager” you need to run the “Switch-AzureMode” which will prevent you from using the Azure commands such as Get-AzureVM on the same PowerShell session.  Now, keep in mind that you can always open another session to keep working with the Azure module commands.

PowerShell with module Autoload On
PowerShell with module Autoload On

At the same time, If you need to use the command “Import-Module Azure“, you’ll notice that it will give an error telling that it can’t find the module. The trick here is, if you haven’t turned off the PowerShell Module Autoload option, the commands will be available.  Here’s a TechNet link on how to Turn-Off the PowerShell Autoload module (not recommended): http://blogs.technet.com/b/heyscriptingguy/archive/2013/02/20/powertip-turn-off-powershell-module-autoload.aspx

Import-Module Azure Error
Import-Module Azure Error

This is a bug that have been recently reported to the Microsoft Azure PowerShell team.  By default, PowerShell have the Module Autoload “ON” and you will be able to list all the Azure module commands.

PowerShell Autoload Azure commands
PowerShell Autoload Azure commands

So, you can still work with your Azure PowerShell commands and use the “Switch-AzureMode” on another session.

Use the "Switch-AzureMode" to preview the new AzureResourceManager cmdlets

Now you can continue to work with PowerShell Azure command and check out is new (Preview) module AzureResourceManager.

List of the Preview Module AzureResourceManager Cmdlets:Azure_ListPreview_06


Happy PowerShelling!

Problem installing the latest Azure PowerShell Cmdlets with WMF v5.0 Preview

If you’re experiencing problems installing the latest Azure PowerShell cmdlets (0.8.0 – 04/03/2014)  after installing Windows Management Framework v5.0 Preview (WMF) then don’t worry.

You can go back the uninstall the WMF v5.0 Preview, then proceed to download/install the recent *Azure PowerShell cmdlets, and go back to re-install the WMF v5.0 Preview.  It will back be working like nothing happened.


Looks like there are two Azure modules in this update:

  1. Azure
  2. AzureProfile

*Note: I’m this steps may need to be repeated until WMF v5.0 becomes RTM.