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

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


    Готовясь к 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.
    Поделиться публикацией

    Комментарии 11

      0
      все мои 30+ скриптов
      А где с ними можно ознакомиться?
        0
        Я хочу забацать описание того что автоматизировано и что стоит автоматизировать, а что не стоит. А что автоматизируете вы — чтото уже автоматизировано или вы присматриваетесь?

        P.S. В коде есть завязки на специфику инфраструктуры, так что я собираюсь постить кусочки кода. Из готовых статей, почитайте мою статью об одной автоматизации: habr.com/ru/post/434678
          0
          Еще не все автоматизировано, но даже то что есть можно улучшить. Всегда найдется тот, у кого лаконичнее, оптимальнее, быстрее.
            0
            Пока прочтите новую статью)
            А в третьей, которую напишу, я как раз хотел обсудить с публикой список востребованных jobs.
              0
              Я уже все прочитал :) Я уже подписался. И я уверен, что в Вас есть лаконичное, оптимальное и быстрое решение по созданию планов обслуживания. У меня это последний кусочек паззла, который мешает мне доделать свою статью по 1Ске в разрезе CI/CD
                0

                А какой именно смысл вы вкладываете в слова "план обслуживания"?

                  0
                  Набор операций над MSSQL базой данных вроде переиндексации, обновления статистик, бэкапов и прочих чисток кэшей. Обыкновенный Maintance Plan
                    0
                    Ну эти операции, скорее всего, определены, вы скорее всего имеете виду их выполнение вне расписания — срочно сделать DBCC CLEANPROCTEXT, или убить блокирующий spid, итд?
                      0
                      У меня периодически возникают задачи, у совершенно разнообразных заказчиков, развернуть 1Сный сервер с базоводом, потому что на старом творится песец и нужно срочно спасти. Есть в разработке и отладке PS скрипт, который на чистой винде разворачивает MSSQL нужной версии, настраивает, прописывает доступы, вгружает из бэкапов базы и настраивает их. Ставит сервер 1С: Предприятия, настраивает, прописывает сервер ключей, подключает базы в базоводе, создает v8i файлы для автоподключения баз пользователям и прочие свистелки и перделки автоматизации.

                      И вот с планами обслуживания неудобства. Не нашел (просто плохо искал) я решения, что бы точечно по базам по разному настраивать стандартные планы, особенно в части бэкапа. Большая часть делается руками, поскольку у всех свои нюансы, и импорт заданий агента скульного сервака не подходящий по гибкости вариант.
                        0

                        Так а вы используете maintenance plans через SSIS? Я все это изгнал и перешёл на скрипты, там контролируешь все сам

                          0
                          И это имеет место быть в зонах с высоким контролем, но чаще всего мы делаем абсолютно типовое решение.

      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

      Самое читаемое