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

Тази статия е чисто практическа и е посветена на моята тъжна история

Подготвям се за Zero Touch PROD за RDS (MS SQL), за който всички ни бръмчаха в ушите, направих презентация (POC - Proof Of Concept) на автоматизацията: набор от скриптове на powershell. След презентацията, когато бурните, продължителни аплодисменти стихнаха, превръщайки се в непрестанни аплодисменти, те ми казаха - всичко това е добре, но само по идеологически причини, всички наши роби на Дженкинс работят на Linux!

Възможно ли е това? Вземете такъв топъл, лампов DBA от под Windows и го пъхнете в самата топлина на powershell под Linux? Това не е ли жестоко?

Трябваше да се потопя в тази странна комбинация от технологии. Разбира се, всичките ми над 30 скрипта спряха да работят. За моя изненада успях да оправя всичко за един работен ден. Пиша по горещи следи. И така, какви клопки можете да срещнете, когато прехвърляте Powershell скриптове от Windows към Linux?

sqlcmd срещу Invoke-SqlCmd

Нека ви напомня за основната разлика между тях. Добрата стара програма sqlcmd Работи и под Linux, с почти идентична функционалност. Предаваме заявката за изпълнение на -Q, входния файл като -i и изходния като -o. Но имената на файловете, разбира се, са чувствителни към малки и главни букви. Ако използвате -i, тогава във файла напишете в края:

GO
EXIT

Ако няма ИЗХОД в края, тогава sqlcmd ще продължи да изчаква въвеждане и ако преди EXIT няма GO, тогава последната команда няма да работи. Изходният файл съдържа целия изход, селекции, съобщения, печат и т.н.

Invoke-SqlCmd произвежда резултата като DataSet, DataTables или DataRows. Следователно, ако обработите резултата от обикновен избор, можете да използвате sqlcmd, след като анализира изхода си, е почти невъзможно да се извлече нещо сложно: за това има Извикване-SqlCmd. Но този отбор има и свои собствени шеги:

• Ако прехвърлите файл към нея чрез -Входен файл, Тогава EXIT не е необходимо, освен това създава синтактична грешка
• -Изходния файл не, командата ви връща резултата като обект
• Има два синтаксиса за указване на сървър: -ServerInstance -Потребителско име -Парола -База данни и чрез -ConnectionString. Колкото и да е странно, в първия случай не е възможно да посочите порт, различен от 1433.
• текстов изход, тип PRINT, който просто се „хваща“ sqlcmdза Извикване-SqlCmd е проблем
• И най-важното: Най-вероятно вашият Linux няма тази команда!

И това е основният проблем. Само през март този cmdlet стана достъпен за платформи, различни от Windows, и най-накрая можем да продължим напред!

Променливо заместване

sqlcmd има заместване на променлива с помощта на -v, например така:

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

В SQL скрипта използваме замествания:

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

И така, ето го. В *nix заместванията на променливи не работят... Параметър -v игнориран. U Извикване-SqlCmd игнориран -Променливи. Въпреки че параметърът, който указва самите променливи, се игнорира, самите замествания работят - можете да използвате всякакви променливи от 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"

Това, както разбирате, е тест вече от версията на Unix.

Качване на файлове

Във версията на Windows всяка операция беше придружена от проверка: стартирахме sqlcmd, получихме някаква злоупотреба в изходния файл, прикачихме този файл към таблото за проверка. За щастие SQL сървърът работеше на същия сървър като 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 = '' '',CODEPAGE = ''ACP'')'
  exec (@sql)
  select @sql=filer from #multi
  update JenkinsAudit set multiliner=@sql where ID=@id
  return

По този начин ние поглъщаме BCP файла изцяло и го пъхаме в полето nvarchar(max) на таблицата за проверка. Разбира се, цялата тази система се разпадна, защото вместо SQL сървър получих 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() 
  }  

За да изберете съдържание, трябва да изберете по ID, като изберете в ред n (идентичност).

В следващата статия ще разгледам по-подробно как всичко това взаимодейства с Дженкинс.

Източник: www.habr.com