🥇Automatizace SQL serveru v Jenkinsu: vracíme výsledek hezky

Znovu pokračování tématu uspořádání Zero Touch PROD pod RDS. Budoucí DBA nebudou mít možnost připojit se přímo k PROD serverům, ale budou moci využívat Jenkins úlohy pro omezenou sadu operací. DBA spustí úlohu a po nějaké době obdrží e-mail s přehledem provedení této operace. Podívejme se na způsoby prezentace těchto výsledků uživateli.

Plain Text

Začněme tím nejtriviálnějším. První způsob je tak jednoduchý, že vlastně o něm ani nemá smysl mluvit (autor zde a dále používá FreeStyle jobs):

sqlcmd něco vykonává, a my to prezentujeme uživateli. Ideální pro například zálohovací úlohy:

Nesmíme zapomenout, že pod RDS je backup/restore asynchronní, takže je třeba počkat:

declare @rds table
  (id int, task_type varchar(128), database_name sysname, pct int, duration int, 
   lifecycle varchar(128), taskinfo varchar(max) null, 
   upd datetime, cre datetime,
   s3 varchar(256), ovr int, KMS varchar(256) null)
waitfor delay '00:00:20' 
insert into @rds exec msdb.dbo.rds_task_status @db_name='{db}'
select @xid=max(id) from @rds

again:
waitfor delay '00:00:02'
delete from @rds
insert into @rds exec msdb.dbo.rds_task_status @db_name='{db}' 
# {db} nahrazeno názvem db ve powershellu
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Druhý způsob, CSV

Tady je také vše velmi jednoduché:

Tento způsob však funguje pouze v případě, že data vrácená v CSV jsou „jednoduchá“. Pokud se pokusíte tímto způsobem vrátit například seznam TOP N dotazů s vysokou spotřebou CPU, CSV by se „rozpadlo“ kvůli tomu, že text dotazu může obsahovat jakékoli znaky - čárky, uvozovky, a dokonce i nové řádky. Takže budeme potřebovat něco složitějšího.

Krásné tabulky na HTML

Představím hned fragment kódu

$Header = @"
<style>
TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #6495ED;}
TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
</style>
"@
  
$Result = invoke-Sqlcmd -ConnectionString $jstr -Query "select * from DbInv" `
  | Select-Object -Property * -ExcludeProperty "ItemArray", "RowError", "RowState", "Table", "HasErrors"
if ($Result -eq $null) { $cnt = 0; }
elseif ($Result.getType().FullName -eq "System.Management.Automation.PSCustomObject") { $cnt = 1; }
else { $cnt = $Result.Rows.Count; } 
if ($cnt -gt 0) {
  $body = "<h2>Moje tabulka</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Žádná data</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

Mimochodem, věnujte pozornost řádku se System.Management.Automation.PSCustomObject, je to kouzelné, pokud je v mřížce pouze jeden řádek, objevily se nějaké problémy. Řešení bylo převzato z internetu, aniž bych se do toho příliš zabýval. Výsledkem bude výstup vypadat asi takto:

Kreslíme grafy

Pozor: zvrácený kód níže!
Existuje zábavný dotaz na SQL server, který vypisuje CPU za posledních N minut - ukazuje se, že kolega major si vše pamatuje! Zkuste tento dotaz:

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) 
  FROM sys.dm_os_sys_info WITH (NOLOCK)); 
SELECT TOP(256) 
  DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime],
  SQLProcessUtilization AS [SQLCPU], 
  100 - SystemIdle - SQLProcessUtilization AS [OtherCPU]
FROM (SELECT record.value('(. /Record/@id)[1]', 'int') AS record_id, 
  record.value('(. /Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
    AS [SystemIdle], 
  record.value('(. /Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') 
    AS [SQLProcessUtilization], [timestamp] 
  FROM (SELECT [timestamp], CONVERT(xml, record) AS [record] 
  FROM sys.dm_os_ring_buffers WITH (NOLOCK)
  WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
    AND record LIKE N'%%') AS x) AS y 
ORDER BY 1 DESC OPTION (RECOMPILE);

Nyní použijeme toto formátování (proměnná $Fragment)

<table style="width: 100%"><tbody><tr style="background-color: white; height: 2pt;">
  <td style="width: SQLCPU%; background-color: green;"></td>
  <td style="width: OtherCPU%; background-color: blue;"></td>
  <td style="width: REST%; background-color: #C0C0C0;"></td></tr></tbody>
</table>

Můžeme vytvořit tělo e-mailu:

$Result = invoke-Sqlcmd -ConnectionString $connstr -Query $Query `
  | Select-Object -Property * -ExcludeProperty `
  "ItemArray", "RowError", "RowState", "Table", "HasErrors"
if ($Result.HasRows) {
  foreach($item in $Result) 
    { 
    $time = $itemEventTime 
    $sqlcpu = $item.SQLCPU
    $other = $itemOtherCPU
    $rest = 100 - $sqlcpu - $other
    $f = $fragment -replace "SQLCPU", $sqlcpu
    $f = $f -replace "OtherCPU", $other
    $f = $f -replace "REST", $rest
    $f | Out-File "res.log" -Append -Encoding UTF8
    }

Které bude vypadat takto:

Opravdu, monsieur ví, co je to úchylka! Zajímavé je, že tento kód obsahuje: Powershell (na kterém je napsán), SQL, Xquery, HTML. Je škoda, že k HTML nemůžeme přidat Javascript (protože to je pro e-mail), ale zdokonalit kód Pythonu (který lze použít v SQL) je povinností každého!

Výstup SQL profiler trace

Je jasné, že trace „neprojde“ do CSV kvůli poli TextData. Ale zobrazovat trace v gridě v e-mailu je také podivné — a to jak kvůli velikosti, tak proto, že se tato data často používají pro další analýzu. Proto děláme následující: voláme přes invoke-SqlCmd určitý skript, v jehož hlubinách se dělá

select 
  SPID,EventClass,TextData,
  Duration,Reads,Writes,CPU,
  StartTime,EndTime,DatabaseName,HostName,
  ApplicationName,LoginName
   from ::fn_trace_gettable ( @filename , default )

Dále, na přítelem serveru, dostupném DBA, existuje databáze Traces s prázdnou šablonou, tabulkou Model, připravenou přijmout všechny uvedené sloupce. Tuto šablonu kopírujeme do nové tabulky s unikátním názvem:

$dt = Get-Date -format "yyyyMMdd"
$tm = Get-Date -format "hhmmss"
$tableName = $srv + "_" + $dt + "_" + $tm
$copytab = "select * into " + $tableName + " from Model"
invoke-SqlCmd -ConnectionString $tstr -Query $copytab

A nyní do ní můžeme zapsat náš trace pomocí Data.SqlClient.SqlBulkCopy — příklad tohoto jsem již uvedl shora. Ano, také by nebylo špatné udělat maskování konstant v TextData:

# mask data
foreach ($Row in $Result)
{ 
  $v = $Row["TextData"]
  $v = $v -replace "'([^']{2,})'", "'str'" -replace "[0-9][0-9]+", '999'
  $Row["TextData"] = $v
}

Nahrazujeme čísla delší než jeden symbol na 999 a řetězce delší než jeden symbol nahrazujeme na 'str'. Čísla od 0 do 9 se často používají jako vlajky, a my je neměníme, stejně jako prázdné a jednoslovné řetězce — mezi nimi často bývá 'Y', 'N' atd.

Přidáme barvy do našeho života (pouze 18+)

V tabulkách často chceme zvýraznit buňky, které vyžadují pozornost. Například FAILS, vysoká úroveň fragmentace atd. Samozřejmě, že to lze udělat i v čistém SQL, vytvářením HTML pomocí PRINT, a v Jenkinsu nastavit typ souboru na HTML:

vyhlásit @body varchar(max), @chunk varchar(max)
nastavit @body='<font face="Lucida Console" size="3">'
nastavit @body=@body+'<b>Nazev serveru: '+@@servername+'</b><br>'
nastavit @body=@body+'<br><br>'
nastavit @body=@body+'<table><tr><th>Úloha</th><th>Poslední běh</th><th>Průměrná doba, sec</th><th>Poslední běh, sec</th><th>Poslední stav</th></tr>'
print @body

VYHLÁSIT tab CURSOR FOR SELECT '<tr><td>'+name+'</td><td>'+
  PosledníBěh+'</td><td>'+
  convert(varchar,PrůměrnáDoba)+'</td><td>'+
  convert(varchar,PosledníDoba)+'</td><td>'+
    případ když PosledníStav<>'Usnulo' potom '<font color="red">' jinak '' konec+
      PosledníStav+
      případ když PosledníStav<>'Usnulo' potom '</font>' jinak '' konec+
     +'</td><td>'
  z #j2
OTEVŘÍT tab;  
FETCH DALŠÍ Z tab do @chunk
KDYŽ @@FETCH_STATUS = 0  
ZAČÍT
  print @chunk
  FETCH DALŠÍ Z tab do @chunk;  
KONEC  
UZAVŘÍT tab;  
DEALLOCATE tab;
print '</table>'

Proč jsem napsal takový kód?

Ale existuje elegantnější řešení. ConvertTo-HTML nám nedává možnost obarvit buňky, ale můžeme to udělat dodatečně. Například chceme zvýraznit buňky s úrovní fragmentace nad 80 a nad 90. Přidáme styly:

.SQLmarkup-red { color: red; background-color: yellow; }
.SQLmarkup-yellow { color: black; background-color: #FFFFE0; }
.SQLmarkup-default { color: black; background-color: white; }

Do samotného dotazu přidáme fiktivní sloupec přesně před sloupec, který chceme obarvit. Sloupec by se měl jmenovat SQLmarkup-něco:

case  
  when ps.avg_fragmentation_in_percent >= 90.0 then 'SQLmarkup-red'
  when ps.avg_fragmentation_in_percent >= 80.0 then 'SQLmarkup-yellow'
  else 'SQLmarkup-default' 
  end as [SQLmarkup-1], 
ps.avg_fragmentation_in_percent,

Nyní, když máme HTML vytvořené Powershellem, odstraníme fiktivní sloupec z hlavičky a ve těle dat přesuneme hodnotu ze sloupce do stylu. To se dělá pouhými dvěma substitucemi:

$html = $html `
  -nahradit "<th>SQLmarkup[^<]*</th>", "" `
  -nahradit "<td>SQLmarkup-(.+?)</td><td>",'<td class="SQLmarkup-$1">'

Result:

Není to elegantní? Ačkoliv ne, něco mi na tomto zvýraznění připomíná

Zdroj: habr.com

Automatizace SQL serveru v Jenkins: vrátíme výsledek krásně.