G’day,

Recently I was updating some of my Powershell scripts for managing SQL SERVER instances.

Generally, when writing a Powershell script to do something SQL related, the script takes the form of the following,

<#

Author : Martin Catherall

Get status of sql instances (must have sql 10 libraries installed)

#>

Clear-Host;

$ServerList = @();

$ServerList = “localhost” , “.” #list sql instances – can be remote

$SQLServiceDetailsList = @();

foreach ($Server in $ServerList)

{

$SqlServices = Get-WmiObject -Namespace root\Microsoft\SqlServer\ComputerManagement10 -Class SqlService -ComputerName $Server;

foreach ($Service in $SQLServices)

{

$SQLServiceDetails = New-Object PSObject;

$SQLServiceDetails | Add-Member -MemberType NoteProperty -Name “Computer Name” -Value $Server;

$SQLServiceDetails | Add-Member -MemberType NoteProperty -Name “Service Name” -Value $Service.ServiceName;

$SQLServiceDetails | Add-Member -MemberType NoteProperty -Name “Service State” -Value ($Service.State);

$SQLServiceDetails | Add-Member -MemberType NoteProperty -Name “Service Type” -Value ($Service.SQLServiceType);

$SQLServiceDetailsList += $SQLServiceDetails;

$SQLServiceDetails = $null;

}

$SqlServices = $null;

};

$SQLServiceDetailsList;

I find that looping though a list of computers (in the case above) or SQL instances, allows me the flexibility to run the script on either a single machine or a collection of machines.

The thing to notice here is the reference to the line

Get-WmiObject -Namespace root\Microsoft\SqlServer\ComputerManagement10 -Class SqlService

basically, the script must be run on a computer that has got this version (sql 10) of the service control manager installed on it.

The script will also not pick up any version of SQL SERVER 11 (2012)  installed on any of the machines that it is directed to (contained in the array $ServerList)

so the first question to ask is “how to pick up SQL 11 versions”

and the answer is simply to use the SQL 11 version of the service control manager, like so

Get-WmiObject -Namespace root\Microsoft\SqlServer\ComputerManagement11 -Class SqlService -ComputerName $Server;

Thanks to all the folks on the twitter #sqlhelp tag who helped me out with this

so, problem solved – well yes, but it could be better.

You see now the script must be run from a machine that has the SQL SERVER 2012 service control manager functionality on it – or you’ll get an error.

Sometimes I just want to drag the above script out and run in on the local machine – Regardless of what version of SQL SERVER may be on there.

So, I found using SMO was a more flexible approach.

The below script will work on machines with SQL 2008 and also SQL SERVER 2012 installed (and probably 2005 – but not tested!)

Clear-Host;

$SQLServiceDetailsList = @();

$ServerList = @();

$ServerList = “localhost” # , “.” #replace server list here (should be actual server names – or aliases)

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null;

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”) | Out-Null;

foreach ($Server in $ServerList)

{

$ManagedComputer = New-Object (‘Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer’) $Server;

foreach ($SQLService in $ManagedComputer.Services)

{

$SQLServiceDetails = New-Object PSObject;

$SQLServiceDetails | Add-Member -MemberType NoteProperty -Name “Computer Name” -Value $ManagedComputer.Name;

$SQLServiceDetails | Add-Member -MemberType NoteProperty -Name “Service Name” -Value $SQLService.Name;

$SQLServiceDetails | Add-Member -MemberType NoteProperty -Name “Display Name” -Value $SQLService.DisplayName;

$SQLServiceDetails | Add-Member -MemberType NoteProperty -Name “Service StartMode” -Value $SQLService.StartMode;

$SQLServiceDetails | Add-Member -MemberType NoteProperty -Name “Service State” -Value $SQLService.ServiceState;

$SQLServiceDetailsList += $SQLServiceDetails;

$SQLServiceDetails = $null;

};

}

$SQLServiceDetailsList | Format-Table -AutoSize

The thing to be aware of is, if it is running using the SQL SERVER 2008 libraries then it will not pick up SQL SERVER 2012 instances.

However if the script is being run from a machine with the SQL SERVER 2012 libraries then it will pick up prior versions as well as SQL SERVER 2012 instances.

You can the query the object $SQLServiceDetailsList to get things like

• All services that have a start type of Auto and are currently not running.
• All services that are disabled
• pretty much anything you like – just add the desired attributes.

Have a good day.

Cheers

Martin.