🥇Automatizarea SQL server în Jenkins: întoarcem rezultatele frumos

Din nou Continuând tema amenajării Zero Touch PROD sub RDS. Viitorii DBA nu vor avea posibilitatea de a se conecta direct la serverele PROD, dar vor putea folosi Jenkins job-uri pentru un set limitat de operațiuni. DBA lansează un job și, după un timp, primește un email cu un raport despre finalizarea acestei operații. Să examinăm metodele de prezentare a acestor rezultate utilizatorului.

Text simplu

Să începem cu cel mai trivial. Prima metodă este atât de simplă, încât nu prea avem ce să spunem (autorul folosește aici și mai departe job-uri FreeStyle):

sqlcmd execută ceva, iar noi prezentăm asta utilizatorului. Este ideal pentru, de exemplu, job-uri de backup:

Nu uitați, de altfel, că sub RDS backup/restaurarea este asincron, așa că ar trebui să așteptați:

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} substituit cu numele bazei de date prin powershell
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

A doua metodă, CSV

Aici totul este de asemenea foarte simplu:

Cu toate acestea, această metodă funcționează doar dacă datele returnate în CSV sunt „simple”. Dacă încercați să returnați de exemplu o listă de TOP N interogări intensive CPU, atunci CSV-ul se va „dezintegra” din cauza faptului că textul interogării poate conține orice simboluri — virgule, ghilimele și chiar tranziții de linie. Așadar, va trebui să folosim ceva mai complex.

Tabele frumoase în HTML

Voi prezenta imediat un fragment de cod

$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>Tabelul meu</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Nu există date</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

De altfel, observați linia cu System.Management.Automation.PSCustomObject, este magică, dacă în grid există exact o linie, atunci apăreau anumite probleme. Soluția a fost preluată de pe internet fără a mă familiariza prea mult. Ca rezultat, veți obține o ieșire prezentată aproximativ astfel:

Desenăm grafice

Atenție: codul ciudat de mai jos!
Există o interogare amuzantă pe serverul SQL, care afișează CPU-ul pentru ultimele N minute — se dovedește că tovarășul maior își amintește totul! Încercați această interogare:

DECLARA @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);

Acum, folosind această formatare (variabila $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>

Putem forma corpul scrisorii:

$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
    }

Care va arăta astfel:

Așa că da, domnule știe ce înseamnă excesele! Interesant este că acest cod conține: Powershell (pe care este scris), SQL, Xquery, HTML. Din păcate, nu putem adăuga Javascript la HTML (deoarece este pentru o scrisoare), dar a rafina codul Python (care poate fi utilizat în SQL) este datoria fiecăruia!

Iesirea trace-ului SQL profiler

Este clar că trace-ul "nu va intra" în CSV din cauza câmpului TextData. Dar a afișa trace-ul în grilă în scrisoare este, de asemenea, ciudat — atât din cauza dimensiunii, cât și pentru că aceste date sunt adesea utilizate pentru analize ulterioare. Așadar, facem următorul lucru: apelăm prin invoke-SqlCmd un script, în adâncurile căruia se face

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

Mai departe, pe alt server, disponibil DBA, există o bază de date Traces cu un șablon gol, o tabelă Model, pregătită să primească toate coloanele menționate. Copiăm acest model într-o nouă tabelă cu un nume unic:

$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

Și acum putem scrie în ea trace-ul nostru cu ajutorul Data.SqlClient.SqlBulkCopy — un exemplu al acestuia l-am dat deja mai sus. Da, ar fi bine să facem masking constantelor în TextData:

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

Înlocuim numerele cu mai mult de un simbol lungime cu 999, iar șirurile mai lungi de un simbol le înlocuim cu 'str'. Numerele de la 0 la 9 sunt adesea folosite ca pose, și nu le atingem, precum și șirurile goale sau cu un singur caracter - printre ele se regăsesc frecvent 'Y', 'N' etc.

Să aducem culoare în viața noastră (strict 18+)

În tabele, adesea este necesar să evidențiem celulele care necesită atenție. De exemplu, FAILS, nivel ridicat de fragmentare etc. Desigur, acest lucru se poate face și cu SQL simplu, generând HTML folosind PRINT, iar în Jenkins setăm tipul de fișier HTML:

declare @body varchar(max), @chunk varchar(max)
set @body='<font face="Lucida Console" size="3">'
set @body=@body+'<b>Numele serverului: '+@@servername+'</b><br>'
set @body=@body+'<br><br>'
set @body=@body+'<table><tr><th>Job</th><th>Ultima execuție</th><th>Durata medie, sec</th><th>Ultima execuție, sec</th><th>Ultimul status</th></tr>'
print @body

DECLARE tab CURSOR FOR SELECT '<tr><td>'+name+'</td><td>'+
  UltimaExecutie+'</td><td>'+
  convert(varchar,DurataMedie)+'</td><td>'+
  convert(varchar,DurataUltima)+'</td><td>'+
    case when UltimulStatus<>'Reușit' then '<font color="red">' else '' end+
      UltimulStatus+
      case when UltimulStatus<>'Reușit' then '</font>' else '' end+
     +'</td><td>'
  from #j2
OPEN tab;  
FETCH NEXT FROM tab into @chunk
WHILE @@FETCH_STATUS = 0  
BEGIN
  print @chunk
  FETCH NEXT FROM tab into @chunk;  
END  
CLOSE tab;  
DEALLOCATE tab;
print '</table>'

De ce am scris un astfel de cod?

Dar există o soluție mai elegantă. ConvertTo-HTML nu ne permite să colorăm celulele, dar putem face acest lucru ulterior. De exemplu, vrem să evidențiem celulele cu un nivel de fragmentare mai mare de 80 și 90. Să adăugăm stiluri:

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

În interogare, vom adăuga o coloană fictivă direct înainte de coloana pe care dorim să o colorăm. Columna trebuie să se numească SQLmarkup-ceva:

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,

Acum, obținând HTML creat de Powershell, vom elimina coloana fictivă din antet, iar în corpul datelor vom muta valoarea din coloană în stil. Acest lucru se face cu doar două substituții:

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

Rezultatul:

Nu-i așa, elegant? Deși nu, această colorare îmi aduce aminte de ceva.

Sursa: habr.com

Automatizarea serverului SQL în Jenkins: returnăm rezultatul frumos.