This article is purely practical and is dedicated to my sad story.
Preparing for Zero Touch PROD for RDS (MS SQL), about which we've been hearing non-stop, I made a presentation (POC — Proof Of Concept) for automation: a set of PowerShell scripts. After the presentation, when the enthusiastic, prolonged applause turned into unending ovations, I was told — all this is great, but due to ideological reasons, all our Jenkins slaves run on Linux!
Is it really possible? To take such a warm, cozy DBA from under Windows and throw them into the blazing fire 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, I managed to fix everything in just one working day. I'm writing this while the details are still fresh. So, what pitfalls might you encounter when migrating PowerShell scripts from Windows to Linux?
sqlcmd vs Invoke-SqlCmd
Let me remind you of the main difference between them. The old good utility sqlcmd works on Linux as well, with almost identical functionality. We pass the query for execution using -Q, the input file with -i, and the output with -o. Just remember that file names are case-sensitive. If you use -i, then write the following at the end of the file:
GO
EXITIf there is no EXIT at the end, sqlcmd will wait for input, and if before EXIT will not work GO, then the last command will not execute. All output, selects, messages, prints, etc. go to the output file.
Invoke-SqlCmd returns the result as a DataSet, DataTables, or DataRows. Therefore, while you can process the result of a simple select through sqlcmd, analyzing its output, getting something complex out is practically impossible: for that, we have Invoke-SqlCmd. But this command has its quirks:
- If you pass a file to it via -InputFile, then EXIT is not needed, moreover, it throws a syntax error.
- -OutputFile no, the command returns the result to you as an object.
- To specify the server, there are two syntaxes: -ServerInstance -Username -Password -Database and through -ConnectionString. Strangely, in the first case, it is not possible to specify a port other than 1433.
- Text output, such as PRINT, which can easily be 'caught' sqlcmdthere is a wrapper: Invoke-SqlCmd
- And the main point:
And this is the main issue. Only in March did this cmdlet , and finally we can move forward!
Variable substitution
In sqlcmd, you can substitute variables using -v, for example, like this:
# $conn содержит начало команды sqlcmd
$cmd = $conn + " -i D:appsSlaveJobsKillSpid.sql -o killspid.res
-v spid =`"" + $spid + "`" -v age =`"" + $age + "`""
Invoke-Expression $cmdIn the SQL script we use substitutions:
set @spid=$(spid)
set @age=$(age)So, in *nix variable substitutions do not work. The parameter -v are ignored. In Invoke-SqlCmd is ignored -Variables. Although the parameter that defines the variables themselves is ignored, the substitutions work—you can use any variables from Shell. However, I became frustrated with variables and decided to completely avoid them, and took a rough and primitive approach, fortunately 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 understood, is a test already with the Unix version.
File uploads
In the Windows version, any operation was accompanied by auditing: we executed sqlcmd, received some kind of error in the output file, and attached this file to the audit table. Fortunately, the SQL server ran on the same server as Jenkins, this was done roughly 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
returnThus, we are ingesting the BCP file entirely and pumping it into the nvarchar(max) field of the audit table. Naturally, this whole system collapsed, as instead of SQL server I got RDS, and BULK INSERT does not work over UNC due to the attempt to take an exclusive lock on the file, and that is hopeless with RDS from the outset. So, I decided to redesign 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 to this table like this:
function WriteAudit([string]$Filename, [string]$ConnStr,
[string]$Tabname, [string]$Jobname)
{
# get $lastid of the last execution -- skipped for the article
#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 the content, you need to do a select by ID, choosing in order n (identity).
In the next article, I will detail how all this interacts with Jenkins.
Source: habr.com
