Jaki jest najbardziej skuteczny sposób na wykrycie wszystkich uruchomionych wystąpień SQL Server przy użyciu PowerShell?

Zadanie polegało mi na odkryciu wszystkich wystąpień SQL Server, które działają w naszej domenie. W kilku przypadkach istnieje wiele wystąpień na serwer. Widziałem dwie różne metody wyszukiwania tych wystąpień w PowerShell, ale żadna z nich nie wydaje się znajdować wszystkich.

1) Użyj WMI

        $srvr = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $computerName
    $instances = $srvr | ForEach-Object {$_.ServerInstances} | Select @{Name="fullName";Expression={$computerName +"\"+ $_.Name}}   
    return $instances

2) Użyj zdalnego rejestru (jak w Get-SQLInstance 1 )

Największym problemem, na jaki się natknęłam, jest to, że nie wszystkie serwery, o których wiem, działają z dostawcą WMI programu SQL Server, ani nie wszystkie zezwalają na zdalny rejestr. Czy istnieje trzecia metoda? Mogę użyć Pulpitu zdalnego, aby uzyskać dostęp do wszystkich serwerów, ale patrzę na około 30 komputerów i chciałbym w miarę możliwości unikać ręcznych kroków. To musi działać tylko w SQL Server 2008 i nowszych wersjach i chociaż dobrze byłoby wiedzieć o innych usługach SQL Server (SSIS / SSAS / SSRS), moim głównym celem jest sam SQL Server.

Jeśli chcesz czegoś, co będzie przydatne w przyszłości, prawdopodobnie unikałbym przeszukiwania rejestru. Ule dla SQL Server zmieniły się nieco na przestrzeni lat i nadążanie za nimi może być kłopotliwe.

Metoda z SqlDataSourceEnumeratorczasem jest niestabilna i chociaż jej użyję, nie ma konkretnych dowodów na to, że instancje są w sieci. Uważam, że zależy to również od usługi przeglądarki SQL, która przez większość czasu jest wyłączona.

Wykorzystam klasę WMI win32_Service. Używam tego, ponieważ oferuje więcej informacji o usłudze niż Get-Servicecmdlet.

Piszę wszystko jako funkcje ogólnie, ponieważ można tego użyć do codziennego sprawdzania lub weryfikacji usługi w celu rozwiązania problemów.

function Get-ServiceStatus ([string[]]$server)
{
 foreach ($s in $server)
 {
   if(Test-Connection $s -Count 2 -Quiet)
   {
    Get-WmiObject win32_Service -Computer $s |
     where {$_.DisplayName -match "SQL Server"} | 
     select SystemName, DisplayName, Name, State, Status, StartMode, StartName
   }
 }
}

To trochę więcej niż zwykle używam, ale na wypadek, gdyby ktoś inny się zetknął i chciałby z niego skorzystać. Jest to Test-Connectionrównoznaczne z ping myserverpoleceniem DOS, a -Quietflaga po prostu zwraca ją truelub false. Będzie to domyślnie 4 pingi, więc ustawienie -Count 2powoduje, że zamiast tego zrobi to dwa razy.

Zmienna [string[]]$serverjest metodą stosowaną do stwierdzenia, że $serverzaakceptuje tablicę nazw serwerów. Przykładowe wywołanie tej funkcji może wyglądać mniej więcej tak:

Get-ServiceStatus -server (Get-Content C:\temp\MyServerList.txt)

lub

$servers = 'MyServer1','MyServer2','MyServer3'
Get-ServiceStatus -server $servers

EDYTOWAĆ

Zauważony komentarz jest taki, że powyższe zależy od dostarczonej listy serwerów. W przypadkach, w których nie ma tej listy, masz kilka innych opcji.

• Jeśli jestem w środowisku Active Directory, mogę użyć modułu ActiveDirectory w PowerShell, aby pobrać listę wszystkich serwerów w domenie za pomocą polecenia Get-ADComputercmdlet. Słowo ostrzeżenia jednak upewnij się, że używasz dobra -Filterw dużych domenach.

• Po prostu wykonałem również skanowanie IP (za zgodą) sieci, która daje mi adresy IP, na których znaleziono port 1433. Wezmę tę listę adresów IP i wykorzystam Get-ADComputerdo znalezienia nazw komputerów w domenie, a następnie przekażę to do powyższej funkcji

Przykład:

Import-Module ActiveDirectory
$sList = $ipList | Select -ExpandProperty IP
$results = foreach ($i in $sList) { 
 Get-ADComputer -Filter 'IPv4Address -eq $i' -Properties * | Select Name}
Get-ServiceStatus -server $results

EDYTOWAĆ

Sugerowana edycja do wykorzystania, Write-Verbosea także dodania w bloku try / catch, chociaż może to być przydatne, aw większości przypadków praktyka kodu, pozostawię to osobie, która chce użyć tej funkcji, aby dodać ten dodatkowy kod lub funkcjonalność. Po prostu próbuję podać podstawowy przykład. Dodałem SystemNamewłaściwość do danych wyjściowych, aby uwzględnić rzeczywiste informacje o zwrocie nazwy serwera, zrób to na innych funkcjach, ale na ogół nie używaj tego dla więcej niż jednego serwera na raz, więc o tym pomyślałem.

Jedynym sposobem, w jaki znam wykrywanie instancji w środowisku bez znajomości wszystkich możliwych serwerów będących ich właścicielami i ich konkretnych nazw, byłoby wywołanie funkcji System.Data.Sql.SqlDataSourceEnumerator.GetDataSources (). Ta metoda ma jednak wiele przypisów. Oto fragment, który jest pobierany bezpośrednio z tego zasobu MSDN:

Ze względu na naturę mechanizmu używanego przez SqlDataSourceEnumerator do lokalizowania źródeł danych w sieci, metoda nie zawsze zwróci pełną listę dostępnych serwerów , a lista może nie być taka sama przy każdym wywołaniu. Jeśli planujesz użyć tej funkcji, aby umożliwić użytkownikom wybranie serwera z listy, upewnij się, że zawsze podajesz opcję wpisania nazwy, której nie ma na liście, na wypadek, gdyby wyliczenie serwera nie zwróciło wszystkich dostępnych serwerów . Ponadto wykonanie tej metody może zająć dużo czasu , dlatego należy zachować ostrożność podczas wywoływania jej, gdy wydajność jest krytyczna.

Wywołanie jest proste z PowerShell:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

Ta metoda zwraca DataTableobiekt, który można odpowiednio obsłużyć.

Jeśli usługa przeglądarki SQL jest aktywna, możesz wysłać zapytanie do usługi o wystąpienia SQL za pomocą poniższego kodu PowerShell. Implementuje następujące komendy do wykonywania zapytań:

• Get-SqlBrowserInstanceList
• Get-SqlBrowserInstanceInfo

• Get-SqlBrowserInstanceDac

  function Parse-ServerResponse([byte[]] $responseData)
  {
      [PSObject[]] $instances = @()
  
      if (($responseData -ne $null) -and ($responseData[0] -eq 0x05))
      {
          $responseSize = [System.BitConverter]::ToInt16($responseData, 1)
  
          if ($responseSize -le $responseData.Length - 3)
          {
              # Discard any bytes beyond the received response size. An oversized response is usually the result of receiving multiple replies to a broadcast request.
              $responseString = [System.Text.Encoding]::Default.GetString(($responseData | Select -Skip 3 -First $responseSize))
              $instanceResponses = $responseString.Split(@(";;"), [System.StringSplitOptions]::RemoveEmptyEntries)
  
              $instances = foreach ($instanceResponse in $instanceResponses)
              {
                  $instanceResponseValues = $instanceResponse.Split(";")
                  $instanceResponseHash = @{}
                  for ($index = 0; $index -lt $instanceResponseValues.Length; $index += 2)
                  {
                      $instanceResponseHash[$instanceResponseValues[$index]] = $instanceResponseValues[$index + 1]
                  }
  
                  New-Object PSObject -Property $instanceResponseHash
              }
          }
          else
          {
              Write-Warning "The response was too short. Expected $($responseSize) bytes but got $($responseData.Length - 3)."
          }
      }
  
      return ,$instances
  }
  
  function Parse-ServerResponseDac([byte[]] $responseData)
  {
      $dacPort = 0
  
      if (($responseData -ne $null) -and ($responseData[0] -eq 0x05))
      {
          $responseSize = [System.BitConverter]::ToUInt16($responseData, 1)
  
          if (($responseData.Length -eq 6) -and ($responseSize -eq 6))
          {
              if ($responseData[3] -eq 0x01)
              {
                  $dacPort = [System.BitConverter]::ToUInt16($responseData, 4)
              }
              else
              {
                  Write-Error "An unexpected protocol version was returned. Expected 0x01 but got $($requestData[3])."
              }
          }
          else
          {
              Write-Error "The response size was incorrect."
          }
      }
  
      return $dacPort
  }
  
  function Get-SqlBrowserInstanceList
  {
      <#
      .SYNOPSIS
      Gets the list of available SQL Instances on the server.
      .DESCRIPTION
      Gets the list of available SQL Instances on the server by querying the SQL Browser Service on port 1434.
      .EXAMPLE
      Get-SqlBrowserInstanceList servername
      .EXAMPLE
      Get-SqlBrowserInstanceList servername.dnsdomain.tld
      .EXAMPLE
      Get-SqlBrowserInstanceList $env:COMPUTERNAME
      .EXAMPLE
      Get-SqlBrowserInstanceList 192.168.1.255 -Broadcast
      .EXAMPLE
      Get-SqlBrowserInstanceList 255.255.255.255 -Broadcast
      .PARAMETER $ServerName
      The name or IP Address of the server.
      .PARAMETER $Broadcast
      If the broadcast switch is specified, the query will be sent as a broadcast and may receive replies from multiple hosts; otherwise, the query is sent to a single server.
      #>
      [CmdletBinding(SupportsShouldProcess = $False)]
      param
      (
          [Parameter(Mandatory = $True, ValueFromPipeLine = $True)]
          [string] $ServerName,
          [switch] $Broadcast
      )
  
      process
      {   
          [System.Net.IPAddress] $ipAddress = [System.Net.Dns]::GetHostAddresses($serverName) | Select -First 1
          $parsedResponses = @()
  
          if ($ipAddress -ne $null)
          {
              [System.Net.IPEndPoint] $localIPEndPoint = New-Object System.Net.IPEndPoint([System.Net.IPAddress]::Any, 0)
              [System.Net.IPEndPoint] $remoteIPEndPoint = New-Object System.Net.IPEndPoint($ipAddress, 1434)
  
              if ($ipAddress -eq [System.Net.IPAddress]::Broadcast)
              {
                  $Broadcast = $true
              }
  
              [System.Net.Sockets.UdpClient] $receiver = New-Object System.Net.Sockets.UdpClient
              $receiver.Client.ReceiveTimeout = 30000
  
              [byte] $queryMode = 0x03
              $sleepDuration = 1
              [System.Net.Sockets.UdpClient] $sender = $null
  
              if ($Broadcast -eq $true)
              {
                  Write-Verbose "Using broadcast mode."
                  $queryMode = 0x02
                  $sleepDuration = 30
  
                  # Set the receiver to allow another client on the same socket.
                  $receiver.Client.SetSocketOption([System.Net.Sockets.SocketOptionLevel]::Socket, [System.Net.Sockets.SocketOptionName]::ReuseAddress, $true)
                  $receiver.Client.Bind($localIPEndPoint)
  
                  # Because broadcasting from this UdpClient instance causes the underlying socket to be unable to receive normally, a separate sender must be bound to the same socket as the receiver.
                  # NOTE: Windows Firewall does not view a reused socket as being part of the same conversation. If Windows Firewall is active, this requires special firewall rules to work.
                  $sender = New-Object System.Net.Sockets.UdpClient
                  $sender.EnableBroadcast = $Broadcast
                  $sender.Client.SetSocketOption([System.Net.Sockets.SocketOptionLevel]::Socket, [System.Net.Sockets.SocketOptionName]::ReuseAddress, $true)
                  $sender.Client.Bind($receiver.Client.LocalEndPoint);
              }
              else
              {
                  $sender = $receiver
                  $receiver.Client.Bind($localIPEndPoint)
              }
  
  
              $responses = @{}
  
              try
              {
                  # Send the broadcast.
                  Write-Verbose "Sending request to $($ipAddress)..."
                  $sender.Connect($remoteIPEndPoint)
                  $bytesSent = $sender.Send(@($queryMode), 1)
  
                  # Wait to give responses time to arrive.
                  Sleep $sleepDuration
  
                  do
                  {
                      [System.Net.IPEndPoint] $responderIPEndPoint = $null
                      $response = $receiver.Receive([ref] $responderIPEndPoint)
                      $responder = $responderIPEndPoint.ToString()
  
                      if ($responses.Contains($responder))
                      {
                          $responses[$responder] += $response
                      }
                      else
                      {
                          $responses.Add($responder, $response)
                      }
                  } while ($receiver.Available -gt 0)
              }
              finally
              {
                  if ($sender -ne $receiver)
                  {
                      $sender.Close()
                      $sender.Dispose()
                  }
  
                  $receiver.Close()
                  $receiver.Dispose()
              }
  
              foreach ($responseItem in $responses.GetEnumerator())
              {
                  Write-Verbose "Parsing the response from $($responseItem.Name)..."
                  $parsedResponse = Parse-ServerResponse $responseItem.Value
                  $parsedResponses += $parsedResponse
                  Write-Verbose ($parsedResponse | ft ServerName, InstanceName, tcp, np, Version, IsClustered -AutoSize |Out-String)
              }
          }
  
          return $parsedResponses
      }
  }
  
  function Get-SqlBrowserInstanceInfo
  {
      <#
      .SYNOPSIS
      Gets information about the specified SQL Instance from the server.
      .DESCRIPTION
      Gets information about the specified SQL Instance from the server by querying the SQL Browser Service on port 1434.
      .EXAMPLE
      Get-SqlBrowserInstanceInfo servername instancename
      .EXAMPLE
      Get-SqlBrowserInstanceInfo servername.dnsdomain.tld instancename
      .EXAMPLE
      Get-SqlBrowserInstanceInfo $env:COMPUTERNAME
      .PARAMETER $ServerName
      The name or IP Address of the server.
      .PARAMETER $InstanceName
      The name of the SQL Instance.    #>
      [CmdletBinding(SupportsShouldProcess = $False)]
      param
      (
          [Parameter(Mandatory = $True, ValueFromPipeLine = $True)]
          [string] $ServerName,
          [Parameter(Mandatory = $True, ValueFromPipeLine = $False)]
          [string] $InstanceName
      )
  
      process
      {   
          $instances = @()
          [System.Net.IPAddress] $ipAddress = $null
  
          $ipAddress = [System.Net.Dns]::GetHostAddresses($serverName) | Select -First 1
  
          if ($ipAddress -ne $null)
          {
              [System.Net.IPEndPoint] $ipEndPoint = New-Object System.Net.IPEndPoint($ipAddress, 1434)
              [System.Net.Sockets.UdpClient] $udpClient = New-Object System.Net.Sockets.UdpClient
              $udpClient.Client.ReceiveTimeout = 10000
  
              $instanceNameData = [System.Text.Encoding]::Default.GetBytes($instanceName)
              [byte[]] $requestData = @(0x04) + $instanceNameData + 0x00
              [byte[]] $responseData = $null
  
              try
              {
                  $udpClient.Connect($ipEndPoint)
  
                  $bytesSent = $udpClient.Send($requestData, $requestData.Length)
  
                  $responseData = do
                  {
                      $udpClient.Receive([ref] $ipEndPoint)
                  } while ($udpClient.Available -gt 0)
              }
              finally
              {
                  $udpClient.Close()
                  $udpClient.Dispose()
              }
  
              $instances = Parse-ServerResponse $responseData
          }
  
          return $instances
      }
  }
  
  function Get-SqlBrowserInstanceDac
  {
      <#
      .SYNOPSIS
      Gets the Dedicated Administrator Connection port number for the specified SQL Instance on the server.
      .DESCRIPTION
      Gets the Dedicated Administrator Connection port number for the specified SQL Instance on the server by querying the SQL Browser Service on port 1434.
      .EXAMPLE
      Get-SqlBrowserInstanceDac servername instancename
      .EXAMPLE
      Get-SqlBrowserInstanceDac servername.dnsdomain.tld instancename
      .EXAMPLE
      Get-SqlBrowserInstanceDac $env:COMPUTERNAME instancename
      .PARAMETER $ServerName
      The name or IP Address of the server.
      .PARAMETER $InstanceName
      The name of the SQL Instance.
      #>
      [CmdletBinding(SupportsShouldProcess = $False)]
      param
      (
          [Parameter(Mandatory = $True, ValueFromPipeLine = $True)]
          [string] $ServerName,
          [Parameter(Mandatory = $True, ValueFromPipeLine = $False)]
          [string] $InstanceName
      )
  
      process
      {   
          [System.UInt16] $dacPort = 0
          [System.Net.IPAddress] $ipAddress = $null
  
          $ipAddress = [System.Net.Dns]::GetHostAddresses($serverName) | Select -First 1
  
          if ($ipAddress -ne $null)
          {
              [System.Net.IPEndPoint] $ipEndPoint = New-Object System.Net.IPEndPoint($ipAddress, 1434)
              [System.Net.Sockets.UdpClient] $udpClient = New-Object System.Net.Sockets.UdpClient
              $udpClient.Client.ReceiveTimeout = 30000
  
              $instanceNameData = [System.Text.Encoding]::Default.GetBytes($instanceName)
              [byte[]] $requestData = @(0x0F) + 0x01 + $instanceNameData + 0x00
              [byte[]] $responseData = $null
  
              try
              {
                  $udpClient.Connect($ipEndPoint)
  
                  $bytesSent = $udpClient.Send($requestData, $requestData.Length)
  
                  $responseData = do
                  {
                      $udpClient.Receive([ref] $ipEndPoint)
                  } while ($udpClient.Available -gt 0)
              }
              finally
              {
                  $udpClient.Close()
                  $udpClient.Dispose()
              }
  
              $dacPort = Parse-ServerResponseDac($responseData)
          }
  
          return $dacPort
      }
  }

Innym sposobem identyfikacji możliwych wystąpień SQL jest sprawdzenie nazw zasad usługi (SPN) wymienionych w usłudze Active Directory. Kiedy łączysz się z SQL Server zdalnie za pomocą uwierzytelniania systemu Windows, w procesie uwierzytelniania używana jest nazwa SPN. Obecność nazwy SPN nie oznacza, że ​​serwer / instancja jest zdecydowanie dostępna i działa, ale daje listę możliwych instancji, które według mnie są bardziej wyczerpujące w porównaniu z innymi podejściami.

Aby ułatwić życie, używam polecenia cmdlet Get-SPN z: https://gallery.technet.microsoft.com/scriptcenter/Get-SPN-Get-Service-3bd5524a

Pobierz skrypt Get-SPN.ps1, zapisz go w C: \ powershell_scripts \ Get-SPN.ps1 i uruchom następujące polecenie w PowerShell:

. "C:\powershell_scripts\Get-SPN.ps1"
Get-SPN -ServiceClass MSSQLSvc

(Oczywiście możesz zapisać skrypt w innej lokalizacji, wystarczy zaktualizować pierwszy wiersz zgodnie z wymaganiami).

Spowoduje to wyświetlenie wszystkich nazw SPN programu SQL Server w bieżącej domenie, w tym „specyfikacji” odnoszącej się do portu / instancji usługi.

Get-Service -ComputerName * MSSQL * | Where-Object {$ _. Status -eq „Running”}

To powinno być nazwane i domyślne wystąpienia. Wystarczy powtórzyć listę maszyn itp.

Właśnie próbowałem tego: [System.Data.Sql.SqlDataSourceEnumerator] :: Instance.GetDataSources ()

Nie zwrócono zbyt wielu danych, ale wykryło wszystkie serwery SQL uruchomione w środowisku VM.