Problem
I will be working for a new company soon and on the first day I want to understand the scope of the new environment in terms of the number of installed SQL Server instances (including SSAS, SSRS etc.), their versions / editions and service accounts. I am told they have hundreds of SQL Server instances, from SQL Server 2005 to SQL Server 2014, on around 200+ boxes, how can I get all of this information?
Solution
In SQL SMO, there is a class named ManagedComputer, which can provide information about SQL Server services (like SSAS, SSIS, SSRS, and SQL Server Engine) installed on a computer. With this information, we can then take a different approach to retrieve version and edition values of each service. So here are the key points in designing the solution:
- For the SQL Server service, we will use SMO Server class to retrieve version/edition values.
- For the SSAS service, we will use AMO Server class to retrieve version/edition values.
- For the SSRS service, it is tricky. For SSRS 2005, we will use the SSRS executable file version as the SSRS version number, and we will use WMI class MSReportServer_Instance to find the edition value. For SSRS 2008+, we will simply use MSReportServer_Instance to get both version and edition values.
- For the SSIS service, it is even trickier because it seems Microsoft never officially stores SSIS edition anywhere, or in other words, it seems there is no edition property for the SSIS service. But in logic, it does not make sense because if I install a Standard SQL Server version on my computer together with SSIS, I expect this SSIS to be Standard edition (instead of Enterprise per se). So here, I will use the file version of the SSIS executable file as the SSIS version and then use registry data to find the SSIS edition data.
- We will use PowerShell to drive the whole logic and save the results to a data table, which can be saved to a csv file or to a SQL Server table.
The PS code below is to be run on a computer with PowerShell V3+ and SQL Server 2012 Client Tools SDK installed (as the minimum requirement), and you should have access permissions to all servers you want to scan. In my case, I have local admin privileges on all the servers against which the script runs.
Code
#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= 'SrvDev06', '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')
{
#for sql 2005 SSRS, there is no direct version number from WMI interface, so I have to use SSRS executable file version info as SSRS version
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 | 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 | export-csv -path c:\temp\test.csv -notypeinfo -Force
After running the script in PS ISE, I will get the following result. Note: all values, except for the [Type] and [Version] columns, have been modified for security reasons. Also the ServiceAccount is purposely omitted.
Summary
Completing a SQL Server inventory review is very useful for understanding the scope and complexity of your working environment. Inventory data collection can include many more data points, for example, OS version, disk drives, CPU, memory, etc. Even for the SQL Server instance itself, you may collect data points such as server configuration, databases, security (i.e. logins, credentials, certificates, etc.), replication, mirroring, log-shipping, HAGs, etc. In this tip, I only reviewed the basic (yet arguably the most important) data points i.e. SQL Services, that a DBA needs to know before starting his/her work.
Note: this script does not apply to SQL Server 2000 services, which at this point I assume are very few and seldom used.
Next Steps
- You may do the following as your next steps:
- Modify the script to save the result into a SQL Server table, and further you can schedule a SQL Server Agent Job to run the script weekly/monthly so you will have an inventory table for the history of your SQL Server services. It can be an important tool for you to better manage your environment.
- Modify the script to retrieve a server list from Active Directory directly and this way the script can scan all servers for inventory information.
- Check out these additional resources:
