Powershell Script to find Sql services Version , Edition Details and store the result to .CSV file

Inventory to Get SQL Services Version and Edition Details

We have a class named ManagedComputer in SQL SMO, which provides information about all SQL Server services like SSAS, SSIS, SSRS, and SQL Server Database Engine installed on a computer. By this information, we can take a different method to retrieve version and edition values of each service. Below are points in designing the solution:

  • For SQL Server service, we use SMO Server class to get version/edition values.
  • For SSAS service, we use AMO Server class to get version/edition values.
  • For SSRS service, it is some what tricky. For SSRS 2005, we use SSRS executable file version as SSRS version number, and we use WMI class MSReportServer_Instance to find the edition value. For SSRS 2008+ version, we simply use MSReportServer_Instance to get both version and edition values.
  • For SSIS service, it is some more trickier because Microsoft never stores SSIS edition anywhere, or in other words,  there is no edition property for SSIS service. But actually in logic, it did not make any sense because if we install a Standard Version on my computer along with SSIS, I expect ]SIS to be Standard edition (instead of Enterprise ). So here, we use file version of the SSIS executable file as SSIS version and then using registry data to get the SSIS edition data.
  • Here we are using PowerShell to go through the whole logic and save results to a data table, which will be saved to a csv file or also can be saved to a SQL Server table.

The PowerShell code below is to be run on a computer with PowerShell version V3+ and SQL Server 2012 Client Tools SDK to be installed (this is minimum requirement), and you should have permissions to access all servers you want to scan. In our case, I have local admin privileges on all servers against which the script runs.

In script we have to change some parameters like :

  • Give your Server Name or Machine
  • change path where ever you want to store .CSV file or create the folder C:\temp
#Requires -version 3.0
add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, version=11.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91";
add-type -AssemblyName "Microsoft.AnalysisServices, version=11.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91";

$data_table = New-Object "system.data.datatable";
$col = New-Object "system.data.datacolumn" ('MachineName', [System.String]);
$data_table.columns.Add($col);
$col = New-Object "system.data.datacolumn" ('ServerInstance', [System.String]);
$data_table.columns.Add($col);
$col = New-Object "system.data.datacolumn" ('Type', [System.String]); #type=SQLServer / AnalysisServer / ReprtServer / IntegrationService
$data_table.columns.Add($col);
$col = New-Object "system.data.datacolumn" ('Version', [System.String]);
$data_table.columns.Add($col);
$col = New-Object "system.data.datacolumn" ('Edition', [System.String]);
$data_table.columns.Add($col);
$col = New-Object "system.data.datacolumn" ('ServiceAccount', [System.String]);
$data_table.columns.Add($col);




[string[]]$server_list= 'NODE1';#, 'SvrQA26', 'SvrDev26', 'SvrPre57','SvrQA04'; #change to your own server names
# [string[]]$server_list = gc -path 'c:\temp\server_list.txt' #you can put your server list in a text file, each [ServerName] uses one line

foreach ($machine_name in $server_list)
{ "processing : $machine_name";
try
{
$mc = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $machine_name;
$mc.services | ? {($_.type -in ("SqlServer", "AnalysisServer", "ReportServer", 'SqlServerIntegrationService') ) -and ($_.ServiceState -eq 'Running')} | # % {($_.name).contains("$")}
% { $s = $_.name;
[string]$svc_acct = $_.ServiceAccount;
switch ($_.type)
{ "sqlserver" { if ($s.contains("$")) {$sql_instance= "$($machine_name)\$($s.split('$')[1])"} else {$sql_instance=$machine_name;}
$sql_svr = new-object "microsoft.sqlserver.management.smo.server" $sql_instance;
$row = $data_table.NewRow();
$row.Edition = $sql_svr.Edition;
$row.Version = $sql_svr.Version;
$row.Type = 'SQLServer';
$row.ServerInstance = $sql_instance;
$row.ServiceAccount = $svc_acct;
$row.MachineName=$machine_name;
$data_table.Rows.Add($row);
} #sqlserver

"AnalysisServer" { if ($s.contains("$")) {$as_instance= "$($machine_name)\$($s.split('$')[1])"} else {$as_instance=$machine_name;}
$as_svr = New-Object "Microsoft.AnalysisServices.Server";
$as_svr.connect("data source=$as_instance");
$row = $data_table.NewRow();
$row.Edition = $as_svr.Edition;
$row.Version = $as_svr.Version;
$row.Type = 'AnalysisServer';
$row.ServerInstance = $as_instance;
$row.ServiceAccount = $svc_acct;
$row.MachineName=$machine_name;
$data_table.Rows.Add($row);
} #AnalysisServer

"ReportServer" {
$pathname = ($mc.services[$s]).PathName;
$pathname= "\\$machine_name\" + ($pathname.replace(':\', '$\')).replace('"', '')

$item=get-item $pathname
[string]$ver='V' + ($item.VersionInfo.ProductMajorPart).ToString();
[string]$file_version = $item.VersionInfo.ProductVersion;

if ($s.Contains('$')) # this is a named instance of SSRS
{

[string]$instance_name = (($s.split('$'))[1]).replace('_', '_5f'); #SSRS instance name is encoded
[string]$rs_name="RS_$($instance_name)";
}

else
{
[string]$instance_name = 'MSSQLSERVER';
[string]$rs_name='RS_MSSQLServer';
}

if ($ver -eq 'V9')
{
#sql 2005 SSRS
gwmi -class MSReportServer_Instance –Namespace “root\microsoft\sqlserver\reportserver\V9” -ComputerName $machine_name |
Where-Object {$_.__Path -like "*InstanceName=`"$($instance_name)`"" } |
% { $row = $data_table.NewRow();
$row.Edition = $_.EditionName;
$row.Version = $File_Version;
$row.Type = 'ReportServer';
$row.ServerInstance = $s;
$row.ServiceAccount = $svc_acct;
$row.MachineName=$machine_name;
$data_table.Rows.Add($row);

}

}
else
{
gwmi -class MSReportServer_Instance –Namespace “root\microsoft\sqlserver\reportserver\$rs_name\$ver” -ComputerName $machine_name |
Where-Object {$_.__Path -like "*InstanceName=`"$($instance_name)`"" } |
% { $row = $data_table.NewRow();
$row.Edition = $_.EditionName;
$row.Version = $_.version;
$row.Type = 'ReportServer';
$row.ServerInstance = $s;
$row.ServiceAccount = $svc_acct;
$row.MachineName=$machine_name;
$data_table.Rows.Add($row);
}
}
} #ReportServer
'SqlServerIntegrationService' {
$pathname = ($mc.services[$s]).PathName;
$pathname= "\\$machine_name\" + ($pathname.replace(':\', '$\')).replace('"', '');

$item=get-item $pathname;
[string]$ver= ($item.VersionInfo.ProductMajorPart).ToString() +'0';
[string]$file_version = $item.VersionInfo.ProductVersion;

#finding the SSIS edition by reading the registry
$key="SOFTWARE\MICROSOFT\Microsoft SQL Server\$ver\Tools\Setup";
$type = [Microsoft.Win32.RegistryHive]::LocalMachine;
$regkey=[Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $machine_name);
$r=$regkey.OpenSubKey($key).GetValue('edition');

$row = $data_table.NewRow();
$row.Edition = $r;
$row.Version = $file_version;
$row.Type = 'IntegrationService';
$row.ServerInstance = $s;
$row.ServiceAccount = $svc_acct;
$row.MachineName=$machine_name;
$data_table.Rows.Add($row);

} #sqlserverIntegrationService

}#switch
}
}#try
catch
{
Write-Error $Error[0].Exception
}
}#foreach

$data_table | select machineName, serverinstance, type, version, edition, ServiceAccount | ft -auto

#if you want to export to an csv file, you can do the following, assuming you have c:\temp\ folder
$data_table | select machineName, serverinstance, type, version, edition, ServiceAccount | export-csv -path c:\temp\test.csv -notypeinfo -Force

Author: Sri

Hello Friends, This blog is to help the IT professionals who want to become a professional SQL Server DBA but don't know how and from where to start with. So, I am going to share my experiences and my learning in this blog. Will talk about what are the pre-requisite skills required to become a Professional SQL Server DBA, how much time it takes to be a good DBA and what are the additional skill sets are required to become a good DBA. Apart from that I will also post real time sql server settings on server level and database level, Configuring High Availability. Also will share the Client requirements with real time setups if possible I will provide you with screenshots.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.