🥇Automatització del servidor SQL a Jenkins: retornant el resultat molt bé

De nou continuant el tema de l'arranjament Zero Touch PROD sota RDS. Els futurs DBA no es podran connectar directament als servidors PROD, però sí que ho podran utilitzar Jenkins llocs de treball per a un conjunt limitat d'operacions. El DBA llança la feina i després d'un temps rep una carta amb un informe sobre la finalització d'aquesta operació. Vegem maneres de presentar aquests resultats a l'usuari.

Text simple

Comencem pel més trivial. El primer mètode és tan senzill que realment no hi ha res a parlar (l'autor d'ara endavant utilitza treballs de FreeStyle):

sqlcmd fa alguna cosa i la presentem a l'usuari. Ideal per, per exemple, feines de còpia de seguretat:

No oblideu, per cert, que la còpia de seguretat/restauració de RDS és asíncrona, així que cal esperar-la:

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

Segon mètode, CSV

Tot aquí també és molt senzill:

Tanmateix, aquest mètode només funciona si les dades retornades al CSV són "simples". Si intenteu retornar, per exemple, una llista de consultes intensives de CPU TOP N d'aquesta manera, el CSV es "corrodirà" a causa del fet que el text de la consulta pot contenir qualsevol caràcter: comes, cometes i fins i tot salts de línia. Per tant, necessitem quelcom més complicat.

Bells signes en HTML

Et donaré un fragment de codi de seguida

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

Per cert, presteu atenció a la línia amb System.Management.Automation.PSCustomObject, és màgic si hi ha exactament una línia a la quadrícula, llavors van sorgir alguns problemes. La solució es va agafar d'Internet sense gaire comprensió. Com a resultat, obtindreu un format de sortida com aquest:

Dibuix de gràfics

Avís: codi pervers a continuació!
Hi ha una consulta divertida al servidor SQL que mostra la CPU durant els últims N minuts: resulta que el camarada Major ho recorda tot! Prova aquest qüestionari:

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

Ara, utilitzant aquest format (variable $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>

Podem formar el cos de la lletra:

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

Que quedarà així:

Sí, el senyor sap molt de perversions! És interessant que aquest codi contingui: Powershell (escrit en ell), SQL, Xquery, HTML. És una llàstima que no puguem afegir Javascript a HTML (ja que és per escriure), però polir el codi Python (que es pot utilitzar en SQL) és deure de tots!

Sortida de traça del perfilador SQL

És clar que la traça no encaixarà al CSV a causa del camp TextData. Però mostrar una graella de traça en una carta també és estrany, tant per la mida com perquè aquestes dades s'utilitzen sovint per a una anàlisi posterior. Per tant, fem el següent: cridem via invocar-SqlCmd un determinat guió, en les profunditats del qual es fa

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

A continuació, encès amic Al servidor accessible pel DBA, hi ha una base de dades Traces amb una plantilla buida, la placa Model, preparada per acceptar totes les columnes especificades. Copiem aquest model a una taula nova amb un nom únic:

$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

I ara podem escriure-hi el nostre rastre fent servir Data.SqlClient.SqlBulkCopy - Ja n'he posat un exemple més amunt. Sí, també seria bo emmascarar constants a TextData:

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

Substituïm els nombres de més d'un caràcter per 999 i les cadenes de més d'un caràcter per 'str'. Els nombres del 0 al 9 s'utilitzen sovint com a banderes i no els toquem, així com cadenes buides i d'un sol caràcter: 'Y', 'N', etc., sovint es troben entre elles.

Afegim una mica de color a les nostres vides (estrictament majors de 18 anys)

A les taules, sovint voleu destacar les cel·les que requereixen atenció. Per exemple, FAILS, alt nivell de fragmentació, etc. Per descomptat, això es pot fer en SQL nu, generant HTML mitjançant PRINT i establint el tipus de fitxer a HTML a 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>'

Per què vaig escriure aquest codi?

Però hi ha una solució més bonica. Convertir a HTML no ens deixa pintar les cel·les, però ho podem fer després del fet. Per exemple, volem seleccionar cel·les amb un nivell de fragmentació superior a 80 i superior a 90. Afegim estils:

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

A la consulta mateixa afegirem una columna ficticia immediatament abans columna que volem pintar. S'ha d'anomenar la columna SQLmarkup-alguna cosa:

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,

Ara, havent rebut l'HTML generat per Powershell, eliminarem la columna ficticia de la capçalera, i al cos de les dades transferirem el valor de la columna a l'estil. Això es fa amb només dues substitucions:

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

Resultat:

No és elegant? Encara que no, aquest color em recorda alguna cosa

Font: www.habr.com

Automatització del servidor SQL a Jenkins: retornant el resultat molt bé