🥇 SQL-palvelimen automatisointi Jenkinsissä: palauttaa tuloksen kauniisti

uudelleen Jatketaan järjestelyteemaa Zero Touch PROD RDS:n alla. Tulevat DBA:t eivät voi muodostaa yhteyttä PROD-palvelimiin suoraan, mutta voivat käyttää Jenkins työpaikkoja rajoitetulle määrälle toimintoja. DBA aloittaa työn ja saa jonkin ajan kuluttua kirjeen, jossa on raportti tämän toimenpiteen valmistumisesta. Katsotaanpa tapoja esittää nämä tulokset käyttäjälle.

Plain Text

Aloitetaan triviaalimmasta. Ensimmäinen menetelmä on niin yksinkertainen, ettei siitä oikeastaan ole mitään puhuttavaa (kirjoittaja käyttää jäljempänä FreeStyle-töitä):

sqlcmd tekee jotain ja esittelemme sen käyttäjälle. Ihanteellinen esimerkiksi varmuuskopiointitöihin:

Älä muuten unohda, että RDS-varmuuskopiointi/palautus on asynkroninen, joten sinun on odotettava sitä:

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} substituted with db name by powershell
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Toinen menetelmä, CSV

Kaikki täällä on myös hyvin yksinkertaista:

Tämä menetelmä toimii kuitenkin vain, jos CSV:ssä palautetut tiedot ovat "yksinkertaisia". Jos yrität palauttaa esimerkiksi luettelon TOP N CPU-intensiivisistä kyselyistä tällä tavalla, CSV "syövyttää" johtuen siitä, että kyselyteksti voi sisältää mitä tahansa merkkejä - pilkkuja, lainausmerkkejä ja jopa rivinvaihtoja. Siksi tarvitsemme jotain monimutkaisempaa.

Kauniita merkkejä HTML:ssä

Annan sinulle koodinpätkän heti

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

Muuten, kiinnitä huomiota System.Management.Automation.PSCustomObjectin riviin, se on maaginen; jos ruudukossa on täsmälleen yksi rivi, syntyi joitain ongelmia. Ratkaisu haettiin Internetistä ymmärtämättä. Seurauksena on, että tulosteet muotoillaan näin:

Kaavioiden piirtäminen

Varoitus: perverssi koodi alla!
SQL-palvelimella on hauska kysely, joka näyttää CPU:n viimeisen N minuutin ajalta - käy ilmi, että toveri majuri muistaa kaiken! Kokeile tätä tietokilpailua:

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'%<SystemHealth>%') AS x) AS y 
ORDER BY 1 DESC OPTION (RECOMPILE);

Nyt käyttämällä tätä muotoilua ($Fragment-muuttuja)

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

Voimme muodostaa kirjeen rungon:

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

Joka näyttää tältä:

Kyllä, monsieur tietää paljon perversioista! On mielenkiintoista, että tämä koodi sisältää: Powershell (siihen kirjoitettu), SQL, Xquery, HTML. Harmi, ettemme voi lisätä Javascriptiä HTML:ään (koska se on kirjoittamista varten), mutta Python-koodin hiominen (jota voidaan käyttää SQL:ssä) on kaikkien velvollisuus!

SQL-profiilin jäljitystulos

On selvää, että jäljitys ei mahdu CSV-tiedostoon TextData-kentän vuoksi. Mutta jäljitysruudukon näyttäminen kirjeessä on myös outoa - sekä koon takia että siksi, että näitä tietoja käytetään usein lisäanalyysiin. Siksi teemme seuraavaa: soitamme kautta invoke-SqlCmd tietty käsikirjoitus, jonka syvyyksissä se tehdään

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

Seuraavaksi muut DBA:n käytettävissä olevalla palvelimella on Traces-tietokanta, jossa on tyhjä malli, Mallilevy, joka on valmis hyväksymään kaikki määritetyt sarakkeet. Kopioimme tämän mallin uuteen taulukkoon, jolla on yksilöllinen nimi:

$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 nyt voimme kirjoittaa jälkemme siihen käyttämällä Data.SqlClient.SqlBulkCopy - Olen jo antanut esimerkin tästä edellä. Kyllä, olisi myös mukavaa peittää vakiot TextDatassa:

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

Korvaamme useammat kuin yhden merkin pituiset numerot 999:llä ja yli yhden merkin pituiset merkkijonot 'str':llä. Lippuina käytetään usein numeroita 0-9, emmekä koske niihin, samoin kuin tyhjiä ja yksimerkkisiä merkkijonoja - 'Y', 'N' jne. - löytyy usein niiden joukosta.

Lisätään elämäämme väriä (18+)

Taulukoissa halutaan usein korostaa huomiota vaativia soluja. Esimerkiksi FAILS, korkea pirstoutuneisuus jne. Tietysti tämä voidaan tehdä paljaalla SQL:llä, luomalla HTML PRINT:llä ja asettamalla tiedostotyypiksi HTML Jenkinsissä:

declare @body varchar(max), @chunk varchar(max)
set @body='<font face="Lucida Console" size="3">'
set @body=@body+'<b>Server name: '+@@servername+'</b><br>'
set @body=@body+'<br><br>'
set @body=@body+'<table><tr><th>Job</th><th>Last Run</th><th>Avg Duration, sec</th><th>Last Run, Sec</th><th>Last Status</th></tr>'
print @body

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

Miksi kirjoitin tällaisen koodin?

Mutta on olemassa kauniimpi ratkaisu. Muunna HTML-muotoon ei anna meidän värjätä soluja, mutta voimme tehdä sen jälkikäteen. Haluamme esimerkiksi valita soluja, joiden sirpaloitumistaso on yli 80 ja yli 90. Lisätään tyylejä:

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

Itse kyselyyn lisäämme valesarakkeen välittömästi ennen sarake, jonka haluamme värittää. Kolumni pitäisi kutsua SQLmarkup-jotain:

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,

Nyt, kun olemme saaneet Powershellin luoman HTML:n, poistamme dummy-sarakkeen otsikosta ja siirrämme datan rungossa arvon sarakkeesta tyyliin. Tämä tehdään vain kahdella vaihdolla:

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

Результат:

Eikö olekin tyylikästä? Vaikka ei, tämä väritys muistuttaa minua jostain

Lähde: will.com

SQL-palvelimen automatisointi Jenkinsissä: palauttaa tuloksen kauniisti