🥇Otometi le SQL server i Jenkins: toe faʻafoʻi mai iʻuga matagofie

Toe faaauauina o le autu o le faatulagaga Zero Touch PROD i lalo ole RDS. O DBA i le lumanaʻi o le a le mafai ona faʻafesoʻotaʻi saʻo i sapalai PROD, ae o le a mafai ona faʻaoga Jenkins galuega mo se seti faatapulaa o galuega. O le DBA e fa'alauiloa galuega ma ina ua mavae sina taimi e maua se tusi ma se lipoti i le mae'a o lenei ta'aloga. Se'i o tatou va'ava'ai i auala e tu'uina atu ai nei fa'ai'uga i le tagata fa'aoga.

Tusitusiga Laugatasi

Tatou amata i mea e sili ona faatauvaa. O le auala muamua e matua faigofie lava e leai se mea e talanoa ai (le tusitala o loʻo mulimuli ane faʻaaogaina galuega FreeStyle):

sqlcmd faia se mea ma matou tuʻuina atu i le tagata faʻaoga. Lelei mo, mo se faataitaiga, galuega faaleoleo:

Aua neʻi galo, i le ala, i lalo ole RDS backup/restore e le fetaui, o lea e tatau ai ona e faʻatali i ai:

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

Auala lona lua, CSV

O mea uma iinei e matua faigofie lava:

Ae ui i lea, o lenei metotia e naʻo le aoga pe a fai o faʻamatalaga toe faafoi i le CSV e "faigofie". Afai e te taumafai e toe foʻi, mo se faʻataʻitaʻiga, o se lisi o TOP N CPU fesili malosi i lenei auala, o le CSV o le a "faʻaleagaina" ona o le mea moni e mafai ona i ai i le tusitusiga fesili soʻo se mataitusi - koma, upusii, ma e oʻo lava i laina laina. O le mea lea, matou te manaʻomia se mea e sili atu ona lavelave.

Faailoga matagofie ile HTML

O le a ou avatu ia te oe se snippet code i le taimi lava lena

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

I le auala, faʻalogo i le laina ma System.Management.Automation.PSCustomObject, e faʻapitoa pe afai e tasi le laina i le faasologa, ona tulaʻi mai ai lea o ni faʻafitauli. O le fofo na aumai mai le Initaneti e aunoa ma se malamalama tele. O le iʻuga, o le ae maua le faʻatulagaina o mea e pei o lenei:

Tusia kalafi

Lapataiga: kinky code i lalo!
O loʻo i ai se fesili malie i luga o le SQL server e faʻaalia ai le PPU mo N minute mulimuli - e foliga mai e manatua e Comrade Major mea uma! Taumafai le su'ega lenei:

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

Ia, faʻaaoga lenei faʻasologa ($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>

E mafai ona tatou faia le tino o le tusi:

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

O le a foliga e pei o lenei:

Ioe, e tele mea e iloa e Monsieur e uiga i faiga faapi'opi'o! E ese le manaia o lenei tulafono e aofia ai: Powershell (tusi i totonu), SQL, Xquery, HTML. O se mea e faanoanoa ai e le mafai ona matou faʻaopoopoina le Javascript i le HTML (talu ai mo le tusitusi), ae o le faʻamalamalamaina o le Python code (lea e mafai ona faʻaaoga i SQL) o le tiute o tagata uma!

SQL profiler su'ega galuega

E manino lava o le a le fetaui le suʻega i le CSV ona o le TextData fanua. Ae o le faʻaalia o se faʻasologa faʻasologa i totonu o se tusi e ese foi - ona o le tele ma ona o nei faʻamatalaga e masani ona faʻaaogaina mo nisi auiliiliga. O le mea lea, matou te faia mea nei: matou te valaʻau e ala i valaau-SqlCmd o se tusitusiga patino, i le loloto o le mea na faia ai

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

Sosoo ai, i luga uo server, e mafai ona maua e le DBA, o loʻo i ai se faʻamaumauga o Traces ma se faʻataʻitaʻiga gaogao, o se laulau Faʻataʻitaʻiga, ua sauni e talia koluma uma ua faʻamaoti mai. Matou te kopiina lenei faʻataʻitaʻiga i se laulau fou ma se igoa tulaga ese:

$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

Ma o lea e mafai ona matou tusia a matou faʻasologa i totonu e faʻaaoga ai Data.SqlClient.SqlBulkCopy - Ua uma ona ou tuʻuina atu se faʻataʻitaʻiga o lenei mea i luga. Ioe, e manaia foi le ufiufi o mea tumau i TextData:

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

Matou te suia numera e sili atu ma le tasi le umi i le 999, ma matou suia le manoa umi atu nai lo le tasi mataitusi i le 'str'. O numera mai le 0 i le 9 e masani ona faʻaaogaina e fai ma fuʻa, ma matou te le paʻi atu ia i latou, faʻapea foʻi ma manoa gaogao ma tasi - 'Y', 'N', ma isi e masani ona maua i totonu ia i latou.

Se'i o tatou fa'aopoopoina sina lanu i o tatou olaga (fa'atoa 18+)

I laulau, e masani ona e manaʻo e faʻamaonia sela e manaʻomia le gauai. Mo se faʻataʻitaʻiga, FAILS, maualuga maualuga o vaevaega, ma isi. Ioe, e mafai ona faia lenei mea i le SQL, fatuina HTML e faʻaaoga ai LOMI, ma seti le faila faila ile HTML i 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>'

Aisea na ou tusia ai sea code?

Ae o loʻo i ai se fofo sili atu ona matagofie. Su'e I-HTML e le faatagaina i tatou e valivali sela, ae e mafai ona tatou faia pe a uma le mea moni. Mo se faʻataʻitaʻiga, matou te mananaʻo e filifili sela e iai se vaega faʻavae e sili atu i le 80 ma sili atu i le 90. Tatou faʻaopoopo sitaili:

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

I le fesili lava ia o le a matou faʻaopoopoina se koluma faʻailoga muamua atu koluma matou te fia valivali. E tatau ona valaau le koluma SQLmarkup-mea:

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,

I le taimi nei, i le mauaina o le HTML na faia e Powershell, o le a matou aveese le koluma dummy mai le ulutala, ma i totonu o le tino o faʻamatalaga o le a matou faʻafeiloaʻi le tau mai le koluma i le sitaili. E faia lenei mea i na'o le lua suiga:

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

Taunuʻuga:

E le matagofie ea? E ui lava e leai, o lenei lanu e faamanatu mai ai ia te au se mea

puna: www.habr.com

Otometi o le SQL server i Jenkins: toe faafoi matagofie le taunuuga