🥇Automatisierung des SQL-Servers in Jenkins: Das Ergebnis wunderbar zurückgeben

Wieder Fortsetzung des Themas der Anordnung Zero Touch PROD unter RDS. Zukünftige Datenbankadministratoren werden nicht in der Lage sein, eine direkte Verbindung zu PROD-Servern herzustellen, können diese aber nutzen Jenkins Jobs für eine begrenzte Anzahl von Operationen. Der DBA startet den Job und erhält nach einiger Zeit einen Brief mit einem Bericht über den Abschluss dieses Vorgangs. Schauen wir uns Möglichkeiten an, diese Ergebnisse dem Benutzer zu präsentieren.

Nur-Text

Beginnen wir mit dem Trivialsten. Die erste Methode ist so einfach, dass es eigentlich keinen Grund zur Diskussion gibt (der Autor verwendet im Folgenden FreeStyle-Jobs):

sqlcmd macht etwas und wir präsentieren es dem Benutzer. Ideal zum Beispiel für Backup-Jobs:

Vergessen Sie übrigens nicht, dass die Sicherung/Wiederherstellung unter RDS asynchron erfolgt, Sie müssen also darauf warten:

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

Zweite Methode, CSV

Auch hier ist alles ganz einfach:

Diese Methode funktioniert jedoch nur, wenn die in der CSV zurückgegebenen Daten „einfach“ sind. Wenn Sie auf diese Weise beispielsweise versuchen, eine Liste der TOP N CPU-intensiven Abfragen zurückzugeben, wird die CSV „korrodieren“, da der Abfragetext beliebige Zeichen enthalten kann – Kommas, Anführungszeichen und sogar Zeilenumbrüche. Deshalb brauchen wir etwas Komplizierteres.

Schöne Schilder in HTML

Ich gebe Ihnen gleich einen Codeausschnitt

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

Achten Sie übrigens auf die Zeile mit System.Management.Automation.PSCustomObject, sie ist magisch; wenn es genau eine Zeile im Raster gibt, sind einige Probleme aufgetreten. Die Lösung wurde ohne großes Verständnis aus dem Internet übernommen. Als Ergebnis erhalten Sie eine Ausgabe, die etwa so formatiert ist:

Diagramme zeichnen

Achtung: Verrückter Code unten!
Es gibt eine lustige Abfrage auf dem SQL-Server, die die CPU für die letzten N Minuten anzeigt – es stellt sich heraus, dass sich Genosse Major alles merkt! Probieren Sie dieses Quiz aus:

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

Verwenden Sie nun diese Formatierung ($Fragment-Variable)

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

Wir können den Hauptteil des Briefes bilden:

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

Was so aussehen wird:

Ja, Monsieur weiß viel über Perversionen! Interessant ist, dass dieser Code Folgendes enthält: Powershell (darin geschrieben), SQL, Xquery, HTML. Es ist schade, dass wir Javascript nicht zu HTML hinzufügen können (da es zum Schreiben dient), aber das Polieren des Python-Codes (der in SQL verwendet werden kann) ist jedermanns Aufgabe!

SQL-Profiler-Trace-Ausgabe

Es ist klar, dass der Trace aufgrund des TextData-Felds nicht in die CSV-Datei passt. Aber auch die Darstellung eines Spurrasters in einem Brief ist seltsam – sowohl wegen der Größe als auch weil diese Daten oft für weitere Analysen verwendet werden. Deshalb machen wir Folgendes: Wir rufen über an invoke-SqlCmd ein bestimmtes Drehbuch, in dessen Tiefen es gemacht wird

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

Als nächstes weiter другом Auf dem für den DBA zugänglichen Server befindet sich eine Traces-Datenbank mit einer leeren Vorlage, der Modellplatte, die bereit ist, alle angegebenen Spalten aufzunehmen. Wir kopieren dieses Modell in eine neue Tabelle mit einem eindeutigen Namen:

$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

Und jetzt können wir unseren Trace mit hineinschreiben Data.SqlClient.SqlBulkCopy - Ich habe oben bereits ein Beispiel dafür gegeben. Ja, es wäre auch schön, Konstanten in TextData zu maskieren:

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

Wir ersetzen Zahlen mit mehr als einem Zeichen durch 999 und Zeichenfolgen mit mehr als einem Zeichen durch „str“. Als Flags werden oft Zahlen von 0 bis 9 verwendet, die wir nicht berühren, und auch Leer- und Einzelzeichenfolgen – „Y“, „N“ usw. – finden sich häufig darunter.

Bringen wir etwas Farbe in unser Leben (ab 18 Jahren)

In Tabellen möchten Sie häufig Zellen hervorheben, die Aufmerksamkeit erfordern. Zum Beispiel FAILS, hoher Fragmentierungsgrad usw. Dies kann natürlich in reinem SQL erfolgen, indem HTML mit PRINT generiert und der Dateityp in Jenkins auf HTML gesetzt wird:

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

Warum habe ich solchen Code geschrieben?

Aber es gibt eine schönere Lösung. ConvertTo-HTML lässt uns die Zellen nicht einfärben, aber wir können es nachträglich tun. Wir möchten beispielsweise Zellen mit einem Fragmentierungsgrad von mehr als 80 und mehr als 90 auswählen. Fügen wir Stile hinzu:

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

In der Abfrage selbst fügen wir eine Dummy-Spalte hinzu unmittelbar bevor Spalte, die wir einfärben möchten. Die Spalte sollte aufgerufen werden SQLmarkup-etwas:

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,

Nachdem wir nun den von Powershell generierten HTML-Code erhalten haben, entfernen wir die Dummy-Spalte aus der Kopfzeile und übertragen im Datenkörper den Wert aus der Spalte in den Stil. Dies geschieht mit nur zwei Ersetzungen:

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

Ergebnis:

Ist es nicht elegant? Obwohl nein, erinnert mich diese Farbgebung an etwas

Source: habr.com

Automatisierung des SQL-Servers in Jenkins: Das Ergebnis wunderbar zurückgeben