PowerShell Usage in DBA Work – Case Study 5/7

One challenge in a super-multiple server (say a few hundred servers) environment is to know what sql services (SSRS/SSAS/SSIS/Engine/Agent etc) are installed and what their versions and the startup accounts are.

Case Scenario: (SQL Service Inventory)

We are managing hundreds of physical servers in our environment, most of the servers have SQL Server service and SQL Server Agent service, but there can be other services, like SSRS / SSAS / SSIS. DBA team wants to know the version of each service and so we can decide when to apply the proper patches to which server. So how can we retrieve all the information we need?

PowerShell Solution:

Note: You need to have the proper privilege to run the script against your target server. In my environment, my domain account belongs to each server’s local admin group.

#get sql service inventory (no error handling for simplicity)
#executed on sql 2005+ / win7 / win 2008 R2
# if you installed only sql server 2008, change version= to version=, for sql 2005, version=
add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

[string]$server='MyServer';#change to your proper server name
$mc = New-Object "microsoft.sqlserver.management.smo.wmi.managedcomputer" $server;
$svc = $mc.Services

#only get the executable file name in first double quotation
[string]$pattern = '(["][^"]+["]).?'; # regular expression pattern
$reg = new-object "system.text.regularexpressions.regex" ($pattern);

$dtbl= new-object "system.data.datatable";

$dc = new-object "system.data.datacolumn"("ServerName", [system.type]::gettype("System.String") );

$dc = new-object "system.data.datacolumn"("Name", [system.type]::gettype("System.String") );
$dc = new-object "system.data.datacolumn"("serviceAccount", [system.type]::gettype("System.String") );
$dc = new-object "system.data.datacolumn"("Version", [system.type]::gettype("System.String") );
$dc = new-object "system.data.datacolumn"("FilePath", [system.type]::gettype("System.String") );

foreach ($sc in $svc)
$dr = $dtbl.NewRow();
if ($m.Success)
{ [string]$pth=$m.groups[1].Value; $pth=$pth.replace('"', '');}
{ $pth='';}
$dr.ServerName = $server;
$dr.FilePath = $pth;
$dr.serviceAccount = $sc.ServiceAccount;
if ($pth -ne '')
{   $pth = '\\'+$server+'\'+$pth.replace(':', '$');
$dr.Version = (gci -path $pth).VersionInfo.ProductVersion;}
$dtbl | ft -auto;

The result will be something like the following (I purposely removed ServerName, ServiceAccount columns by using $dtbl | select name, version, filepath | ft -auto)


Advantage Analysis:

1. I do not need to log into a service like “sql server engine service” or SSAS to get the version number, which means even if the service is not online, I can still get the version info.

2. It is efficient. For example, if we want to find the version of SSIS / SSRS, what can we do? (yes, there are ways, but not as efficient as this PS approach)

Other Thoughts:

If we have access to Active Directory server with the right privilege, we can get the name list of all servers, and then  run this PS against each server to check the sql service inventory.

If you are new to a company, it will be good for you to get a picture of the whole environment and thus have a justified psychological preparation for your future workload.

This entry was posted in Administration and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s