Automation ea seva sa SQL ho Jenkins: ho khutlisa sephetho hantle

Hape ho tsoela pele ka sehlooho sa tokisetso Zero Touch PROD tlas'a RDS. Li-DBA tsa kamoso li ke ke tsa khona ho hokela ho li-server tsa PROD ka kotloloho, empa li tla khona ho li sebelisa Jenkins mesebetsi bakeng sa sete e lekanyelitsoeng ea ts'ebetso. DBA e qala mosebetsi mme kamora nako e itseng e fumana lengolo le nang le tlaleho ea ho phetheloa ha ts'ebetso ena. Ha re shebeng mekhoa ea ho hlahisa liphetho ho mosebelisi.

Mongolo o hlakileng

Ha re qaleng ka tse sa reng letho. Mokhoa oa pele o bonolo hoo ho seng letho leo u ka buang ka lona (mongoli ka mor'a mona o sebelisa mesebetsi ea FreeStyle):

sqlcmd e etsa ntho mme re e hlahisa ho mosebedisi. E loketse, mohlala, mesebetsi ea bekapo:

Se ke oa lebala, ka tsela, hore tlas'a bekapo / ho khutlisa ea RDS ha ho bonolo, kahoo o hloka ho e emela:

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

Mokhoa oa bobeli, CSV

Ntho e 'ngoe le e' ngoe mona e bonolo haholo:

Leha ho le joalo, mokhoa ona o sebetsa feela haeba data e khutliselitsoeng ho CSV "e bonolo". Haeba u leka ho khutlisa, ho etsa mohlala, lethathamo la lipotso tse matla tsa TOP N CPU ka tsela ena, CSV e tla "senyeha" ka lebaka la hore mongolo oa potso o ka ba le litlhaku life kapa life - lifehelo, mantsoe a qotsitsoeng, esita le likheo tsa mela. Ka hona, re hloka ho hong ho rarahaneng ho feta.

Lipontšo tse ntle ka HTML

Ke tla u fa sekhechana sa khoutu hang-hang

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

Ha e le hantle, ela hloko mohala o nang le System.Management.Automation.PSCustomObject, ke ea boselamose; haeba ho na le mola o le mong ka har'a marang-rang, joale mathata a ile a hlaha. Tharollo e nkiloe Inthaneteng ntle le kutloisiso e ngata. Ka lebaka leo, u tla fumana tlhahiso e hlophisitsoeng ntho e kang ena:

Ho taka kerafo

Tlhokomeliso: khoutu ea kinky ka tlase!
Ho na le potso e qabolang ho seva sa SQL e bonts'ang CPU metsotsong e fetileng ea N - ho ile ha fumaneha hore Comrade Major o hopola tsohle! Leka potso ena:

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

Joale, u sebelisa mokhoa ona ($ 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>

Re ka theha 'mele oa lengolo:

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

E tla shebahala tjena:

Ee, Monsieur o tseba haholo ka likhothaletso! Hoa thahasellisa hore khoutu ena e na le: Powershell (e ngotsoeng ho eona), SQL, Xquery, HTML. Ke masoabi hore ebe re ke ke ra eketsa Javascript ho HTML (kaha ke ea ho ngola), empa ho pholisa khoutu ea Python (e ka sebelisoang ho SQL) ke mosebetsi oa motho e mong le e mong!

SQL profiler trace output

Ho hlakile hore trace e ke ke ea lumellana le CSV ka lebaka la tšimo ea TextData. Empa ho hlahisa marang-rang ka lengolong ho boetse hoa makatsa - ka lebaka la boholo le hobane data ena e atisa ho sebelisoa bakeng sa tlhahlobo e eketsehileng. Ka hona, re etsa tse latelang: re letsetsa ka kopa-SqlCmd mongolo o itseng, botebong ba ona o etsoang

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

E latelang, tsoela pele motsoalle Ho seva e fumanehang ke DBA, ho na le database ea Traces e nang le template e se nang letho, poleiti ea Model, e itokiselitseng ho amohela litšiea tsohle tse boletsoeng. Re kopitsa mohlala ona tafoleng e ncha e nang le lebitso le ikhethang:

$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 

'Me joale re ka ngola mohlala oa rona ho eona re sebelisa Data.SqlClient.SqlBulkCopy - Ke se ke fane ka mohlala oa sena ka holimo. Ee, ho ka ba monate ho pata li-constants ho TextData:

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

Re kenya linomoro tse fetang tlhaku e le 'ngoe ka 999, 'me re fetola lithapo tse telele ho feta tlhaku e le' ngoe ka 'str'. Linomoro ho tloha ho 0 ho isa ho 9 hangata li sebelisoa e le lifolakha, 'me ha re li ame, hammoho le likhoele tse se nang letho le tsa tlhaku e le' ngoe - 'Y', 'N', joalo-joalo hangata li fumanoa har'a tsona.

Ha re kenyeng mebala maphelong a rona (ka 18+)

Litafoleng, hangata u batla ho totobatsa lisele tse hlokang tlhokomelo. Ka mohlala, FAILS, boemo bo phahameng ba ho arohana, joalo-joalo. Ehlile, sena se ka etsoa ka SQL e se nang letho, ho hlahisa HTML ho sebelisa PRINT, le ho beha mofuta oa faele ho HTML ho 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>'

Ke hobane'ng ha ke ngotse khoutu e joalo?

Empa ho na le tharollo e ntle ho feta. ConvertTo-HTML ha e re lumelle ho kenya mebala liseleng, empa re ka e etsa kamora 'nete. Ka mohlala, re batla ho khetha lisele tse nang le boemo ba ho arohana ho feta 80 le ho feta 90. Ha re kenye mekhoa:

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

Potsong ka boeona re tla eketsa kholomo ea dummy hang pele kholomo eo re batlang ho e mebala. Kholomo e lokela ho bitsoa SQLmarkup-ntho e itseng:

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, 

Hona joale, ha re se re fumane HTML e hlahisoang ke Powershell, re tla tlosa dummy column ho tloha hloohong, 'me' meleng oa data re tla fetisetsa boleng ho tloha kholomong ho ea ho setaele. Sena se etsoa ka likarolo tse peli feela:

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

Sephetho:


Na ha se bokhabane? Le hoja che, 'mala ona o nkhopotsa ho hong


Source: www.habr.com