🥇Automatización del servidor SQL en Jenkins: devolver el resultado maravillosamente

De nuevo Continuando con el tema del arreglo. Cero toque PROD bajo RDS. Los futuros DBA no podrán conectarse directamente a los servidores PROD, pero podrán utilizar Jenkins trabajos para un conjunto limitado de operaciones. El DBA inicia el trabajo y después de un tiempo recibe una carta con un informe sobre la finalización de esta operación. Veamos formas de presentar estos resultados al usuario.

Texto sin formato

Empecemos por lo más trivial. El primer método es tan simple que realmente no hay nada de qué hablar (el autor de ahora en adelante usa trabajos FreeStyle):

sqlcmd hace algo y se lo presentamos al usuario. Ideal para, por ejemplo, trabajos de copia de seguridad:

Por cierto, no olvides que en RDS la copia de seguridad/restauración es asincrónica, por lo que debes esperar:

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í también es muy sencillo:

Sin embargo, este método sólo funciona si los datos devueltos en el CSV son "simples". Si intenta devolver, por ejemplo, una lista de las N consultas con uso intensivo de CPU de esta manera, el CSV se "corroerá" debido al hecho de que el texto de la consulta puede contener cualquier carácter: comas, comillas e incluso saltos de línea. Por tanto, necesitamos algo más complicado.

Hermosos letreros en HTML

Te daré 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 cierto, preste atención a la línea con System.Management.Automation.PSCustomObject, es mágica, si hay exactamente una línea en la cuadrícula, entonces surgieron algunos problemas. La solución fue sacada de Internet sin mucha comprensión. Como resultado, obtendrá una salida con un formato similar a este:

Dibujar gráficos

Advertencia: ¡código pervertido a continuación!
Hay una consulta divertida en el servidor SQL que muestra la CPU durante los últimos N minutos: ¡resulta que el camarada mayor recuerda todo! Pruebe 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);

Ahora, usando este formato ($variable Fragmento)

<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 el cuerpo de la carta:

$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í:

¡Sí, el señor sabe mucho de perversiones! Es interesante que este código contenga: Powershell (escrito en él), SQL, Xquery, HTML. Es una pena que no podamos agregar Javascript a HTML (ya que es para escribir), ¡pero pulir el código Python (que se puede usar en SQL) es tarea de todos!

Salida de seguimiento del perfilador SQL

Está claro que el seguimiento no cabe en el CSV debido al campo TextData. Pero mostrar una cuadrícula de seguimiento en una letra también es extraño, tanto por el tamaño como porque estos datos se utilizan a menudo para análisis posteriores. Por lo tanto, hacemos lo siguiente: llamamos vía invocar-SqlCmd un determinado guión, en cuyas profundidades se hace

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

Siguiente otro En el servidor al que puede acceder el DBA, hay una base de datos de Traces con una plantilla vacía, la placa del modelo, lista para aceptar todas las columnas especificadas. Copiamos este modelo a una nueva tabla con un nombre ú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

Y ahora podemos escribir nuestro rastro en él usando Datos.SqlClient.SqlBulkCopy - Ya he dado un ejemplo de esto arriba. Sí, también sería bueno 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
}

Reemplazamos números de más de un carácter con 999 y reemplazamos cadenas de más de un carácter con 'str'. Los números del 0 al 9 se utilizan a menudo como banderas y no los tocamos, y entre ellos a menudo se encuentran cadenas vacías y de un solo carácter: 'Y', 'N', etc.

Agreguemos un poco de color a nuestras vidas (estrictamente mayores de 18 años)

En las tablas, a menudo desea resaltar las celdas que requieren atención. Por ejemplo, FAILS, alto nivel de fragmentación, etc. Por supuesto, esto se puede hacer en SQL simple, generando HTML usando PRINT y configurando el tipo de archivo 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 qué escribí ese código?

Pero hay una solución más hermosa. Convertir a HTML no nos permite colorear las celdas, pero podemos hacerlo después del hecho. Por ejemplo, queremos seleccionar celdas con un nivel de fragmentación superior a 80 y superior a 90. Agreguemos estilos:

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

En la consulta misma agregaremos una columna ficticia. inmediatamente antes columna que queremos colorear. La columna debe llamarse 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,

Ahora, habiendo recibido el HTML generado por Powershell, eliminaremos la columna ficticia del encabezado y en el cuerpo de los datos transferiremos el valor de la columna al estilo. Esto se hace con sólo dos sustituciones:

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

Resultado:

¿No es elegante? Aunque no, este color me recuerda a algo.

Fuente: habr.com

Automatización del servidor SQL en Jenkins: devolver el resultado maravillosamente