Automation ntawm SQL neeg rau zaub mov hauv Jenkins: rov qab cov txiaj ntsig zoo nkauj

Ib zaug ntxiv txuas ntxiv lub ntsiab ntawm kev npaj Zero Kov PROD nyob rau hauv RDS. Yav tom ntej DBAs yuav tsis tuaj yeem txuas rau PROD servers ncaj qha, tab sis yuav siv tau Jenkins ua haujlwm rau ib qho kev ua haujlwm tsawg. DBA pib ua haujlwm thiab tom qab qee lub sijhawm tau txais tsab ntawv nrog tsab ntawv ceeb toom txog kev ua tiav ntawm txoj haujlwm no. Cia peb saib txoj hauv kev los nthuav tawm cov txiaj ntsig no rau tus neeg siv.

Lub tiaj ntawv nyeem

Cia peb pib nrog qhov tsis tseem ceeb tshaj plaws. Thawj txoj hauv kev yog qhov yooj yim heev uas tsis muaj dab tsi los tham txog (tus sau no tom qab siv FreeStyle txoj haujlwm):

sqlcmd ua ua ib yam dab tsi thiab peb nthuav qhia rau tus neeg siv. Zoo tagnrho rau, piv txwv li, cov haujlwm thaub qab:

Tsis txhob hnov ​​​​qab, los ntawm txoj kev, uas nyob rau hauv RDS thaub qab / rov qab yog asynchronous, yog li koj yuav tsum tau tos rau nws:

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

Thib ob, CSV

Txhua yam ntawm no kuj yooj yim heev:

Txawm li cas los xij, txoj kev no tsuas yog ua haujlwm yog tias cov ntaub ntawv xa rov qab hauv CSV yog "yooj yim". Yog tias koj sim rov qab, piv txwv li, ib daim ntawv teev npe ntawm TOP N CPU cov lus nug hnyav nyob rau hauv txoj kev no, CSV yuav "corrode" vim qhov tseeb tias cov lus nug tuaj yeem muaj cov cim - commas, quotes, thiab txawm tias kab tawg. Yog li ntawd, peb xav tau ib yam dab tsi nyuab dua.

Cov cim zoo nkauj hauv HTML

Kuv yuav muab ib tug code snippet rau koj tam sim ntawd

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

Los ntawm txoj kev, xyuam xim rau txoj kab nrog System.Management.Automation.PSCustomObject, nws yog tej yam yees siv; yog hais tias muaj raws nraim ib kab nyob rau hauv daim phiaj, ces muaj ib co teeb meem tshwm sim. Qhov kev daws teeb meem tau muab los ntawm Is Taws Nem tsis muaj kev nkag siab ntau. Yog li ntawd, koj yuav tau txais cov zis formatted tej yam zoo li no:

Daim duab kos duab

Ceeb toom: kinky code hauv qab no!
Muaj lus nug lom zem ntawm SQL neeg rau zaub mov uas qhia CPU rau N feeb kawg - nws hloov tawm tias Comrade Major nco ntsoov txhua yam! Sim cov lus nug no:

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

Tam sim no, siv qhov formatting ($ Fragment kuj sib txawv)

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

Peb tuaj yeem tsim lub cev ntawm tsab ntawv:

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

Uas yuav zoo li no:

Yog lawm, Monsieur paub ntau yam txog kev hloov pauv! Nws yog qhov nthuav tias cov cai no muaj: Powershell (sau hauv nws), SQL, Xquery, HTML. Nws yog kev khuv leej uas peb tsis tuaj yeem ntxiv Javascript rau HTML (vim nws yog rau kev sau ntawv), tab sis polishing Python code (uas tuaj yeem siv hauv SQL) yog txhua tus lub luag haujlwm!

SQL profiler taug qab tso zis

Nws yog qhov tseeb tias cov kab yuav tsis haum rau CSV vim yog TextData teb. Tab sis kev nthuav qhia kab kab sib chaws hauv ib tsab ntawv kuj coj txawv txawv - ob qho tib si vim qhov loj thiab vim tias cov ntaub ntawv no feem ntau siv rau kev tshuaj xyuas ntxiv. Yog li ntawd, peb ua cov hauv qab no: peb hu ntawm npe-SqlCmd ib tsab ntawv, nyob rau hauv qhov tob uas nws yog ua

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

Tom ntej no, ntawm phooj ywg Ntawm cov neeg rau zaub mov nkag mus tau los ntawm DBA, muaj cov ntaub ntawv Traces nrog cov qauv khoob, cov qauv phaj, npaj txhij lees txais tag nrho cov kab ntawv teev tseg. Peb luam cov qauv no rau lub rooj tshiab nrog lub npe tshwj xeeb:

$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 

Thiab tam sim no peb tuaj yeem sau peb cov kab hauv nws siv Data.SqlClient.SqlBulkCopy - Kuv twb muab piv txwv li saum toj no. Yog lawm, nws kuj yuav zoo rau npog qhov tsis tu ncua hauv TextData:

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

Peb hloov cov lej ntau dua ib tus cim ntev nrog 999, thiab peb hloov cov hlua ntev dua ib tus cim nrog 'str'. Tus lej ntawm 0 txog 9 feem ntau yog siv los ua tus chij, thiab peb tsis txhob kov lawv, nrog rau cov hlua khoob thiab ib tus cim - 'Y', 'N', thiab lwm yam. feem ntau pom ntawm lawv.

Cia peb ntxiv ib co xim rau peb lub neej (tseem ceeb 18+)

Hauv cov rooj, koj feem ntau xav qhia cov hlwb uas xav tau kev saib xyuas. Piv txwv li, FAILS, qib siab ntawm fragmentation, thiab lwm yam. Tau kawg, qhov no tuaj yeem ua tiav hauv SQL liab qab, tsim HTML siv PRINT, thiab teeb tsa hom ntaub ntawv rau HTML hauv 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>'

Vim li cas kuv thiaj sau li no?

Tab sis muaj kev daws teeb meem zoo nkauj dua. Konvertiere HML tsis cia peb xim lub hlwb, tab sis peb tuaj yeem ua nws tom qab qhov tseeb. Piv txwv li, peb xav xaiv cov cell nrog ib theem fragmentation ntau tshaj 80 thiab ntau tshaj 90. Cia peb ntxiv cov qauv:

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

Hauv cov lus nug nws tus kheej peb yuav ntxiv ib kab dummy tam sim ua ntej kem peb xav xim. Pawg yuav tsum hu ua SQLmarkup-ib yam dab tsi:

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, 

Tam sim no, tau txais HTML tsim los ntawm Powershell, peb yuav tshem tawm cov kab dummy los ntawm header, thiab hauv lub cev ntawm cov ntaub ntawv peb yuav hloov tus nqi ntawm kem mus rau qhov style. Qhov no tsuas yog ua nrog ob qho kev hloov pauv:

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

Tshwm sim:


Tsis yog nws elegant? Txawm hais tias tsis yog, qhov xim no ua rau kuv nco txog qee yam


Tau qhov twg los: www.hab.com