🥇Automatización do servidor SQL en Jenkins: devolvendo o resultado moi ben

De novo continuando o tema do arranxo PROD Zero Touch baixo RDS. Os futuros DBA non poderán conectarse directamente aos servidores PROD, pero poderán usar Jenkins traballos para un conxunto limitado de operacións. O DBA lanza o traballo e despois dun tempo recibe unha carta cun informe sobre a conclusión desta operación. Vexamos formas de presentar estes resultados ao usuario.

Texto sen formato

Comecemos polo máis trivial. O primeiro método é tan sinxelo que realmente non hai nada do que falar (o autor en adiante usa traballos de FreeStyle):

sqlcmd fai algo e presentámosllo ao usuario. Ideal para, por exemplo, traballos de copia de seguridade:

Non esquezas, por certo, que a copia de seguridade/restauración de RDS é asíncrona, polo que tes que agardar:

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

Segundo método, CSV

Todo aquí tamén é moi sinxelo:

Non obstante, este método só funciona se os datos devoltos no CSV son "simples". Se tentas devolver, por exemplo, unha lista de consultas con uso intensivo de CPU N TOP N deste xeito, o CSV "corroderase" debido ao feito de que o texto da consulta pode conter calquera carácter: comas, comiñas e mesmo saltos de liña. Polo tanto, necesitamos algo máis complicado.

Fermosos sinais en HTML

Dareiche un fragmento de código de inmediato

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

Por certo, presta atención á liña con System.Management.Automation.PSCustomObject, é máxico; se hai exactamente unha liña na grella, xurdiron algúns problemas. A solución foi tomada de Internet sen moito entendemento. Como resultado, obterás un formato de saída como isto:

Debuxar gráficas

Aviso: código perverso a continuación!
Hai unha consulta divertida no servidor SQL que amosa a CPU durante os últimos N minutos; resulta que o camarada maior recorda todo! Proba este cuestionario:

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

Agora, usando este formato (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>

Podemos formar o corpo da letra:

$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 se verá así:

Si, o señor sabe moito de perversións! É interesante que este código conteña: Powershell (escrito nel), SQL, Xquery, HTML. É unha mágoa que non poidamos engadir Javascript a HTML (xa que é para escribir), pero pulir o código Python (que se pode usar en SQL) é deber de todos!

Saída de rastrexo do perfil SQL

Está claro que o rastro non encaixará no CSV debido ao campo TextData. Pero mostrar unha cuadrícula de trazos nunha carta tamén é estraño, tanto polo tamaño como porque estes datos adoitan usarse para unha análise posterior. Polo tanto, facemos o seguinte: chamamos via invocar-SqlCmd un determinado guión, no fondo do que se fai

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

A continuación, en amigo No servidor accesible polo DBA, hai unha base de datos Traces cun modelo baleiro, a placa Modelo, lista para aceptar todas as columnas especificadas. Copiamos este modelo a unha nova táboa cun nome único:

$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

E agora podemos escribir o noso rastro nel usando Data.SqlClient.SqlBulkCopy - Xa dei un exemplo diso arriba. Si, tamén sería bo enmascarar constantes en 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ímos os números de máis dun carácter por 999 e as cadeas de máis dun carácter por "str". Os números do 0 ao 9 utilízanse a miúdo como bandeiras e non os tocamos, así como cadeas baleiras e dun só carácter: "Y", "N", etc. adoitan atoparse entre elas.

Imos engadir algo de cor ás nosas vidas (estrictamente maiores de 18 anos)

Nas táboas, moitas veces quere destacar as celas que requiren atención. Por exemplo, FALLAS, alto nivel de fragmentación, etc. Por suposto, isto pódese facer en SQL simple, xerando HTML usando PRINT e configurando o tipo de ficheiro en HTML en 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>'

Por que escribín tal código?

Pero hai unha solución máis bonita. Converter a HTML non nos deixa colorear as celas, pero podemos facelo despois do feito. Por exemplo, queremos seleccionar celas cun nivel de fragmentación superior a 80 e superior a 90. Engademos estilos:

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

Na propia consulta engadiremos unha columna ficticia inmediatamente antes columna que queremos colorear. A columna debería chamarse SQLmarkup-algo:

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,

Agora, recibindo o HTML xerado por Powershell, eliminaremos a columna ficticia da cabeceira e no corpo dos datos transferiremos o valor da columna ao estilo. Isto faise con só dúas substitucións:

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

Resultado:

Non é elegante? Aínda que non, esta cor lémbrame a algo

Fonte: www.habr.com

Automatización do servidor SQL en Jenkins: devolvendo o resultado moi ben