Tzimie Apr 7 2019 at 20:20

Работа с MS SQL из Powershell на Linux

4 min

9.5K

Microsoft SQL Server*Database Administration*

Эта статья чисто практическая и посвящена моей грустной истории

Готовясь к Zero Touch PROD для RDS (MS SQL), про который нам прожужжали все уши, я сделал презентацию (POC — Proof Of Concept) автоматизации: набора powershell скриптов. После презентации, когда стихли бурные, продолжительные аплодисменты, переходящие в несмолкаемые овации, мне сказали — все это хорошо, но вот только по идеологическим причинам у нас все Jenkins slaves работают под Linux!

Разве так можно? Взять такого теплого, лампового DBA из под Windows и сунуть его в самое пекло powershell под Linux? Разве это не жестоко?

Пришлось погрузиться в эту странную комбинацию технологий. Разумеется, все мои 30+ скриптов перестали работать. К моему удивлению, за один рабочий день мне все удалось исправить. Пишу по горячим следам. Итак, какие подводные камни могут встретиться вам при переносе powershell скриптов из Windows под Linux?

sqlcmd vs Invoke-SqlCmd

Напомню основную разницу между ними. Старая добрая утилита sqlcmd работает и под линуксами, с почти идентичной функциональностью. Кверь для выполнения мы передаем -Q, входной файл как -i, а вывод -o. Вот только имена файлов, разумеется, делаются case-sensitive. Если вы используете -i, то в файле напишите в конце:

GO
EXIT

Если в конце не будет EXIT, то sqlcmd перейдет к ожиданию ввода, а если перед EXIT не будет GO, то последняя команда не отработает. В файл вывода попадает весь вывод, selects, сообщения, print итд.

Invoke-SqlCmd выдает результат в виде DataSet, DataTables или DataRows. Поэтому, если обработать результат простого select вы можете и через sqlcmd, разобрав его вывод, то вывести что-то сложное практически нереально: для этого есть Invoke-SqlCmd. Но есть у этой команды и свои приколы:

Если вы передаете ей файл через -InputFile, то EXIT не нужен, более того, он выдает синтаксическую ошибку
-OutputFile нет, команда возвращает вам результат в виде объекта
Для указания сервера есть два синтаксиса: -ServerInstance -Username -Password -Database и через -ConnectionString. Как ни странно, в первом случае указать порт, отличный от 1433, не получается.
текстовый вывод, типа PRINT, который элементарно «ловится» sqlcmd, для Invoke-SqlCmd является проблемой
И главное: скорее всего в вашем линуксе этого cmdlet нет!

И это главная проблема. Только в марте этот cmdlet стал доступен для не-windows платформ, и наконец мы можем двигаться вперед!

Подстановка переменных

В 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. Хотя параметр, который задает сами переменные, игнорируется, сами подстановки работают — вы можете использовать любые переменные из Shell. Однако я обиделся на переменные и решил от них вообще не зависеть, и поступил грубо и примитивно, благо скрипты на 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"

Это, как вы поняли, тест уже с юниксовой версии.

Загрузка файлов

В виндовой версии у меня любая операция сопровождалась аудитом: выполнили sqlcmd, получили какую-то ругань в output file, приложили этот файл к табличке аудита. Благо SQL server работал на том же сервере, что и 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 server я получил RDS, а BULK INSERT вообще по \\UNC не работает из-за попытки взять эксклюзивный лок на файл, а с RDS это вообще изначально обречено. Так что я решил изменить дизайн системы, храня аудит построчно:

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

Для выбора содержимого надо делать select по ID, выбирая в порядке n (identity).

В следующей статье я более подробно остановлюсь на том, как это все взаимодействует с Jenkins.

Tags:

Hubs: