Как найти все sql сервера в сети

Searching for and cataloging the SQL server instances on your network is a key part of Administration. Powershell offers some quick and simple methods to find servers base on the their name or description in Active Directory. We can use the Powershell module to collect these servers and add them to a CSV file or a SQL table updated regularly.

In our example we will be looking for all of the SQL servers in Active Directory based on their Name or Description and then run some remote code to grab the SQL instances. Our assumption here is that you have implemented some level of Server naming convention and/or kept your AD Descriptions accurate.

The Active Directory module is not installed by default, at least not on Windows 10. You may need to install the tools called RSAT to make this module available. To see if it is already installed you can simple run the Import-Module command.

Install The Active Directory Module

Import-module -Name ActiveDirectory

Install Remote Server Administrative Tools (RSAT)

I was going to add screenshots and a walk through on how to add RSAT through the Control Panel -> Windows Features screen but then I found a Powershell method to do this,.. so much easier.

You will need to import the ServerManager module first to Add Windows Features, Use the following to do so in a Powershell window.

Import-Module -Name ServerManager
Add-WindowsFeature -Name "RSAT-AD-PowerShell" –IncludeAllSubFeature

Import-module -Name ActiveDirectory

Query Active Directory Using Powershell

With the AD module imported we now have access to the Get-ADComputer cmdlet. This will provide the details from Active Directory and allow us to filter the results based on our search criteria. Begin with a simple search looking for the Windows Operating system.

Get-ADComputer -Filter 'operatingSystem -like "*Windows *"' -Properties *

In this command we are filtering based on the “OperatingSystem” property of the AD object. You will notice we are using the asterisk to search for text that “contains” Windows. Change this to filter based on the Windows version if need be. We are also using the asterisk for the “-Properties” command switch so the search includes all properties in AD.

At this point we us the Pipeline and pipe the results to a Where-Object cmdlet to filter the results based on the “Name” or “Description” properties in Active Directory. As stated before the key to this search being useful is that the server naming convention or Description includes “SQL” in it’s text.

Change the ‘*SQL*’ to search for your naming convention or use it to further filter Production or Test servers based on their Name.

 Get-ADComputer -Filter 'operatingSystem -like "*Windows *"' -Properties * |
 where {$_.Name -like '*SQL*' -or $_.CN -like '*SQL*' -or $_.Description -like '*SQL*'} 

Since the command can take a long time depending your network size, we can assign the results to a variable that will stay populated as long as your Powershell session is active.

$SQLServers = Get-ADComputer -Filter 'operatingSystem -like "*Windows *"' -Properties * |
 where {$_.Name -like '*SQL*' -or $_.CN -like '*SQL*' -or $_.Description -like '*SQL*'} 

When this command completes, the $SQLServers variable can be piped to filter further or display. Pipe the results to a basic Select-Object cmdlet like this

Note: Be sure to highlight and then Run using the “Run Selection” (F8) instead of “Run Script” (F5).

$SQLServers | Select -Property Name,DNSHostName,operatingSystem, Description
$SQLServers | format-table -AutoSize
$SQLServers | Out-Gridview

Get SQL Instances On Remote Server

Now that we have a list of the Servers that should contain a SQL Service we can connect to each and find the SQL services and their instances. This code applies to a single static server but can be used inside a ForEach loop to go through the above results and collect SQL instances from each server.

This method can be applied to any service on the machine but we are focusing on SQL Instances here. The default SQL instance is called “MSSQLServer” and any named instances are prefaced with “MSSQL$” and the Instance name.

Get-Service -ComputerName VMDESKTOP3 | 
Where-Object { ($_.Name -eq 'mssqlserver' -or $_.Name -like 'mssql$*') -and $_.DisplayName -like 'SQL Server*'}

Take note of the Where-Object syntax as some will get confused grouping their “Equal, Like, And’s and Or’s”. The parenthesis allows us to group the filter as Equal to “MSSQLSERVER” or Like “MSSQL$” and the brackets enclose the full WHERE cmdlet.

      В данной статье рассмотрим два варианта получения доступных в сети SQL серверов.

• С использованием стандартного класса «SqlDataSourceEnumerator» из Microsoft Net. Fraemwork; 
• С использованием WinAPI.

1)      Для получения доступных SQL серверов на локальном компьютере или в сети необходимо воспользоваться классом «SqlDataSourceEnumerator», который обеспечивает доступ к этим сведениям, предоставляя объект «DataTable» с данными обо всех видимых серверах. Для этого необходимо вызвать метод «GetDataSources», который возвращает таблицу «DataTable» со сведениями о доступных серверах:

DataTable dt = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources()

      Таблица, возвращенная в результате вызова этого метода, содержит следующие столбцы, причем все эти столбцы содержат значения «string».

• «ServerName» – Имя сервера.
• «InstanceName» – Имя экземпляра сервера. Пуст, если сервер выполняется как используемый по умолчанию экземпляр.
• «IsClustered» – Указывает, является ли сервер частью кластера.
• «Version» – Версия сервера.

      Сведения о серверах из списка могут включать или не включать такие дополнительные данные, как «IsClustered» и версия (Version). Это зависит от того, каким образом был получен список. В списках серверов, полученных с помощью службы обозревателя «SQL Server», присутствует больше сведений, чем в списках серверов, найденных с помощью инфраструктуры Windows и содержащих только имена.
      Так же из-за особенностей механизма, используемого «SqlDataSourceEnumerator» для поиска источников данных в сети, этот метод не всегда возвращает полный список доступных серверов, и для последовательных вызовов содержимое списка может изменяться в зависимости от таких факторов, как время ожидания и сетевой трафик. Это может привести к тому, что при двух последовательных вызовах будут получены разные списки. В список входят только серверы, находящиеся в одной сети. Широковещательные пакеты обычно не проходят через маршрутизаторы, поэтому некоторый сервер может отсутствовать в списке, но будет стабильно работать.
      Если вы планируете применять данную функцию, чтобы дать возможность пользователю выбрать сервер из списка, обязательно предоставляйте пользователю возможность ввести отсутствующее в списке имя сервера на случай, если перечисление вернет не все доступные серверы.
      Кроме того, этот метод может выполняться довольно долго, поэтому в ситуациях, когда приоритетным является быстродействие, пользоваться им следует с осторожностью.

      В «SQL Server 2000» данные для «SqlDataSourceEnumerator» предоставляются внутренним образом. Но в версии «SQL Server 2005» эти сведения предоставляются с использованием внешней службы Windows, называемой обозревателем SQL. Применение этой службы разрешено по умолчанию, но администраторы могут ее выключать или запрещать, в результате чего соответствующий экземпляр сервера становится невидимым для указанного класса. Эта служба применяется только в версии «SQL Server 2005» и не оказывает влияния на поведение «SQL Server 2000». Оборудование и программное обеспечение так же могут налагать свои ограничения на возможность поиска экземпляров «SQL Server».
      Ниже представлен пример получения и вывода сокращенной информации обо всех найденных в сети и (или) локальных SQL серверов в элемент управления «ComboBox» и более полной информации в элемент управления «RichTextBox».

//Переменная для хранения информации о 
//найденных серверах.
string ServerInfo = string.Empty;

//Получение доступных SQL серверов.
DataTable dt = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources();
foreach (DataRow dr in dt.Rows)
{
    //Вывод найденной информации о серверах
    //в элемент управления СomboBox.
    comboBox1.Items.Add(string.Concat(dr["ServerName"], "\", dr["InstanceName"]));

    //Добавление пустой строки после получения 
    //информации о сервере
    foreach (DataColumn col in dt.Columns)
    {
        ServerInfo += String.Format("{0,-15}: {1}", col.ColumnName, dr[col]) + Environment.NewLine;
    }

    //Добавление пустой строки после получения 
    //информации о сервере.
    ServerInfo += Environment.NewLine;                
}

//Вывод найденной информации о серверах
//в элемент управления RichTextBox.
richTextBox1.Text = ServerInfo;

2)      В данном примере необходимо добавить класс «SqlLocator» в листинг формы, где будет выполняться запуск процесса поиска и вывода найденных SQL серверов в сети.

public class SqlLocator
{
[System.Runtime.InteropServices.DllImport("odbc32.dll")]
private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle);
[System.Runtime.InteropServices.DllImport("odbc32.dll")]
private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength);
[System.Runtime.InteropServices.DllImport("odbc32.dll")]
private static extern short SQLFreeHandle(short hType, IntPtr handle);
[System.Runtime.InteropServices.DllImport("odbc32.dll", CharSet = System.Runtime.InteropServices.CharSet.Ansi)]
private static extern short SQLBrowseConnect(IntPtr hconn, StringBuilder inString,
 short inStringLength, StringBuilder outString, short outStringLength,
 out short outLengthNeeded);

private const short SQL_HANDLE_ENV = 1;
private const short SQL_HANDLE_DBC = 2;
private const int SQL_ATTR_ODBC_VERSION = 200;
private const int SQL_OV_ODBC3 = 3;
private const short SQL_SUCCESS = 0;

private const short SQL_NEED_DATA = 99;
private const short DEFAULT_RESULT_SIZE = 1024;
private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";

private SqlLocator() { }

public static string[] GetServers()
{
    string[] retval = null;
    string txt = string.Empty;
    IntPtr henv = IntPtr.Zero;
    IntPtr hconn = IntPtr.Zero;
    StringBuilder inString = new StringBuilder(SQL_DRIVER_STR);
    StringBuilder outString = new StringBuilder(DEFAULT_RESULT_SIZE);
    short inStringLength = (short)inString.Length;
    short lenNeeded = 0;

    try
    {
        if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv))
        {
            if (SQL_SUCCESS == SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, 0))
            {
                if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn))
                {
                    if (SQL_NEED_DATA == SQLBrowseConnect(hconn, inString, inStringLength, outString,
                     DEFAULT_RESULT_SIZE, out lenNeeded))
                    {
                        if (DEFAULT_RESULT_SIZE < lenNeeded)
                        {
                            outString.Capacity = lenNeeded;
                            if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength, outString,
                             lenNeeded, out lenNeeded))
                            {
                                throw new ApplicationException("Unabled to aquire SQL Servers from ODBC driver.");
                            }
                        }
                        txt = outString.ToString();
                        int start = txt.IndexOf("{") + 1;
                        int len = txt.IndexOf("}") - start;
                        if ((start > 0) && (len > 0))
                        {
                            txt = txt.Substring(start, len);
                        }
                        else
                        {
                            txt = string.Empty;
                        }
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {                
        #if (DEBUG)
        MessageBox.Show(ex.Message, "Acquire SQL Servier List Error");
        #endif
        txt = string.Empty;
    }
    finally
    {
        if (hconn != IntPtr.Zero)
        {
            SQLFreeHandle(SQL_HANDLE_DBC, hconn);
        }
        if (henv != IntPtr.Zero)
        {
            SQLFreeHandle(SQL_HANDLE_ENV, hconn);
        }
    }

    if (txt.Length > 0)
    {
        retval = txt.Split(",".ToCharArray());
    }

    return retval;
}

     Для получения списка SQL серверов с помощью данного класса необходимо вызвать из него метод «GetServers», который возвращает строковый массив с доступными в сети серверами.

//Предварительно очищаем все элементы управления
//в которые будут выводиться данные
comboBox1.Items.Clear();
richTextBox1.Text = string.Empty;

string[] theAvailableSqlServers = SqlLocator.GetServers();
if (theAvailableSqlServers != null)
{
    comboBox1.DataSource = theAvailableSqlServers;
    foreach (string NameServer in theAvailableSqlServers)
    {
         richTextBox1.Text += NameServer + Environment.NewLine;
    }
}
else
{
    MessageBox.Show("SQL сервера не найдены!");
}