🥇Otomasi server SQL ing Jenkins: ngasilake asil kanthi apik

Maneh neruske tema atur PROD Tutul Zero ing RDS. DBA mangsa ora bakal bisa nyambung menyang server PROD langsung, nanging bakal bisa digunakake Jenkins jobs kanggo pesawat winates saka operasi. DBA mbukak proyek lan sawise sawetara wektu nampa layang karo laporan ing completion saka operasi iki. Ayo goleki cara kanggo nampilake asil kasebut menyang pangguna.

Teks Polos

Ayo dadi miwiti karo paling sepele. Cara sing sepisanan gampang banget nganti ora ana sing kudu diomongake (penulis ing ngisor iki nggunakake proyek FreeStyle):

sqlcmd nindakake soko lan kita saiki kanggo pangguna. Cocog kanggo, contone, proyek serep:

Aja lali, yen ing cadangan / pamulihan RDS ora sinkron, dadi sampeyan kudu ngenteni:

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

Cara kapindho, CSV

Kabeh ing kene uga gampang banget:

Nanging, cara iki mung bisa digunakake yen data bali ing CSV "prasaja". Yen sampeyan nyoba bali, contone, dhaptar pitakon intensif CPU TOP N kanthi cara iki, CSV bakal "corrode" amarga teks pitakon bisa ngemot karakter apa wae - koma, kuotasi, lan malah baris. Mulane, kita butuh sing luwih rumit.

Pratandha ayu ing HTML

Aku bakal menehi sampeyan potongan kode langsung

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

Miturut cara, mbayar manungsa waé kanggo baris karo System.Management.Automation.PSCustomObject, iku gaib, yen ana persis siji baris ing kothak, banjur ana sawetara masalah. Solusi kasebut dijupuk saka Internet tanpa akeh pangerten. Akibaté, sampeyan bakal entuk format output kaya iki:

Nggambar grafik

Pènget: kode kusut ing ngisor iki!
Ana pitakon lucu ing server SQL sing nampilake CPU sajrone N menit pungkasan - ternyata Comrade Major ngelingi kabeh! Coba kuis iki:

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

Saiki, nggunakake format iki ($ variabel 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>

Kita bisa mbentuk awak surat:

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

Sing bakal katon kaya iki:

Ya, Monsieur ngerti akeh babagan perversions! Iku menarik sing kode iki ngandhut: Powershell (ditulis ing), SQL, Xquery, HTML. Sayange kita ora bisa nambah Javascript kanggo HTML (amarga iku kanggo nulis), nanging polishing kode Python (sing bisa digunakake ing SQL) tugas saben wong!

SQL profiler tilak output

Cetha yen jejak kasebut ora cocog karo CSV amarga kolom TextData. Nanging nampilake kothak trace ing layang uga aneh - amarga saka ukuran lan amarga data iki asring digunakake kanggo analisis luwih. Mulane, kita nindakake ing ngisor iki: kita nelpon liwat invoke-SqlCmd script tartamtu, ing ambane kang rampung

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

Sabanjure, ing kanca Ing server diakses dening DBA, ana database Tilak karo cithakan kosong, piring Model, siap kanggo nampa kabeh kolom kasebut. Kita nyalin model iki menyang tabel anyar kanthi jeneng unik:

$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

Lan saiki kita bisa nulis tilak kita menyang nggunakake Data.SqlClient.SqlBulkCopy - Aku wis menehi conto ing ndhuwur. Ya, luwih becik kanggo mask konstanta ing TextData:

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

Kita ngganti nomer luwih saka siji karakter dawa karo 999, lan kita ngganti strings maneh saka siji karakter karo 'str'. Angka saka 0 nganti 9 asring digunakake minangka gendera, lan kita ora ndemek, uga strings kosong lan siji-karakter - 'Y', 'N', etc.

Ayo nambah warna ing urip kita (khusus 18+)

Ing tabel, sampeyan kerep pengin nyorot sel sing mbutuhake perhatian. Contone, FAILS, tingkat fragmentasi sing dhuwur, lsp. Mesthi wae, iki bisa ditindakake ing SQL kosong, ngasilake HTML nggunakake PRINT, lan nyetel jinis file menyang HTML ing 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>'

Yagene aku nulis kode kasebut?

Nanging ana solusi sing luwih apik. Ngonversi menyang HTML ora ngidini kita werna sel, nanging kita bisa nindakake iku sawise kasunyatan. Contone, kita pengin milih sel kanthi tingkat fragmentasi luwih saka 80 lan luwih saka 90. Ayo nambah gaya:

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

Ing query dhewe kita bakal nambah kolom goblok sanalika sadurunge kolom kita pengin werna. Kolom kasebut kudu diarani SQLmarkup-soko:

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,

Saiki, sawise nampa HTML sing digawe dening Powershell, kita bakal mbusak kolom goblok saka header, lan ing awak data kita bakal nransfer nilai saka kolom menyang gaya. Iki ditindakake kanthi mung rong substitusi:

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

Asil:

Apa ora elegan? Sanajan ora, pewarnaan iki ngelingake aku

Source: www.habr.com

Otomasi server SQL ing Jenkins: ngasilake asil kanthi apik