ProHoster > Blog > administracja > Praca z MS SQL z poziomu Powershell na Linuksie

Praca z MS SQL z poziomu Powershell na Linuksie

Ten artykuł ma charakter czysto praktyczny i jest poświęcony mojej smutnej historii

Przygotowywać się do Zero Touch PROD dla RDS (MS SQL), o którym szumiały nam wszystkie uszy, zrobiłem prezentację (POC - Proof Of Concept) automatyzacji: zestaw skryptów PowerShell. Po prezentacji, gdy burzliwe, długotrwałe oklaski ucichły, zamieniając się w nieustanne brawa, powiedzieli mi - wszystko to dobrze, ale tylko ze względów ideologicznych, wszyscy nasi niewolnicy Jenkinsa pracują na Linuksie!

czy to możliwe? Wziąć taki ciepły, lampowy DBA spod Windowsa i wsadzić go w sam ogień powershella pod Linuksem? Czy to nie jest okrutne?

Praca z MS SQL z poziomu Powershell na Linuksie
Musiałem zanurzyć się w tym dziwnym połączeniu technologii. Oczywiście wszystkie moje ponad 30 skryptów przestało działać. Ku mojemu zdziwieniu udało mi się wszystko naprawić w ciągu jednego dnia roboczego. Piszę w pogoni za. Jakie zatem pułapki można napotkać podczas przesyłania skryptów PowerShell z systemu Windows do systemu Linux?

sqlcmd vs Invoke-SqlCmd

Przypomnę, jaka jest główna różnica między nimi. Stare, dobre narzędzie sqlcmd Działa również pod Linuksem, z niemal identyczną funkcjonalnością. Przekazujemy zapytanie do wykonania -Q, plik wejściowy jako -i, a wynik jako -o. Ale w nazwach plików oczywiście rozróżniana jest wielkość liter. Jeśli użyjesz -i, to w pliku napisz na końcu:

GO
EXIT

Jeśli na końcu nie ma EXIT, sqlcmd będzie czekać na wprowadzenie danych, a jeśli wcześniej EXIT nie będzie GO, wtedy ostatnie polecenie nie zadziała. Plik wyjściowy zawiera wszystkie dane wyjściowe, selekcje, komunikaty, wydruki itp.

Invoke-SqlCmd generuje wynik jako zestaw danych, tabele danych lub wiersze danych. Dlatego jeśli przetworzysz wynik prostego wyboru, możesz użyć sqlcmd, po przeanalizowaniu wyników, prawie niemożliwe jest wyprowadzenie czegoś złożonego: w tym celu istnieje Invoke-SqlCmd. Ale ten zespół ma też swoje własne żarty:

  • Jeśli prześlesz jej plik przez -Plik wejściowynastępnie EXIT nie jest potrzebne, ponadto powoduje błąd składniowy
  • -Plik wyjściowy nie, polecenie zwraca wynik jako obiekt
  • Istnieją dwie składnie określania serwera: -ServerInstance -Nazwa użytkownika -Hasło -Baza danych i przez -Ciąg połączenia. Co dziwne, w pierwszym przypadku nie można określić portu innego niż 1433.
  • wyjście tekstowe typu PRINT, które jest po prostu „złapane” sqlcmddla Invoke-SqlCmd jest problem
  • I co najważniejsze: Najprawdopodobniej Twój Linux nie ma tego polecenia cmdlet!

I to jest główny problem. Tylko w marcu to polecenie cmdlet stał się dostępny dla platform innych niż Windowsi wreszcie możemy ruszyć do przodu!

Podstawianie zmiennych

sqlcmd ma podstawienie zmiennych za pomocą -v, na przykład tak:

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

W skrypcie SQL stosujemy podstawienia:

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

Więc oto jest. W *nix podstawienia zmiennych nie działają. Parametr -v ignorowane. U Invoke-SqlCmd ignorowane -Zmienne. Chociaż parametr określający same zmienne jest ignorowany, same podstawienia działają — możesz użyć dowolnych zmiennych z powłoki. Jednak obraziłem się na zmienne i postanowiłem w ogóle na nich nie polegać, zachowując się niegrzecznie i prymitywnie, ponieważ skrypty SQL są krótkie:

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

Jak rozumiesz, jest to test już z wersji uniksowej.

Przesyłanie plików

W wersji dla systemu Windows każdej operacji towarzyszył audyt: uruchomiliśmy sqlcmd, otrzymaliśmy jakieś nadużycie w pliku wyjściowym, dołączyliśmy ten plik do płytki audytu. Na szczęście serwer SQL działał na tym samym serwerze co Jenkins, zrobiono to mniej więcej tak:

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

W ten sposób całkowicie połykamy plik BCP i umieszczamy go w polu nvarchar(max) tabeli audytu. Oczywiście cały ten system się rozpadł, bo zamiast serwera SQL dostałem RDS, a BULK INSERT w ogóle nie działa przez UNC ze względu na próbę wzięcia blokady na wyłączność na plik, a przy RDS jest to generalnie skazane na porażkę Sam początek. Zdecydowałem się więc zmienić projekt systemu, przechowując audyt linia po linii:

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

I napisz w tej tabeli w ten sposób:

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

Aby wybrać treść, musisz wybrać według identyfikatora, wybierając w kolejności n (tożsamość).

W następnym artykule omówię bardziej szczegółowo, jak to wszystko współdziała z Jenkinsem.

Źródło: www.habr.com

Dodaj komentarz