Jenkinsda SQL serverini avtomatlashtirish: natijani chiroyli tarzda qaytarish

Yana tartibga solish mavzusini davom ettirish Zero Touch PROD RDS ostida. Kelajakdagi DBA'lar to'g'ridan-to'g'ri PROD serverlariga ulana olmaydi, lekin ulardan foydalanishi mumkin Jenkins cheklangan operatsiyalar to'plami uchun ish o'rinlari. DBA ishni boshlaydi va bir muncha vaqt o'tgach, ushbu operatsiyani bajarish to'g'risida hisobot bilan xat oladi. Keling, ushbu natijalarni foydalanuvchiga taqdim etish usullarini ko'rib chiqaylik.

Oddiy matn

Keling, eng ahamiyatsizidan boshlaylik. Birinchi usul shunchalik soddaki, haqiqatan ham gaplashadigan hech narsa yo'q (muallif bundan keyin FreeStyle ishlaridan foydalanadi):

sqlcmd biror narsa qiladi va biz uni foydalanuvchiga taqdim etamiz. Masalan, zaxira ishlari uchun ideal:

Aytgancha, RDS ostida zaxiralash/tiklash asinxron ekanligini unutmang, shuning uchun uni kutishingiz kerak:

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

Ikkinchi usul, CSV

Bu erda hamma narsa juda oddiy:

Biroq, bu usul faqat CSVda qaytarilgan ma'lumotlar "oddiy" bo'lsa ishlaydi. Agar siz, masalan, TOP N CPU intensiv so'rovlari ro'yxatini shu tarzda qaytarishga harakat qilsangiz, so'rov matnida har qanday belgilar - vergullar, tirnoqlar va hatto qatorlar bo'lishi mumkinligi sababli CSV "korroziyaga uchraydi". Shuning uchun bizga murakkabroq narsa kerak.

HTMLda chiroyli belgilar

Men sizga darhol kod parchasini beraman

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

Aytgancha, System.Management.Automation.PSCustomObject bilan chiziqqa e'tibor bering, bu sehrli, agar panjarada aniq bitta chiziq bo'lsa, unda ba'zi muammolar paydo bo'ldi. Yechim Internetdan ko'p tushunmasdan olingan. Natijada siz quyidagi kabi formatlangan chiqishni olasiz:

Grafiklarni chizish

Ogohlantirish: pastda noaniq kod!
SQL serverida so'nggi N daqiqada protsessorni ko'rsatadigan kulgili so'rov bor - ma'lum bo'lishicha, o'rtoq mayor hamma narsani eslaydi! Ushbu testni sinab ko'ring:

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

Endi ushbu formatlashdan foydalanish ($Fragment o'zgaruvchisi)

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

Biz xatning asosiy qismini shakllantirishimiz mumkin:

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

Bu shunday ko'rinadi:

Ha, janob buzuqlik haqida ko'p narsani biladi! Qizig'i shundaki, ushbu kod quyidagilarni o'z ichiga oladi: Powershell (unda yozilgan), SQL, Xquery, HTML. Afsuski, biz Javascriptni HTML-ga qo'sha olmaymiz (chunki u yozish uchun), lekin Python kodini (SQL-da ishlatilishi mumkin) jilolash hammaning burchidir!

SQL profili izi chiqishi

TextData maydoni tufayli iz CSV ga mos kelmasligi aniq. Ammo xatda iz panjarasini ko'rsatish ham g'alati - ham kattaligi tufayli, ham bu ma'lumotlar ko'pincha keyingi tahlil qilish uchun ishlatiladi. Shuning uchun biz quyidagilarni qilamiz: orqali qo'ng'iroq qilamiz invoke-SqlCmd ma'lum bir skript, uning chuqurligida amalga oshiriladi

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

Keyingi, yoniq другом DBA tomonidan kirish mumkin bo'lgan serverda barcha belgilangan ustunlarni qabul qilishga tayyor bo'sh shablonga ega bo'lgan Traces ma'lumotlar bazasi, Model plitasi mavjud. Biz ushbu modelni noyob nomga ega yangi jadvalga nusxalaymiz:

$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 

Va endi biz uning yordamida o'z izimizni yozishimiz mumkin Data.SqlClient.SqlBulkCopy - Yuqorida bunga misol keltirdim. Ha, TextData-da konstantalarni maskalash ham yaxshi bo'lar edi:

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

Biz bir belgidan ortiq uzunlikdagi raqamlarni 999 bilan almashtiramiz va bir belgidan uzunroq satrlarni “str” bilan almashtiramiz. Ko'pincha bayroq sifatida 0 dan 9 gacha raqamlar ishlatiladi va biz ularga tegmaymiz, shuningdek, bo'sh va bir belgidan iborat qatorlar - 'Y', 'N' va boshqalar ko'pincha ular orasida uchraydi.

Keling, hayotimizga biroz rang qo'shaylik (qat'iy 18+)

Jadvallarda siz ko'pincha e'tibor talab qiladigan hujayralarni ajratib ko'rsatishni xohlaysiz. Masalan, FAILS, yuqori darajadagi parchalanish va boshqalar. Albatta, buni yalang'och SQL-da, PRINT yordamida HTML yaratish va Jenkins-da fayl turini HTML-ga o'rnatish mumkin:

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

Nega bunday kod yozdim?

Ammo yanada chiroyli yechim bor. HTML-ga aylantirish bizga hujayralarni rang berishga ruxsat bermaydi, lekin biz buni haqiqatdan keyin qila olamiz. Masalan, biz parchalanish darajasi 80 dan va 90 dan ortiq bo'lgan hujayralarni tanlamoqchimiz. Uslublarni qo'shamiz:

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

So'rovning o'zida biz qo'g'irchoq ustun qo'shamiz darhol oldin biz rang berishni xohlaymiz ustun. Ustun chaqirilishi kerak SQLmarkup-nimadur:

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, 

Endi Powershell tomonidan yaratilgan HTML-ni olganimizdan so'ng, biz sarlavhadan qo'g'irchoq ustunni olib tashlaymiz va ma'lumotlarning tanasida qiymatni ustundan uslubga o'tkazamiz. Bu faqat ikkita almashtirish bilan amalga oshiriladi:

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

natija:


Bu oqlangan emasmi? Yo'q bo'lsa-da, bu rang menga nimanidir eslatadi


Manba: www.habr.com