Добро пожаловать на вторую часть нашей серии блогов о автоматическом обнаружении экземпляров SQL Server в домене Active Directory. В этом посте мы продолжим с того места, где остановились в первой части, и рассмотрим, как проверить, настроены ли удаленные серверы на экземпляры SQL Server и каков их текущий статус.

В предыдущем посте мы обсудили сценарий PowerShell, который генерирует файл CSV, содержащий список серверов в домене. Мы будем использовать этот файл CSV в качестве входных данных для скрипта, который мы будем обсуждать сегодня.

Прежде чем мы погрузимся в скрипт, давайте посмотрим на однострочник PowerShell, который можно использовать для поиска служб SQL Server на одном удаленном компьютере:

Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "SQLSERVERVM1" -ErrorAction Continue

Теперь давайте посмотрим на скрипт PowerShell, который находит экземпляры SQL Server на удаленных серверах:

# Этот сценарий PowerShell использует WMI для подключения к каждому серверу и проверки наличия служб Windows, соответствующих шаблону '%%sqlservr.exe%%'.
# Для работы этого сценария необходимо иметь доступ к серверам. Если у вас нет доступа, он выдаст ошибку "Отказано в доступе".
# Однако, поскольку мы получаем список серверов для проверки из файла CSV, он продолжит работу с следующим сервером после возникновения ошибки.
# В конце он отображает список серверов, с которыми успешно установлено соединение, и отдельный список серверов, где возникли ошибки.
# Он также экспортирует список обнаруженных экземпляров SQL в файл CSV.

(Get-Date).ToString() + ": Начало"

try {
    $user = "" # Должен быть в формате Domain\UserName
    $pass = ""
    
    if ($user -eq "") {
        $user = $Null
    }
    
    # Если указана пара пользователь/пароль, аутентифицируем ее в домене
    if ($user -ne $Null) {
        "Аутентификация пользователя $user в домене AD"
        $domain = $user.Split("\")[0]
        $domainObj = "LDAP://" + (Get-ADDomain $domain).DNSRoot
        $domainBind = New-Object System.DirectoryServices.DirectoryEntry($domainObj, $user, $pass)
        $domainDN = $domainBind.distinguishedName
        
        "DN домена: " + $domainDN
        
        # Прерываем выполнение, если аутентификация пользователя не удалась по какой-либо причине
        If ($domainDN -eq $Null) {
            "Пожалуйста, проверьте пароль и убедитесь, что пользователь существует и включен в домен: $domain"
            throw "Ошибка аутентификации пользователя: $user"
            exit
        }
        else {
            "Учетная запись $user успешно аутентифицирована в домене: $domain"
        }
        
        $passWord = ConvertTo-SecureString -String $pass -AsPlainText -Force
        $credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $user, $passWord
    }
    
    $csv_file_name = "new_servers.csv"
    $CSVData = Import-CSV $csv_file_name
    $export_file_name = "sql_server_instances.csv"
    $csv_row_count = $CSVData.Count
    
    (Get-Date).ToString() + ": Общее количество строк в файле CSV: " + $csv_row_count
    
    $servers = $CSVData.DNSHostName
    $SqlInstancesList = @()
    $ErrorServers = @()
    
    $servers
    
    # Перебираем каждый сервер и ищем на них службы SQL
    foreach ($server in $servers) {
        try {
            if ($user -ne $Null) {
                $SqlServices = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -credential $credentials -ComputerName $server -ErrorAction Continue
            }
            else {
                $SqlServices = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName $server -ErrorAction Continue
            }
            
            $SqlInstancesList += $SqlServices
        }
        catch {
            # Несмотря на возникшую ошибку, он продолжит работу с следующим сервером
            "Ошибка при поиске экземпляров SQL на: " + $server
            $ErrorServers += $server + " (" + $_.Exception.Message + ")"
            $_
        }
    }
    
    # Если возникли ошибки с каким-либо из серверов, выводим имена этих серверов вместе с сообщением об ошибке/причиной
    if ($ErrorServers.Count -gt 0) {
        "Ошибка при поиске экземпляров SQL на следующих серверах:"
        "--------------------------------------------------------"
        $ErrorServers
    }
    
    "Обнаруженные экземпляры SQL:"
    "-------------------"
    
    $SqlInstancesList | select-object -Property PSComputerName, @{n="SqlInstance"; e={$_.Name -replace "MSSQL$", ""}}, Name, ProcessID, StartMode, State, Status, ExitCode, PathName | Export-CSV $export_file_name -NoTypeInformation -Encoding UTF8
    
    Import-Csv -Encoding UTF8 -Path $export_file_name | ft -AutoSize
    
    (Get-Date).ToString() + ": Завершено"
}
Catch {
    (Get-Date).ToString() + ": Произошла ошибка"
    $_
}

Теперь, когда мы собрали информацию об экземплярах SQL Server на удаленных серверах, давайте перейдем к загрузке этой информации в SQL-таблицу стадии.

Вот сценарий PowerShell для загрузки собранной информации в SQL-таблицу стадии:

# Этот сценарий использует учетные данные подключенного пользователя для подключения к экземпляру SQL Server.
# Поскольку мы загружаем данные в таблицу стадии, он сначала очищает таблицу, а затем загружает данные в нее.

(Get-Date).ToString() + ": Начало загрузки данных в SQL-таблицу стадии"

$sql_instance_name = 'mssqlservervmSQL2016AG01'
$db_name = 'AdminDBA'
$destination_table_name = "dbo.sql_server_instances_stage"
$export_file_name = "sql_server_instances.csv"
$truncate_table_command = "truncate table " + $destination_table_name

"Команда очистки таблицы: " + $truncate_table_command

invoke-sqlcmd -Database $db_name -Query $truncate_table_command -serverinstance $sql_instance_name

$SqlServices = Import-Csv -Encoding UTF8 -Path $export_file_name

foreach ($sqlservice in $SqlServices) {
    $PSComputerName = $SqlService.PSComputerName
    $Name = $SqlService.Name
    $SqlInstance = $SqlService.SqlInstance
    $PathName = $SqlService.PathName
    $ExitCode = $SqlService.ExitCode
    $ProcessID = $SqlService.ProcessID
    $StartMode = $SqlService.StartMode
    $State = $SqlService.State
    $Status = $SqlService.Status
    
    $query = "INSERT INTO " + $destination_table_name + " (PSComputerName,ServiceName, InstanceName,PathName,ExitCode,ProcessID,StartMode,State,Status) VALUES ('$PSComputerName','$Name','$SqlInstance','$PathName','$ExitCode','$ProcessID','$StartMode','$State','$Status')"
    
    $execute_query = invoke-sqlcmd -Database $db_name -Query $query -serverinstance $sql_instance_name
}

$query = "select count(*) rcount from " + $destination_table_name
$rcount = invoke-sqlcmd -Database $db_name -Query $query -serverinstance $sql_instance_name -As DataTables

"Количество записей, вставленных в SQL-таблицу: " + $rcount[0].rcount

(Get-Date).ToString() + ": Завершено загрузка данных в SQL-таблицу стадии"

Наконец, вот SQL-скрипт для создания таблицы dbo.sql_server_instances_stage:

USE [AdminDBA]
GO

DROP TABLE [dbo].[sql_server_instances_stage]
GO

CREATE TABLE [dbo].[sql_server_instances_stage](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [PSComputerName] [varchar](500) NULL,
    [ServiceName] [varchar](500) NULL,
    [InstanceName] [varchar](500) NULL,
    [PathName] [varchar](1500) NULL,
    [ExitCode] [int] NULL,
    [ProcessID] [int] NULL,
    [StartMode] [varchar](500) NULL,
    [State] [varchar](500) NULL,
    [Status] [varchar](500) NULL,
    [InsertedDate] [datetime] NULL,
    PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    ) WITH (
        PAD_INDEX = OFF, 
        STATISTICS_NORECOMPUTE = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[sql_server_instances_stage] ADD DEFAULT (getdate()) FOR [InsertedDate]
GO

Это все для этого поста! В следующей части этой серии мы углубимся в скрипт и рассмотрим, как проверить доступ к экземплярам SQL Server и определить их версии. Следите за обновлениями!