主持人 > 博客 > 管理 > 在 Linux 上通过 Powershell 使用 MS SQL

在 Linux 上通过 Powershell 使用 MS SQL

本文纯粹实用，献给我的悲伤故事

准备好 零接触产品 对于我们所有人都在议论的 RDS (MS SQL)，我做了一个自动化演示（POC - 概念验证）：一组 powershell 脚本。 演讲结束后，当暴风雨般的、经久不息的掌声平息下来，变成经久不息的掌声时，他们告诉我——这一切都很好，但只是出于意识形态的原因，我们所有的Jenkins奴隶都在Linux上工作！

这可能吗？ 把Windows下这么热情、灯火通明的DBA放到Linux下火热的powershell里？ 这不是很残忍吗？

在 Linux 上通过 Powershell 使用 MS SQL
我必须让自己沉浸在这种奇怪的技术组合中。 当然，我的 30 多个脚本全部停止工作。 令我惊讶的是，我在一个工作日内就解决了所有问题。 我正在紧追不舍地写作。 那么，将 powershell 脚本从 Windows 转移到 Linux 时会遇到哪些陷阱呢？

sqlcmd 与 Invoke-SqlCmd

让我提醒您它们之间的主要区别。 好旧的实用工具 命令行 它也可以在 Linux 下运行，具有几乎相同的功能。 我们传递查询来执行 -Q，输入文件作为 -i，输出作为 -o。 但文件名当然是区分大小写的。 如果使用-i，则在文件末尾写入：

GO
EXIT

如果最后没有EXIT，那么sqlcmd会继续等待输入，如果之前 退出 不会 GO，那么最后一条命令将不起作用。 输出文件包含所有输出、选择、消息、打印等。

Invoke-SqlCmd 生成 DataSet、DataTables 或 DataRows 形式的结果。 因此，如果您处理简单选择的结果，您可以使用 命令行，解析其输出后，几乎不可能得出复杂的东西：为此，有 调用-SqlCmd。 不过这个团队也有自己的笑话：

  • 如果您通过以下方式将文件传输给她 -输入文件，然后 退出 不需要，而且它会产生语法错误
  • -输出文件 不，该命令将结果作为对象返回给您
  • 有两种指定服务器的语法： -服务器实例-用户名-密码-数据库 并通过 -连接字符串。 奇怪的是，在第一种情况下，无法指定 1433 以外的端口。
  • 文本输出，键入 PRINT，这只是“捕获” 命令行为 调用-SqlCmd 是一个问题
  • 最重要的是： 您的 Linux 很可能没有这个 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 被忽略。 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 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) 字段中。 当最开始的时候。 所以我决定改变系统设计，逐行存储审计：

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（身份）选择。

在下一篇文章中，我将更详细地介绍这一切如何与 Jenkins 交互。

来源： habr.com

添加评论