Working with MS SQL from Powershell on Linux

This article is purely practical and is dedicated to my sad story

Getting ready for Zero Touch PROD for RDS (MS SQL), about which all ears buzzed to us, I made a presentation (POC - Proof Of Concept) of automation: a set of powershell scripts. After the presentation, when the stormy, prolonged applause subsided, turning into unceasing applause, I was told - all this is good, but only for ideological reasons, all Jenkins slaves work with Linux!

Is it possible? Take such a warm, lamp-like DBA from under Windows and stick it in the middle of powershell under Linux? Isn't that cruel?

I had to dive into this strange combination of technologies. Of course, all my 30+ scripts stopped working. To my surprise, in one working day I managed to fix everything. I am writing in hot pursuit. So, what are the pitfalls you may encounter when porting powershell scripts from Windows to Linux?

sqlcmd vs Invoke-SqlCmd

Let me remind you the main difference between them. Good old utility sqlcmd works under Linux, with almost identical functionality. For execution, we pass -Q, the input file as -i, and the output as -o. Here are just the names of the files, of course, are made case-sensitive. If you use -i, then in the file write at the end:

GO
EXIT

If there is no EXIT at the end, then sqlcmd will go to waiting for input, and if before EXIT will not be GO, then the last command will not work. The output file gets all the output, selects, messages, print, etc.

Invoke-SqlCmd returns the result as a DataSet, DataTables, or DataRows. Therefore, if you process the result of a simple select, you can also use sqlcmd, having parsed its output, it is almost impossible to deduce something complex: for this there is Invoke-SqlCmd. But this team also has its own jokes:

• If you send her a file via -InputFilethen EXIT not needed, moreover, it throws a syntax error
• -OutputFile no, the command returns you the result as an object
• There are two syntaxes for specifying a server: -ServerInstance -Username -Password -Database and through -ConnectionString. Oddly enough, in the first case, you cannot specify a port other than 1433.
• text output, such as PRINT, which is simply "caught" sqlcmdFor Invoke-SqlCmd is a problem
• And most importantly: most likely your Linux does not have this cmdlet!

And this is the main problem. Only in March this cmdlet became available for non-windows platformsand finally we can move forward!

Variable substitution

sqlcmd has variable substitution with -v, like so:

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

In the SQL script, we use substitutions:

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

So. In *nix variable substitutions don't work... Parameter -v ignored. At Invoke-SqlCmd ignored -Variables. Although the parameter that sets the variables themselves is ignored, the substitutions themselves work - you can use any variables from the Shell. However, I was offended by the variables and decided not to depend on them at all, and acted rudely and primitively, since the sql scripts are short:

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

This, as you understand, is a test already from the Unix version.

File upload

In the Windows version, any operation was accompanied by an audit for me: they executed sqlcmd, got some kind of abuse in the output file, attached this file to the audit plate. Fortunately, SQL server was running on the same server as Jenkins, it was done like this:

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

Thus, we swallow the entire BCP file, and shove it into the nvarchar (max) field of the audit table. Of course, this whole system fell apart, because instead of SQL server I got RDS, and BULK INSERT generally does not work on UNC because of an attempt to take an exclusive lock on a file, and with RDS it is generally doomed from the very beginning. So I decided to change the design of the system, storing the audit line by line:

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

And write in this table like this:

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

To select content, select by ID, choosing n (identity) in order.

In the next article, I'll go into more detail on how this all interacts with Jenkins.

Source: habr.com