การทำงานกับ MS SQL จาก Powershell บน Linux

บทความนี้ใช้ได้จริงและเน้นไปที่เรื่องราวเศร้าของฉันโดยเฉพาะ

เตรียมพร้อมสำหรับ ผลิตภัณฑ์ Zero Touch สำหรับ RDS (MS SQL) ซึ่งทุกคนต่างพากันฮือฮา ฉันได้สร้างการนำเสนอ (POC - Proof Of Concept) ของระบบอัตโนมัติ: ชุดสคริปต์ PowerShell หลังจากการนำเสนอเมื่อเสียงปรบมือที่รุนแรงและยาวนานลดลงและกลายเป็นเสียงปรบมือไม่หยุดหย่อนพวกเขาบอกฉัน - ทั้งหมดนี้เป็นสิ่งที่ดี แต่ด้วยเหตุผลทางอุดมการณ์เท่านั้น ทาสเจนกินส์ของเราทั้งหมดทำงานบน Linux!

เป็นไปได้ไหม? นำ DBA หลอดไฟที่อบอุ่นจากใต้ Windows มาติดไว้ใน PowerShell ที่ร้อนแรงภายใต้ Linux หรือไม่? มันไม่โหดร้ายเหรอ?

ฉันต้องดำดิ่งลงไปในการผสมผสานเทคโนโลยีที่แปลกประหลาดนี้ แน่นอนว่าสคริปต์มากกว่า 30 รายการของฉันทั้งหมดหยุดทำงาน ฉันประหลาดใจมากที่สามารถแก้ไขปัญหาทุกอย่างได้ภายในหนึ่งวันทำการ ฉันกำลังเขียนตามอย่างร้อนแรง ดังนั้นคุณจะพบข้อผิดพลาดอะไรบ้างเมื่อถ่ายโอนสคริปต์ PowerShell จาก Windows ไปยัง Linux

sqlcmd กับเรียกใช้-SqlCmd

ฉันขอเตือนคุณถึงความแตกต่างที่สำคัญระหว่างพวกเขา ยูทิลิตี้เก่าที่ดี ตร.ซม นอกจากนี้ยังใช้งานได้บน Linux ด้วยฟังก์ชันการทำงานที่เกือบจะเหมือนกัน เราส่งข้อความค้นหาเพื่อดำเนินการ -Q ไฟล์อินพุตเป็น -i และเอาต์พุตเป็น -o แต่แน่นอนว่าชื่อไฟล์จะต้องคำนึงถึงขนาดตัวพิมพ์ หากคุณใช้ -i ดังนั้นในไฟล์จะเขียนต่อท้าย:

GO
EXIT

หากไม่มีทางออกในตอนท้าย sqlcmd จะดำเนินการรออินพุตต่อไป และหากก่อนหน้านั้น EXIT จะไม่ GOจากนั้นคำสั่งสุดท้ายจะไม่ทำงาน ไฟล์เอาต์พุตประกอบด้วยเอาต์พุต การเลือก ข้อความ การพิมพ์ ฯลฯ ทั้งหมด

Invoke-SqlCmd สร้างผลลัพธ์เป็นชุดข้อมูล DataTables หรือ DataRows ดังนั้น หากคุณประมวลผลผลลัพธ์ของการเลือกแบบง่าย คุณก็สามารถนำไปใช้ได้ ตร.ซมเมื่อแยกวิเคราะห์ผลลัพธ์แล้วแทบจะเป็นไปไม่ได้เลยที่จะได้รับสิ่งที่ซับซ้อน: เพราะสิ่งนี้จึงมี เรียกใช้-SqlCmd. แต่ทีมนี้ก็มีเรื่องตลกของตัวเองเช่นกัน:

• หากคุณโอนไฟล์ให้เธอผ่านทาง -ไฟล์อินพุตแล้ว EXIT ไม่จำเป็น ยิ่งกว่านั้น ยังก่อให้เกิดข้อผิดพลาดทางไวยากรณ์อีกด้วย
• -OutputFile ไม่ คำสั่งจะส่งคืนผลลัพธ์เป็นอ็อบเจ็กต์ให้กับคุณ
• มีสองไวยากรณ์สำหรับการระบุเซิร์ฟเวอร์: -ServerInstance -ชื่อผู้ใช้ -รหัสผ่าน -ฐานข้อมูล และผ่าน -การเชื่อมต่อสตริง. น่าแปลกที่ในกรณีแรกไม่สามารถระบุพอร์ตอื่นที่ไม่ใช่ 1433 ได้
• เอาต์พุตข้อความพิมพ์ PRINT ซึ่งเรียกง่ายๆว่า "จับ" ตร.ซมสำหรับ เรียกใช้-SqlCmd เป็นปัญหา
• และที่สำคัญที่สุด: เป็นไปได้มากว่า Linux ของคุณไม่มี cmdlet นี้!

และนี่คือปัญหาหลัก เฉพาะในเดือนมีนาคม cmdlet นี้ พร้อมใช้งานสำหรับแพลตฟอร์มที่ไม่ใช่ Windowsและในที่สุดเราก็สามารถก้าวไปข้างหน้าได้!

การแทนที่ตัวแปร

sqlcmd มีการทดแทนตัวแปรโดยใช้ -v เช่น:

# $conn содержит начало команды sqlcmd
$cmd = $conn + " -i D:appsSlaveJobsKillSpid.sql -o killspid.res 
  -v spid =`"" + $spid + "`" -v age =`"" + $age + "`""
Invoke-Expression $cmd

ในสคริปต์ SQL เราใช้การทดแทน:

set @spid=$(spid)
set @age=$(age)

ดังนั้นนี่คือ ใน *ระวัง การทดแทนตัวแปรไม่ทำงาน. พารามิเตอร์ -v ละเลย ยู เรียกใช้-SqlCmd ละเลย -ตัวแปร. แม้ว่าพารามิเตอร์ที่ระบุตัวแปรจะถูกละเว้น แต่การทดแทนเองก็ใช้งานได้ คุณสามารถใช้ตัวแปรใดก็ได้จาก Shell อย่างไรก็ตาม ฉันรู้สึกขุ่นเคืองกับตัวแปรและตัดสินใจที่จะไม่พึ่งพาตัวแปรเหล่านั้นเลย และกระทำการอย่างหยาบคายและดั้งเดิม เนื่องจากสคริปต์ SQL สั้น:

# prepend the parameters  
"declare @age int, @spid int" | Add-Content "q.sql"
"set @spid=" + $spid | Add-Content "q.sql"
"set @age=" + $age | Add-Content "q.sql"

foreach ($line in Get-Content "Sqlserver/Automation/KillSpid.sql") { 
  $line | Add-Content "q.sql" 
  }
$cmd = "/opt/mssql-tools/bin/" + $conn + " -i q.sql -o res.log"

ตามที่คุณเข้าใจนี่เป็นการทดสอบจากเวอร์ชัน Unix แล้ว

กำลังอัพโหลดไฟล์

ในเวอร์ชัน Windows การดำเนินการใด ๆ จะมาพร้อมกับการตรวจสอบ: เราใช้ sqlcmd ได้รับการละเมิดบางอย่างในไฟล์เอาต์พุต แนบไฟล์นี้เข้ากับแผ่นตรวจสอบ โชคดีที่เซิร์ฟเวอร์ SQL ทำงานบนเซิร์ฟเวอร์เดียวกันกับเจนกินส์ โดยมีการดำเนินการดังนี้:

CREATE procedure AuditUpload
  @id int, @filename varchar(256)
as
  set nocount on
  declare @sql varchar(max)

  CREATE TABLE #multi (filer NVARCHAR(MAX))
  set @sql='BULK INSERT #multi FROM '''+@filename
    +''' WITH (ROWTERMINATOR = '' '',CODEPAGE = ''ACP'')'
  exec (@sql)
  select @sql=filer from #multi
  update JenkinsAudit set multiliner=@sql where ID=@id
  return

ดังนั้นเราจึงกลืนไฟล์ BCP ทั้งหมดและผลักมันลงในฟิลด์ nvarchar(max) ของตารางการตรวจสอบ แน่นอนว่าระบบทั้งหมดนี้พังทลายลงเพราะแทนที่จะเป็นเซิร์ฟเวอร์ SQL ฉันได้รับ RDS และ BULK INSERT ไม่ทำงานเลยผ่าน UNC เนื่องจากความพยายามที่จะล็อคไฟล์แบบเอกสิทธิ์เฉพาะบุคคลและด้วย RDS โดยทั่วไปแล้วจะถึงวาระจาก จุดเริ่มต้นมาก ดังนั้นฉันจึงตัดสินใจเปลี่ยนการออกแบบระบบ โดยจัดเก็บการตรวจสอบทีละบรรทัด:

CREATE TABLE AuditOut (
  ID int NULL,
  TextLine nvarchar(max) NULL,
  n int IDENTITY(1,1) PRIMARY KEY
  )

และเขียนลงในตารางดังนี้:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # get $lastid of the last execution  -- проскипано для статьи
	
  #create grid and populate it with data from file
  $audit =  Get-Content $Filename
  $DT = new-object Data.DataTable   

  $COL1 =  new-object Data.DataColumn; 
  $COL1.ColumnName = "ID"; 
  $COL1.DataType =  [System.Type]::GetType("System.Int32") 

  $COL2 =  new-object Data.DataColumn; 
  $COL2.ColumnName = "TextLine"; 
  $COL2.DataType =  [System.Type]::GetType("System.String") 
  
  $DT.Columns.Add($COL1) 
  $DT.Columns.Add($COL2) 
  foreach ($line in $audit) 
    { 
    $DR = $dt.NewRow()   
    $DR.Item("ID") = $lastid
    $DR.Item("TextLine") = $line
    $DT.Rows.Add($DR)   
    } 

  # write it to table
  $conn=new-object System.Data.SqlClient.SQLConnection 
  $conn.ConnectionString = $ConnStr
  $conn.Open() 
  $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnStr
  $bulkCopy.DestinationTableName = $Tabname 
  $bulkCopy.BatchSize = 50000
  $bulkCopy.BulkCopyTimeout = 0
  $bulkCopy.WriteToServer($DT) 
  $conn.Close() 
  }  

ในการเลือกเนื้อหา คุณต้องเลือกตาม ID โดยเลือกตามลำดับ n (ตัวตน)

ในบทความถัดไป ฉันจะลงรายละเอียดเพิ่มเติมว่าทั้งหมดนี้โต้ตอบกับเจนกินส์อย่างไร

ที่มา: will.com