Automation of SQL server in Jenkins: u soo celinta natiijada si qurux badan

Haddana sii wadida dulucda habaynta Zero Touch PROD hoos RDS. DBA-yada mustaqbalka ma awoodi doonaan inay si toos ah ugu xidhmaan adeegayaasha PROD, laakiin waxay awoodi doonaan inay isticmaalaan Jenkins Shaqooyin loogu talagalay hawlo xaddidan. DBA waxay bilawday shaqada oo muddo kadib waxay helaysaa warqad ay ku qoran tahay warbixin ku saabsan dhamaystirka hawlgalkan. Aynu eegno siyaabaha loo soo bandhigo natiijooyinkan isticmaalaha.

Qoraal cad

Aan ku bilowno inta ugu yar. Habka ugu horreeya waa mid aad u fudud oo runtii ma jiraan wax laga hadlo (qoraagu hadda dabadeed wuxuu adeegsadaa shaqooyinka FreeStyle):

sqlcmd wax uu qabto waxaanu u soo bandhigaynaa isticmaalaha. Ku habboon, tusaale ahaan, shaqooyinka kaydinta:

Ha iloobin, habka, in hoosta RDS kaabaynta / soo celinta ay tahay mid aan caadi ahayn, markaa waxaad u baahan tahay inaad sugto:

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

Habka labaad, CSV

Wax kasta oo halkan sidoo kale waa mid aad u fudud:

Si kastaba ha ahaatee, habkani wuxuu shaqeeyaa kaliya haddii xogta lagu soo celiyay CSV ay tahay "fudud". Haddii aad isku daydo inaad soo noqoto, tusaale ahaan, liiska TOP N CPU su'aalaha degdega ah ee habkan, CSV-gu wuu "dami doonaa" sababtoo ah qoraalka weydiinta wuxuu ka koobnaan karaa xarfo kasta - hakad, xigashooyin, iyo xitaa jebinta khadka. Sidaa darteed, waxaan u baahanahay wax ka sii adag.

Calaamadaha quruxda badan ee HTML

Waxaan ku siin doonaa gabal kood isla markaaba

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

Jid ahaan, fiiro gaar ah u yeelo xariiqda System.Management.Automation.PSCustomObject, waa sixir; haddii ay jirto hal xariiq oo sax ah oo ka mid ah shabakadaha, markaa dhibaatooyinka qaarkood ayaa kacay. Xalka waxaa laga qaatay Internetka iyadoon wax badan la fahmin. Natiijo ahaan, waxaad heli doontaa wax soo saarka qaabaysan wax sidan oo kale ah:

garaafyada sawirida

Digniin: code kinky hoos!
Waxaa jira su'aal qosol leh oo ku saabsan server-ka SQL oo soo bandhigaya CPU daqiiqadihii N ee ugu dambeeyay - waxaa soo baxday in Jaalle Major uu xasuusto wax walba! Isku day su'aalahan:

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

Hadda, addoo isticmaalaya qaabayntan ($ 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>

Waxaan samayn karnaa jirka warqadda:

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

Kaas oo u ekaan doona sidan:

Haa, Monsieur wax badan ayuu ka og yahay qalloocyada! Waxaa xiiso leh in koodhkani ka kooban yahay: Powershell (ku qoran), SQL, Xquery, HTML. Waa wax laga xumaado in aanan ku dari karin Javascript HTML (maaddaama ay tahay qoraal), laakiin nadiifinta code Python (kaas oo loo isticmaali karo SQL) waa qof kasta waajibaadkiisa!

Soo saarista raadraaca profiler SQL

Way caddahay in raadku aanu ku haboonayn CSV sababtoo ah goobta TextData. Laakin soo bandhigida shabkada raadraaca xarafka sidoo kale waa yaab - labadaba sababtoo ah cabbirka iyo sababtoo ah xogtan waxaa badanaa loo isticmaalaa falanqayn dheeraad ah. Sidaa darteed, waxaan samaynaa kuwan soo socda: waxaan ku wacnaa via wac-SqlCmd qoraal gaar ah, oo qoto dheer oo la sameeyo

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

Marka xigta, on saaxiib Server-ka ay heli karto DBA, waxa jira kayd xog raadraac ah oo leh qaab madhan, saxan Model, oo diyaar u ah in uu aqbalo dhammaan tiirarka la cayimay. Qaabkan waxaanu ku koobiyaynnaa miis cusub oo magac gaar ah leh:

$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 

Oo hadda waxaan ku qori karnaa raadkeenna annaga oo adeegsanayna Xogta.SqlClient.SqlBulkKoobi - Mar hore ayaan tusaale u soo qaatay tan sare. Haa, sidoo kale way fiicnaan lahayd in lagu qariyo waxyaabaha joogtada ah ee TextData:

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

Waxaan ku bedelnaa tiro ka badan hal xaraf oo dheer 999, waxaana ku bedelnaa xargaha in ka badan hal xaraf 'str'. Nambarada 0 ilaa 9 ayaa inta badan loo isticmaalaa calan ahaan, mana taaban iyaga, sidoo kale xargaha madhan iyo hal xaraf - 'Y', 'N', iwm ayaa inta badan laga helaa iyaga.

Aan ku darno midab nolosheena (si adag 18+)

Miisaska, waxaad inta badan dooneysaa inaad muujiso unugyada u baahan feejignaan. Tusaale ahaan, FASHILAN, heer sare oo kala qaybsanaan, iwm. Dabcan, tan waxaa lagu samayn karaa SQL qaawan, abuurista HTML iyadoo la adeegsanayo PRINT, iyo dejinta nooca faylka HTML ee 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>'

Maxaan u qoray koodkaas?

Laakiin waxaa jira xal aad u qurux badan. U beddelo-HTML ma ogola in aan midabeyno unugyada, laakiin waxaan sameyn karnaa xaqiiqda ka dib. Tusaale ahaan, waxaan rabnaa in aan dooranno unugyo leh heer jajab oo ka badan 80 iyo in ka badan 90. Aan ku darno qaababka:

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

Weydiinta lafteeda waxaan ku dari doonaa tiir khariidad ah isla markiiba ka hor tiirka waxaan rabnaa inaan midabeyno. Tiirka waa in loo yeeraa SQLmarkup-wax:

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, 

Hadda, markii aan helnay HTML-ka ay soo saartay Powershell, waxaan ka saari doonaa tiirarka dummy ee madaxa, iyo jirka xogta waxaan ka wareejin doonaa qiimaha tiirka ilaa qaabka. Tan waxa lagu sameeyaa laba beddel oo keliya:

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

Natiijada:


Miyaanay qurux ahayn? Inkastoo maya, midabayntani waxay i xasuusinaysaa wax


Source: www.habr.com