Linux의 Powershell에서 MS SQL 작업

이 기사는 순전히 실용적이며 내 슬픈 이야기에 전념합니다

준비하기 제로터치프로드 우리 모두가 웅성거리고 있는 RDS(MS SQL)에 대해 자동화에 대한 프레젠테이션(POC - Proof Of Concept), 즉 일련의 Powershell 스크립트를 만들었습니다. 프레젠테이션이 끝난 후 폭풍우가 치는 긴 박수가 사라지고 끊임없는 박수로 변했을 때 그들은 나에게 말했습니다. 이 모든 것이 좋지만 이념적 이유로 만 모든 Jenkins 노예가 Linux에서 작업합니다!

이것이 가능한가? Windows에서 따뜻한 램프 DBA를 가져와 Linux에서 Powershell의 열기에 붙이시겠습니까? 이건 잔인하지 않나요?

나는 이 이상한 기술의 조합에 몰입해야 했습니다. 물론 30개 이상의 스크립트가 모두 작동을 멈췄습니다. 놀랍게도 나는 근무일 기준으로 하루 만에 모든 것을 고칠 수 있었습니다. 나는 뜨거운 추구에 글을 쓰고 있습니다. 그렇다면 Powershell 스크립트를 Windows에서 Linux로 전송할 때 어떤 함정에 직면할 수 있습니까?

sqlcmd 대 Invoke-SqlCmd

그들 사이의 주요 차이점을 상기시켜 드리겠습니다. 좋은 오래된 유틸리티 SQLcmd 또한 거의 동일한 기능으로 Linux에서도 작동합니다. -Q를 실행하기 위해 쿼리를 전달하고, 입력 파일을 -i로, 출력을 -o로 전달합니다. 그러나 파일 이름은 물론 대소문자를 구분합니다. -i를 사용하는 경우 파일 끝에 다음을 씁니다.

GO
EXIT

끝에 EXIT가 없으면 sqlcmd는 계속해서 입력을 기다립니다. EXIT ~하지 않을 것이다 GO, 마지막 명령은 작동하지 않습니다. 출력 파일에는 모든 출력, 선택, 메시지, 인쇄 등이 포함됩니다.

Invoke-SqlCmd는 결과를 DataSet, DataTables 또는 DataRows로 생성합니다. 따라서 단순 선택의 결과를 처리하면 다음과 같이 사용할 수 있습니다. SQLcmd, 출력을 구문 분석한 후에는 복잡한 것을 파생하는 것이 거의 불가능합니다. 호출-SqlCmd. 하지만 이 팀에는 다음과 같은 농담도 있습니다.

• 다음을 통해 그녀에게 파일을 전송하는 경우 -입력 파일, 그런 다음 EXIT 필요하지 않으며 또한 구문 오류가 발생합니다.
• -결과물 파일 아니요, 명령은 결과를 객체로 반환합니다.
• 서버를 지정하는 데는 두 가지 구문이 있습니다. -ServerInstance -사용자 이름 -암호 -데이터베이스 그리고 통해 -연결 문자열. 이상하게도 첫 번째 경우에는 1433 이외의 포트를 지정할 수 없습니다.
• 텍스트 출력에 PRINT를 입력하면 간단히 "잡히게" 됩니다. SQLcmd~을 위해 호출-SqlCmd 문제이다
• 그리고 가장 중요한 것은 : Linux에 이 cmdlet이 없을 가능성이 높습니다!

그리고 이것이 주요 문제입니다. XNUMX월에만 이 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 무시되었습니다. 유 호출-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를 얻었고 파일에 대한 배타적 잠금을 시도하기 때문에 UNC를 통해 BULK INSERT가 전혀 작동하지 않고 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(ID) 순으로 선택해야 합니다.

다음 기사에서는 이 모든 것이 Jenkins와 어떻게 상호 작용하는지 자세히 설명하겠습니다.

출처 : habr.com