🥇Automazione SQL server in Jenkins: restituiamo il risultato in modo elegante

Ancora continuando il tema dell'installazione Zero Touch PROD sotto RDS. I futuri DBA non potranno connettersi direttamente ai server PROD, ma potranno utilizzare Jenkins job per un insieme limitato di operazioni. Il DBA avvia un job e dopo un po' riceve un'email con un rapporto sull'esecuzione di quest'operazione. Esaminiamo i modi per presentare questi risultati all'utente.

Plain Text

Iniziamo con la soluzione più banale. Il primo metodo è così semplice che non c'è molto di cui parlare (l'autore qui e in seguito utilizza FreeStyle jobs):

sqlcmd esegue qualcosa e presentiamo il risultato all'utente. Si adatta perfettamente, per esempio, ai backup jobs:

Non dimentichiamo, tra l'altro, che il backup/restore in RDS è asincrono, quindi sarebbe bene aspettarlo:

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} sostituito con il nome del db da powershell
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Il secondo metodo, CSV

Qui tutto è molto semplice:

Tuttavia, questo metodo funziona solo se i dati restituiti in CSV sono "semplici". Se provi a restituire, ad esempio, un elenco delle TOP N query intensive sulla CPU, il CSV si "disgrega" perché il testo della query può contenere qualsiasi simbolo: virgole, virgolette e persino ritorni a capo. Pertanto, avremo bisogno di qualcosa di più complesso.

Belle tabelle in HTML

Ecco un frammento di codice

$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>La mia tabella</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Nessun dato</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

A proposito, fai attenzione alla riga con System.Management.Automation.PSCustomObject, è magica; se nel grid c'è esattamente una riga, sorgono alcuni problemi. La soluzione è stata presa da internet senza esaminare troppo. Alla fine, otterrai un output formattato in questo modo:

Disegniamo grafici

Attenzione: il codice pervertito qui sotto!
C'è una divertente query su SQL Server che restituisce l'uso della CPU negli ultimi N minuti: sembra che il compagno maggiore memorizzi tutto! Prova questa query:

DICHIARA @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) 
  FROM sys.dm_os_sys_info WITH (NOLOCK)); 
SELEZIONA TOP(256) 
  DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime],
  SQLProcessUtilization AS [SQLCPU], 
  100 - SystemIdle - SQLProcessUtilization AS [OtherCPU]
DA (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 
ORDINA PER 1 DESC OPZIONE (RECOMPILA);

Adesso, utilizzando questo formato (variabile $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>

Possiamo formare il corpo dell'email:

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

Che apparirà così:

Certo, monsieur sa bene come si fa! È interessante notare che questo codice contiene: Powershell (in cui è scritto), SQL, Xquery, HTML. Peccato che a HTML non possiamo aggiungere Javascript (poiché è per la scrittura), ma rifinire il codice Python (che può essere usato in SQL) è un dovere di ogni sviluppatore!

Output della traccia del profiler SQL

È chiaro che la traccia "non potrà" essere inserita in un CSV a causa del campo TextData. Tuttavia, presentare la traccia in un grid nella mail è strano — sia per le dimensioni, sia perché spesso questi dati vengono utilizzati per ulteriori analisi. Quindi facciamo quanto segue: chiamiamo attraverso invoke-SqlCmd un certo script, in cui viene eseguito

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

Successivamente, su amico server, accessibile al DBA, esiste un database Traces con un template vuoto, una tabella Model, pronta ad accettare tutte le colonne indicate. Copiamo questo modello in una nuova tabella con un nome unico:

$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

E ora possiamo registrare la nostra traccia utilizzando Data.SqlClient.SqlBulkCopy — ho già fatto un esempio di questo sopra. Sì, sarebbe anche utile mascherare le costanti in TextData:

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

Sostituiamo i numeri con più di un carattere lungo con 999, e le stringhe più lunghe di un carattere le sostituiamo con ‘str’. I numeri da 0 a 9 sono spesso utilizzati come flag e non li tocchiamo, così come le stringhe vuote e di un solo carattere — tra queste ci sono spesso ‘Y’, ‘N’, ecc.

Aggiungiamo colori alla nostra vita (solo per maggiorenni)

Nelle tabelle spesso vogliamo evidenziare le celle che necessitano di attenzione. Ad esempio, FAILS, alto livello di frammentazione, ecc. Certamente, è possibile farlo anche con puro SQL, generando HTML tramite PRINT, e in Jenkins impostare il tipo di file su HTML:

dichiarare @body varchar(max), @chunk varchar(max)
set @body='<font face="Lucida Console" size="3">'
set @body=@body+'<b>Nome del server: '+@@servername+'</b><br>'
set @body=@body+'<br><br>'
set @body=@body+'<table><tr><th>Job</th><th>Ultima esecuzione</th><th>Durata media, sec</th><th>Ultima esecuzione, sec</th><th>Ultimo stato</th></tr>'
print @body

DICHIARARE tab CURSOR PER SELEZIONARE '<tr><td>'+name+'</td><td>'+ LastRun+'</td><td>'+ convert(varchar,AvgDuration)+'</td><td>'+ convert(varchar,LastDuration)+'</td><td>'+ case when LastStatus<>'Riuscito' then '<font color="red">' else '' end+ LastStatus+ case when LastStatus<>'Riuscito' 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>'

Perché ho scritto questo codice?

Ma c'è una soluzione più elegante. ConvertTo-HTML non ci consente di colorare le celle, ma possiamo farlo successivamente. Ad esempio, vogliamo evidenziare le celle con un livello di frammentazione superiore all'80 e al 90. Aggiungiamo stili:

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

Nella query aggiungeremo una colonna fittizia immediatamente prima della colonna che vogliamo colorare. La colonna deve chiamarsi SQLmarkup-qualcosa:

caso  
  quando ps.avg_fragmentation_in_percent>=90.0 allora 'SQLmarkup-red'
  quando ps.avg_fragmentation_in_percent>=80.0 allora 'SQLmarkup-yellow'
  altrimenti 'SQLmarkup-default' 
  fine come [SQLmarkup-1], 
ps.avg_fragmentation_in_percent,

Ora, una volta ottenuto l'HTML generato da Powershell, rimuoveremo la colonna fittizia dall'intestazione e trasferiremo il valore dalla colonna nello stile del corpo dei dati. Questo avviene con sole due sostituzioni:

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

Risultato:

Non è elegante? Anche se, qualcosa in questo colorito mi sembra familiare

Fonte: habr.com

Automazione del server SQL in Jenkins: presentiamo i risultati in modo elegante