If you want to install SQL Server 2005 on Windows Server 2008 (formally Longhorn server), you need to be aware of the process.

Note: This was worked out on Windows Server Longhorn Beta 3 (build 6001), so procedures may change by the time Windows Server 2008 RTM’s.

Prerequisites

If you’re planning on installing SQL Reporting on Windows Server 2008, you need to add the IIS Role through server manager and you need the following service components installed:

• Common HTTP Features
  • Static Content
  • Default Document
  • HTTP Redirection
  • Directory Browsing
• Application Development
  • ASP.Net
  • ISAPI Extension
  • ISAPI Filters
• Security
  • Windows Authentication
• Management Tools
  • IIS Metabase
  • IIS 6 WMI

For more information I suggest that you read William Li’s blog post on the subject. It’s referring to an older version of Longhorn Server, but is still pretty sound.

Installation

Installing SQL Server 2005 is a little painful now-a-days; the process goes like this if you do a complete install like I did:

1. Install SQL Server 2005 - Click “Run Program” to every prompt (a total of three for my install. 2 for SQL and 1 for Visual Studio because it’s installed as part of the client install)
2. Install SQL Server 2005 Service Pack 2 (KB921896)
3. Install SQL Server 2005 post SP2 hot fix rollup (KB933508)
4. Install SQL Server 2005 Book Online update for February 2007 (optional)
5. Install critical update for SQL Server 2005 SP2 (KB934458)
6. Install Visual Studio 2005 Service Pack 1 (KB918526)
7. Install Visual Studio 2005 SP1 Vista extensions (KB929470)

Windows update will take care of all of the updating for you if you prefer not to do it by hand or by script, but it takes a while. Personally, I prefer the scripted approach because it’s more re-usable.

What would be really nice is a SQL Server 2005 R2 with all of this integrated so you don’t have to run 6 update packages just to get SQL updated!

I’m currently downloading the March CTP of Visual Studio Orcas. There’s two versions this month, a Virtual PC image and for the first time a full install. Combined this all totals 11.9GB and 20 files to download!

So how long will it take to download on my 4Mbs Internet Connection?

1> New-TimeSpan -seconds $(11.9gb / (4mb / 8))

Days : 0
Hours : 6
Minutes : 46
Seconds : 11
Milliseconds : 0
Ticks : 243710000000
TotalDays : 0.282071759259259
TotalHours : 6.76972222222222
TotalMinutes : 406.183333333333
TotalSeconds : 24371
TotalMilliseconds : 24371000

Of cause, that’s the theoretical maximum of my line, in reality I tend to get about 480KBs:

2> New-TimeSpan -seconds $(11.9gb / (480kb))

Days : 0
Hours : 7
Minutes : 13
Seconds : 16
Milliseconds : 0
Ticks : 259960000000
TotalDays : 0.30087962962963
TotalHours : 7.22111111111111
TotalMinutes : 433.266666666667
TotalSeconds : 25996
TotalMilliseconds : 25996000

Not bad, it’ll e done by breakfast J

I often compute this on a calculator, but PowerShell offers us a better alternative, but typing it into the command line every time will get old very fast. So I’ve just knocked together a little script to do this for me.

Usage: Calc-DownloadTime.ps1 <downloadSize> [connectionSpeed]

Both parameters are int64’s and if connectionSpeed is not supplied, it will try to use a global variable named $networkSpeed. Should the $networkSpeed global variable not exist, the script will throw an exception.

3> .\Calc-DownloadTime.ps1 11.9gb 480kb
Your download will take 07:13:16 to complete

4> .\Calc-DownloadTime.ps1 11.9gb
Usage: calc-downloadTime.ps1 <downloadSize> [connectionSpeed]
If connectionSpeed is not supplied, global variable $networkSpeed will be used.
Please specify a connectionSpeed or set $networkSpeed variable to use as default
At D:\PsScripts\Calc-DownloadTime.ps1:13 char:7
+ throw <<<< “Please specify a connectionSpeed or set `$networkSpeed variable to use as default”;

5> $networkSpeed = 480kb

6> .\Calc-DownloadTime.ps1 11.9gb
Your download will take 07:13:16 to complete

Because I do this a lot and my connection speed is generally static, I’ve added this variable to my profile so it’s there every time I load PowerShell. I’ve seen some people connect that PowerShell profiles should be a light as possible, so if you prefer you can customise the script to default to your connection speed.

1> .\Calc-DownloadTime.ps1 11.9gb
Your download will take 07:13:16 to complete

I can still override this default though should I need to.

2> .\Calc-DownloadTime.ps1 11.9gb $(100mb/8) # My Local LAN
Your download will take 00:16:15 to complete

3> .\Calc-DownloadTime.ps1 11.9gb $(54mb/8) # My WiFi connection
Your download will take 00:30:05 to complete

4> .\Calc-DownloadTime.ps1 11.9gb $(33kb/8) # My phone (GPRS)
Your download will take 35.00:16:23 to complete

Ouch! I better not do this over GPRS!

Download the script here: Calc-DownloadTime.ps1

Mamood had a problem! He needed to get some information on a bunch of machines he manages and like a good follower of the church of PowerShell, he fired up his favourite blue and white command line tool (which he for some reason changes to be black with green text) and set about trying to obtain the information required.

Somewhere along the way he got stuck, so somehow he managed to find his Office Communicator window in amongst what must have been at least 50 others including several terminal server client sessions (each containing at least another 20 windows) and contacted me. He needed to perform a nslookup on an IP Address and grep the resolved host name for his information.

I sent him back the following three functions (cobbled together on the spur of the moment):

function Ping-Host([string]$hostName) {
$pinger = new-object system.Net.NetworkInformation.Ping;
$pinger.Send($hostName);
}

function Resolve-HostByAddress([string]$ipAddress) {
return [System.Net.Dns]::GetHostByAddress($ipAddress);
}

function Resolve-HostByName([string]$hostName) {
return [System.Net.Dns]::GetHostByName($hostName);
}

I then told him to save them in a script called networkTools.ps1 and then Dot-source them into his session and have a play to see if they do what he wanted:

[D:\PsScripts]
1> . .\networkTools.ps1

[D:\PsScripts]
2> resolve-hostByName “localhost”
HostName Aliases AddressList
——– ——- ———–
machine1.cpatinliteral.net {} {127.0.0.1}

[D:\PsScripts]
3> resolve-hostByAddress “192.168.8.5″
HostName Aliases AddressList
——– ——- ———–
machine1.cpatinliteral.net {} {192.168.8.5}

[D:\PsScripts]
4> “machine1″, “machine2″, “machine3″ | % { resolve-hostByName $_ }
HostName Aliases AddressList
——– ——- ———–
machine1.captainliteral.net {} {192.168.8.5}
machine2.captainliteral.net {} {192.168.8.3}
machine3.captainliteral.net {} {192.168.8.8}

He said they were spot on and went away happy – until a little while later. He was struggling to tie in all the information he had obtained into a single report (formatted table) and wondered if I had any ideas. Of cause I did, but I didn’t know if they would work! I spend a lot of time piping objects to Get-Member and see a lot of extras provided by the Extendible Type System that PowerShell provides and wondered if I could use this to make a struct of sorts…

[D:\PsScripts]
5> $machine = New-Object System.Object

[D:\PsScripts]
6> Add-Member -InputObject $machine -MemberType NoteProperty -name “HostName” -Value “”

[D:\PsScripts]
7> $machine | gm

TypeName: System.Object

Name MemberType Definition
—- ———- ———-
Equals Method System.Boolean Equals(Object obj)
GetHashCode Method System.Int32 GetHashCode()
GetType Method System.Type GetType()
ToString Method System.String ToString()
HostName NoteProperty System.String HostName=

[D:\PsScripts]
8> $machine.hostName = “machine2″

[D:\PsScripts]
9> $machine

HostName
——–
machine2

Looking good – I’ve managed to create a basic object and add an extra property (HostName) to it, assign to this new property and then output is back to the console – and I’ve done all this without going near the C# compiler. This is new for me!

Lets add some more properties:

[D:\PsScripts]
10> Add-Member -InputObject $machine -MemberType NoteProperty -name “IpAddress” -Value “”

[D:\PsScripts]
11> Add-Member -InputObject $machine -MemberType NoteProperty -name “MacAddress” -Value “”

[D:\PsScripts]
12> $machine | gm

TypeName: System.Object

Name MemberType Definition
—- ———- ———-
Equals Method System.Boolean Equals(Object obj)
GetHashCode Method System.Int32 GetHashCode()
GetType Method System.Type GetType()
ToString Method System.String ToString()
HostName NoteProperty System.String HostName=machine2
IpAddress NoteProperty System.String IpAddress=
MacAddress NoteProperty System.String MacAddress=

[D:\PsScripts]
13> $machine.IpAddress = $(resolve-hostbyname $machine.HostName).AddressList[0]

[D:\PsScripts]
14> $machine
HostName IpAddress MacAddress
——– ——— ———-
machine2 192.168.8.3

This might just work! Lets wrap it in another function that we’ll append to the end of networkTools.ps1:

function New-MachineObject($hostName = “”, $ipAddress = “”, $macAddress = “”, $alive = “”) {
    $machine = New-Object System.Object;
    Add-Member -InputObject $machine -MemberType NoteProperty -name “HostName” -Value $hostName;
    Add-Member -InputObject $machine -MemberType NoteProperty -name “IpAddress” -Value $ipAddress;
    Add-Member -InputObject $machine -MemberType NoteProperty -name “MacAddress” -Value $macAddress;
    Add-Member -InputObject $machine -MemberType NoteProperty -name “Alive” -Value $alive;
    return $machine;
}

Dot-source this into the current session again and we’re up and running.

[D:\PsScripts]
15> . .\networkTools.ps1

[D:\PsScripts]
16> New-MachineObject “localhost” “127.0.0.1″ “0×00000000″ $false

HostName IpAddress MacAddress Alive
——– ——— ———- —–
localhost 127.0.0.1 0×00000000 False

Mamood’s total scenario was that he had a list of machine names (in a .csv file), and needed to get the fully-qualified domain name for the host along with the IP address, corresponding MAC Address and then test to see if it was responding to ping requests. Actually, I added in the test part as the only way I have of getting the MAC Address is WMI and there’s no point trying to connect to the device if it’s down is there? I had a quick look at the System.Net namespace, but couldn’t see how to get a remote machines MAC address – please, if you know how to do it within .NET, let me know.

Before we solve the complete problem however, yet another function (or two) is required for networkTools.ps1 (and it needs dot-sourcing into the current session again):

function Get-MacAddressForHost($hostName, $ipAddress) {
    $networkAdapters = get-wmiobject Win32_NetworkAdapterConfiguration -computername $hostName -Filter “IPenabled = ‘True’”;
foreach ($adapter in $networkAdapters) {
        if ($adapter.IPAddress -eq $ipAddress) {
    return $adapter.MACAddress;
        }
}
}

function Get-AliveStatus($hostName) {
    $response = Ping-Host $hostName;
    if ($response.Status -eq [System.Net.NetworkInformation.IPStatus]::Success) {
        return $true;
    }
    else {
        return $false;
    }
}

A little crude perhaps, but works and gets around the issue of virtual network adapter from programs like VMWare and Windows Mobile 5 devices J

It’s worth noting that you may need some credentials to connect to the remote machine with WMI – Mamood certainly did! If so, just append –credential $creds the first line of Get-MacAddressForHost and then type the following into your session to get PowerShell to prompt you for the username and password that you’ll use to connect to remote machines. Don’t worry, your password is stored in a Secure String, so it shouldn’t be possible to read it by typing $creds – doesn’t stop a call into the .NET classes to get the clear-text version though. So when you’re finished, set it to $null ($creds = $null;)

[D:\PsScripts]
48> $creds = Get-Credential

And now the final solution…

[D:\PsScripts]
36> . .\networkTools.ps1

# Create a CSV file, just like Mamood has
[D:\PsScripts]
38> “MachineName”, “machine1″, ” machine2″, ” machine3″, ” machine4″ > machineList.csv

# Get machine information from DNS
[D:\PsScripts]
40> $machines = @();

[D:\PsScripts]
41> import-csv machineList.csv | % { $machineInfo = resolve-hostByName $_.MachineName; $machines += New-MachineObject $machineInfo.Hostname $machineInfo.AddressList[0] }

# Ping each host to get alive status
[D:\PsScripts]
43> $machines | % { $_.Alive = Get-AliveStatus $_.HostName }

# Get MAC Address for each alive host
[D:\PsScripts]
45> $machines | ? { $_.Alive } | % { $_.MacAddress = Get-MacAddressForHost $_.HostName $_.IpAddress }

# Finally, output as a table (or you could export to CSV if you prefer)
[D:\PsScripts]
47> $machines | ft

HostName IpAddress MacAddress Alive
——– ——— ———- —–
machine1.captainliteral.net 192.168.8.3 00:04:FF:0B:23:B2 True
machine2.captainliteral.net 192.168.8.8 False
machine3.captainliteral.net 192.168.7.1 False
machine4.captainliteral.net 192.168.8.5 00:16:17:0E:23:B2 True

And that’s that.

This is an interesting procedure because it required multiple passes the get all the required data. Sure, I could have put this into one (long) line and there are probably other, more concise ways of achieving the same task (please post comments if you have one), but what I wanted to show here is that by using the Extensible Type System, you can create simple struct like objects to store information in a typed fashion.

I for one had not played much with the type extensions possible in PowerShell, but I’m going to go a read all about them now (and find out what I did wrong in the process J) Jeffrey Snover recently blogged about this feature of PowerShell and points to you Jim Truher’s blog post explaining it all a lot further and a lot better then I.

If you want my original version of networkTools.ps1, download it here.

For more information on dot-sourcing and running scripts, read my previous entry on the subject here.

The earlier functions in networktools.ps1 use the .NET Framework, read more about what the syntax means here.

In the end Mamood got what he needed and the next time he has to produce the same report (probably tomorrow, or at least by next week), he’ll have it done In a couple of seconds.

One of the reasons why PowerShell is so good and so easy to sell to people is because of .NET. PowerShell is a .NET 2.0 application and it uses .NET to achieve virtually everything it does – but how can users leverage to power of .NET from within PowerShell?

How do I create an object?

There are basically two way to get an object to work with in PowerShell, either call new-object or get one returned by calling something.

Things return objects?

[D:\PsScripts]
1> get-process

Handles NPM(K) PM(K) WS(K) VM(M) CPU(s) Id ProcessName
——- —— —– —– —– —— — ———–
256 12 77716 70736 558 0.91 5680 powershell

What’s actually going on here? It turns out that the get-process cmdlet returns .NET objects, in the above case it’s an array of System.Diagnosticts.Process objects and that allows us to work with them the same as if we had created them in code.

You can work with objects by either keeping a reference to them or by passing them down the pipeline to another cmdlet, object or piece of script. If you want to know what you can do with an object, call get-member:

[D:\PsScripts]
2> $processes = get-process
[D:\PsScripts]
3> $processes.Length
90
[D:\PsScripts]
4> get-member -InputObject $processes

TypeName: System.Object[]

Name MemberType Definition
—- ———- ———-
Count AliasProperty Count = Length
… … …

An object array? Where are the process objects?

[D:\PsScripts]
5> get-member -InputObject $processes[0]

TypeName: System.Diagnostics.Process

Name MemberType Definition
—- ———- ———-
Handles AliasProperty Handles = Handlecount
Name AliasProperty Name = ProcessName
… … …

An you can achieve the same by passing the object down the pipeline:

[D:\PsScripts]
6> Get-Process | Get-Member

TypeName: System.Diagnostics.Process

Name MemberType Definition
—- ———- ———-
Handles AliasProperty Handles = Handlecount
Name AliasProperty Name = ProcessName
… … …

What if I don’t want an object of type System.Diagnostics.Process?

The other way to get an object is to call the new-object cmdlet.

[D:\PsScripts]
7> $date = New-Object DateTime
[D:\PsScripts]
8> $date

01 January 0001 00:00:00

And if you need to specify arguments to the constructer, just pass them in after the type:

[D:\PsScripts]
9> $date2 = New-Object DateTime 2007, 2, 18
[D:\PsScripts]
10> $date2

18 February 2007 00:00:00

How to I call a method on my object?

Calling methods is the same in PowerShell as it is in C# (object.method()):

[D:\PsScripts]
11> $date2.ToShortDateString()
18/02/2007

Properties are also the same (object.Property):

[D:\PsScripts]
12> $date2.Year
2007

Tab complete works with object methods and properties by the way, making it easier to get at what you want.

What type is my object?

The easiest way to determine an objects type is to just call the GetType() method of the object;

[D:\PsScripts]
13> $date2.GetType()

IsPublic IsSerial Name BaseType
——– ——– —- ——–
True True DateTime System.ValueType

[D:\PsScripts]
14> $processes.GetType()

IsPublic IsSerial Name BaseType
——– ——– —- ——–
True True Object[] System.Array

[D:\PsScripts]
15> $processes[0].GetType()

IsPublic IsSerial Name BaseType
——– ——– —- ——–
True False Process System.ComponentModel.Component

What about Statics?

Statics can be invoked by using the following syntax: [Type]::Property or [Type]::Method(). For example:

[D:\PsScripts]
16> [DateTime]::DaysInMonth(2007, 2)
28

[D:\PsScripts]
17> [DateTime]::Now

18 February 2007 20:16:55

OK, but I have this assembly with types in it I’d like to call…

Before we talk about loading a custom assembly into the PowerShell Application Domain, you’re going to want to know if you need to or not. The following shows how to get all the loaded assemblies:

[D:\PsScripts]
18> $psAppDomain = [AppDomain]::CurrentDomain

[D:\PsScripts]
19> $psAppDomain.GetAssemblies()

GAC Version Location
— ——- ——–
True v2.0.50727 C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\mscorlib.dll
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PowerShell.ConsoleHos…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e08…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\System.Management.Automation\1….
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\System.Configuration.Install\2….
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PowerShell.Commands.M…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PowerShell.Security\1…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PowerShell.Commands.U…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c56193…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\System.Management\2.0.0.0__b03f…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\System.DirectoryServices\2.0.0….
True v2.0.50727 C:\WINDOWS\assembly\GAC_64\System.Data\2.0.0.0__b77a5c561934…
False v2.0.50727 C:\Program Files (x86)\PowerShell Community Extensions\pscx.dll

The best way to load an additional assembly into the PowerShell App Domain is to use the System.Reflection.Assembly class. It exposes several statics methods for loading assemblies. You can find details of these methods on MSDN, they all begin Load – I suggest that they be studied so you know which methods to call for what type of assembly:

[D:\PsScripts]
20> [System.Reflection.Assembly]::Load(”System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”)

GAC Version Location
— ——- ——–
True v2.0.50727 C:\WINDOWS\assembly\GAC_64\System.Web\2.0.0.0__b03f5f7f11d50a3a\System.Web.dll

[D:\PsScripts]
21> [System.Reflection.Assembly]::LoadFrom(”SolveSudoku.dll”)

GAC Version Location
— ——- ——–
False v2.0.50727 d:\PsScripts\SolveSudoku.dll

[D:\PsScripts]
22> $psAppDomain.GetAssemblies()

GAC Version Location
— ——- ——–
True v2.0.50727 C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\mscorlib.dll
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PowerShell.ConsoleHos…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e08…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\System.Management.Automation\1….
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\System.Configuration.Install\2….
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PowerShell.Commands.M…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PowerShell.Security\1…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PowerShell.Commands.U…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c56193…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\System.Management\2.0.0.0__b03f…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\System.DirectoryServices\2.0.0….
True v2.0.50727 C:\WINDOWS\assembly\GAC_64\System.Data\2.0.0.0__b77a5c561934…
False v2.0.50727 C:\Program Files (x86)\PowerShell Community Extensions\pscx.dll
True v2.0.50727 C:\WINDOWS\assembly\GAC_64\System.Web\2.0.0.0__b03f5f7f11d50…
False v2.0.50727 d:\PsScripts\SolveSudoku.dll

What’s next?

Well, that’s all you need to know to really get going with calling .NET from within Windows PowerShell. Using the information provided here and armed with what-ever SDK you need, you can use PowerShell to script anything that exposes a .NET API. This includes SharePoint and your own custom .NET application.

IT Pro’s – you better start asking developers for better class documentation so you can maintain and support bespoke applications better.

Whilst invoking .Net from the command line is powerful, it’s no substitute for cmdlets and providers. That said, application developers need to start creating cmdlets before they can be used. In the meantime use .NET API’s to script what couldn’t be scripted before J

I’ve been using PowerShell now for a few months and it’s become a standard feature on my desktop. Having used it for a while I have some observations to share:

IDE

PowerShell doesn’t have an IDE shipped as part of the product, so the out-of-the-box experience of scripting it limited to nothing more fancy than Notepad. My experience though has been that an IDE of some sorts really is required for working with anything but the most basic .ps1 script files. Additionally, I’ve had bugs in one-liners that pipe together several command and full IDE support for pipelines would be a real winner – imagine being able to set a breakpoint between piped commands.

The main features that I would like an IDE for PowerShell to provide are:

• Colourisation of text – Different types and operators get different colours when viewed in an IDE, so you can see what you’re working on more clearly.
• Full Debugging capabilities – Including breakpoints and watch windows. Without these basic tools, scripts get littered with write-Debug commands all over the place, it kind of reminds me of days gone by when I’ve been debugging ASP3 web applications – doable, but not as easy as it should be.
• IntelliSense – tab completion is a godsend, but you need to know what’s there before it really works, I’m constantly piping to get-member to find out what an object supports.
• Code-snippets – I know this is a very Visual studio term, but in essence I’m talking about templated commands rather than scripts. For example, a simple getNamedProcesses snippet could contain: Get-Process [ProcessName], where the user just overwrites the [ProcessName] block with the desired information and away it runs. Again, linked with a visual menu so one can browse code-snippets would be the ultimate.
  This could be a very powerful tool for PowerShell as it provides another way to reuse functionally.
• Source code Control – Creating a corporate or personal script library will take time and require multiple scripter’s/developers working on the same solution. This is a situation that cries out for source code control and solution management and not just a bunch of files on a share somewhere.

I know there are a few IDE’s for PowerShell already and over the next couple of week I intend to install them all to see how they are, but what I’d really like is Visual studio to understand PowerShell, or at least .ps1 files. I use Visual Studio every day so I’m very comfortable in it, but I understand that the vast majority of PowerShell users are going to be IT Pro’s who do not have Visual Studio at hand – but that’s no reason not to use it for this purpose. Of cause, a lighter-weight version would be required for the non-development activities that occur on production servers inside the data-centre – I really would not like to be the one who says “Install VS on all those servers please.”

Right Tool, Right Job

PowerShell is a new tool for the toolbox, but unlike other tools it isn’t another item to carry around and only get out when required. PowerShell is much more like a multi-function screwdriver – the ones with a billion different mutli-changeable bits to achieve anything (bad simile I know and not to be taken literally). It’s a tool that does away with several others and like all multi-purpose tools has some drawbacks and isn’t suited for every job. Fortunately, it interfaces with tools that can be used like .NET or native .exe’s.

One area where I found PowerShell suffers is for performance. Normally performance is not necessarily at the top of the features list – especially when we’re talking about admin scripting, which is what PowerShell is designed for. But sometimes you want a script to run as quickly as possible, you need a script to run as quickly as possible. Take the Sudoku solving script I posted a couple of weeks ago, I wanted to make it perform. True, I did not take advantage of any unique-to-shell-features (such as Pipelines) and as a result those features (which are normally what I use PowerShell for) added an overhead. The correct tool for that job was .NET itself, invoked through PowerShell of cause.

Maybe it’ll get optimised in a future version and my only purpose for listing it here is to try and make people aware of the choices that are available and to know the pros/cons of each so the best tool can be chosen for the particular job at hand. I really don’t see performance as a problem for this tool, so don’t worry too much about it – it’s quick enough for 99.999% of the things I do in it.

Because I can create .NET classes or call static methods at the command line and interact with them, I will be able to throw functionality into .NET classes and have .DLL’s as part of my personal script library without having the overhead and formality of writing an entire cmdlet/snapin.

Operators

PowerShell has good support for a wide array of operators, but it doesn’t support all that are provided to other .NET languages like C#. Examples are %= (modulus assignment), << (left-shift),
>> (right-shift) and ?: (conditional). Jeffrey Snover has blogged about ?: on the PowerShell Team blog.

This isn’t a big problem as you can wrap these operators up in managed code and invoke them as statics if required – perhaps the first class I should add to my .dll-enhanced script library?

I hope that future releases of PowerShell will provide additional operator support, but right now, the main operators (the ones used daily) are supported.

Remote Machine Support

One of the IT Pro’s where I work, who is very pro-scripting has commented that PowerShell’s lack of remote machine working ability is a problem for him. This is a fair comment, PowerShell is set-up for local working and very little support is provided for remote connectivity and management of computers. I say very little because PowerShell has great support for WMI, ADSI and .NET – so you can do all this, it’s just not a simple as the following examples:

get-process –machineName server.domain.local

stop-service –name serviceName –machineName server.domain.local

The IT Pro mentioned above isn’t a developer and I think that WMI and .NET are not the simplest things to pickup and learn – they both have massive object models – so I think that the community is going to come out with these sorts of tools more.

To mitigate this, I know that PowerShell Remoting addresses this issue by providing a service on a remote machine that allowing a client to connect – like telnet – and even goes one further by providing extra cmdlets that you can use to get the information over the network to the client computer. It’s nicely thought out and it’s another one that I’m going to add to my //ToInvistigateFurther: list.

Looking ahead to Longhorn Server, administrators will be able to expose PowerShell as a remote application – imagine a Remote Desktop Session, but for only one application instead of the entire desktop. I’m not sure I like this approach though, I want tools that don’t encourage administrators to use mstsc and don’t require them to start a desktop session on a server.

Finally, I don’t mind this as I believe that all changes to servers in data centres should be run through a central controller so the command, output and executing user can all be logged and queried later – how else are you going to know what happened on any given machine? I’m a big fan of Automated Deployment Services (ADS) and this is the administration model it promotes. As an aside, you can run PowerShell commands and scripts from ADS by calling powershell.exe as the command and –command “command list” as a parameter. For example:

powershell.exe –command “& { gps }”

powershell.exe –command “& { C:\PsScripts\dosomething.ps1 }”

Type powershell -? for more details from the command line.

Documentation

I like the way that the get-command and get-help cmdlets are used to get help, but it’s not always easy to find the help topic I’m interested in – Example: param – can’t find any help on it using get-help, I had to resort to Windows Search to find the xml file it’s in – perhaps a simple search-help cmdlet might work? Again, I think that the community will provide this over time.

Whilst on the subject of documentation, one thing that really bugs me with the x64 release of PowerShell on a machine with Office installed is that clicking on any of the start menu documentation shortcuts results in Word not being able to find the requested document because it’s a 32bit process and PowerShell only installed the .rft files to the 64bit version of System32. Still, you can open them in WordPad or just copy them to the correct place in the SysWow64 folder.

To Finish

Phew, what a lot to type and I’m sure that I’m going to have forgotten loads that will come to me over time, so I may end up doing another one of these posts in the future.

I hope that this doesn’t read like a rant, because I didn’t intend it to be one. If you haven’t gathered already, I really like PowerShell and think that it has massive potential. Given time, I’m sure that we’ll see more and more support for it in other application – perhaps Microsoft will add it to their Common Engineering Criteria for server based applications?

I mention the community a lot in the above essay and I am part of that community. Right now, I’m working on some network tools for PowerShell that should make life a little easier.

Edit: Thanks to /\/\o\/\/ for point out that I missed that PowerShell does actually have the modulus assignment operator (%=) - missed that one, D’Oh!

I spent a lot of time automating SharePoint when the company I work for installed SharePoint Portal Server 2003, so I was quite disappointed when SharePoint version 3 was released without PowerShell support – after all, Exchange 2007 managed it and it’s part of the Office 2007 Server suite, so why didn’t the other server products?

On a positive note though, I did find that stsadm.exe had been extended to now support a lot more operations:

[C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN]
3> .\stsadm.exe –help

Usage:
stsadm.exe -o <operation> [<parameters>]
stsadm.exe -help [<operation>]

Operations:


activatefeature
addalternatedomain
addcontentdb
addpath
addpermissionpolicy
addsolution
addtemplate
adduser
addwppack
addzoneurl
authentication
backup
backuphistory
binddrservice
blockedfilelist
canceldeployment
changepermissionpolicy
copyappbincontent
createadminvs
creategroup
createsite
createsiteinnewdb
createweb
databaserepair
deactivatefeature
deleteadminvs
deletealternatedomain
deleteconfigdb
deletecontentdb
deletegroup
deletepath
deletepermissionpolicy
deletesite
deletesolution
deletetemplate
deleteuser
deleteweb
deletewppack
deletezoneurl
deploysolution
deploywppack
disablessc
displaysolution
email
enablessc
enumalternatedomains
enumcontentdbs
enumdeployments
enumgroups
enumroles
enumservices
enumsites
enumsolutions
enumsubwebs
enumtemplates
enumusers
enumwppacks
enumzoneurls
execadmsvcjobs
export
extendvs
extendvsinwebfarm
forcedeletelist
getadminport
getproperty
getsitelock
geturlzone
import
installfeature
listlogginglevels
localupgradestatus
managepermissionpolicylevel
migrateuser
provisionservice
refreshdms
refreshsitedms
registerwsswriter
removedrservice
removesolutiondeploymentlock
renameserver
renameweb
restore
retractsolution
retractwppack
scanforfeatures
setadminport
setapppassword
setconfigdb
setlogginglevel
setproperty
setsitelock
setworkflowconfig
siteowner
spsearch
spsearchdiacriticsensitive
syncsolution
unextendvs
uninstallfeature
unregisterwsswriter
updateaccountpassword
updatealerttemplates
updatefarmcredentials
upgrade
upgradesolution
upgradetargetwebapplication
userrole

For information about other operations and parameters,
use “stsadm.exe -help” or “stsadm.exe -help <operation>”

But how useful are these commands? Consider the example of enumsites – this command lists all Site Collections or a given Web Application.

[C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN]
5> .\stsadm.exe -help enumsites

stsadm.exe -o enumsites
-url <virtual server url>
-showlocks
-redirectedsites

[C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN]
7> .\stsadm.exe -o enumsites -url http://localhost

<Sites Count=”1″>
<Site Url=”http://wss” Owner=”domain\administrator” SecondaryOwner=”domain\user” ContentDatabase=”WSS_C
ontent_Default” StorageUsedMB=”0.5″ StorageWarningMB=”0″ StorageMaxMB=”0″ />
</Sites>

Great, it gives me the output as XML, so I suppose that I can assign it to a variable within PowerShell and perform some XML queries on it - Not quite what I had in mind though when I thought about how PowerShell could automate SharePoint.

Fortunately though the SharePoint Team do an excellent API that exposed 100% via .NET – so PowerShell is right at home here thanks to its ability to just consume all things .NET, including the System.Reflection.Assembly class which can be used to load an assembly into the PowerShell Application Domain, so it can be called directly from the command line. T load Microsoft.SharePoint.dll, all we need do is the following:

[C:\PsScripts]
3> [System.Reflection.Assembly]::Load(”Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c”);

GAC Version Location
— ——- ——–
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SharePoint\ 12.0.0.0__71e9bce111e9429c\Microsoft.SharePo…

Now we can call into the SharePoint object model to get the Application that we wish to enumerate the Site Collections for:

[C:\PsScripts]
4> $webApplication = [Microsoft.SharePoint.Administration.SPWebApplication]::Lookup(”http://localhost”)

[C:\PsScripts]
5> $webApplication.Sites.Count
1

[C:\PsScripts]
6> $webApplication.Sites | ft url, Owner, SecondaryContact

Url Owner SecondaryContact
— —– —————-
http://wss domain\administrator domain\user

How cool is that? The PowerShell Team have given us a full scripting tool for SharePoint because the SharePoint Team wrote a very good API and we can work with object, not strings or XML.

As a final demo, I’ll show you that PowerShell can write through the SharePoint API as well as read from it:

[C:\PsScripts]
3> . .\Load-sharePoint.ps1;

GAC Version Location
— ——- ——–
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SharePoint\ 12.0.0.0__71e9bce111e9429c\Microsoft.SharePo…
True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SharePoint.Workflows\ 12.0.0.0__71e9bce111e9429c\Microso…

[C:\PsScripts]
4> $sc = new-object Microsoft.SharePoint.SPSite “http://localhost”;

[C:\PsScripts]
5> $rootWeb = $sc.Rootweb;

[C:\PsScripts]
6> write-host $rootWeb.Title;
WSS Root Web

[C:\PsScripts]
7> $rootWeb.Title = “PowerShell has change the title!”;

[C:\PsScripts]
8> $rootweb.update();

[C:\PsScripts]
9> write-host $rootWeb.Title;

Conclusion

Job Done! Not only have we got the same functionality that stsadm.exe provides us for the enumsites operation, but we’ve got the ability to change the WSS installation on the fly – Now that’s what I mean when I talk about automating SharePoint!

When we get round to deploying MOSS 2007 at my place of work, I’ll probably end up recreating all of stsadm.exe’s operations into PowerShell functions. But for now, I’ve created a PowerShell script (Load-SharePoint.ps1) that will load a couple of the SharePoint dll’s and then create a few functions:

• Get-SiteCollection – Returns the site collection at the specified URL.
• Get-SiteCollections – Returns an array of Site Collections for the given Web Application, same as demo above
• Get-Sites – Returns an array of sites for the given Site Collection

For details on running .ps1 scripts, please see my previous post - PowerShell Basics – Running Scripts.

If you want to explore the SharePoint object model (with or without PowerShell), you’re going to want to get the WSS 3 SDK and/or the MOSS 2007 SDK as it’s the only place to get a full description of the classes that you’re going to work with.

This post is only the tip of the iceberg, I’ve been using PowerShell and the MOSS 2007/WSS 3 API to upload InfoPath form templates, create sites/lists and to try things out before I commit them to C# assemblies. As a developer, PowerShell saves me from the write, compile, run loop that I usually get into when I’m learning an API.

One of the comments I had on a post I did last week was about how to run scripts. It’s a fair point, somebody new to PowerShell will find it a little odd that they can’t just run a script found on the Internet – I find it reassuring.

On a newly installed PowerShell system, running a script will result in something like the following:

[D:\PsScripts]
1> & D:\PsScripts\test-scriptExecution1.ps1
File D:\PsScripts\test-scriptExecution1.ps1 cannot be loaded because the execution of scripts is disabled on this syste
m. Please see “get-help about_signing” for more details.
At line:1 char:38
+ D:\PsScripts\test-scriptExecution1.ps1 <<<<

Understanding the Execution Policy

The reason for this is the Execution Policy in place on the machine prevents all scripts from running by default. There are 4 Execute Policies defined in PowerShell, according to the help files, they are:

• Restricted – Does not load configuration files or run scripts. “Restricted” is the default.
• AllSigned – Requires that all scripts and configuration files be signed by a trusted publisher, including scripts that you write on the local computer.
• RemoteSigned – Requires that all scripts and configuration files downloaded from the Internet be signed by a trusted publisher.
• Unrestricted – Loads all configuration files and runs all scripts. If you run an unsigned script that was downloaded from the Internet, you are prompted for permission before it runs.

To control execution policies, PowerShell ships with a couple of cmdlets Get-ExecutionPolicy and Set-ExecutionPolicy. They basically do what they say on the tin:

[D:\PsScripts]
2> Get-ExecutionPolicy
Restricted

[D:\PsScripts]
3> Set-ExecutionPolicy Unrestricted

[D:\PsScripts]
4> Get-ExecutionPolicy
Unrestricted

[D:\PsScripts]
5> & D:\PsScripts\test-scriptExecution1.ps1
this output is from test-scriptExecution1.ps1

Using an Unrestricted Execution Policy will allow you to execute any script. I would not recommend that you run with this setting on a production server in your data centre though – the next script from hackmybox.com you download could include a command to uninstall IIS from your web server!

For more information about setting execution policies and signing scripts (something that I’m not going to go into here) try the following help commands:

[D:\PsScripts]
7> Get-Help Set-ExecutionPolicy | more

[D:\PsScripts]
8> Get-Help about_signing | more

Invoking Scripts

Scripts are text file with a .ps1 extension. When a script is invoked, the commands inside the script are executed. The scope that the script executes in is local to the script, so functions and variables will not be accessible to the calling scope.

To invoke a script you use the call/invoke operator (&) and either the local or the full path to the script:

[D:\PsScripts]
9> & D:\PsScripts\test-scriptInvoking1.ps1 #Execute with full path
This output is from test-scriptInvoking1.ps1. Arguments: No arguments passed

[D:\PsScripts]
10> & .\test-scriptInvoking1.ps1 #Execute with local path
This output is from test-scriptInvoking1.ps1. Arguments: No arguments passed

The call/invoke operator (&) isn’t always needed though, .\test-scriptExecution.ps1 works just fine, providing you’re in the same location as the script.

To pass arguments to the script, just invoke it with a space-separated list of arguments after the script file:

[D:\PsScripts]
18> & .\test-scriptInvoking1.ps1 “Argument 1″
This output is from test-scriptInvoking1.ps1. Arguments: Argument 1

Dot Sourcing Scripts

Dot Sourcing a script alters the way the script’s scope is created. When a script is Dot Sourced, it executes under the current scope and not a local one. Consider the following:

[D:\PsScripts]
19> type test-scriptInvoking2.ps1
# internal function to the script
function internalScriptFunction() {
write-host “this is from internalScriptFunction”;
}

# call function from within script
internalScriptFunction;

[D:\PsScripts]
20> & D:\PsScripts\test-scriptInvoking2.ps1    #Invoke script
this is from internalScriptFunction

[D:\PsScripts]
21> dir function: | where-object { $_.Name -match “internal” }    #Check for function in current scope

[D:\PsScripts]
22> . D:\PsScripts\test-scriptInvoking2.ps1    #Dot Source script
this is from internalScriptFunction

[D:\PsScripts]
23> dir function: | where-object { $_.Name -match “internal” }    #Check for function in current scope

CommandType Name Definition
———– —- ———-
Function internalScriptFunction write-host “this is from internalScriptFunction”…

[D:\PsScripts]
24> internalScriptFunction    #Invoke function directly from current scope
this is from internalScriptFunction

Invoking Scripts from the Internet

OK, so we know how to run scripts and control execution scope, but why the following when a script is run from the Internet?

[D:\PsScripts]
25> & D:\PsScripts\test-invokeScriptFromInternet.ps1

Security Warning
Run only scripts that you trust. While scripts from the Internet can be useful, this script can potentially harm your
computer. Do you want to run D:\PsScripts\test-invokeScriptFromInternet.ps1?
[D] Do not run [R] Run once [S] Suspend [?] Help (default is “D”):

It seems that the PowerShell Team thought about this scenario and made PowerShell take note of files that IE marks as coming from an untrusted or partially-trusted source such as sites Internet Explorer classes as coming from the Internet Zone. When PowerShell comes across such a script file, it gives you a chance to say “no thanks, don’t run that script!”

What’s really neat though is that you can copy the file around, rename it and even edit it and PowerShell will still know the file came from the Internet and present you with the extra challenge/response prompt – very nice feature.

To Finish

Well, that’s all you need to know about executing scripts in PowerShell – I’m happy to know that it’s not easy to run a script, and the error messages do guide you towards getting the Execution Policy right for your situation.

There is just one last thing that should be mentioned to complete the story – The PowerShell Team released an Admin Template (.adm file used in Group plicy) for PowerShell that can be found on the Microsoft Downloads site. The template only contains one setting that can be controlled via Group Policy – Execution Policy.

This will allow you to enforce a policy on machines where you don’t want people to just execute scripts that aren’t signed. What’s really good about this is that PowerShell will not let the user override this from the command line:

PS C:\> Set-ExecutionPolicy unrestricted
Set-ExecutionPolicy : Windows PowerShell updated your local preference successfully, but the setting is overridden by t
he group policy applied to your system. Due to the override, your shell will retain its current effective execution po
licy of “AllSigned”. Contact your group policy administrator for more information.
At line:1 char:20
+ Set-ExecutionPolicy <<<< unrestricted

Of cause, this isn’t full proof – there’s nothing to stop me opening a script in notepad and copy/pasting the contents to a PowerShell session – but is should be enough to remind people that they are on a restricted server.

Update: The PowerShell Team has just posted here details of how to manage the execution policy on Windows Vista. Their post completes the story detailed in this post. If you run Windows Vista and run PowerShell scripts, you need to read it.

I’m moving my blog away from Windows Live Spaces to CaptainLiteral.NET, so if anybody has subscribed to my feed, then you’d best update it to point at http://feeds.feedburner.com/CaptainLiteral. I’ve also set-up a FeedBurner thing for my RSS, so people won’t be hit by changes in the future.

Why Captain Literal? The guys as work call me Captain Literal because lunchtime conversations’ can go something like this:
Dan: “You can’t reboot a computer on a plane, it’ll crash,” followed by a hand movement to indicate that a plane would fall from the sky.
Mark (aka Captain Literal): “That’s why life-critical systems such as planes have multiple fail-safe’s on them. The space shuttle for example has multiple computers and they all vote on what’s going to happen. You could reboot one of those with no problems and the shuttle wouldn’t spiral off into outer-space.”
So that’s it, I have been nick-named Captain Literal and now Mark really is CaptainLiteral.NET.

Before I go out on a rant and document why I’m moving away from Spaces, I first need to thank Ashleigh (http://www.nakedcleaner.com) for firstly, hosting CaptainLiteral.NET. Secondly, for forcing me to setup CaptainLiteral.NET. And thirdly, for spending time last night actually setting up CaptainLIteral.NET – thanks Ashleigh.

And now for the fun bit – why am I leaving Windows Live Spaces?

Live Spaces has some things that merely annoying – these are the things that one can live with. But it also has some things that are very annoying – things that, when realised, encourage you to drop Windows Live Spaces and go look for some other blog provisioning service or friend to host for you.

The merely annoying stuff (in no particular order):

• The statistics page is so badly laid out, it’s next to meaningless. For example, every page begins with the name of the blog the stats page chops the page title, so all I see if somebody viewed a page on my blog followed by … - very useful!.
• You have to read the refer URL to work out what people searched for to find you on the stats page.
• You can pay to get rid of adverts by subscribing to the hotmail advanced package, but don’t get any other features for doing so (i.e. being able to change the page layout more than anybody else.) for somebody like myself, who doesn’t even have a hotmail account, this is never going to work!
• Trackbacks don’t seem to work like they should.

But the killer, the one that got me to move blog was that fact that you can’t upload non-image files! So when I posted Sudoku vs. PowerShell last week and wanted to upload 3 simple text files, I couldn’t! again, Ashleigh came to the rescue and allowed me to host the files on his other blog (http://www.f1-blog.co.uk) – thanks again Ashleigh.

Anyway, now that’s off my chest, I hope to see you over at CaptainLIteral.NET – where I intend to blog more often as I’ve realised that I actually have quite a lot to say in the technology space. I will not be posting personal posts as I really don’t have an interesting life unlike professionals (aka celebrities.)

Fill in the form below, and I will get back to you. 

I’ve been obsessed since Adrian (ps1.soapyfrog.com) posted his Sudoku solving PowerShell script a couple of weeks ago. When I first read his post on the subject, I pulled down his script and ran it - at the time he didn’t know how long it would take to solve the harder of the two puzzles he posted - after about an hour PowerShell had crashed on my computer! Since then Adrian has posted saying that his script takes 1.2 days to solve his harder puzzle!

Having peeked at his script I quickly discovered that it works on a brute-force principle (i.e. if you don’t know what number you should have in a square, guess), this seemed to work well for the easier of his samples (solved in less than a second). I decided that I’d see if I could do better and learn a few things along the way. And so started my obsession with implementing a speedy Sudoku solver in PowerShell – this shouldn’t have taken two weeks, but work, life and a couple of other things got in the way. I swear, I don’t know how people play with technology and constantly post about it.

To start with, I had no idea how to solve a Sudoku puzzle with a computer, yet alone in PowerShell – Enter a trusty search engine to the rescue. I quickly found Andrew’s Sudoku solver. Now, what’s interesting about Andrew’s site is that he not only solves Sudoku, he shows you step by step how to do it and he’s published over 30 different strategies for solving Sudoku, complete with pictures. J

Armed with details of how to solve Sudoku, I set about scripting it and making it perform better than the couple of minutes it took when I first solved the problem. In the end I got the script to solve Adrian’s hard puzzle in 12 seconds – not bad! For a comparison I ported the script into C# and got it to solve the same puzzle in less than 0.1 seconds. Now, I know that PowerShell isn’t designed for processing performance, but rather admin performance, but it’s important to know the limits of a piece of software so you can get the right tool out of the toolbox when you need it. The C# port was written as a static and I just invoked it directly from within PowerShell and has the exact same output as from a .ps1 script – I could just have easily written this as a cmdlet.

Fortunately, I only had to implement a few of the “basic” strategies described on Andrew’s site to solve the harder puzzle – some of the advanced strategies didn’t look like they’d be fun to implement. I’ve taken this script as far as I want to take it and learnt a lot about PowerShell and the way it works. My lessons and observations will form the thesis of a blog post to follow in the next few days.

In the meantime, here’s my solve-sudoku script, Adrian’s original simple puzzle and his original hard puzzle for you to play with. Just execute the script specifying the puzzle to solve as an argument (.\solve-Sudoku.ps1 profile-puzzle.txt)