🥇Automatisierung des SQL-Servers in Jenkins: Ergebnisse ansprechend zurückgeben

Wieder weiter zum Thema der Einrichtung Zero Touch PROD unter RDS. Zukünftige DBAs werden keinen direkten Zugriff auf PROD-Server haben, sondern können Jenkins Jobs für einen eingeschränkten Satz von Operationen nutzen. Der DBA startet einen Job und erhält nach einiger Zeit eine E-Mail mit einem Bericht über die Durchführung dieser Operation. Lassen Sie uns die Möglichkeiten zur Präsentation dieser Ergebnisse an den Benutzer untersuchen.

Reiner Text

Beginnen wir mit dem banalsten. Die erste Methode ist so einfach, dass es wenig zu sagen gibt (der Autor verwendet hier und später FreeStyle-Jobs):

sqlcmd führt etwas aus, und wir präsentieren dies dem Benutzer. Ideal geeignet für beispielsweise Backup-Jobs:

Vergessen Sie nicht, dass unter RDS Backup/Wiederherstellung asynchron ist, daher sollten wir 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} wird durch den Datenbanknamen von PowerShell ersetzt
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Die zweite Methode, CSV

Hier ist alles auch sehr einfach:

Diese Methode funktioniert jedoch nur, wenn die im CSV zurückgegebenen Daten "einfach" sind. Wenn Sie versuchen, beispielsweise eine Liste der CPU-intensivsten Abfragen zurückzugeben, wird das CSV-Format aufgrund von Zeichen wie Kommas, Anführungszeichen und sogar Zeilenumbrüchen beschädigt. Daher benötigen wir eine ausgeklügeltere Lösung.

Schöne Tabellen in HTML

Hier ist gleich ein 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>Meine Tabelle</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Keine Daten</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

Übrigens, beachten Sie die Zeile mit System.Management.Automation.PSCustomObject. Sie ist magisch. Wenn im Grid genau eine Zeile vorhanden ist, kann es zu Problemen kommen. Die Lösung wurde aus dem Internet übernommen, ohne viel darüber nachzudenken. Das Ergebnis wird ungefähr so aussehen:

Wir zeichnen Grafiken

Achtung: Der nachfolgende Code ist verrückt!
Es gibt eine lustige Abfrage auf dem SQL-Server, die die CPU-Leistung der letzten N Minuten anzeigt — es stellt sich heraus, dass der Kollege alles speichert! Probieren Sie diese Abfrage aus:

DEKLARIEREN Sie @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'%%') AS x) AS y 
ORDER BY 1 DESC OPTION (RECOMPILE);

Jetzt verwenden wir dieses 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>

Wir können den Inhalt der E-Mail erstellen:

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

Das wird so aussehen:

Ja, Monsieur weiß, worum es geht! Interessant ist, dass dieser Code Folgendes enthält: Powershell (darauf basiert er), SQL, Xquery, HTML. Es ist schade, dass wir zu HTML kein Javascript hinzufügen können (da dies für E-Mails gedacht ist), aber den Python-Code (den man in SQL verwenden kann) zu optimieren, ist unser aller Pflicht!

Ausgabe des SQL Profiler Traces

Es ist klar, dass der Trace aufgrund des Feldes TextData nicht in eine CSV-Datei eingegeben werden kann. Aber die Ausgabe des Traces in einer E-Mail ist auch seltsam – sowohl wegen der Größe als auch weil diese Daten oft für weitergehende Analysen verwendet werden. Daher machen wir Folgendes: wir rufen über invoke-SqlCmd ein Skript auf, in dessen Tiefen Folgendes gemacht wird:

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

Daraufhin gibt es auf einem Freund gesprochen Server, auf das der DBA Zugriff hat, eine Datenbank Traces mit einem leeren Template, einer Tabelle Modell, die bereit ist, alle angegebenen Spalten aufzunehmen. Wir kopieren dieses Modell in eine neue Tabelle mit einem einzigartigen 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 Hilfe von Data.SqlClient.SqlBulkCopy — Ein Beispiel dafür habe ich bereits oben genannt. Ja, es wäre auch gut, die 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, während wir längere Zeichenfolgen durch 'str' ersetzen. Zahlen von 0 bis 9 werden oft als Flags verwendet und bleiben unberührt, ebenso wie leere und einzeilige Zeichenfolgen – darunter finden sich häufig 'Y', 'N' usw.

Lasst uns Farbe in unser Leben bringen (nur für 18+)

In Tabellen möchte man häufig Zellen hervorheben, die Aufmerksamkeit erfordern. Zum Beispiel FAILS, hohes Fragmentierungsniveau usw. Natürlich kann man dies auch mit einfachem SQL tun, indem man HTML mit PRINT generiert und in Jenkins den Dateityp auf HTML setzt:

declare @body varchar(max), @chunk varchar(max)
set @body='<font face="Lucida Console" size="3">'
set @body=@body+'<b>Servername: '+@@servername+'</b><br>'
set @body=@body+'<br><br>'
set @body=@body+'<table><tr><th>Auftrag</th><th>Letzte Ausführung</th><th>Durchschnittliche Dauer, Sek.</th><th>Letzte Ausführung, Sek.</th><th>Letzter 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<>'Erfolgreich' then '<font color="red">' else '' end+
      LastStatus+
      case when LastStatus<>'Erfolgreich' 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 diesen Code geschrieben?

Es gibt jedoch eine schönere Lösung. ConvertTo-HTML gibt uns keine Möglichkeit, die Zellen zu färben, aber wir können das nachträglich tun. Zum Beispiel wollen wir Zellen mit einer Fragmentierung von mehr als 80 und mehr als 90 hervorheben. Fügen wir Stile hinzu:

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

In der Abfrage fügen wir eine fiktive Spalte hinzu direkt vor der Spalte, die wir einfärben möchten. Die Spalte sollte den Namen SQLmarkup-etwas haben:

Fall  
  wenn ps.avg_fragmentation_in_percent >= 90,0 dann 'SQLmarkup-rot'
  wenn ps.avg_fragmentation_in_percent >= 80,0 dann 'SQLmarkup-gelb'
  sonst 'SQLmarkup-standard' 
  Ende als [SQLmarkup-1], 
ps.avg_fragmentation_in_percent,

Nun, nachdem wir das mit Powershell erzeugte HTML erhalten haben, werden wir die fiktive Spalte aus dem Header entfernen und den Wert aus der Spalte in den Stil des Datenkörpers übertragen. Das geschieht mit nur zwei Platzhaltern:

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

Das Ergebnis:

Ist das nicht elegant? Allerdings erinnert mich diese Farbgebung an etwas.

Quelle: habr.com

Automatisierung von SQL-Server in Jenkins: So präsentieren Sie das Ergebnis ansprechend