🥇Linux 上の Powershell から MS SQL を操作する

この記事は純粋に実用的なものであり、私の悲しい物語に捧げられています

準備中 ゼロタッチ製品 RDS (MS SQL) については、私たちの耳がざわめいていましたが、私は自動化のプレゼンテーション (POC - 概念実証)、つまり一連の PowerShell スクリプトを作成しました。プレゼンテーションの後、嵐のように長く続いた拍手が静まり、絶え間ない拍手に変わったとき、彼らは私にこう言いました。これはすべて良いことですが、イデオロギー上の理由からのみで、私たちの Jenkins スレーブはすべて Linux 上で動作します。

これは可能でしょうか? Windows 環境からこのような温かいランプ DBA を取り出し、Linux 環境の PowerShell の非常に熱い環境に貼り付けるのでしょうか? これは残酷ではありませんか？

私はこの奇妙なテクノロジーの組み合わせに没頭する必要がありました。もちろん、30 以上のスクリプトはすべて動作しなくなってしまいました。驚いたことに、XNUMX 営業日ですべてを修正することができました。熱く追求して書いています。では、PowerShell スクリプトを Windows から Linux に転送するときに、どのような落とし穴に遭遇する可能性があるでしょうか?

sqlcmd と Invoke-SqlCmd の比較

それらの主な違いを思い出させてください。古き良きユーティリティ sqlcmd Linux でも動作し、機能はほぼ同じです。 -Q を実行するクエリを渡し、入力ファイルを -i として、出力を -o として渡します。ただし、ファイル名では、当然ながら大文字と小文字が区別されます。 -i を使用する場合は、ファイルの最後に次のように記述します。

GO
EXIT

最後に EXIT がない場合、sqlcmd は入力を待ち続けます。 EXIT 〜されません GOの場合、最後のコマンドは機能しません。出力ファイルには、すべての出力、選択、メッセージ、印刷などが含まれます。

Invoke-SqlCmd は、結果を DataSet、DataTables、または DataRows として生成します。したがって、単純な選択の結果を処理する場合は、次のように使用できます。 sqlcmd、その出力を解析した後、複雑なものを導き出すことはほとんど不可能です。これには次のようなものがあります。 Invoke-SqlCmd。しかし、このチームには独自のジョークもあります。

ファイルを彼女に転送すると、 -入力ファイルその後 EXIT 必要ありません。さらに、構文エラーが発生します
-出力ファイル いいえ、コマンドは結果をオブジェクトとして返します
サーバーを指定するには XNUMX つの構文があります。 -ServerInstance -ユーザー名 -パスワード -データベース そしてそれを通して -接続文字列。奇妙なことに、最初のケースでは 1433 以外のポートを指定することはできません。
テキスト出力。PRINT と入力します。これは単に「キャッチ」されます。 sqlcmdのために Invoke-SqlCmd 問題です
最も重要な：おそらく、お使いの Linux にはこのコマンドレットがありません。

そしてこれが主な問題です。 XNUMX 月のみこのコマンドレット 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 無視されました。 U Invoke-SqlCmd 無視されました -変数。変数自体を指定するパラメーターは無視されますが、置換自体は機能します。シェルの任意の変数を使用できます。しかし、私は変数に腹を立て、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 サーバーは 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) フィールドに押し込みます。もちろん、SQL サーバーの代わりに RDS を導入したため、このシステム全体が崩壊しました。また、ファイルに排他ロックをかけようとしたため、UNC 経由では BULK INSERT がまったく機能しませんでした。RDS では、これは通常、次のような運命にあります。一番初めのこと。そこで、システム設計を変更して、監査を XNUMX 行ずつ保存することにしました。

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 とどのように相互作用するかについて詳しく説明します。

出所： habr.com

Linux 上の Powershell から MS SQL を操作する

この記事は純粋に実用的なものであり、私の悲しい物語に捧げられています

sqlcmd と Invoke-SqlCmd の比較

変数の置換

ファイルのアップロード

コメントを追加します 返信をキャンセル

コメントを追加します返信をキャンセル