主持人 > 博客 > 管理 > 在 Linux 上透過 Powershell 使用 MS SQL

在 Linux 上透過 Powershell 使用 MS SQL

本文純粹實用，獻給我的悲傷故事

準備好 零接觸產品 對於我們所有人都在討論的 RDS (MS SQL)，我做了一個自動化演示（POC - 概念驗證）：一組 powershell 腳本。 演講結束後，當暴風雨般的、經久不息的掌聲平息下來，變成經久不息的掌聲時，他們告訴我——這一切都很好，但只是出於意識形態的原因，我們所有的Jenkins奴隸都在Linux上工作！

這可能嗎？ 把Windows下這麼熱情、燈火通明的DBA放到Linux下火熱的powershell裡？ 這不是很殘忍嗎？

在 Linux 上透過 Powershell 使用 MS SQL
我必須讓自己沉浸在這種奇怪的技術組合中。 當然，我的 30 多個腳本全部停止工作。 令我驚訝的是，我在一個工作天內就解決了所有問題。 我正在緊追不捨地寫作。 那麼，將 powershell 腳本從 Windows 轉移到 Linux 時會遇到哪些陷阱呢？

sqlcmd 與 Invoke-SqlCmd

讓我提醒您它們之間的主要區別。 好舊的實用工具 命令行 它也可以在 Linux 下運行，具有幾乎相同的功能。 我們傳遞查詢來執行 -Q，輸入檔案作為 -i，輸出作為 -o。 但檔名當然是區分大小寫的。 如果使用-i，則在文件末尾寫入：

GO
EXIT

如果最後沒有EXIT，那麼sqlcmd會繼續等待輸入，如果之前 退出 不會 GO，那麼最後一條指令將不起作用。 輸出檔案包含所有輸出、選擇、訊息、列印等。

Invoke-SqlCmd 產生 DataSet、DataTables 或 DataRows 形式的結果。 因此，如果您處理簡單選擇的結果，您可以使用 命令行，解析其輸出後，幾乎不可能得出複雜的東西：為此，有 調用-SqlCmd。 不過這個團隊也有自己的笑話：

  • 如果您透過以下方式將文件傳輸給她 -輸入檔案，然後 退出 不需要，而且它會產生語法錯誤
  • -輸出文件 不，該命令將結果作為物件傳回給您
  • 有兩種指定伺服器的語法： -伺服器實例-使用者名稱-密碼-資料庫 並透過 -連接字串。 奇怪的是，在第一種情況下，無法指定 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)

所以就是這樣。 在 *nix 中 變數替換不起作用。 範圍 -v 被忽略。 U 調用-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 Server 與 Jenkins 運行在同一台伺服器上，它是這樣做的：

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) 欄位中。 當最開始的時候。 所以我決定改變系統設計，逐行儲存審計：

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（身分）選擇。

在下一篇文章中，我將更詳細地介紹這一切如何與 Jenkins 互動。

來源： www.habr.com

添加評論