如何查找数据库服务器ip_多服务器管理–查找数据库服务器

如何查找数据库服务器ip

Every organization has at least one of those guys. You are the guy if you have been with the company long enough that you are very familiar with all of the servers in multiple environments. You can easily translate the SharePoint server into its server name, CL-DB-001-B\SP_2010 and you know all of the DNS aliases pointing to that server as well. Your super power also makes you a magnet for everyone in IT to come and ask you where this database is and what is on that server.

每个组织都有至少一个这样的人。 你是，如果你已经与该公司足够长的时间，你都非常熟悉所有在多种环境中的服务器的家伙 。 您可以轻松地在SharePoint本身 rver转化为它的服务器名，CL-DB-001-B \ SP_2010，你知道所有指向该服务器以及该DNS别名。 您的超级能力也使您吸引了IT界的每个人，并询问您该数据库在哪里以及该服务器上的内容。

While that guy may look like the nerdy stud that everyone is impressed with, this situation is actually a symptom of a documentation or tooling problem because, someday, that guy will leave the company with everyone else fumbling to find their databases.

虽然那个家伙看起来像每个人都对它印象深刻的书呆子，但这种情况实际上是文档或工具问题的征兆，因为总有一天， 那个家伙将离开公司，而其他所有人都在寻找他们的数据库。

As I mentioned, the solution to this problem is documentation, tooling, or both. You could document your server list in something such as an Excel spreadsheet or text file. A method like this can work but only if your team has a high level of discipline. That spreadsheet will only be accurate until the next time something changes, unless it is updated continuously. Maybe adding or removing a server name isn’t very difficult or frequent but I’ve found that databases come, go, and move, more often than people are willing to update documentation. For this reason, I prefer using a tool to help me manage my servers and search for databases.

正如我提到的，解决此问题的方法是使用文档，工具或同时使用两者。 您可以用诸如Excel电子表格或文本文件之类的文件记录服务器列表。 这样的方法可以工作，但前提是您的团队必须有严格的纪律。 该电子表格只有在下一次更改之前才是准确的，除非不断更新。 也许添加或删除服务器名称不是很困难或不常见，但是我发现数据库的出现，迁移和移动的频率比人们愿意更新文档的频率更高。 因此，我更喜欢使用一种工具来帮助我管理服务器和搜索数据库。

In this article I will cover my preferred organizational method for setting up a CMS server list, demonstrate a T-SQL search method which can easily be put into a SQL Server Reporting Services report (SSRS is not in scope for this article), and demonstrate a PowerShell search function.

在本文中，我将介绍用于设置CMS服务器列表的首选组织方法，演示可轻松放入SQL Server Reporting Services报表中的T-SQL搜索方法（本文不涉及SSRS），并演示PowerShell搜索功能。

SQL Server中央管理服务器 (SQL Server Central Management Server)

SQL Server has a feature called Central Management Server (CMS). It has a number of useful features such as multiple server querying, a central point of management for SQL Agent jobs, and central administration of policy based management. However, today we are going to focus on the most basic of features, its ability to store a server list.

SQL Server具有称为中央管理服务器 （CMS）的功能。 它具有许多有用的功能，例如多服务器查询，SQL Agent作业的中央管理点以及基于策略的管理的中央管理。 但是，今天，我们将重点关注最基本的功能，即其存储服务器列表的功能。

For the sake of brevity I am going to skip the tutorial on how to set up your CMS and add servers to the list. If you have never used CMS before, check out these instructions.

为简洁起见，我将跳过有关如何设置CMS并将服务器添加到列表的教程。 如果您以前从未使用过CMS，请查看以下说明 。

For those of you who already have a text file or Excel document with a list of servers, here is a PowerShell script which will import them into your CMS to prevent a lot of copy & pasting.

对于已经拥有带有服务器列表的文本文件或Excel文档的用户，以下是一个PowerShell脚本，该脚本会将它们导入到CMS中，以防止大量复制和粘贴。

NOTE: Make sure to update the server list source, server name , and CMS folder, highlighted above.

注：请务必更新服务器 列表源， 服务器名称 和 CMS文件夹，上面所强调的。

使用T-SQL搜索 (Searching with T-SQL)

I have found that there are two common types of searches that an administrator would typically conduct on a server list. The first is a search on various characteristics of the server. For example, you might want to find all servers that are still on SQL Server 2005, servers that support a particular application, or servers owned by a particular analyst or business unit. The second search is when you are looking for the correct server name or connection string for a database.

我发现管理员通常会在服务器列表上进行两种常见的搜索类型。 首先是搜索服务器的各种特征。 例如，您可能想要查找仍在SQL Server 2005上的所有服务器，支持特定应用程序的服务器或特定分析师或业务部门拥有的服务器。 第二个搜索是在寻找数据库的正确服务器名称或连接字符串时。

元数据搜索 (Metadata search)

When you create a registered server in the CMS, you can add a description. This is a great opportunity to store your searchable metadata. Typically I will create a description string similar to this.

在CMS中创建注册服务器时，可以添加描述。 这是存储可搜索元数据的绝佳机会。 通常，我将创建与此类似的描述字符串。

SQL Version: SQL Server 2005 – 9.00.1399.06; OS Version: Windows Server 2003 – SP1; Owner: Jimmy Smith; Application(s): SharePoint, HomeGrownApp2; Alias(es): None; Environment: Production;

SQL版本：SQL Server 2005 – 9.00.1399.06； 操作系统版本：Windows Server 2003 – SP1； 所有者：吉米·史密斯（Jimmy Smith）； 应用程序：SharePoint，HomeGrownApp2； 别名：无； 环境：生产；

With this information, plus the required display name and server name, most of the commonly search attributes can be searched by querying just the CMS. Below is a highly generic query for finding your servers with any folder CMS structure. Check out the reference for a more advanced version that is more specific to a particular structure.

使用此信息，再加上必需的显示名称和服务器名称，可以通过仅查询CMS来搜索大多数常用搜索属性。 下面是一个通用查询，用于查找具有任何文件夹CMS结构的服务器。 请查阅参考资料，以获得更特定于特定结构的更高级版本。

Reference: Querying Central Management Servers

参考： 查询中央管理服务器

数据库搜索 (Database search)

Searching for a database incurs a bit more overhead than searching for metadata. This is because a server’s metadata will not likely change often, which makes manually updating it as part of your change process reasonable. Databases on the other hand, could come and go at a much higher frequency, depending upon how your organization works. Also, keeping a large number of database names in your CMS registered server’s description block is not ideal.

搜索数据库比搜索元数据产生更多的开销。 这是因为服务器的元数据不太可能经常更改，这使得作为更改过程一部分的手动更新变得合理。 另一方面，取决于组织的工作方式，数据库的来回频率可能更高。 同样，在CMS注册服务器的描述块中保留大量数据库名称也不是理想的选择。

In order to know that you have the most up-to-date information, we will have to query each server in your list to find any database(s) with a particular keyword in its name. Fortunately, CMS comes with a feature to query multiple servers at once.

为了知道您具有最新信息，我们将必须查询列表中的每台服务器以查找名称中带有特定关键字的任何数据库。 幸运的是，CMS具有一次查询多个服务器的功能。

Simply right click on one of your CMS folders and select New Query to create a query window that is connected to all of the servers in that folder.

只需右键单击一个CMS文件夹，然后选择“ 新建查询”即可创建一个查询窗口，该窗口连接到该文件夹​​中的所有服务器。

Reference: MSDN instructions

参考： MSDN指令

Once your window is open you will see that the bar at the bottom of the query window is a different color and there will be an indicator of how many servers have been successfully connected to. From here, you can run this query to a list of the servers with databases that match your keyword.

窗口打开后，您将看到查询窗口底部的栏为不同的颜色，并且将指示已成功连接了多少服务器。 从这里，您可以对包含与关键字匹配的数据库的服务器列表运行此查询。

使用PowerShell搜索 (Searching with PowerShell)

I have a natural love for PowerShell. Fortunately, that is not the only reason to use it for this type of searching. When I present the T-SQL solutions, I have received feedback suggesting that the database names get added to the metadata of the server. That way you would not need two queries and you would not need to connect to each server, which can be slow. I don’t like putting database names in the metadata because databases come, go, and get renamed, far more often than the server metadata does. Including the databases in the metadata causes a maintenance burden and the purpose here is to reduce the need for your time and effort.

我天生喜欢PowerShell。 幸运的是，这不是将其用于此类搜索的唯一原因。 当我介绍T-SQL解决方案时，我收到了反馈，建议将数据库名称添加到服务器的元数据中。 这样，您将不需要两个查询，也不需要连接到每个服务器，这可能很慢。 我不喜欢在元数据中添加数据库名称，因为数据库的出现，迁移和重命名的频率远高于服务器元数据。 将数据库包含在元数据中会造成维护负担，此处的目的是减少您的时间和精力。

I, however, agree with it being a hassle to have two queries and you are going to want to maximize your use of asynchronous processes when querying all of the servers on your list for a database name search. PowerShell enables us to do all of this in a single function or cmdlet.

但是，我同意麻烦的是要进行两个查询，并且在查询列表中的所有服务器以进行数据库名称搜索时，您将希望最大限度地利用异步过程。 PowerShell使我们能够在单个函数或cmdlet中完成所有这些操作。

元数据搜索 (Metadata search)

For the metadata search I will leverage a PowerShell function which I first blogged about here. In that post I demonstrated how you can easily retrieve a list of servers based on the group or folder structure of your CMS. Those functions are used in this Search-CMS function which will search the description field of your registered servers.

对于元数据搜索，我将利用我首先在此处发布的PowerShell函数。 在那篇文章中，我演示了如何轻松地基于CMS的组或文件夹结构检索服务器列表。 这些功能在此Search-CMS功能中使用，该功能将搜索已注册服务器的描述字段。

 
Function Search-CMS 
{
    param
    (
        [Parameter(Mandatory=$false,
                   ValueFromPipeline=$True,
                   ValueFromPipelineByPropertyName=$True)]
        [string[]]$Keywords = $null, 
        
        [Parameter(Mandatory=$false,
                   ValueFromPipeline=$True,
                   ValueFromPipelineByPropertyName=$True)]
        [string]$cmsServer = '.\sqlexpress', 
 
        [Parameter(Mandatory=$false,
                   ValueFromPipeline=$True,
                   ValueFromPipelineByPropertyName=$True)]
        [string]$rootServerGroup = 'DataCenter1'
    )
 
    begin
    {
        Function Get-CMSServerList ([string]$cmsName, [string]$serverGroup, [switch]$recurse)
        {
            begin
            {
                if(-not (Get-Module | where {$_.Name -eq 'SQLPS'}))
                {
                    Import-Module SQLPS -DisableNameChecking
                }
 
                Function Parse-CMSServerGroup($serverGroup)
                {
                    $results = $serverGroup.RegisteredServers;
                    foreach($group in $serverGroup.ServerGroups)
                    {
                        $results += Parse-CMSServerGroup -serverGroup $group;
                    }
                    return $results;
                }
            }
 
            process
            {
                $connectionString = "data source=$cmsName;initial catalog=master;integrated security=sspi;"
                $sqlConnection = New-Object ("System.Data.SqlClient.SqlConnection") $connectionstring
                $conn = New-Object ("Microsoft.SQLServer.Management.common.serverconnection") $sqlconnection
                $cmsStore = New-Object ("Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore") $conn
                $cmsRootGroup = $cmsStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups[$serverGroup]
     
                if($recurse)
                {
                    return Parse-CMSServerGroup -serverGroup $cmsRootGroup
                }
                else
                {
                    return $cmsRootGroup.RegisteredServers
                }
            }
        }
    }
 
    process
    {
        Trap {
          $err = $_.Exception
          while ( $err.InnerException )
            {
            $err = $err.InnerException
            write-output $err.Message
            };
            continue
          }
 
        if(-not $Keywords)
        {
            Write-Warning "No keywords were provided. Dumping the complete server list and descriptions.";
            $SearchDatabaseName = $false;
        }
      
        $tempResults = @();
        $Keywords = ($Keywords | % { [regex]::escape($_) } ) -join "|"
 
        $serverList = Get-CMSServerList -cmsName $cmsServer -serverGroup $rootServerGroup -recurse;    $serverListOutput = $serverList | Where-Object {$_.Description -match $Keywords} | Select-Object ServerName, Description;
 
        foreach($result in $serverListOutput)
        {
            $tempResults += $result;
        }
            
        $results = ($tempResults | Select-Object ServerName, Description -Unique) | Format-List;
    
        return $results;
    }
} 
 

To use this function you can pass in a single string or an array of strings for search keywords. I like to default my cmsServer and rootServerGroup parameters, as seen in the example above, but you can also pass them in as I have below.

要使用此功能，您可以为搜索关键字传递单个字符串或字符串数​​组。 我喜欢默认设置cmsServer和rootServerGroup参数，如上面的示例所示，但是您也可以像下面一样将它们传递给我。

Here I have searched the metadata for known version indicators. I know that the SQL Server version is in the description and I want to find all servers still running SQL Server 2005. I also want to see all servers which are still operating on Windows Server 2003.

在这里，我已经在元数据中搜索了已知的版本指示符。 我知道说明中包含SQL Server版本，并且我想找到所有仍在运行SQL Server 2005的服务器。我还想查看所有仍在Windows Server 2003上运行的服务器。

This is already very useful but it has not, yet, met the full requirements. Let’s take a look at what needs to change to incorporate the database search.

这已经非常有用，但尚未满足全部要求。 让我们看一下合并数据库搜索需要进行哪些更改。

添加数据库搜索 (Adding the database search)

To perform our database search efficiently, we will need to spawn asynchronous threads to query each server in parallel. A typical iterative loop would take too long and our time is very important, especially if your server list is quite large. To achieve this, we will use the Start-Job cmdlet.

为了有效地执行数据库搜索，我们将需要生成异步线程来并行查询每个服务器。 典型的迭代循环将花费很长时间，并且我们的时间非常重要，尤其是在您的服务器列表很大的情况下。 为此，我们将使用Start-Job cmdlet。

The link provided covers the basics if you need a refresher or overview.

如果需要复习或概述，提供的链接涵盖了基础知识。

Here I will modify the process block of the Search-CMS function shown above and add a new parameter, [switch]$SearchDatabaseName.

在这里，我将修改上面显示的Search-CMS函数的处理块，并添加一个新参数[switch ] $ SearchDatabaseName 。

 
process
    {
        Trap {
          $err = $_.Exception
          while ( $err.InnerException )
            {
            $err = $err.InnerException
            write-output $err.Message
            };
            continue
          }
 
        if(-not $Keywords)
        {
            Write-Warning "No keywords were provided. Dumping the complete server list and descriptions.";
            $SearchDatabaseName = $false;
        }
      
        $tempResults = @();
        $Keywords = ($Keywords | % { [regex]::escape($_) } ) -join "|"
 
        $serverList = Get-CMSServerList -cmsName $cmsServer -serverGroup $rootServerGroup -recurse;
 
        if($SearchDatabaseName)
        {
            $jobs = @();
            foreach($server in $serverList)
            {
                $args = @();
                $args += $connTimeout;
                $args += $server.Name;
                $args += $Keywords;
                
                $jobs += Start-Job -ScriptBlock {   
                    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
                    $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $args[1]
                    $server.ConnectionContext.ConnectTimeout = $args[0];
                    if($server.Version.Major -eq $null) 
                    { 
                        Write-Host "Could not establish connection to $($args[1])." 
                    } 
                    else 
                    { 
                        $servers = @();
                        foreach($database in ($server.Databases).Name)
                        {
                            if($database -match $($args[2]))
                            {
                                $obj = New-Object psobject;
                                Add-Member -InputObject $obj -MemberType NoteProperty -Name Result -Value 'True';
                                Add-Member -InputObject $obj -MemberType NoteProperty -Name ServerName -Value $($server.Name);
                                $servers += $obj;
                            }
                        }
                        return $servers;
                    } 
                } -ArgumentList $args;
            }
          
            $joboutput = ($jobs | Wait-Job | Receive-Job | Where-Object { $_.Result -eq 'True' } | Select-Object ServerName)
            $jobs | Remove-Job;
 
            foreach($server in $joboutput)
            {
                $tempResults += $server;
            }
 
            $results = ($tempResults | Select-Object ServerName -Unique) | Format-Table;
        }
        else
        {
            $serverListOutput = $serverList | Where-Object {$_.Description -match $Keywords} | Select-Object ServerName, Description;
 
            foreach($result in $serverListOutput)
            {
                $tempResults += $result;
            }
            
            $results = ($tempResults | Select-Object ServerName, Description -Unique) | Format-List;
        }
    
        return $results;
    } 
 

This process:

这个流程：

1. Checks to see if you want to search databases or not.
   检查是否要搜索数据库。
2. If database search is selected, a thread will be spawned for each server in your list.
   如果选择了数据库搜索，将为列表中的每个服务器生成一个线程。
3. On each thread, the Microsoft.SqlServer.Management.Smo.Server class is used to query the database list.
   在每个线程上，Microsoft.SqlServer.Management.Smo.Server类用于查询数据库列表。
4. If the database is found, the server name is appended to a temporary variable.
   如果找到数据库，则服务器名称将附加到临时变量。
5. The temporary variable is then distinct sorted and formatted for output.
   然后对临时变量进行不同的排序和格式化以输出。
6. Finally, you have a list of all servers with databases that match your keyword(s).
   最后，您将获得所有服务器的列表，这些服务器的数据库与您的关键字相匹配。

结语 (Wrap-up)

In this article I have discussed the challenges of managing multiple servers and a couple of common questions that come up often. I then demonstrated how SQL Server’s Central Management Server can organize your server list and maintain it in a central location which is advantageous in a collaborative environment. There are T-SQL means of searching and sorting your server list, if you prefer to use SQL Server Management Studio. Personally, I prefer to use a single custom PowerShell function to meet those needs.

在本文中，我讨论了管理多个服务器的挑战以及经常出现的几个常见问题。 然后，我演示了SQL Server的中央管理服务器如何组织服务器列表并将其维护在中央位置，这在协作环境中非常有利。 如果您更喜欢使用SQL Server Management Studio，则可以使用T-SQL搜索和排序服务器列表的方法。 就个人而言，我更喜欢使用单个自定义PowerShell功能来满足这些需求。

Download the complete Search-CMS script.

下载完整的Search-CMS脚本 。

翻译自: https://www.sqlshack.com/multiple-server-management-finding-the-database-server/

如何查找数据库服务器ip