Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Inventory SQL Server Services Version and Edition


By:   |   Last Updated: 2015-03-11   |   Comments (13)   |   Related Tips: 1 | 2 | 3 | More > SQL Server Configurations

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.

Inventory_list

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


Last Updated: 2015-03-11


next webcast button


next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, November 03, 2016 - 1:59:34 PM - Josh Back To Top

Could not get this to work.  I am not a DB guy, only dabble in coding.  I'm a Sys Admin/Infrastructure guy tryin' t get an inventory of (amongst other things) SQL servers in my organization.  Didn't work for me.  Nice try tho.


Wednesday, May 25, 2016 - 12:07:16 PM - jeff_yao Back To Top

Hi Slogmeister

I said in the tip

"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 environment, I run the script on a dedicated DBA server box with PS V4 and SQL Server 2012 client tools sdk installed, but the sql servers I need to scan include sql server 2005/2k8/2k8R2/2012/2014.

Hope this helps/clarifies your confusion.

 

Thanks,

Jeff_yao

 

 

 


Wednesday, May 25, 2016 - 10:21:13 AM - Slogmeister Extraordinaire Back To Top
If you know to use this line: add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, version=11.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91"; You already know the version of SQL. Your line only works with SQL 2012, it doesn't work with SQL 2008. Not terribly useful.

Monday, April 13, 2015 - 1:21:03 PM - jeff_yao Back To Top

Hi Steven,

I have not tested how to get required info without sysadmin privilege. I guess you can try to create an account on your own computer without giving the account sysadmin privilege and then just test it by incremently granting necessary privileges until the privilege is just good enough to get the info. If you really take this approach, I recommend you to use Profiler (with some strong filters) to check each step, and it will be much easier.

 

Thanks,

Jeffrey


Monday, April 13, 2015 - 9:32:18 AM - Steven Back To Top

Hi Jeffrey,

I have a simular script to get SQL server information. My problem is that I'm not allowed to use a locak admin account or SQL sysadmin. The account I use has remote WMI permissions and datareader access to MSDB and Master database. Additionaly it also has view server state.

I get all the info I need except Service Pack info and Edition. 

 

$SQLSMOinfo = New-Object Microsoft.SqlServer.Management.Smo.Server $computer

$SQLSMOinfo.productlevel

$SQLSMOinfo.edition

Do you have any idea how I can get this information without sysadmin rights?

 

Thanks,

 

Steven


Tuesday, March 17, 2015 - 1:06:56 PM - jeff_yao Back To Top

@Daisy, not sure why you encounter the issue. Are you using cluster name directly (which will not work) or using the cluster node machine name (which should work)?

Also have you tried run the script against a non-clustered server box and do you still see the same issue?


Tuesday, March 17, 2015 - 8:40:00 AM - Jeff Bennett Back To Top

Jeffrey Yao,

If you are getting information stating "Not Supported", perhaps you are connecting to older versions of SQL 2000 and previous editions, or MSDE instead of SQL Express.  If that is the case, you will have to research those individual servers on a case-by-case basis, because the MAP tool version is too recent, and while it can detect the SQL instance for older servers, it can not gather a lot of the infrastructure information on those legacy servers.

I have never had problems running the tool against multiple instances on a server.  What version of MAP are you running?

Regarding the preference for '2012SP1' or '11.0.3393.0', it is probably in the tables somewhere, but you will have to search for that, and may be better off just using a case statement against the specific tables.  Some of those tables within the MAP database are easy to find, but difficult to decipher.  Try these schemas

SQLServer_Reporting

SQLServer_Assessment

SQLServer_Inventory

WinServer_Assessment schema

WinServer_Inventory

 


Monday, March 16, 2015 - 3:56:23 PM - Daisy Back To Top

Hi, Jeffrey, 

I tried to run the script to retrieve the info from a clustered sql server with multiple instances, how does this works? (for sql 2008 r2 and sql 2012). 

Also I tried some of other properties, like ProductLevel or OSVersion, keep saying not supported. So what is the right server properties if I want to restrieve something like this 2012SP1 instead of 11.0.3393.0. thanks

 


Wednesday, March 11, 2015 - 6:10:47 PM - jeff_yao Back To Top

@JeffBennett, thanks for your comments.

I will try MPA tool some time later. 

The code I presented here is actually part of my "inventory collection" project. It is nimble in the sense that you can almost run it from a PS ISE. I have also created a few PS cmdlet based on the similar code that I can use in various scenarios, one example is that after our server team patches OS on a few servers, and then I need to check whether my sql services on these servers start as expected, all I need to run is such a cmdlet:

get-SQLServiceInfo -Computer 'Server1', 'Server2', 'ServerN' 

What I mean is that understanding how to collect sql service info conveniently may save us quite some time in daily work.

I am personally always interested in figuring out how something is done even though it had been done by someone before. :-)

But I agree, if we want to deal with MS licensing issue, we'd better let MS tool to tell us the details.

 

Kind regards,

Jeff Yao


Wednesday, March 11, 2015 - 4:27:05 PM - Jeff Bennett Back To Top

When I took over as team lead at my company, I found about 80 instances that were not known by my manager or team members, simply by running the Microsoft Analysis and Planning Toolkit.    Iwould not ever want to have to use the excuse that 'since I didn't know about it, I don't have to manage it' in response to a problematic SQL server in my corporate domain.   I would never expect my management to know more about the servers I support than I do.   

The MAP Tool provides ready-for-presentation reports showing SQL Server counts in use by version, SQL Server counts by Edition, Core licensing, components in use (SSRS, SSAS, SSIS), Windows OS information, as well as detailed information on each database instance, including computer name, physical or virtual, operating system edition, number of cores, number of logical processors, SQL Server version, SQL Server edition, service pack level, CLR version, default collation, security model, and number of users.

In the database details report, you get datatabases by server, dbsize, data file size and name, log file size and name, log file space used %, compatibility level, status, db_owner, created timestamp, last backup, number of tables, views, sprocs, functions, and file-growth characteristics.

Now anyone can code to collect all this data, but when you have to true-up with Microsoft for licensing, the MAP tool puts all the data in a standardized format that Microsoft and their licensing partners are familiar with and it will make your job so much easier.  I do not recommend coding to collect all of this information unless you are simply looking to broaden your coding sills.

 

Thanks

Jeff Bennett

St. Louis, MO

 


Wednesday, March 11, 2015 - 1:15:37 PM - jeff_yao Back To Top

@ThmasFranz, thanks for your comments.

I heard of MAPT but never used it. But regarding your concerns that a DBA will not know all the server names, I'd agree to some extent. The reason is if there is no list of server names (I mean hosting machine names) to a DBA's knowledge, it means you do not need to manage it. In another word, when I join a new company, my manager or my DBA team will definitely inform me the list of server names. They may tell you what version/services/editions are there, but the server names will be known to me in one way or another (such as via AD)


Wednesday, March 11, 2015 - 12:27:59 PM - Jeff Bennett Back To Top

Definitely use the MAPP tool provided free from Microsoft.   Captures everything you need to know, with the possible exception of core counts on some older versions of SQL.  Download it.   You will need domain level privileges to get the most out of it, but it will come in handy when you true up annually with Microsoft for licensing.

 

Jeff Bennett

Missouri, US


Wednesday, March 11, 2015 - 4:40:03 AM - Thomas Franz Back To Top

If you are new to a company you will probably not know all the server names and even if you are working for years in a company there is a chance that there are some long forgotten SQL servers running in some dusty corners in your data center.

To get a list of all (running) servers / instances and some other important informations (as versions) you could use the free Microsoft Assessment and Planning Toolkit (http://www.microsoft.com/en-us/download/details.aspx?id=7826). It scans the whole network for all installations of the selected product (pick SQL Server before running it).

 


Learn more about SQL Server tools