🥇Automasjon av SQL-server i Jenkins: returnerer resultatet vakkert

igjen fortsetter temaet arrangement Zero Touch PROD under RDS. Fremtidige DBA-er vil ikke kunne koble til PROD-servere direkte, men vil kunne bruke Jenkins jobber for et begrenset sett med operasjoner. DBA lanserer jobb og mottar etter en tid et brev med en rapport om fullføring av denne operasjonen. La oss se på måter å presentere disse resultatene for brukeren på.

Ren tekst

La oss starte med det mest trivielle. Den første metoden er så enkel at det egentlig ikke er noe å snakke om (forfatteren bruker heretter FreeStyle-jobber):

sqlcmd gjør noe og vi presenterer det for brukeren. Ideell for for eksempel backupjobber:

Ikke glem forresten at under RDS er sikkerhetskopiering/gjenoppretting asynkron, så du må 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

Andre metode, CSV

Alt her er også veldig enkelt:

Denne metoden fungerer imidlertid bare hvis dataene som returneres i CSV-en er "enkle". Hvis du prøver å returnere for eksempel en liste over TOP N CPU-intensive spørringer på denne måten, vil CSV-en "korrodere" på grunn av det faktum at spørringsteksten kan inneholde alle tegn - komma, anførselstegn og til og med linjeskift. Derfor trenger vi noe mer komplisert.

Vakre skilt i HTML

Jeg gir deg en kodebit med en gang

$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 forresten oppmerksom på linjen med System.Management.Automation.PSCustomObject, den er magisk; hvis det er nøyaktig en linje i rutenettet, oppsto det noen problemer. Løsningen ble hentet fra Internett uten særlig forståelse. Som et resultat vil du få utdata formatert noe slikt:

Tegne grafer

Advarsel: kinky kode nedenfor!
Det er en morsom spørring på SQL-serveren som viser CPU de siste N minuttene - det viser seg at kamerat Major husker alt! Prøv denne quizen:

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

Nå bruker du denne formateringen ($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 hoveddelen av brevet:

$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 vil se slik ut:

Ja, Monsieur vet mye om perversjoner! Det er interessant at denne koden inneholder: Powershell (skrevet i den), SQL, Xquery, HTML. Det er synd at vi ikke kan legge til Javascript i HTML (siden det er for å skrive), men å polere Python-kode (som kan brukes i SQL) er alles plikt!

SQL profiler sporing utgang

Det er klart at sporet ikke vil passe inn i CSV-en på grunn av TextData-feltet. Men å vise et spornett i en bokstav er også merkelig – både på grunn av størrelsen og fordi disse dataene ofte brukes til videre analyse. Derfor gjør vi følgende: vi ringer via invoke-SqlCmd et bestemt manus, i dybden som det er gjort

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

Videre venn På serveren som er tilgjengelig for DBA, er det en Traces-database med en tom mal, modellplaten, klar til å akseptere alle spesifiserte kolonner. Vi kopierer denne modellen til en ny tabell 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 nå kan vi skrive sporet vårt inn i det ved å bruke Data.SqlClient.SqlBulkCopy – Jeg har allerede gitt et eksempel på dette ovenfor. Ja, det ville også vært fint å 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 tall med mer enn ett tegn med 999, og vi erstatter strenger som er lengre enn ett tegn med 'str'. Tall fra 0 til 9 brukes ofte som flagg, og vi berører dem ikke, så vel som tomme og enkelttegnsstrenger - 'Y', 'N' osv. finnes ofte blant dem.

La oss gi litt farge til livene våre (strengt 18+)

I tabeller vil du ofte fremheve celler som krever oppmerksomhet. For eksempel FAILS, høy grad av fragmentering, etc. Selvfølgelig kan dette gjøres i bare SQL, generere HTML ved hjelp av PRINT, og sette 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 en slik kode?

Men det finnes en vakrere løsning. Konverter til HTML lar oss ikke fargelegge cellene, men vi kan gjøre det i ettertid. For eksempel ønsker vi å velge celler med et fragmenteringsnivå på mer enn 80 og mer enn 90. La oss legge til stiler:

<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 spørringen vil vi legge til en dummy-kolonne rett før kolonne vi ønsker å fargelegge. Kolonnen skal kalles SQLmarkup-noe:

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å, etter å ha mottatt HTML generert av Powershell, vil vi fjerne dummy-kolonnen fra overskriften, og i hoveddelen av dataene vil vi overføre verdien fra kolonnen til stilen. Dette gjøres med bare to erstatninger:

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

Resultat:

Er det ikke elegant? Selv om nei, denne fargen minner meg om noe

Kilde: www.habr.com

Automatisering av SQL-server i Jenkins: returnerer resultatet vakkert