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

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

RDS（MS SQL）向けのZero Touch PRODの準備について、私たちの耳が騒々しかったので、私は自動化のプレゼンテーション（POC-概念実証）を作成しました：一連のPowerShellスクリプト。 プレゼンテーションの後、大声で長時間の拍手が止まり、絶え間ない拍手turningになったとき、私はこれはすべて良いと言われましたが、イデオロギー的な理由のために、私たちは皆、Linuxの下でJenkinsの奴隷を走らせています！



それは可能ですか？ Windowsからこのような温かい管のDBAを取得し、Linuxの下でpowershellの非常に暑い場所に置くには？ それは残酷ではありませんか？









この奇妙なテクノロジーの組み合わせに飛び込む必要がありました。 もちろん、30以上のスクリプトはすべて機能しなくなりました。 驚いたことに、1営業日ですべてを修正できました。 私は熱い追求で書いています。 それでは、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があります。 しかし、このチームには独自のジョークがあります。

• -InputFileを介してファイルを渡す場合、 EXITは必要ありません。さらに、構文エラーが発生します。
• -OutputFileではなく、コマンドは結果をオブジェクトとして返します
• サーバーを指定するには、 -ServerInstance -Username -Password -Databaseおよびthrough -ConnectionStringの 2つの構文があります。 奇妙なことに、最初のケースでは1433以外のポートを指定できません。
• タイプPRINTのテキスト出力は、基本的な方法でsqlcmdによって「キャッチ」され、 Invoke-SqlCmdの 問題です
• そして最も重要なことは、このコマンドレットがLinuxにない可能性が高いことです。

そしてこれが主な問題です。 このコマンドレットは 、 Windows以外のプラットフォームで使用できるようになったのは3月になり 、ようやく前進できます！

変数置換

Sqlcmdには、次のような-vの変数置換があります。

 # $conn    sqlcmd $cmd = $conn + " -i D:\apps\SlaveJobs\KillSpid.sql -o killspid.res -v spid =`"" + $spid + "`" -v age =`"" + $age + "`"" Invoke-Expression $cmd
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

SQLスクリプトでは、置換を使用します。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

だからここに。 * nix では、変数置換は機能しません 。 -vオプションは無視されます。 Invoke-SqlCmdは-Variablesを無視します 。 変数自体を設定するパラメーターは無視されますが、置換自体は機能します。シェルの任意の変数を使用できます。 しかし、私は変数に腹を立て、それらにまったく依存しないことに決めました。そして、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 = ''\0'',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が機能しないため、このシステム全体が崩れました。 そこで、監査を行ごとに保存して、システムの設計を変更することにしました。

 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（ID）を順番に選択します。



次の記事では、これらすべてがジェンキンスとどのように相互作用するかについて詳しく説明します。