🥇Bekerja dengan MS SQL daripada Powershell pada Linux

Artikel ini praktikal semata-mata dan didedikasikan untuk kisah sedih saya

Bersedia untuk PROD Sentuhan Sifar Untuk RDS (MS SQL), yang semua orang telah heboh tentangnya, saya telah membuat pembentangan (POC - Bukti Konsep) tentang automasi: satu set skrip PowerShell. Selepas pembentangan, apabila tepukan gemuruh yang berpanjangan, bertukar menjadi tepukan gemuruh, telah reda, mereka memberitahu saya - ini semua baik dan bagus, tetapi atas sebab-sebab ideologi, semua hamba Jenkins kita sedang berjalan di bawah... Linux!

Adakah ini mungkin? Untuk mengambil DBA seperti tiub yang hangat dari bawah Windows dan masukkan ke dalam powershell yang sangat tebal LinuxBukankah itu kejam?

Saya terpaksa menyelami gabungan teknologi yang pelik ini. Sememangnya, kesemua 30+ skrip saya berhenti berfungsi. Saya terkejut apabila berjaya membaiki semuanya dalam satu hari bekerja. Saya menulis ini sementara berita masih baharu. Jadi, apakah perangkap yang mungkin anda hadapi semasa memindahkan skrip PowerShell dari Windows di bawah Linux?

sqlcmd vs Invoke-SqlCmd

Biar saya ingatkan anda tentang perbezaan utama antara mereka. Utiliti lama yang baik sqlcmd Ia juga berfungsi di bawah Linux, dengan fungsi yang hampir sama. Kami lulus pertanyaan untuk melaksanakan -Q, fail input sebagai -i, dan output sebagai -o. Tetapi nama fail, sudah tentu, dibuat sensitif huruf besar. Jika anda menggunakan -i, maka dalam fail tulis pada akhir:

GO
EXIT

Jika tiada EXIT pada penghujungnya, maka sqlcmd akan terus menunggu input, dan jika sebelum ini EXIT tidak akan GO, maka arahan terakhir tidak akan berfungsi. Fail output mengandungi semua output, pilihan, mesej, cetakan, dsb.

Invoke-SqlCmd menghasilkan hasilnya sebagai DataSet, DataTables atau DataRows. Oleh itu, jika anda memproses hasil pilihan mudah, anda boleh gunakan sqlcmd, setelah menghuraikan outputnya, hampir mustahil untuk memperoleh sesuatu yang kompleks: untuk ini ada Invoke-SqlCmd. Tetapi pasukan ini juga mempunyai jenakanya sendiri:

Jika anda memindahkan fail kepadanya melalui -InputFail, Kemudian EXIT tidak diperlukan, lebih-lebih lagi, ia menghasilkan ralat sintaks
-Fail keluaran tidak, arahan itu mengembalikan hasilnya kepada anda sebagai objek
Terdapat dua sintaks untuk menentukan pelayan: -ServerInstance -Nama Pengguna -Kata Laluan -Pangkalan Data dan melalui -ConnectionString. Anehnya, dalam kes pertama adalah tidak mungkin untuk menentukan port selain 1433.
output teks, taip CETAK, yang hanya "ditangkap" sqlcmduntuk Invoke-SqlCmd adalah masalah
Dan yang paling penting: Kemungkinan besar Linux anda tidak mempunyai cmdlet ini!

Dan ini adalah masalah utama. Hanya pada bulan Mac cmdlet ini tersedia untuk platform bukan Windows, dan akhirnya kita boleh maju!

Penggantian Pembolehubah

sqlcmd mempunyai penggantian berubah menggunakan -v, contohnya seperti ini:

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

Dalam skrip SQL kami menggunakan penggantian:

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

Jadi begini. Dalam *nix penggantian berubah tidak berfungsi. Parameter -v diabaikan. U Invoke-SqlCmd diabaikan -Pembolehubah. Walaupun parameter yang menentukan pembolehubah itu sendiri diabaikan, penggantian itu sendiri berfungsi—anda boleh menggunakan sebarang pembolehubah daripada Shell. Walau bagaimanapun, saya tersinggung dengan pembolehubah dan memutuskan untuk tidak bergantung pada mereka sama sekali, dan bertindak kasar dan primitif, kerana skrip SQL adalah 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 faham, adalah ujian daripada versi Unix.

Fail dimuatnaik

Dalam versi Windows, sebarang operasi disertakan dengan audit: kami menjalankan sqlcmd, menerima beberapa jenis penyalahgunaan dalam fail output, melampirkan fail ini pada plat audit. Nasib baik, pelayan SQL berfungsi pada pelayan yang sama seperti Jenkins, ia 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 = '' '',CODEPAGE = ''ACP'')'
  exec (@sql)
  select @sql=filer from #multi
  update JenkinsAudit set multiliner=@sql where ID=@id
  return

Oleh itu, kami menelan fail BCP sepenuhnya dan memasukkannya ke dalam medan nvarchar(maks) jadual audit. Sudah tentu, keseluruhan sistem ini runtuh, kerana bukannya pelayan SQL saya mendapat RDS, dan BULK INSERT tidak berfungsi sama sekali melalui UNC kerana percubaan untuk mengambil kunci eksklusif pada fail, dan dengan RDS ini biasanya ditakdirkan daripada awal-awal lagi. Jadi saya memutuskan untuk menukar reka bentuk sistem, menyimpan audit baris demi baris:

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

Dan tulis dalam jadual 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 kandungan, anda perlu memilih mengikut ID, memilih mengikut urutan n (identiti).

Dalam artikel seterusnya saya akan pergi ke lebih terperinci tentang bagaimana ini semua berinteraksi dengan Jenkins.

Sumber: www.habr.com