Π Π°Π±ΠΎΡ‚Π° с MS SQL ΠΈΠ· Powershell Π½Π° Linux

Π­Ρ‚Π° ΡΡ‚Π°Ρ‚ΡŒΡ чисто практичСская ΠΈ посвящСна ΠΌΠΎΠ΅ΠΉ грустной истории

Π“ΠΎΡ‚ΠΎΠ²ΡΡΡŒ ΠΊ Zero Touch PROD для RDS (MS SQL), ΠΏΡ€ΠΎ ΠΊΠΎΡ‚ΠΎΡ€Ρ‹ΠΉ Π½Π°ΠΌ ΠΏΡ€ΠΎΠΆΡƒΠΆΠΆΠ°Π»ΠΈ всС ΡƒΡˆΠΈ, я сдСлал ΠΏΡ€Π΅Π·Π΅Π½Ρ‚Π°Ρ†ΠΈΡŽ (POC β€” Proof Of Concept) Π°Π²Ρ‚ΠΎΠΌΠ°Ρ‚ΠΈΠ·Π°Ρ†ΠΈΠΈ: Π½Π°Π±ΠΎΡ€Π° powershell скриптов. ПослС ΠΏΡ€Π΅Π·Π΅Π½Ρ‚Π°Ρ†ΠΈΠΈ, ΠΊΠΎΠ³Π΄Π° стихли Π±ΡƒΡ€Π½Ρ‹Π΅, ΠΏΡ€ΠΎΠ΄ΠΎΠ»ΠΆΠΈΡ‚Π΅Π»ΡŒΠ½Ρ‹Π΅ аплодисмСнты, пСрСходящиС Π² нСсмолкаСмыС ΠΎΠ²Π°Ρ†ΠΈΠΈ, ΠΌΠ½Π΅ сказали β€” всС это Ρ…ΠΎΡ€ΠΎΡˆΠΎ, Π½ΠΎ Π²ΠΎΡ‚ Ρ‚ΠΎΠ»ΡŒΠΊΠΎ ΠΏΠΎ идСологичСским ΠΏΡ€ΠΈΡ‡ΠΈΠ½Π°ΠΌ Ρƒ нас всС Jenkins slaves Ρ€Π°Π±ΠΎΡ‚Π°ΡŽΡ‚ ΠΏΠΎΠ΄ Linux!

Π Π°Π·Π²Π΅ Ρ‚Π°ΠΊ ΠΌΠΎΠΆΠ½ΠΎ? Π’Π·ΡΡ‚ΡŒ Ρ‚Π°ΠΊΠΎΠ³ΠΎ Ρ‚Π΅ΠΏΠ»ΠΎΠ³ΠΎ, Π»Π°ΠΌΠΏΠΎΠ²ΠΎΠ³ΠΎ DBA ΠΈΠ· ΠΏΠΎΠ΄ Windows ΠΈ ΡΡƒΠ½ΡƒΡ‚ΡŒ Π΅Π³ΠΎ Π² самоС ΠΏΠ΅ΠΊΠ»ΠΎ powershell ΠΏΠΎΠ΄ Linux? Π Π°Π·Π²Π΅ это Π½Π΅ ТСстоко?

Π Π°Π±ΠΎΡ‚Π° с MS SQL ΠΈΠ· 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:appsSlaveJobsKillSpid.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 = '' '',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.

Π˜ΡΡ‚ΠΎΡ‡Π½ΠΈΠΊ: habr.com