Мониторинг SQL Server с помощью PowerShell. Часть 1. Базовая настройка

05.07.2020 Автор:Пётр Окунев
1 498 Просмотров Комментариев нет

Monitoring SQL Server with PowerShell. Part 1. Basic setupДанный материал является переводом оригинальной статьи "MSSQLTips : Alejandro Cobar : Monitoring SQL Server with PowerShell Core Object Setup".

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

В рамках предлагаемого решения мониторинга в данной статье мы рассматриваем первый базовый модуль с компонентами, которые будут использоваться другими частями решения. Как уже упоминалось, мы будем использовать PowerShell для сбора данных, а также для создания объектов базы данных на централизованном сервере базы данных.

Рассматриваемый далее модуль PowerShell создаст базовые объекты базы данных, необходимые для остальных модулей. Модуль создает БД для хранения данных, в числе которых несколько специальных таблиц и список серверов, которые мы хотим отслеживать.

 

Объекты базы данных

Прежде чем мы начнем собирать данные, основные компоненты БД будут настроены с помощью первого сценария PowerShell. В частности будет создано следующее:

  1. База данных "DBA". Это база данных централизованного сбора. Имя БД при необходимости можно изменить (см. далее секцию "Файл Settings.ini"). БД будет создана по настройкам из БД model.
  2. Схемы
    • audit - эта схема будет содержать все объекты базы данных, используемые для целей аудита.
    • inventory - эта схема будет содержать объекты базы данных, содержащие список экземпляров SQL Server.
    • monitoring - эта схема будет содержать объекты базы данных, используемые для сбора данных
  3. Таблицы
  • monitoring.ErrorLog - фиксирует любые ошибки, которые встречаются
    • serverid - привязывает к inventory.MasterServerList.serverid
    • script – скрипт, который был запущен
    • message – сообщение об ошибке, сгенерированное скриптом
    • error_timestamp – дата и время ошибки
  • inventory.MasterServerList – хранит список наблюдаемых серверов
    • serverid – сгенерированный системой уникальный id
    • server_name – имя сервера, где установлен SQL
    • instance – имя экземпляра SQL Server
    • ip - IP адрес экземпляра SQL Server
    • port – номер порта экземпляра SQL Server
    • is_active - 1 = active, 0 = inactive

 

Файл Settings.ini

Этот файл используется для настройки центрального сервера и базы данных, которые будут использоваться для хранения всех собранных данных.

В этом файле необходимо обновить, как минимум, два параметра:

  • centralServer - имя сервера и экземпляра, на котором вы хотите создать базу данных инвентаризации.
  • inventoryDB – имя базы данных, которая будет создана и будет хранить собранные данные. Если база данных уже существует, она не будет создана.

Пример содержимого файла:

[General]
centralServer=DESKTOP-QUDLLRR\SQL2
inventoryDB=DBA

Если вы хотите использовать логин SQL, вам нужно настроить файл Settings.ini, указав логин и пароль SQL, которые будут использоваться для подключения к центральному серверу. В этом случае содержимое файла может выглядеть следующим образом:

[General]
centralServer=DESKTOP-QUDLLRR\SQL2
inventoryDB=DBA
[Optional]
username=login
password=pwd

 

Файл instances.txt

Файл используется для загрузки данных в таблицу inventory.MasterServerList. Формат данных в текстовом файле следующий:

'Server Name','Instance Name',IP Address','Port'

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

  • Первая строка ниже предназначена для сервера DESKTOP-QUDLLRR, использующего экземпляр SQL Server по умолчанию, поэтому мы указываем экземпляр как MSSQLSERVER с IP-адресом 10.0.0.35 и используем порт по умолчанию 1433;
  • Вторая строка также предназначена для сервера DESKTOP-QUDLLRR, использующего именованный экземпляр SQL2 с IP-адресом 10.0.0.35, но использующим порт 50287.

Содержимое файла в этом случае будет выглядеть следующим образом:

'DESKTOP-QUDLLRR','MSSQLSERVER','10.0.0.35',1433
'DESKTOP-QUDLLRR','SQL2','10.0.0.35',50287

Этот текстовый файл можно обновить с помощью новой информации об экземпляре, и сценарий PowerShell можно запустить снова, или вы можете просто добавить новые строки непосредственно в таблицу inventory.MasterServerList с необходимой информацией.

 

Скрипт PowerShell

Скрипт PowerShell, создающий объекты выше, и вставляющий данные в таблицу inventory.MasterServerList, назван, как Create-Master-Server-List.ps1

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

Он создаст базу данных, таблицы и загрузит данные из файла instances.txt в inventory.MasterServerList.

Содержимое скрипта:

Get-Content "C:\temp\Settings.ini" | foreach-object -begin {$h=@{}} -process { $k = [regex]::split($_,'='); if(($k[0].CompareTo("") -ne 0) -and ($k[0].StartsWith("[") -ne $True)) { $h.Add($k[0], $k[1]) } }
$server        = $h.Get_Item("centralServer")
$inventoryDB   = $h.Get_Item("inventoryDB")
$usingCredentials = 0   if($server.length -eq 0){
    Write-Host "You must provide a value for the 'centralServer' in your Settings.ini file!!!" -BackgroundColor Red
    exit
}
if($inventoryDB.length -eq 0){
    Write-Host "You must provide a value for the 'inventoryDB' in your Settings.ini file!!!" -BackgroundColor Red
    exit
}   if($h.Get_Item("username").length -gt 0 -and $h.Get_Item("password").length -gt 0){
    $usingCredentials = 1
    $username         = $h.Get_Item("username")
    $password         = $h.Get_Item("password")
}   #Function to execute queries (depending on if the user will be using specific credentials or not)
function Execute-Query([string]$query,[string]$database,[string]$instance){
    if($usingCredentials -eq 1){
        Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -Username $username -Password $password -ErrorAction Stop
    }
    else{
        Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -ErrorAction Stop
    }
}   #Central Database creation/verification
$centralDBCreationQuery = "
IF DB_ID('$($inventoryDB)') IS NULL
CREATE DATABASE $($inventoryDB)
"
Execute-Query $centralDBCreationQuery "master" $server   ###############################
#Schemas creation/verification#
###############################
$auditSchemaCreationQuery = "
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'audit')
EXEC('CREATE SCHEMA [audit] AUTHORIZATION [dbo]')
"
Execute-Query $auditSchemaCreationQuery $inventoryDB $server   $inventorySchemaCreationQuery = "
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'inventory')
EXEC('CREATE SCHEMA [inventory] AUTHORIZATION [dbo]')
"
Execute-Query $inventorySchemaCreationQuery $inventoryDB $server   $monitoringSchemaCreationQuery = "
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'monitoring')
EXEC('CREATE SCHEMA [monitoring] AUTHORIZATION [dbo]')
"
Execute-Query $monitoringSchemaCreationQuery $inventoryDB $server   ###################################################################################################
#Create the main table where you will store the information about all the instance under your care#
###################################################################################################
$mslTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[inventory].[MasterServerList]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE [inventory].[MasterServerList](
    [serverId]                  [int] IDENTITY(1,1) NOT NULL,
    [server_name]               [nvarchar](128) NOT NULL,
    [instance]                  [nvarchar](128) NOT NULL,
    [ip]                        [nvarchar](39) NOT NULL,
    [port]                      [int] NOT NULL DEFAULT 1433,
    [trusted]                   [bit] DEFAULT 1,
    [is_active]                 [bit] DEFAULT 1   CONSTRAINT PK_MasterServerList PRIMARY KEY CLUSTERED (serverId),   CONSTRAINT UQ_instance UNIQUE(server_name,instance)
) ON [PRIMARY]   END
"
Execute-Query $mslTableCreationQuery $inventoryDB $server   #######################################
#Error log table creation/verification#
#######################################
$errorLogTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[monitoring].[ErrorLog]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE [monitoring].[ErrorLog](
    [serverId]        [int]NOT NULL,
    [script]          [nvarchar](64) NOT NULL,
    [message]         [nvarchar](MAX) NOT NULL,
    [error_timestamp] [datetime] NOT NULL   CONSTRAINT FK_ErrorLog_MasterServerList FOREIGN KEY (serverId) REFERENCES inventory.MasterServerList(serverId) ON DELETE NO ACTION ON UPDATE CASCADE
)ON [PRIMARY]
END
"
Execute-Query $errorLogTableCreationQuery $inventoryDB $server   #Logic to populate the Master Server List using a .txt file
$flag = 0
foreach($line in Get-Content .\instances.txt){
    $insertMSLQuery = "INSERT INTO inventory.MasterServerList(server_name,instance,ip,port) VALUES($($line))"   try{
        Execute-Query $insertMSLQuery $inventoryDB $server
    }
    catch{
        $flag = 1
        [string]$message = $_
        $query = "INSERT INTO monitoring.ErrorLog VALUES((SELECT serverId FROM inventory.MasterServerList WHERE CASE instance WHEN 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END = '$($server)'),'Create-Master-Server-List','"+$message.replace("'","''")+"',GETDATE())"
        Execute-Query $query $inventoryDB $server
    }
}
if($flag -eq 1){Write-Host "Check the monitoring.ErrorLog table!"}   Write-Host "Done!"

 

Как использовать

После того, как вы ввели записи в файл instances.txt для своих экземпляров SQL Server и настроили файл Settings.ini, вы должны поместить все три файла в один  каталог. В нашем примере мы поместили все эти файлы в каталог "C:\temp".

  • instances.txt
  • Settings.ini
  • Create-Master-Server-List.ps1

Если вы хотите изменить рабочий каталог и использовать что-то отличное от "C:\temp", вам нужно будет отредактировать сценарий PowerShell и изменить первую строку, как показано ниже.

Set execute folder for Create-Master-Server-List.ps1

Перейдите в каталог, в котором вы создали файлы, и запустите скрипт PowerShell одним из вариантов:

  • Щелкните правой кнопкой мыши Create-Master-Server-List.ps1 и выберите Run with PowerShell
  • Откройте командное окно и перейдите в папку, в которой вы сохранили вышеуказанные файлы, и запустите:
powershell "C:\temp\Create-Master-Server-List.ps1"

Это не нужно планировать для запуска в Планировщике задач, поскольку при этом создаются только основные объекты и вставляются экземпляры для мониторинга, которые можно обновлять непосредственно в таблице inventory.MasterServerList, если есть изменения, или вы хотите добавить дополнительные экземпляры для мониторинга.

 

Проверка создания баз данных и объектов

После запуска скрипта PowerShell, у нас должны быть созданы две таблицы. В этом примере, мы использовали "DBA" как центральную базу данных мониторинга и две созданных таблицы, это inventory.MasterServerList и monitoring.ErrorLog.

Database DBA for SQL Server monitoring

Если мы опрашиваем таблицу inventory.MasterServerList, мы должны видеть строки, которые были вставлены.  В нашем случае, вставлены 2 строки, как показано ниже.

inventory.MasterServerList data

 

Проверка на ошибки

Мы можем опросить таблицу monitoring.ErrorLog, чтобы найти любые ошибки с помощью этого модуля.

Например, если мы снова запустим сценарий PowerShell, мы должны получить сообщение об ошибке, потому что мы пытаемся загрузить те же экземпляры - что должно завершиться ошибкой. Мы можем увидеть это ниже, если запросим таблицу monitoring.ErrorLog.

monitoring.ErrorLog data

Опубликовано в :  Microsoft SQL Server
Метки :  , , , , , ,

Добавить комментарий