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=11.0.0.0 to version=10.0.0.0, for sql 2005, version=9.0.0.0
add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, version=11.0.0.0, 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);
[string]$pth='';
$dtbl= new-object "system.data.datatable";
$dc = new-object "system.data.datacolumn"("ServerName", [system.type]::gettype("System.String") );
$dtbl.Columns.add($dc);
$dc = new-object "system.data.datacolumn"("Name", [system.type]::gettype("System.String") );
$dtbl.Columns.add($dc);
$dc = new-object "system.data.datacolumn"("serviceAccount", [system.type]::gettype("System.String") );
$dtbl.Columns.add($dc);
$dc = new-object "system.data.datacolumn"("Version", [system.type]::gettype("System.String") );
$dtbl.Columns.add($dc);
$dc = new-object "system.data.datacolumn"("FilePath", [system.type]::gettype("System.String") );
$dtbl.Columns.add($dc);
foreach ($sc in $svc)
{
$dr = $dtbl.NewRow();
$m=$reg.Match($sc.pathname);
if ($m.Success)
{ [string]$pth=$m.groups[1].Value; $pth=$pth.replace('"', '');}
else
{ $pth='';}
$dr.ServerName = $server;
$dr.name=$sc.name;
$dr.FilePath = $pth;
$dr.serviceAccount = $sc.ServiceAccount;
if ($pth -ne '')
{ $pth = '\\'+$server+'\'+$pth.replace(':', '$');
$dr.Version = (gci -path $pth).VersionInfo.ProductVersion;}
$dtbl.Rows.Add($dr);
}
$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.
