🥇Otomatiki yeSQL server muJenkins: kudzosera mhedzisiro zvinoyevedza

Zvekare kuenderera mberi nedingindira rehurongwa Zero Bata PROD pasi pe RDS. Remangwana DBAs haakwanise kubatana kune PROD maseva zvakananga, asi achakwanisa kushandisa Jenkins mabasa ezvirongwa zvishoma. Iyo DBA inotanga basa uye mushure menguva yakati inogamuchira tsamba ine mushumo wekupedzwa kwekuvhiya uku. Ngatitarisei nzira dzekupa mhinduro idzi kumushandisi.

Rugwaro Rakajeka

Ngatitangei nezvisingakoshi. Nzira yekutanga iri nyore zvekuti hapana chekutaura nezvacho (munyori pano anoshandisa FreeStyle mabasa):

sqlcmd anoita chimwe chinhu uye tinochipa kumushandisi. Yakanakira, semuenzaniso, mabasa ekuchengetedza:

Usakanganwa, nenzira, kuti pasi peRSS backup / kudzorera haina kugadzikana, saka unofanirwa kuimirira:

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

Chechipiri nzira, CSV

Zvese pano zvakare zviri nyore kwazvo:

Nekudaro, iyi nzira inoshanda chete kana iyo data yakadzoserwa muCSV iri "nyore". Kana ukaedza kudzosa, semuenzaniso, runyorwa rwe TOP N CPU yakadzama mibvunzo nenzira iyi, iyo CSV "ichaita corrode" nekuda kwekuti chinyorwa chemubvunzo chinogona kuve nechero mavara - koma, makotesheni, uye kunyange mitsara yekutyora. Naizvozvo, tinoda chimwe chinhu chakaoma.

Zviratidzo zvakanaka muHTML

Ini ndichakupa kodhi snippet ipapo ipapo

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

Nenzira, teerera kumutsara neSystem.Management.Automation.PSCustomObject, ndeyemashiripiti; kana pane mutsara mumwe chete mugridi, ipapo mamwe matambudziko akamuka. Mhinduro yakatorwa kubva kuInternet pasina kunzwisisa kwakawanda. Nekuda kweizvozvo, iwe unowana inobuda yakafomatirwa chinhu chakadai:

Kudhirowa magirafu

Yambiro: kinky kodhi pazasi!
Pane mubvunzo unosetsa paSQL server inoratidza iyo CPU kwemaminetsi ekupedzisira N - zvinoitika kuti Comrade Major anorangarira zvese! Edza mubvunzo uyu:

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

Zvino, uchishandisa iyi fomati ($ Fragment inosiyana)

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

Tinogona kugadzira muviri wetsamba:

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

Izvo zvicharatidzika seizvi:

Hongu, Monsieur anoziva zvakawanda nezve kutsveyamisa! Zvinonakidza kuti kodhi iyi ine: Powershell (yakanyorwa mairi), SQL, Xquery, HTML. Zvine urombo kuti hatigoni kuwedzera Javascript kuHTML (sezvo iri yekunyora), asi kupukuta Python code (iyo inogona kushandiswa muSQL) ibasa remunhu wese!

SQL profiler trace output

Zviripachena kuti trace haikwane muCSV nekuda kweTextData field. Asi kuratidza gidhi rekutsvaga mutsamba zvakare zvinoshamisa - zvese nekuda kwehukuru uye nekuti iyi data inowanzo shandiswa pakuwedzera kuongororwa. Naizvozvo, tinoita zvinotevera: tinofona kuburikidza invoke-SqlCmd imwe script, mukudzika kwayo kwaiitwa

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

Tevere, on shamwari Pane sevha inowanikwa neDBA, pane dhatabhesi yeTraces ine template isina chinhu, iyo Model plate, yakagadzirira kugamuchira makoramu ese akatsanangurwa. Isu tinokopa iyi modhi kutafura nyowani ine zita rakasiyana:

$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

Uye zvino tinogona kunyora tsvakiridzo yedu mairi tichishandisa Data.SqlClient.SqlBulkCopy - Ndatopa muenzaniso weizvi pamusoro. Ehe, zvingavewo zvakanaka kuvhara maconstants muTextData:

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

Isu tinotsiva nhamba dzinopfuura mutsara mumwechete ne 999, uye tinotsiva tambo dzakareba kupfuura mutsara mumwe ne 'str'. Nhamba kubva pa0 kusvika pa9 dzinowanzo shandiswa semireza, uye hatidzibati, pamwe chete netambo dzisina chinhu uye dzine mutsara mumwe - 'Y', 'N', nezvimwe zvinowanzowanikwa pakati pawo.

Ngatiwedzerei rumwe ruvara kuhupenyu hwedu (chaizvo 18+)

Mumatafura, iwe unowanzo kuda kuratidza maseru anoda kutariswa. Semuenzaniso, FAILS, yakakwira mwero wekutsemuka, nezvimwe. Ehe, izvi zvinogona kuitwa mune isina SQL, kugadzira HTML uchishandisa PRINT, uye kuseta iyo faira mhando kuHTML muJenkins:

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

Sei ndakanyora kodhi yakadaro?

Asi pane imwe mhinduro yakanaka. ConvertTo-HTML hazvitibvumire kupendesa masero, asi isu tinogona kuzviita mushure mechokwadi. Semuyenzaniso, tinoda kusarudza maseru ane chidimbu chechikamu chinodarika makumi masere uye anopfuura makumi mapfumbamwe. Ngatiwedzere masitayera:

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

Mumubvunzo pachawo isu tichawedzera dummy column pakarepo pamberi mbiru tinoda kuisa ruvara. Iyo column inofanira kudanwa SQLmarkup-chinhu:

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,

Iye zvino, tagamuchira iyo HTML yakagadzirwa nePowershell, isu tichabvisa dummy column kubva kumusoro, uye mumuviri wedata tichaendesa kukosha kubva kukoromo kuenda kune chimiro. Izvi zvinoitwa nezvinotsiva zviviri chete:

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

Mhinduro:

Haisi kunaka here? Kunyangwe kwete, uku kupendeka kunondiyeuchidza chimwe chinhu

Source: www.habr.com

Automation yeSQL server muJenkins: kudzosera mhedzisiro zvakanaka