🥇Outomatisering van SQL-bediener in Jenkins: gee die resultaat pragtig terug

weer die tema van reëling voort te sit Zero Touch PROD onder RDS. Toekomstige DBA's sal nie direk aan PROD-bedieners kan koppel nie, maar sal wel kan gebruik Jenkins werksgeleenthede vir 'n beperkte stel bedrywighede. Die DBA loods pos en ontvang na 'n geruime tyd 'n brief met 'n verslag oor die voltooiing van hierdie operasie. Kom ons kyk na maniere om hierdie resultate aan die gebruiker voor te stel.

Plain Text

Kom ons begin met die mees onbenullige. Die eerste metode is so eenvoudig dat daar eintlik niks is om oor te praat nie (die skrywer gebruik hierna FreeStyle-take):

sqlcmd doen iets en ons bied dit aan die gebruiker. Ideaal vir byvoorbeeld rugsteuntake:

Moenie vergeet nie, terloops, dat rugsteun/herstel onder RDS asinchroon is, so jy moet daarvoor wag:

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

Tweede metode, CSV

Alles hier is ook baie eenvoudig:

Hierdie metode werk egter net as die data wat in die CSV teruggestuur word "eenvoudig" is. As jy byvoorbeeld probeer om 'n lys van TOP N SVE-intensiewe navrae op hierdie manier terug te gee, sal die CSV "korrodeer" as gevolg van die feit dat die navraagteks enige karakters kan bevat - kommas, aanhalingstekens en selfs reëlbreuke. Daarom het ons iets meer ingewikkeld nodig.

Pragtige tekens in HTML

Ek sal dadelik vir jou 'n kodebrokkie gee

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

Terloops, let op die lyn met System.Management.Automation.PSCustomObject, dit is magies; as daar presies een lyn in die rooster is, dan het 'n paar probleme ontstaan. Die oplossing is sonder veel begrip van die internet af geneem. As gevolg hiervan, sal jy uitset kry wat so iets geformateer is:

Teken grafieke

Waarskuwing: kinky kode hieronder!
Daar is 'n snaakse navraag op die SQL-bediener wat die SVE vir die laaste N minute vertoon - dit blyk dat kameraad majoor alles onthou! Probeer hierdie vasvra:

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

Gebruik nou hierdie formatering ($Fragment-veranderlike)

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

Ons kan die liggaam van die brief vorm:

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

Wat sal so lyk:

Ja, Monsieur weet baie van perversies! Dit is interessant dat hierdie kode bevat: Powershell (geskryf daarin), SQL, Xquery, HTML. Dit is jammer dat ons nie Javascript by HTML kan voeg nie (aangesien dit vir skryf is), maar om Python-kode (wat in SQL gebruik kan word) te poleer is almal se plig!

SQL-profileerder-spooruitset

Dit is duidelik dat die spoor nie in die CSV sal pas nie as gevolg van die TextData-veld. Maar om 'n spoorrooster in 'n letter te vertoon, is ook vreemd - beide vanweë die grootte en omdat hierdie data dikwels vir verdere ontleding gebruik word. Daarom doen ons die volgende: ons bel via invoke-SqlCmd 'n sekere skrif, in die dieptes waarvan dit gedoen word

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

Volgende, aan vriend Op die bediener wat deur die DBA toeganklik is, is daar 'n Traces-databasis met 'n leë sjabloon, die Modelplaat, gereed om al die gespesifiseerde kolomme te aanvaar. Ons kopieer hierdie model na 'n nuwe tabel met 'n unieke naam:

$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

En nou kan ons ons spoor daarin skryf met behulp van Data.SqlClient.SqlBulkCopy - Ek het reeds 'n voorbeeld hiervan hierbo gegee. Ja, dit sal ook lekker wees om konstantes in TextData te masker:

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

Ons vervang getalle meer as een karakter lank met 999, en ons vervang stringe langer as een karakter met 'str'. Getalle van 0 tot 9 word dikwels as vlae gebruik, en ons raak nie daaraan nie, sowel as leë en enkelkarakterstringe - 'Y', 'N', ens. word dikwels tussen hulle gevind.

Kom ons gee 'n bietjie kleur aan ons lewens (streng 18+)

In tabelle wil jy dikwels selle uitlig wat aandag verg. Byvoorbeeld, FAIL, hoë vlak van fragmentasie, ens. Natuurlik kan dit in blote SQL gedoen word, HTML genereer deur PRINT te gebruik en die lêertipe in Jenkins op HTML te stel:

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

Hoekom het ek so kode geskryf?

Maar daar is 'n mooier oplossing. Skakel na HTML laat ons nie die selle inkleur nie, maar ons kan dit agterna doen. Ons wil byvoorbeeld selle kies met 'n fragmentasievlak van meer as 80 en meer as 90. Kom ons voeg style by:

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

In die navraag self sal ons 'n dummy kolom byvoeg onmiddellik voor kolom wat ons wil inkleur. Die kolom moet genoem word SQLmarkup-iets:

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,

Nou, nadat ons die HTML ontvang het wat deur Powershell gegenereer is, sal ons die dummy-kolom van die kopskrif verwyder, en in die liggaam van die data sal ons die waarde van die kolom na die styl oordra. Dit word gedoen met slegs twee vervangings:

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

Gevolg:

Is dit nie elegant nie? Alhoewel nee, hierdie kleursel herinner my aan iets

Bron: will.com

Outomatisering van SQL-bediener in Jenkins: gee die resultaat pragtig terug