When starting a new job, it’s important to understand the scope of the SQL Server environment you’ll be working with. This includes knowing the number of installed SQL Server instances, their versions and editions, and the service accounts associated with them. In this article, we’ll explore how to retrieve this information using PowerShell.

Using SQL SMO

In PowerShell, we can use the SQL Server Management Objects (SMO) library to retrieve information about SQL Server services installed on a computer. The ManagedComputer class in SMO provides access to this information.

Here are the key points in designing the solution:

• For the SQL Server service, we will use the SMO Server class to retrieve the version and edition values.
• For the SSAS service, we will use the Analysis Management Objects (AMO) library and the Server class to retrieve the version and edition values.
• For the SSRS service, we will use different approaches depending on the version. For SSRS 2005, we will use the SSRS executable file version as the SSRS version number, and we will use the WMI class MSReportServer_Instance to find the edition value. For SSRS 2008 and later versions, we can simply use the MSReportServer_Instance class to get both the version and edition values.
• For the SSIS service, we will use the file version of the SSIS executable file as the SSIS version and then use registry data to find the SSIS edition.

PowerShell Code

Here is an example PowerShell code that retrieves the SQL Server instance information:

#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";
# ... (code continues)

After running the script, you will get a table with the following columns: MachineName, ServerInstance, Type, Version, Edition, and ServiceAccount. The table will contain the information for each SQL Server instance.

Summary

Retrieving SQL Server instance information is essential for understanding the scope and complexity of your working environment. By using PowerShell and the SQL Server Management Objects library, you can easily gather this information and save it to a CSV file or a SQL Server table. This allows you to have a comprehensive inventory of your SQL Server instances, their versions, editions, and service accounts.

Keep in mind that this script does not apply to SQL Server 2000 services, as they are rarely used nowadays.