Bekerja dengan MS SQL dari Powershell di Linux

Artikel ini murni praktis dan didedikasikan untuk kisah sedih saya.

Mempersiapkan Zero Touch PROD untuk RDS (MS SQL), tentang mana semua telinga kita berdengung, saya membuat presentasi (POC - Proof Of Concept) otomatisasi: satu set skrip PowerShell. Setelah presentasi, ketika tepuk tangan yang meriah dan berkepanjangan mereda, menjadi tepuk tangan tanpa henti, mereka mengatakan kepada saya bahwa semua ini baik, tetapi hanya untuk alasan ideologis kita semua memiliki budak Jenkins yang berjalan di Linux!

Apakah itu mungkin? Untuk mengambil yang hangat, tabung DBA dari bawah Windows dan memasukkannya ke dalam panas PowerShell di Linux? Bukankah itu kejam?


Saya harus menyelami kombinasi teknologi yang aneh ini. Tentu saja, semua 30+ skrip saya berhenti berfungsi. Yang mengejutkan saya, dalam satu hari kerja saya berhasil memperbaiki semuanya. Saya menulis dalam pengejaran. Jadi, jebakan apa yang bisa Anda temui saat porting skrip PowerShell dari Windows ke Linux?

sqlcmd vs Invoke-SqlCmd

Biarkan saya mengingatkan Anda tentang perbedaan utama di antara mereka. Utilitas sqlcmd tua yang baik juga bekerja di Linux, dengan fungsi yang hampir sama. Untuk mengeksekusi query, kita mengirimkan -Q, file input sebagai -i, dan output -o. Ini hanya nama file, tentu saja, dibuat case-sensitive. Jika Anda menggunakan -i, tulis di file di akhir:

GO EXIT 

Jika tidak ada EXIT di akhir, maka sqlcmd akan menunggu input, dan jika tidak ada GO sebelum EXIT , perintah terakhir tidak akan berfungsi. Semua output, pilih, pesan, cetak, dll., Sampai ke file output.

Invoke-SqlCmd mengembalikan hasilnya sebagai DataSet, DataTables atau DataRows. Oleh karena itu, jika Anda dapat memproses hasil pemilihan sederhana melalui sqlcmd , mem - parsing outputnya, maka mengeluarkan sesuatu yang rumit hampir tidak mungkin: ada Invoke-SqlCmd untuk ini. Tetapi tim ini memiliki lelucon sendiri:

• Jika Anda meneruskan file ke sana melalui -InputFile , maka EXIT tidak diperlukan, apalagi, itu memberikan kesalahan sintaksis
• -OutputFile tidak, perintah mengembalikan Anda hasilnya sebagai objek
• Ada dua sintaks untuk menentukan server: -ServerInstance -Username -Password -Database dan melalui -ConnectionString . Anehnya, dalam kasus pertama, Anda tidak dapat menentukan port selain 1433.
• output teks, dari tipe PRINT, yang "ditangkap" oleh sqlcmd dengan cara dasar, merupakan masalah untuk Invoke-SqlCmd
• Dan yang terpenting: cmdlet ini kemungkinan besar tidak ada di Linux Anda!

Dan ini adalah masalah utama. Hanya pada bulan Maret cmdlet ini tersedia untuk platform non-windows , dan akhirnya kita dapat bergerak maju!

Substitusi variabel

Sqlcmd memiliki substitusi variabel dengan -v, seperti:

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

Dalam skrip SQL, kami menggunakan substitusi:

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

Jadi disini. Di * nix , substitusi variabel tidak berfungsi . Opsi -v diabaikan. Invoke-SqlCmd mengabaikan -Variabel . Meskipun parameter yang mengatur variabel itu sendiri diabaikan, pergantian itu berfungsi - Anda bisa menggunakan variabel apa pun dari Shell. Namun, saya tersinggung oleh variabel dan memutuskan untuk tidak bergantung pada mereka sama sekali, dan saya bertindak kasar dan primitif, karena skrip untuk sql pendek:

 # 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" 

Ini, seperti yang Anda pahami, adalah ujian dari versi Unix.

Unggah file

Dalam versi Windows, setiap operasi yang saya lakukan disertai dengan audit: mereka melakukan sqlcmd, mendapat semacam penyalahgunaan dalam file output, melampirkan file ini ke plat audit. Untungnya, SQL server bekerja pada server yang sama dengan Jenkins, itu dilakukan seperti ini:

 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 

Dengan demikian, kami menelan seluruh file BCP, dan mendorong tabel audit di bidang nvarchar (maks). Tentu saja, seluruh sistem ini hancur, karena alih-alih SQL server yang saya dapatkan RDS, dan BULK INSERT tidak bekerja di \\ UNC karena upaya untuk mengambil kunci eksklusif ke file, dan dengan RDS ini pada awalnya hancur. Jadi saya memutuskan untuk mengubah desain sistem, menyimpan garis audit demi baris:

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

Dan tulis ke tabel ini seperti ini:

 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() } 

Untuk memilih konten, pilih berdasarkan ID, pilih n (identitas) secara berurutan.

Pada artikel selanjutnya saya akan membahas lebih detail tentang bagaimana semua ini berinteraksi dengan Jenkins.