自动发现SQL Server实例的六种方法

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.

DBA作为管理的一部分要执行许多日常任务，但一项总体任务是管理整个SQL Server环境。 为此，必须发现并清点完整SQL Server资源。 为了实现这一点，必须找到所有存在SQL Server实例。

库存 (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.

无论您是顾问还是内部DBA，系统清单始终是提供重要信息的重要文档。 您必须拥有所管理服务器的可靠清单。 清单可以采用不同的方式定义，因为它具有许多不同的维度，但从本质上讲，它是有关系统安装拓扑信息的汇总。 但是，通常，当我们添加服务器或更新软件时，我们往往会忘记更新清单。 我们部署新服务器，停用现有服务器，跟踪环境中的服务器，升级过程，修补，从物理机迁移到虚拟机，迁移到云，许可证验证，IT预测等，这些领域强烈依赖清单清单。

介绍 (Introduction)

In this guide, we’ll talk about the auto discovering SQL Server information using the following technologies

在本指南中，我们将讨论使用以下技术自动发现SQL Server信息

• SQL Providers
  SQL提供者
• WMI (Windows Management Instrumentation)
  WMI（Windows管理规范）
• Windows registry hives
  Windows注册表配置单元
• and SMO (SQL Server Management Objects)
  和SMO（SQL Server管理对象）

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.

如果您熟悉PowerShell对象的用法和各种可用的类库，则这些步骤相当容易理解。 这些类包括有关系统的信息。 它们就像一个小型数据库，具有机器不同硬件和软件的特征。

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.

我们将在本文的每个部分加载所需的库以获取或发现SQL实例。 然后，我们将着手自定义其他不可用的其他类。

• Note: There are plenty of third-party free 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 free third-party tools which make the life of a DBA easier when it comes to configuration management.
• 注意：市场上有许多第三方免费工具，例如Microsoft MAPI工具包， ApexSQL Discover， SQL Ping3，以及一些自定义脚本，可以自动发现SQL Server实例。 这些免费的第三方工具可简化配置管理中DBA的工作。

先决条件 (Pre-requisites)

1. Windows PowerShell 3.0 and above
   Windows PowerShell 3.0及更高版本
2. • query/access all SQL instances
     查询/访问所有SQL实例
   • read input file
     读取输入文件
   • write output files
     写入输出文件

Get-ChildItem cmdlet (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.

PowerShell提供程序以类似于文件系统的已定义结构公开SQL Server实例中的数据库对象。

• Note: The SQL Server module must be installed from the PowerShell Gallery. The following command gets the SQL Server module from PowerShell Gallery.
• 注意：必须从PowerShell库中安装SQL Server模块。 以下命令从PowerShell库获取SQL Server模块。

Import-Module -Name SQLServer

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

要列出所有SQL实例，请导航到根目录并运行Get-Childitem以获取列表。

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

• Note: The path SQLSERVER:\SQL\<ServerName> has all the installed SQL instances of the listed server.注意：路径SQLSERVER：\ SQL \ <ServerName>具有列出的服务器的所有已安装SQL实例。

Next, list the properties such as Name, DisplayName, ServerName, InstanceName of the listed SQL Instances.

接下来，列出所列出SQL实例的属性，例如Name，DisplayName，ServerName，InstanceName。

'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.

Get-ChildItem cmdlet用于列出服务器SQL实例。 然后，我们可以将Get-ChildItem命令的输出传递到Select-Object cmdlet，该cmdlet从Get-ChildItem返回的对象中选择Name，DisplayName，InstanceName和ServerName属性。 ServerName属性是使用带有Label和Expression的哈希表添加的。 Expression的值是一个脚本块，该脚本块从ComputerNamePhysicalNetBIOS属性获取每个SQL实例的ServerName属性。

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

在以下示例中，这些cmdlet列出了远程服务器hqdbt01中默认实例的所有数据库属性。

'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.

在以下输出中，我们可以看到与数据库文件夹一起指定了关键字DEFAULT。

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

在以下示例中，cmdlet列出了命名实例SQL2017的远程服务器hqdbt01的所有数据库属性。

'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.

在以下输出中，我们可以看到已指定实例SQL2017和数据库文件夹。

• Note: If the named SQL instance is down, the instance column will be empty.注意：如果命名SQL实例关闭，则实例列将为空。

Windows注册表配置单元 (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.

在下面的示例中，PowerShell命令显示“ Microsoft SQL Server”注册表子项中包含的每个注册表项的值名称和数据。

• Note: 注意：
  1. By default, PowerShell drive named HKLM: is mapped to the “HKEY_LOCAL_MACHINE” hive of the registry. 默认情况下，名为HKLM：的PowerShell驱动器映射到注册表的“ HKEY_LOCAL_MACHINE”配置单元。
  2. The easiest way to read remote SQL instances is by using Invoke-Command. 读取远程SQL实例的最简单方法是使用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 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.

列出了SQL Server实例的数组作为Invoke-Command的输入。 在这种情况下，将列出两个SQL Server实例hqdbt01和hqdbsp17。 第二个命令使用Get-ItemProperty命令来检索列出的实例的InstalledInstances属性。 然后将输出存储在变量$ SQLInstances中。 后来，该变量通过迭代进一步转换以显示对象的ServerName和InstanceName属性。

• Note: [PSCustomObject] is very helpful to create structured stream of data. The [pscustomobject] is relatively much faster as it treats hashtables properties as objects.
• 注意：[PSCustomObject]对创建结构化的数据流非常有用。 [pscustomobject]相对更快，因为它将哈希表属性视为对象。

In the following example, the input CSV file server_test.csv is read from a local drive. The file contains a list of servers.

在以下示例中，从本地驱动器读取输入的CSV文件server_test.csv 。 该文件包含服务器列表。

PS P:\> Import-Csv -Path c:\server_test.csv

PS P：\>导入Csv-路径c：\ server_test.csv

The output list the content of the CSV file c:\server_test.csv

输出列出了CSV文件c：\ server_test.csv的内容

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

这是一个PowerShell脚本，它接受输入并从注册表中读取SQL Server信息。

$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.

从输入文件中读取ServerName字段，然后将其通过管道传递给Invoke-Command以遍历所有服务器。 从远程服务器读取注册表项，并将其存储在变量中以进行进一步处理。 然后处理分配的值以列出ServerName和InstanceName属性。

• Note: This method ‘Reading registry subkeys’ is relatively much faster than other methods.注意：此方法“读取注册表子项”比其他方法要快得多。

获取服务cmdlet (Get-Service cmdlet)

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

Get-Service是PowerShell cmdlet，它列出了本地或远程服务器上所有已注册的Windows服务。

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.

输出列出了远程计算机SQL实例。 这是因为每个实例还运行相应的服务。 您还可以查看Where子句，可以在其中根据您的要求指定条件。

• 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).注意：我们都知道在服务器上执行的每个SQL安装都会注册Windows服务，因此Get-Service cmdlet是获取服务器上所有SQL实例列表的最简单方法。

使用定义的IP范围 (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.

在以下示例中，输入是定义的IP地址范围。 IP地址范围是10.20.20.1至10.20.20.255。 使用foreach循环遍历IP。 首先，对每台机器进行成功连接测试。 之后，将IP地址馈送到System.Net.Dns类以获取每个条目的主机名。 列出了与每台计算机对应SQL Service，并显示了Get-Service对象的ServerName和InstanceName属性。

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:
  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
• 注意：
  1. GetHostByAddress（IP）是一种从指定的IPAddress创建IPHostEntry（主机名）实例的方法。
  2. 我花了大约9分钟的时间遍历255台计算机以列出所有SQL实例

SMO (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.

SMO代表SQL管理对象。 它是一组专门设计用于SQL Server的.NET库。 这些库存储在.NET DLL中，并在Import-Module期间随类一起加载。 从这些类中，您可以创建检索SQL Server属性的对象。 例如，存在用于数据库，表，列等的类。

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

  2. You can refer to the article: Install SQL Server PowerShell module for more information.

  3. A detailed explanation of the .NET class library is explained in the article: 6 methods to write PowerShell output to a SQL Server table
  4. 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
• 笔记：
  1. 从PSGallery安装PowerShell SQLServer模块

     安装模块-名称SQLServer

  2. 您可以参考文章： 安装SQL Server PowerShell模块以获取更多信息。

  3. .NET类库的详细说明在文章中进行了解释： 6将PowerShell输出写入SQL Server表的方法
  4. 准备好实例详细信息后，此方法非常简单实用。 实例化SMO对象允许查看SQL实例的属性

摘要 (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.

在本文中，我们讨论了各种方法来访问所有SQL实例的基本信息。 自动发现选项使我们可以保留SQL服务器清单，这对于许多事件很有用。 希望您喜欢这篇文章。 随时在下面发表评论。

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