🥇Automatisering af SQL-server i Jenkins: giver smukke resultater

igen fortsætter arrangementstemaet Zero Touch PROD under RDS. Fremtidige DBA'er vil ikke kunne oprette forbindelse til PROD-servere direkte, men vil være i stand til at bruge Jenkins job til et begrænset antal operationer. DBA lancerer job og modtager efter nogen tid et brev med en rapport om afslutningen af denne operation. Lad os se på måder at præsentere disse resultater for brugeren.

Plain Text

Lad os starte med det mest trivielle. Den første metode er så enkel, at der virkelig ikke er noget at tale om (forfatteren bruger i det følgende FreeStyle-job):

sqlcmd gør noget, og vi præsenterer det for brugeren. Ideel til f.eks. backupjob:

Glem forresten ikke, at under RDS backup/gendannelse er asynkron, så du skal vente på det:

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

Anden metode, CSV

Alt her er også meget enkelt:

Denne metode virker dog kun, hvis de data, der returneres i CSV'en, er "simpel". Hvis du forsøger at returnere for eksempel en liste over TOP N CPU-intensive forespørgsler på denne måde, vil CSV'en "korrodere" på grund af det faktum, at forespørgselsteksten kan indeholde alle tegn - kommaer, anførselstegn og endda linjeskift. Derfor har vi brug for noget mere kompliceret.

Smukke skilte i HTML

Jeg giver dig et kodestykke med det samme

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

Vær i øvrigt opmærksom på linjen med System.Management.Automation.PSCustomObject, den er magisk; hvis der er præcis én linje i gitteret, så opstod der nogle problemer. Løsningen blev hentet fra internettet uden den store forståelse. Som et resultat vil du få output formateret noget som dette:

Tegning af grafer

Advarsel: kinky kode nedenfor!
Der er en sjov forespørgsel på SQL-serveren, der viser CPU'en de sidste N minutter - det viser sig, at kammerat Major husker alt! Prøv denne quiz:

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

Brug nu denne formatering ($Fragment variabel)

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

Vi kan danne brevets krop:

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

Som kommer til at se sådan ud:

Ja, Monsieur ved meget om perversioner! Det er interessant, at denne kode indeholder: Powershell (skrevet i den), SQL, Xquery, HTML. Det er ærgerligt, at vi ikke kan tilføje Javascript til HTML (da det er til at skrive), men at polere Python-kode (som kan bruges i SQL) er alles pligt!

SQL profiler sporing output

Det er klart, at sporet ikke passer ind i CSV'en på grund af TextData-feltet. Men at vise et sporgitter i et bogstav er også mærkeligt - både på grund af størrelsen og fordi disse data ofte bruges til yderligere analyse. Derfor gør vi følgende: vi ringer via invoke-SqlCmd et bestemt manuskript, i hvis dybder det er lavet

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

Næste på ven server, tilgængelig for DBA'en, er der en Traces-database med en tom skabelon, en modeltabel, klar til at acceptere alle de angivne kolonner. Vi kopierer denne model til en ny tabel med et unikt navn:

$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

Og nu kan vi skrive vores spor ind i det vha Data.SqlClient.SqlBulkCopy - Det har jeg allerede givet et eksempel på ovenfor. Ja, det ville også være rart at maskere konstanter i TextData:

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

Vi erstatter tal med mere end ét tegn med 999, og vi erstatter strenge længere end ét tegn med 'str'. Tal fra 0 til 9 bruges ofte som flag, og vi rører dem ikke, samt tomme og enkelttegns strenge - 'Y', 'N' osv. findes ofte blandt dem.

Lad os tilføje lidt farve til vores liv (strengt 18+)

I tabeller vil du ofte fremhæve celler, der kræver opmærksomhed. Fx FAILS, høj grad af fragmentering osv. Selvfølgelig kan dette gøres i bare SQL, generere HTML ved hjælp af PRINT og indstille filtypen til HTML i Jenkins:

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

Hvorfor skrev jeg sådan en kode?

Men der er en smukkere løsning. Konverter til HTML lader os ikke farve cellerne, men vi kan gøre det bagefter. For eksempel vil vi vælge celler med et fragmenteringsniveau på mere end 80 og mere end 90. Lad os tilføje typografier:

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

I selve forespørgslen tilføjer vi en dummy-kolonne umiddelbart før kolonne, vi ønsker at farve. Kolonnen skal kaldes SQLmarkup-noget:

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,

Nu, efter at have modtaget HTML genereret af Powershell, fjerner vi dummy-kolonnen fra overskriften, og i kroppen af dataene overfører vi værdien fra kolonnen til stilen. Dette gøres med kun to udskiftninger:

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

Resultat:

Er det ikke elegant? Selvom nej, denne farve minder mig om noget

Kilde: www.habr.com

Automatisering af SQL-server i Jenkins: returnerer resultatet smukt