3 takeaways from SQLSaturday in Oslo

SQLSaturday just took place in Oslo on Sep 1st. Here is what I learned.
-By Flemming Löth, Deputy System Manager, SQL Server.

Quick way to name your NICs in Windows Servers

If you, like me, manage many servers, it’s essential to name network adapters in a way that makes it easy to troubleshoot issues when they arise.

In complex networks with thousands of servers and all servers connected using multiple paths a consistent naming standard is very important!

PowerShell and the cmdlets available in Windows Server makes naming adapters a breeze. The servers we usualy deploy have built in four (4) port network adapters. We like to name the Windows NICs the same as is the default in Linux; eth0, eth1, etc.

In the following example we name the adapters eth0, eth1, eth2 and eth3 in Windows. The NIC with the lowest MAC address gets the name eth0 etc. (If you prefer to to start naming adapters from eth1 change the variable $NICs to 0):

$NICs = -1
Get-NetAdapter Etherne* | Sort-Object MacAddress | % { Rename-NetAdapter -InterfaceAlias $_.InterfaceAlias -NewName eth$NICs }

PowerShell really makes life easy 😉

Basic inventory of HyperV virtual machines using PowerShell

Here at Basefarm we operate at a large scale with thousands of servers running for our customers. Quite often a customers asks for a list of machines with various properties for each machine.

Most of the time the customer want this information in an simple format so that they can use it internally. In this blogpost I will show how you can get the information about memory, CPU count etc for a set of Hyper-V machines from Virtual Machine Manager via PowerShell.

First off, start a PowerShell command line and load the PowerShell Snap-In for Virtual Machine Manager.

Add-PSSnapin -Name Microsoft.SystemCenter.VirtualMachineManager

Now we can work with the commands made available to us by the Snap-In, if you want to find all the commands that are available issue:

Get-Command -Module Microsoft.SystemCenter.VirtualMachineManager

So let’s begin by loading information about all our machines from the VMM host into a variable named $VMs

$VMs = Get-VM -VMMServer hyperv-vmm01.sth.basefarm.net

What the above command does is to load all of the VMs on the host HYPER-V-01.mydomain.com into the variable $VMs. This means we will only do one call to the server which avoid generating unnecessary load.

Now let’s check how many machines we have:


And now that we know we have machines to query, let’s find out what attributes exists (things we can get into our output)

$VMs | Get-Member -MemberType Property

For example, to find all macines that are powered off:

$VMs | where { $_.Status -eq 'PowerOff' } | select VMHost, name , Memory, CPUCount , Status

The above example adds some complexity to the command, but it is to filter so we only see machines that have the status is ‘PowerOff’.

Now let’s get what we wanted from the beginning, a list of machines for a specific customer. The list should include name of the VM host, VM name, memory, number of CPUs and current status.

$VMs | where { $_.Name -Match 'CUST*' } | select VMHost, name , Memory, CPUCount , Status

This will list all machines who’s name begins with ‘CUST’. So we now have found what we wanted!

But instead of copying & pasting this we want to write the result to a CSV file so we can send that to the customer. Let’s make that easier by getting the output of the above command into a variable named $result

$result = $VMs | where { $_.Name -Match 'LFO*' } | select VMHost, name , Memory, CPUCount , Status

Now our ‘report’ is stored in the $result variable and we can use standard PowerShell to export it to a CSV file:

$result | Export-Csv -NoTypeInformation -Delimiter ';' .\report.csv

Now our report is available in a CSV file on the file ‘report.csv’ (in the current directory)

A very basic way of getting your Hyper-V inventory out!

Default = PowerShell

In a earlier blog post I wrote about how to generate passwords using PowerShell. Here comes another PowerShell advice for you.

Now that Windows Server 2012 will be officially available many servers will be installed as ‘core’ servers. That is in itself a very good thing. The bad thing about it is that Microsoft have set the default command Shell to be CMD.EXE. Nothing wrong with that per se but these days administrators should go PowerShell all the way. If you’re like me and want PowerShell to be your default shell even in core servers, do this to make PowerShell your default shell:

$Path = 'Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\winlogon'
Set-ItemProperty -Confirm -Path $Path -Name Shell -Value 'PowerShell.exe -noExit -Command Set-Location "$env:userprofile"

The next time you login to the machine you will get PowerShell as the default shell 🙂

Windows Server 2012 is coming!

A week from today Microsoft releases Windows Server 2012. For ordinary computer users this release may not mean a lot, but for us working with running large server systems it will be a game changer.

Fundamental parts of the Windows Server operating system have been changed. Some changes are visible such as the lack of a graphical user interface on a standard server. Other changes are less visible; new storage options, filesystems etc.

A very big change for operations is that PowerShell really have moved into the core of managing Windows. This will allow us to automate more than before, with ease!

I won’t go into all the details here but if you want to be part of the launch event for Windows Server 2012, setup a reminder here.

Generate passwords using PowerShell

The other day I needed to generate some 1400+ new user passwords. Being a lazy person I figured that PowerShell could rescue me. This is what I did to check that my idea worked:

PS C:\> Add-Type -AssemblyName "System.Web"
PS C:\> [System.Web.Security.Membership]::GeneratePassword(10,2)

As you can see this generates a password that is 10 characters in length and contains at least 2 non-alphanumeric characters.  Now all I needed was to iterate this 1400 times and then output the result to the clipboard, simple as pie:

PS C:\> 1..1400 | % { [System.Web.Security.Membership]::GeneratePassword(10,2) } | clip

And that is a 1400 new passwords stored in the clipboard. I can now paste these or pipe them into a set password routine.

Configuring Windows Server 2008 R2 Features

At Basefarm we frequently need to ensure that many Windows servers are identical in terms of the roles and features they have installed. Adding features can be done in a number of ways. Mostly the graphical userinterface (Server Manager) is used. Or for large operations System Center or similar. I will show you how this can be done more easily using the command line. This method doesn’t require anything beyond Windows Server 2008 R2 (or later) and PowerShell.

The Server Manager module

The Server Manager module (introduced with Windows Server 2008 R2) has three very useful commands, they are:

  • Add-WindowsFeature
  • Get-WindowsFeature
  • Remove-WindowsFeature

Using these is simple. Start a PowerShell session with administrative privileges (Run As…) . Then check that the Servermanager module is available in your server:

PS C:\> Get-Module -ListAvailable

Get-Module -ListAvailable

This shows that the Server Manager module is available on our server but that it is not yet loaded into the PowerShell session. To load it (and make its commands available):

PS C:\> Import-Module Servermanager

Now the commands of the Server Manager module are available to you. Check which commands are exposed by the module:

PS C:\> Get-Command -Module Servermanager

Ok, we’re all set. Let’s use these commands!

HOWTO: Document what is installed

To see what is installed in a server use:

PS C:\> Get-WindowsFeature


ooops, that’s a lot of text flying by on the screen! As you probably can guess only lines with [X] are installed. So we need to filter the list to only show what is actually installed, try this instead:

PS C:\> Get-WindowsFeature | ? { $_.Installed }


A nice clean list showing which features are installed on the server ;-), perfect for documenting your server(s)

HOWTO: Clone installed features to another server

As shown above it’s easy to list what is installed. But just having this list on the screen doesn’t make much sense, we need to be able to store this in a structured way so that we can use the list on another server to install the same features. PowerShell makes this very simple. We use the Export-CliXml cmdlet to save the information in a structured XML file:

PS C:\> Get-WindowsFeature | ? { $_.Installed } | Export-Clixml .\features.xml

The output from the Get-WindowsFeature cmdlet is saved in a structured way in the XML file features.xml. This file can now shared to other servers and used as input for the Add-WindowsFeature cmdlet!

HOWTO: Add features from another server (using XML file)

Start PowerShell with administrative privileges.  Now try this:

PS C:\> Import-Module Servermanager
PS C:\> Import-Clixml .\features.xml

Now you have the same list of installed features on the new server. But… this is simply a list in memory and on screen. The features haven’t been added yet. In order to do that we need to pipe the information into the Add-WindowsFeature cmdlet.

Before I show you how to do that there is one important thing I need to explain. When we exported the list of installed features we included all features that were marked as installed. As you saw in the output this resulted in a tree like structure where “[X] Web Server (IIS)” was on the top followed by “[X] Web Server” and so on.

That looks fine but if we use this as input for the Add-WindowsFeature cmdlet we will end up with more than we asked for. The reason is that when the top level feature such as “Web Server (IIS)” is choosen everyting underneath it will also be installed. And in order to keep our servers a lean as possible we do not want this! We need to go back and filter the output of Get-WindowsFeatre a little more. Try this instead of what I showed you earlier:

PS C:\> Get-WindowsFeature | ? {$_.Installed -AND $_.SubFeatures.Count -eq 0 }

Now the output will only contain information from the bottom-up so to speak. This works fine as input for the next server we want to make identical. Save the new list to a file:

PS C:\> Get-WindowsFeature | ? {$_.Installed -AND $_.SubFeatures.Count -eq 0 } | Export-Clixml .\features.xml

Now we can finally install these features in the new server:

PS C:\> Import-Clixml .\features.xml | Add-WindowsFeature

Est Voilá! The two servers now have the same Windows features installed.

As always with PowerShell, if your environment enables PowerShell remoting these commands could be executed on any number of servers from a single commandline. A Power(full)Shell that is!


This became a longer post than I intended simply because I wanted to explain the details about filtering the export. Here’s a Quick summary of the commands you use to export what is installed:

PS C:\> Import-Module Servermanager
PS C:\> Get-WindowsFeature | ? {$_.Installed -AND $_.SubFeatures.Count -eq 0 } | Export-Clixml .\filename.xml

Copy the file ‘filename.xml’ to a network share or other location where the next server can reach it, then do this on the other server:

PS C:\> Import-Module Servermanager
PS C:\> Import-Clixml .\filename.xml | Add-WindowsFeature

All features are installed on the new server without having to click-around in the graphical server manager! To verify what is installed quickly use:

PS C:\> Get-WindowsFeature | ? { $_.Installed }

I hope I have showed you that PowerShell is much better than giving your arms RSI using the mouse to handle feature installations!