DBAs have plenty of day-to-day tasks to do as part of administration, but one overarching task is managing the entire SQL Server environment. To do this, the full SQL Server estate must be discovered and inventoried. To accomplish that all SQL Server instances, that exist, must be found.
Inventory
A system inventory is always the go-to document for vital information for everyone whether you’re a consultant or an in-house DBA; you must have a reliable inventory of the servers that you manage. The inventory can be defined in different ways since it takes many different dimensions but at its core, it’s an aggregation of information about the system installation topography. However, often, we tend to forget to update the inventory when we add a server or update software. We deploy new servers, decommission existing servers, keeping track of servers in the environment, upgrade process, patching, migration from physical to virtual machines, moving to the cloud, license validation, IT forecasting and many more such areas strongly rely on the inventory list.
Introduction
In this guide, we’ll talk about the auto discovering SQL Server information using the following technologies
- SQL Providers
- WMI (Windows Management Instrumentation)
- Windows registry hives
- and SMO (SQL Server Management Objects)
If you’re familiar with PowerShell object usage and different kinds of available class libraries, the steps are fairly easy to understand. These classes include information about the system; they are like a mini database, of the different hardware and software characteristics of the machine.
We will load the required libraries to fetch or discover SQL instances at every section of this article. And then, we’ll go about customizing additional classes that are not available right out of the box.
- Note: There are plenty of third-party tools such as Microsoft MAPI toolkit, ApexSQL Discover, SQL Ping3, and several custom scripts are available in the market to discover the SQL Server instances automatically. These available third-party tools which make the life of a DBA easier when it comes to configuration management.
Pre-requisites
- Windows PowerShell 3.0 and above
-
Permissions required to
- query/access all SQL instances
- read input file
- write output files
Get-ChildItem cmdlet
PowerShell provider exposes the database objects in an SQL Server instance in a defined structure that is similar to a file system.
- Note: The SQL Server module must be installed from the PowerShell Gallery. The following command gets the SQL Server module from PowerShell Gallery.
|
1 |
Import-Module -Name SQLServer |
To list all the SQL instances, navigate to the root directory and run the Get-Childitem to get the list.
|
1 |
'hqdbt01'| Foreach-Object {Get-ChildItem -Path "SQLSERVER:\SQL\$_"} |
- Note: The path SQLSERVER:\SQL\<ServerName> has all the installed SQL instances of the listed server.
Next, list the properties such as Name, DisplayName, ServerName, InstanceName of the listed SQL Instances.
|
1 |
'hqdbt01'|% {Get-ChildItem -Path "SQLSERVER:\SQL\$_"}|Select-Object -Property @{label='ServerName';expression={$_.ComputerNamePhysicalNetBIOS}}, Name, DisplayName, InstanceName |Format-Table -AutoSize |
The Get-ChildItem cmdlet is used to list the SQL instances of the server. We can then pipe the output of the Get-ChildItem command to the Select-Object cmdlet, which selects Name, DisplayName, InstanceName and ServerName properties from the object returned by Get-ChildItem. The ServerName property is added using a hash table with Label and Expression. The value of the Expression is a script block that gets the ServerName property of each SQL instance from the ComputerNamePhysicalNetBIOS property.
In the following example, the cmdlets list all the database properties from the remote server hqdbt01 for the default instance.
|
1 2 |
'hqdbt01' | ForEach-Object {Get-ChildItem -Path "SQLSERVER:\SQL\$_\DEFAULT\Databases"} |
In the following output, we can see that the keyword DEFAULT is specified along with the databases folder.
In the following example, the cmdlets list all the database properties from the remote server hqdbt01 for the named instance SQL2017.
|
1 2 |
'hqdbt01' | ForEach-Object {Get-ChildItem -Path "SQLSERVER:\SQL\$_\SQL2017\Databases"} |
In the following output, we can see that the named instance SQL2017 is specified along with the databases folder.
- Note: If the named SQL instance is down, the instance column will be empty.
Windows Registry Hives
In the following example, the PowerShell command displays the value name and data of each of the registry entries contained in the “Microsoft SQL Server” registry subkey.
- Note:
- By default, PowerShell drive named HKLM: is mapped to the “HKEY_LOCAL_MACHINE” hive of the registry.
- The easiest way to read remote SQL instances is by using Invoke-Command.
|
1 2 3 4 5 6 7 8 9 |
$SQLInstances = Invoke-Command -ComputerName hqdbt01,hqdbsp17 { (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances } foreach ($sql in $SQLInstances) { [PSCustomObject]@{ ServerName = $sql.PSComputerName InstanceName = $sql } } |
An array of the SQL Server instances is listed as an input for the Invoke-Command. In this case, two SQL Server instances hqdbt01 and hqdbsp17 are listed. The second command uses the Get-ItemProperty command to retrieve the InstalledInstances property of the listed instances. The output is then stored in the variable $SQLInstances. Later the variable is further transformed through an iteration to display ServerName and InstanceName properties of the objects.
- Note: [PSCustomObject] is very helpful to create structured stream of data. The [pscustomobject] is relatively much faster as it treats hashtables properties as objects.
In the following example, the input CSV file server_test.csv is read from a local drive. The file contains a list of servers.
PS P:\> Import-Csv -Path c:\server_test.csv
The output list the content of the CSV file c:\server_test.csv
Here is a PowerShell script that takes input and reads the SQL Server information from the registry.
|
1 2 3 4 5 6 7 8 9 10 |
$SQLInstances = Import-Csv C:\server_test.csv |% { Invoke-Command -ComputerName $_.ServerName { (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances } } foreach ($sql in $SQLInstances) { [PSCustomObject]@{ ServerName = $sql.PSComputerName InstanceName = $sql } } |
The field, ServerName, is read from the input file and then is piped to Invoke-Command to loop through all the servers. The registry entries are read from the remote servers and stored in variable for further processing. The assigned values are then processed to list the ServerName and InstanceName properties.
- Note: This method ‘Reading registry subkeys’ is relatively much faster than other methods.
Get-Service cmdlet
Get-Service is PowerShell cmdlet that lists all the registered Windows services either from a local or a remote server.
|
1 2 |
Invoke-Command -ComputerName 'hqdbt01', 'hqdbsp17' { Get-Service -Name MSSQL* |Where {$_.status -eq "Running" -and ($_.name -ne 'MSSQLFDLauncher')}} | Select-Object -Property PSComputerName, @{label='InstanceName';expression={$_.Name -replace '^.*\$'}} |
The output lists the SQL instances of the remote machines. This is because every instance also runs a corresponding service. You can also see the Where clause in which conditions can be specified as per your requirement.
- Note: We all know that every SQL installation performed on the server registers a windows service so Get-Service cmdlet is the simplest ways to get a list of all the SQL instances on the server(s).
Using a defined IP range
In the following example, the input is a defined IP Address range. The IP address range is 10.20.20.1 to 10.20.20.255. The IP is traversed using a foreach loop. First, each machine is tested for successful connectivity. After that, the IP Address is fed to the System.Net.Dns class to get a hostname for each entry. The SQL Service corresponding to each machine is listed and the ServerName and InstanceName properties of Get-Service object are displayed.
|
1 2 3 4 5 6 7 8 9 10 |
1..255| % { $ip="10.20.20.$_" If (Test-Connection $ip -count 1 -quiet) { $result = ([System.Net.Dns]::GetHostByAddress($ip)).hostname Write "$result->host responded" Invoke-Command -ComputerName $result -ErrorAction SilentlyContinue { Get-Service -Name MSSQL* |Where {$_.status -eq "Running" -and ($_.name -ne 'MSSQLFDLauncher')}} | Select-Object -Property PSComputerName, @{label='InstanceName';expression={$_.Name -replace '^.*\$'}} } } |
- Note:
- The GetHostByAddress(IP) is a method that creates an IPHostEntry(Hostname) instance from the specified IPAddress
- It took me about 9 minutes to traverse the 255 machines to list all the SQL instances
SMO
SMO stands for SQL Management Objects. It is a set of .NET libraries specifically designed for working with SQL Server. These libraries are stored in .NET DLLs and are loaded with classes during Import-Module. From these classes you can create objects that retrieve SQL Server properties. For example, there are classes for databases, tables, columns and much more.
|
1 2 3 4 5 |
Import-Module -Name SQLServer 'hqdbt01','hqdbt01\sql2017' |ForEach-Object { new-object ('Microsoft.SqlServer.Management.Smo.Server') $_ |SELECT @{label='ServerName';expression={$_.ComputerNamePhysicalNetBIOS}}, @{label='InstanceName';expression={$_.Name}} , Edition } |
- Notes:
- Install PowerShell SQLServer module from the PSGallery
Install-Module -Name SQLServer
- A detailed explanation of the .NET class library is explained in the article: 6 methods to write PowerShell output to a SQL Server table
- This method is very simple and useful when you’re ready with the instance details. Instantiating SMO objects allow viewing the properties of the SQL instances
You can refer to the article: Install SQL Server PowerShell module for more information.
- Install PowerShell SQLServer module from the PSGallery
Summary
In this article, we discussed various methods to access the basic information of all the SQL instances. The auto-discover option allows us to keep an inventory of SQL servers, and it’s helpful for many events. I hope you like this article. Feel free to leave comments below.
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021