🥇Jenkins-də SQL serverinin avtomatlaşdırılması: nəticəni gözəl şəkildə qaytarmaq

Yenə də aranjiman mövzusunu davam etdirir Zero Touch PROD RDS altında. Gələcək DBA-lar birbaşa PROD serverlərinə qoşula bilməyəcək, lakin istifadə edə biləcəklər Jenkins məhdud əməliyyatlar dəsti üçün iş. DBA işə başlayır və bir müddət sonra bu əməliyyatın başa çatması haqqında hesabatı olan məktub alır. Gəlin bu nəticələri istifadəçiyə təqdim etməyin yollarına baxaq.

Düz görünüş

Ən mənasız olandan başlayaq. Birinci üsul o qədər sadədir ki, haqqında danışmaq üçün heç bir şey yoxdur (müəllif bundan sonra FreeStyle işlərindən istifadə edir):

sqlcmd bir şey edir və biz onu istifadəçiyə təqdim edirik. Məsələn, ehtiyat işləri üçün idealdır:

Yeri gəlmişkən, unutmayın ki, RDS ehtiyat nüsxəsi/bərpa altında asinxrondur, ona görə də bunu gözləmək lazımdır:

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

İkinci üsul, CSV

Burada da hər şey çox sadədir:

Bununla belə, bu üsul yalnız CSV-də qaytarılan məlumatlar "sadə" olduqda işləyir. Məsələn, TOP N CPU intensiv sorğularının siyahısını bu şəkildə qaytarmağa çalışsanız, sorğu mətnində hər hansı simvol - vergüllər, dırnaqlar və hətta sətir fasilələri ola biləcəyi üçün CSV "korroziyaya uğrayacaq". Ona görə də bizə daha mürəkkəb bir şey lazımdır.

HTML-də gözəl işarələr

Mən sizə dərhal kod parçasını verəcəyəm

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

Yeri gəlmişkən, System.Management.Automation.PSCustomObject ilə xəttə diqqət yetirin, bu sehrlidir, əgər şəbəkədə tam olaraq bir xətt varsa, onda bəzi problemlər yaranıb. Həll çox anlamadan internetdən götürülüb. Nəticədə belə bir formatda çıxış əldə edəcəksiniz:

Qrafiklərin çəkilməsi

Xəbərdarlıq: aşağıda kinky kod!
SQL serverində son N dəqiqə ərzində CPU-nu göstərən gülməli sorğu var - belə çıxır ki, yoldaş mayor hər şeyi xatırlayır! Bu testi sınayın:

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

İndi bu formatdan istifadə edərək ($Fragment dəyişəni)

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

Məktubun gövdəsini yarada bilərik:

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

Hansı belə görünəcək:

Bəli, müsyö pozğunluqlar haqqında çox şey bilir! Maraqlıdır ki, bu kodda aşağıdakılar var: Powershell (içində yazılmışdır), SQL, Xquery, HTML. Təəssüf ki, biz Javascript-i HTML-yə əlavə edə bilmirik (çünki bu yazı üçün), lakin Python kodunu (SQL-də istifadə edilə bilər) cilalamaq hər kəsin borcudur!

SQL profili izi çıxışı

TextData sahəsinə görə izin CSV-yə sığmayacağı aydındır. Ancaq bir məktubda bir iz şəbəkəsinin göstərilməsi də qəribədir - həm ölçüsünə görə, həm də bu məlumatlar daha çox təhlil üçün istifadə edildiyi üçün. Buna görə də, biz aşağıdakıları edirik: vasitəsilə zəng edirik invoke-SqlCmd müəyyən bir ssenari, onun dərinliklərində edilir

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

Sonra, davam другом DBA tərəfindən əlçatan olan serverdə boş şablonu olan Traces verilənlər bazası var, Model lövhəsi, göstərilən bütün sütunları qəbul etməyə hazırdır. Bu modeli unikal adla yeni cədvələ kopyalayırıq:

$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

İndi biz onu istifadə edərək izimizi yaza bilərik Data.SqlClient.SqlBulkCopy - Mən yuxarıda buna misal çəkdim. Bəli, TextData-da sabitləri maskalamaq da yaxşı olardı:

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

Bir simvoldan çox olan nömrələri 999 ilə, bir simvoldan uzun olan sətirləri isə "str" ilə əvəz edirik. Çox vaxt bayraq kimi 0-dan 9-a qədər olan rəqəmlərdən istifadə olunur və biz onlara toxunmuruq, həmçinin boş və bir simvollu sətirlərə - 'Y', 'N' və s.

Gəlin həyatımıza bir az rəng qataq (ciddi olaraq 18+)

Cədvəllərdə tez-tez diqqət tələb edən hüceyrələri vurğulamaq istəyirsiniz. Məsələn, FAILS, yüksək səviyyədə parçalanma və s. Əlbəttə ki, bu, çılpaq SQL-də edilə bilər, PRINT istifadə edərək HTML yaratmaq və Jenkins-də fayl növünü HTML-yə təyin etmək:

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

Niyə belə kodu yazdım?

Ancaq daha gözəl bir həll var. HTML-ə çevirin hüceyrələri rəngləməyimizə imkan vermir, amma faktdan sonra bunu edə bilərik. Məsələn, biz fraqmentasiya səviyyəsi 80-dən və 90-dan çox olan xanaları seçmək istəyirik. Gəlin üslublar əlavə edək:

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

Sorğunun özündə biz dummy sütun əlavə edəcəyik dərhal əvvəl rəngləmək istədiyimiz sütun. Sütun çağırılmalıdır SQLmarkup-bir şey:

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,

İndi Powershell tərəfindən yaradılan HTML-ni aldıqdan sonra biz dummy sütununu başlıqdan çıxaracağıq və verilənlərin gövdəsində dəyəri sütundan üsluba köçürəcəyik. Bu, yalnız iki əvəzetmə ilə edilir:

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

Nəticə:

Qəşəng deyilmi? Yox olsa da, bu rəngləmə mənə nəyisə xatırladır

Mənbə: www.habr.com

Jenkins-də SQL serverinin avtomatlaşdırılması: nəticəni gözəl qaytarmaq