How to Know How Many Sql Servers You Have in Windows

DBAs take enough of day-to-mean solar day tasks to practise as role of administration, but one overarching task is managing the entire SQL Server environs. 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 organization inventory is always the become-to document for vital information for everyone whether y'all're a consultant or an in-house DBA; you must have a reliable inventory of the servers that yous manage. The inventory tin be defined in dissimilar ways since it takes many unlike dimensions merely at its core, information technology'due south an aggregation of information about the organization installation topography. However, often, nosotros 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 listing.

Introduction

In this guide, we'll talk almost the car discovering SQL Server data using the following technologies

• SQL Providers
• WMI (Windows Management Instrumentation)
• Windows registry hives
• and SMO (SQL Server Direction Objects)

If you're familiar with PowerShell object usage and different kinds of available class libraries, the steps are adequately easy to understand. These classes include information about the organisation; they are like a mini database, of the different hardware and software characteristics of the motorcar.

We will load the required libraries to fetch or find SQL instances at every section of this article. And and then, we'll go about customizing additional classes that are non available right out of the box.

• Note: There are enough of third-political 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

1. Windows PowerShell three.0 and above
2. 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 construction that is similar to a file system.

• Annotation: The SQL Server module must be installed from the PowerShell Gallery. The post-obit command gets the SQL Server module from PowerShell Gallery.

Import-Module -Name SQLServer

To list all the SQL instances, navigate to the root directory and run the Get-Childitem to get the list.

'hqdbt01' | Foreach-Object { Get-ChildItem -Path "SQLSERVER:\SQL\$_" }

• Annotation: The path SQLSERVER:\SQL\<ServerName> has all the installed SQL instances of the listed server.

Next, listing the properties such as Proper name, DisplayName, ServerName, InstanceName of the listed SQL Instances.

'hqdbt01' | % { Get-ChildItem -Path "SQLSERVER:\SQL\$_" } | Select-Object -Property @ { label = 'ServerName' ; expression = { $_ . ComputerNamePhysicalNetBIOS } } , Name , DisplayName , InstanceName | Format-Tabular array -AutoSize

The Get-ChildItem cmdlet is used to list the SQL instances of the server. Nosotros can and so 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 holding of each SQL instance from the ComputerNamePhysicalNetBIOS property.

In the following example, the cmdlets listing all the database properties from the remote server hqdbt01 for the default instance.

'hqdbt01' | ForEach-Object { Get-ChildItem -Path "SQLSERVER:\SQL\$_\DEFAULT\Databases" }

In the post-obit output, we can see that the keyword DEFAULT is specified forth with the databases binder.

In the following example, the cmdlets listing all the database properties from the remote server hqdbt01 for the named instance SQL2017.

'hqdbt01' | ForEach-Object { Get-ChildItem -Path "SQLSERVER:\SQL\$_\SQL2017\Databases" }

In the following output, we can meet that the named example SQL2017 is specified forth with the databases binder.

• Annotation: If the named SQL instance is downward, the instance column will exist empty.

Windows Registry Hives

In the following example, the PowerShell control displays the value proper name and data of each of the registry entries contained in the "Microsoft SQL Server" registry subkey.

• Note:
  1. Past default, PowerShell drive named HKLM: is mapped to the "HKEY_LOCAL_MACHINE" hive of the registry.
  2. The easiest mode to read remote SQL instances is by using Invoke-Command.

$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 assortment of the SQL Server instances is listed equally an input for the Invoke-Command. In this case, ii SQL Server instances hqdbt01 and hqdbsp17 are listed. The second command uses the Go-ItemProperty command to retrieve the InstalledInstances holding of the listed instances. The output is and so 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 information. The [pscustomobject] is relatively much faster equally 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 listing the content of the CSV file c:\server_test.csv

Here is a PowerShell script that takes input and reads the SQL Server data from the registry.

$SQLInstances = Import-Csv C : \ server_test . csv | % { Invoke-Command -ComputerName $_ . ServerName { ( Go-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 so is piped to Invoke-Control to loop through all the servers. The registry entries are read from the remote servers and stored in variable for farther processing. The assigned values are then processed to list the ServerName and InstanceName backdrop.

• Notation: This method 'Reading registry subkeys' is relatively much faster than other methods.

Become-Service cmdlet

Get-Service is PowerShell cmdlet that lists all the registered Windows services either from a local or a remote server.

Invoke-Command -ComputerName 'hqdbt01' , 'hqdbsp17' { Get-Service -Name MSSQL * | Where { $_ . status -eq "Running" -and ( $_ . name -ne 'MSSQLFDLauncher' ) } } | Select-Object -Property PSComputerName , @ { label = 'InstanceName' ; expression = { $_ . Proper name -replace '^.*\$' } }

The output lists the SQL instances of the remote machines. This is considering every case too runs a corresponding service. You can as well encounter 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 become a list of all the SQL instances on the server(due south).

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 ten.20.20.255. The IP is traversed using a foreach loop. Showtime, each machine is tested for successful connectivity. Afterwards that, the IP Address is fed to the Organisation.Net.Dns class to go a hostname for each entry. The SQL Service corresponding to each auto is listed and the ServerName and InstanceName properties of Get-Service object are displayed.

1. .255| % { $ip = "10.xx.xx.$_" If ( Test-Connection $ip -count i -quiet ) { $result = ( [ System . Cyberspace . Dns ] :: GetHostByAddress ( $ip ) ) . hostname Write "$event->host responded" Invoke-Command -ComputerName $effect -ErrorAction SilentlyContinue { Get-Service -Name MSSQL * | Where { $_ . status -eq "Running" -and ( $_ . name -ne 'MSSQLFDLauncher' ) } } | Select-Object -Property PSComputerName , @ { label = 'InstanceName' ; expression = { $_ . Name -supercede '^.*\$' } } } }

• Note:
  1. The GetHostByAddress(IP) is a method that creates an IPHostEntry(Hostname) instance from the specified IPAddress
  2. 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 tin create objects that think SQL Server properties. For example, in that location are classes for databases, tables, columns and much more.

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:
  1. Install PowerShell SQLServer module from the PSGallery

     Install-Module -Name SQLServer

  You tin can refer to the commodity: Install SQL Server PowerShell module for more information.

  2. A detailed explanation of the .NET class library is explained in the article: 6 methods to write PowerShell output to a SQL Server table
  3. This method is very elementary and useful when you're ready with the instance details. Instantiating SMO objects let viewing the properties of the SQL instances

Summary

In this commodity, we discussed diverse methods to access the basic information of all the SQL instances. The machine-discover pick allows us to keep an inventory of SQL servers, and it's helpful for many events. I hope yous like this commodity. Feel complimentary to exit comments below.

• Author
• Recent Posts

I'k a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Figurer Application.

My specialty lies in designing & implementing High availability solutions and cantankerous-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

guinnallagarl.blogspot.com

Source: https://www.sqlshack.com/six-methods-to-automatically-discover-sql-server-instances/

Share this post