🥇SQL-serveri automatiseerimine Jenkinsis: tagasime tulemuse ilusalt

Taaskord rikkudes teemat seadistamise Zero Touch PROD RDS all. Tulevased DBA-d ei saa otse PROD serveritele juurde pääseda, kuid saavad kasutada Jenkins töid piiratud hulga toimingute jaoks. DBA käivitab töö ja mõne aja pärast saab ta e-kirja selle operatsiooni täitmise kohta. Vaadakem neid tulemusi kasutajale esitamise viise.

Plain Text

Alustame kõige elementaarsemast. Esimene viis on nii lihtne, et sellest pole tegelikult mõtet rääkida (autor kasutab allpool FreeStyle töid):

sqlcmd midagi täidab, ja me esitame selle kasutajale. Ideaalne näiteks backup tööde jaoks:

Ärge unustage, et RDS all on backup/restore asünkroonne, seega tuleks seda oodata:

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} asendatakse powershelli abil andmebaasi nimega
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Teine viis, CSV

Siin on kõik samuti väga lihtne:

Kuid see meetod töötab ainult siis, kui CSV-s tagastatavad andmed on "lihtsad". Kui proovite sel viisil tagastada näiteks TOP N CPU mahukate päringute loendi, siis CSV "laguneb", kuna päringu tekst võib sisaldada erinevaid sümboleid – komasid, jutumärke ja isegi reavahetusi. Seetõttu vajame midagi keerukamat.

Kaunid tabelid HTML-is

Tõin kohe välja koodilõigu

$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>Minu tabel</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Andmed puuduvad</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

Muide, pöörake tähelepanu reale System.Management.Automation.PSCustomObject, see on maagiline; kui gridis on täpselt üks rida, siis tekkisid mingid probleemid. Lahendus on leitud internetist, erilist süvenemist tegemata. Tulemuseks on ligikaudu selline vormindatud väljund:

Joonistame graafikud

Tähelepanu: allpool on veider kood!
On olemas naljakas päring SQL serveris, mis kuvab CPU viimase N minuti jooksul – selgub, et kapten Major mäletab kõike! Proovige seda päringut:

Määrake @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);

Nüüd, kasutades sellist vormindamist (muutuja $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>

Saame koostada kirja sisu:

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

Mis näeb välja järgmine:

Jah, härra teab tõeliselt, mis on lustakus! Huvitav, et see kood sisaldab: Powershell (millel see on kirjutatud), SQL, Xquery, HTML. Kahju, et HTML-le ei saa lisada Javascripti (kuna see on e-kirja jaoks), kuid Python-koodi lihvimine (mida saab kasutada SQL-is) on igaühe kohustus!

SQL profiler trace väljund

On selge, et trace 'ei pääse' CSV-sse, kuna TextData väli. Kuid trace'i esitamine gridina kirjas on samuti kummaline — mitte ainult suuruse tõttu, vaid ka seetõttu, et neid andmeid kasutatakse sageli edasise analüüsi jaoks. Seetõttu teeme järgmist: kutsume esile invoke-SqlCmd mingi skripti, mille sisu teeb

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

Edasi, sõbraga serveril, kergesti kätte saadaval DBA-le, on olemas Traces andmebaas tühi vorm, tabeliga Model, mis on valmis võtma kõik nimetatud veerud. Me kopeerime selle mudeli uude tabelisse unikaalse nimega:

$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

Ja nüüd saame me sinna salvestada meie trace'i abil Data.SqlClient.SqlBulkCopy — näide sellest olen juba varem toonud. Jah, oleks hea teha masking konstante TextData-s:

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

Asendame numbrid, mis on pikemad kui ühe tegelasega, 999-ga, ja stringid, mis on pikemad kui üks tegelane, asendame 'str'-iga. Numbrid vahemikus 0 kuni 9 kasutatakse sageli lipukestena ja me ei puutu neid, samuti tühjad ja ühesuguste pikkustega stringid — nende seas on sageli 'Y', 'N' jne.

Lisame värve meie ellu (tõsiselt 18+)

Tabelites tahame sageli esile tõsta rakke, mis vajavad tähelepanu. Näiteks FAILS, kõrge fragmentatsiooni tase jne. Muidugi saab seda teha ka puhta SQL-iga, genereerides HTML-i PRINT-i kaudu, ja Jenkinsis määrata failitüübiks HTML:

deklareeri @body varchar(max), @chunk varchar(max)
määratle @body='<font face="Lucida Console" size="3">'
määra @body=@body+'<b>Serveri nimi: '+@@servername+'</b><br>'
määra @body=@body+'<br><br>'
määra @body=@body+'<table><tr><th>Töö</th><th>Viimane käivitamine</th><th>Keskmine kestus, sek</th><th>Viimane käivitamine, sek</th><th>Viimane olek</th></tr>'
print @body

DECLARE tab CURSOR FOR SELECT '<tr><td>'+name+'</td><td>'+
  ViimaneKäivitamine+'</td><td>'+
  convert(varchar,KeskmineKestus)+'</td><td>'+
  convert(varchar,ViimaneKestus)+'</td><td>'+
    case when ViimaneOlek<>'Õnnestus' then '<font color="red">' else '' end+
      ViimaneOlek+
      case when ViimaneOlek<>'Õnnestus' 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>'

Miks ma kirjutasin sellise koodi?

Aga on ilusam lahendus. ConvertTo-HTML ei anna meile rakke värvata, kuid saame seda teha hilisema protsessina. Näiteks tahame esile tõsta rakke, mille fragmentatsiooni tase on üle 80 ja 90. Lisame stiilid:

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

Küsimuses lisame vale veeru otse enne veergu, mida tahame värvida. Veeru nimi peab olema SQLmarkup-midagi:

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,

Nüüd, pärast Powershelli loodud HTML-i saamist, eemaldame pealkirjast vale veeru ja toome andmete kehas väärtuse veerust stiili. See toimub kõigest kahe asendusega:

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

Tulemus:

Ei ole tõsi, et see värvigraafika mulle meenutab

Allikas: habr.com

SQL serveri automatiseerimine Jenkinsis: toome tulemuse ilusasti välja