🥇 Sabar SQL ta atomatik a Jenkins: dawo da sakamakon da kyau

Har yanzu ci gaba da taken tsari Zero Touch PROD karkashin RDS. DBAs na gaba ba za su iya haɗawa zuwa sabar PROD kai tsaye ba, amma za su iya amfani da su Jenkins ayyuka don ƙayyadaddun tsarin ayyuka. DBA ta ƙaddamar da aiki kuma bayan ɗan lokaci ta karɓi wasiƙa tare da rahoto game da kammala wannan aiki. Bari mu kalli hanyoyin gabatar da waɗannan sakamakon ga mai amfani.

Rubutun Bayyana

Bari mu fara da mafi maras muhimmanci. Hanya ta farko tana da sauƙi don haka babu abin da za a yi magana akai (mawallafin nan yana amfani da ayyukan FreeStyle):

sqlcmd yayi wani abu kuma muna gabatar da shi ga mai amfani. Mafi dacewa ga, misali, ayyukan ajiya:

Kar ku manta, ta hanya, cewa ƙarƙashin RDS madadin / maidowa ba daidai ba ne, don haka kuna buƙatar jira shi:

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

Hanya ta biyu, CSV

Komai anan shima mai sauqi ne:

Koyaya, wannan hanyar tana aiki ne kawai idan bayanan da aka dawo a cikin CSV “mai sauƙi ne”. Idan kayi ƙoƙarin dawowa, alal misali, jerin manyan tambayoyin TOP N CPU ta wannan hanyar, CSV zata "lalata" saboda gaskiyar cewa rubutun tambaya na iya ƙunsar kowane haruffa - waƙafi, ƙididdiga, har ma da karya layi. Saboda haka, muna buƙatar wani abu mafi rikitarwa.

Kyawawan alamomi a cikin HTML

Zan ba ku snippet code nan da nan

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

Af, kula da layi tare da System.Management.Automation.PSCustomObject, yana da sihiri; idan akwai daidai layi daya a cikin grid, to, wasu matsalolin sun tashi. An dauki maganin daga Intanet ba tare da fahimta sosai ba. A sakamakon haka, za ka samu fitarwa formatted wani abu kamar haka:

Zane zane

Gargadi: kinky code a kasa!
Akwai tambaya mai ban dariya akan uwar garken SQL wanda ke nuna CPU na mintunan N na ƙarshe - ya zama cewa Comrade Major yana tuna komai! Gwada wannan tambayar:

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

Yanzu, ta amfani da wannan tsarin ($ 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>

Za mu iya samar da jikin wasiƙar:

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

Wanda zai yi kama da haka:

Ee, Monsieur ya san abubuwa da yawa game da ɓarna! Yana da ban sha'awa cewa wannan lambar ta ƙunshi: Powershell (an rubuta a ciki), SQL, Xquery, HTML. Abin takaici ne cewa ba za mu iya ƙara Javascript zuwa HTML ba (tun da yake don rubutawa), amma goge lambar Python (wanda za'a iya amfani dashi a cikin SQL) aikin kowa ne!

SQL profiler gano fitarwa

A bayyane yake cewa alamar ba za ta shiga cikin CSV ba saboda filin TextData. Amma nuna grid alama a cikin wasiƙa kuma baƙon abu ne - duka saboda girman kuma saboda ana amfani da wannan bayanan don ƙarin bincike. Saboda haka, muna yin haka: muna kira via kira-SqlCmd wani rubutun, a cikin zurfafan abin da ake yi

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

Na gaba, kan aboki A kan uwar garken da DBA ke samun dama, akwai bayanan burbushi tare da samfuri mara komai, farantin Model, a shirye don karɓar duk ƙayyadaddun ginshiƙai. Muna kwafin wannan ƙirar zuwa sabon tebur mai suna na musamman:

$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

Kuma yanzu za mu iya rubuta alamar mu ta amfani da shi Data.SqlClient.SqlBulkCopy - Na riga na ba da misalin wannan a sama. Ee, zai kuma yi kyau a rufe ma'auni a cikin TextData:

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

Muna maye gurbin lambobi sama da harafi ɗaya tsayi tare da 999, kuma muna maye gurbin kirtani fiye da harafi ɗaya da 'str'. Yawanci ana amfani da lambobi daga 0 zuwa 9 a matsayin tutoci, kuma ba ma taɓa su ba, haka nan kuma ana yawan samun igiyoyin fanko da haruffa ɗaya - 'Y', 'N', da sauransu.

Bari mu ƙara wasu launi a rayuwarmu (tsaye 18+)

A cikin tebur, sau da yawa kuna son haskaka sel waɗanda ke buƙatar kulawa. Misali, FAILS, babban matakin rarrabuwa, da sauransu. Tabbas, ana iya yin wannan a cikin SQL mara kyau, samar da HTML ta amfani da PRINT, da saita nau'in fayil ɗin zuwa HTML a 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>'

Me yasa na rubuta irin wannan lambar?

Amma akwai mafi kyawun bayani. Maida Zuwa-HTML baya barin mu canza launin sel, amma zamu iya yin hakan bayan gaskiyar. Misali, muna so mu zaɓi sel masu matakin rarrabuwa sama da 80 kuma sama da 90. Bari mu ƙara salo:

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

A cikin tambayar kanta za mu ƙara ginshiƙan guntu nan da nan kafin shafi muna so mu yi launi. Ya kamata a kira shafi SQLmarkup-wani abu:

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,

Yanzu, da samun HTML ɗin da Powershell ya samar, za mu cire ginshiƙi mai jujjuyawa daga taken, kuma a cikin jikin bayanan za mu canza darajar daga shafi zuwa salon. Ana yin wannan tare da maye biyu kawai:

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

Sakamako:

Shin ba kyakkyawa ba ne? Ko da yake a'a, wannan launi yana tunatar da ni wani abu

source: www.habr.com

Sabar SQL ta atomatik a Jenkins: dawo da sakamakon da kyau