کار با MS SQL از Powershell در لینوکس

این مقاله کاملا کاربردی است و به داستان غم انگیز من اختصاص دارد

آماده شدن برای Zero Touch PROD برای RDS (MS SQL)، که همه گوش‌هایمان در مورد آن وزوز می‌کردند، یک ارائه (POC - Proof Of Concept) از اتوماسیون ساختم: مجموعه‌ای از اسکریپت‌های powershell. پس از ارائه، هنگامی که تشویق طوفانی و طولانی خاموش شد و به تشویق بی وقفه تبدیل شد، آنها به من گفتند - همه اینها خوب است، اما فقط به دلایل ایدئولوژیک، همه برده های جنکینز ما روی لینوکس کار می کنند!

آیا این ممکن است؟ چنین DBA گرم و لامپی را از زیر ویندوز بگیرید و آن را در گرمای پاورشل تحت لینوکس بچسبانید؟ آیا این بی رحمانه نیست؟

من باید خودم را در این ترکیب عجیب فناوری ها غوطه ور می کردم. البته تمام اسکریپت‌های بیش از 30 من از کار افتادند. در کمال تعجب، موفق شدم همه چیز را در یک روز کاری درست کنم. من در حال نوشتن هستم. بنابراین، هنگام انتقال اسکریپت های powershell از ویندوز به لینوکس با چه مشکلاتی مواجه می شوید؟

sqlcmd در مقابل Invoke-SqlCmd

بگذارید تفاوت اصلی آنها را یادآوری کنم. ابزار خوب قدیمی sqlcmd همچنین تحت لینوکس کار می کند، با عملکرد تقریبا یکسان. Query را برای اجرای -Q، فایل ورودی به صورت -i و خروجی را به صورت -o ارسال می کنیم. اما نام فایل ها، البته، به حروف کوچک و بزرگ حساس است. اگر از -i استفاده می کنید، در فایل در انتها بنویسید:

GO
EXIT

اگر در انتها EXIT وجود نداشته باشد، sqlcmd منتظر ورودی می ماند و اگر قبل از خروج نخواهد بود GO، سپس آخرین دستور کار نخواهد کرد. فایل خروجی شامل تمام خروجی ها، انتخاب ها، پیام ها، چاپ و غیره است.

Invoke-SqlCmd نتیجه را به صورت DataSet، DataTables یا DataRows تولید می کند. بنابراین، اگر نتیجه یک انتخاب ساده را پردازش کنید، می توانید استفاده کنید sqlcmdبا تجزیه خروجی آن، استخراج چیزی پیچیده تقریبا غیرممکن است: برای این وجود دارد Invoke-SqlCmd. اما این تیم شوخی های خودش را هم دارد:

• اگر فایلی را به او منتقل کنید از طریق -فایل ورودی، و سپس خروج لازم نیست، علاوه بر این، یک خطای نحوی ایجاد می کند
• -فایل خروجی نه، دستور نتیجه را به عنوان یک شی به شما برمی گرداند
• دو نحو برای تعیین یک سرور وجود دارد: -ServerInstance - نام کاربری - رمز عبور - پایگاه داده و از طریق -ConnectionString. به اندازه کافی عجیب، در مورد اول نمی توان پورتی غیر از 1433 را مشخص کرد.
• خروجی متن، تایپ PRINT، که به سادگی "گیر" است sqlcmdبرای Invoke-SqlCmd یک مشکل است
• و از همه مهمتر: به احتمال زیاد لینوکس شما این cmdlet را ندارد!

و این مشکل اصلی است. فقط در ماه مارس این cmdlet برای پلتفرم های غیر ویندوزی در دسترس قرار گرفت، و بالاخره می توانیم جلو برویم!

جایگزینی متغیر

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

همانطور که متوجه شدید، این یک آزمایش از قبل از نسخه یونیکس است.

در حال آپلود فایل ها

در نسخه ویندوز، هر عملیاتی با ممیزی همراه بود: ما sqlcmd را اجرا کردیم، نوعی سوء استفاده در فایل خروجی دریافت کردیم، این فایل را به صفحه حسابرسی پیوست کردیم. خوشبختانه سرور SQL روی همان سرور جنکینز کار می کرد، چیزی شبیه به این انجام شد:

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 گرفتم و 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() 
  }  

برای انتخاب محتوا، باید با شناسه و به ترتیب n (هویت) انتخاب کنید.

در مقاله بعدی به جزئیات بیشتری در مورد نحوه تعامل این همه با جنکینز خواهم پرداخت.

منبع: www.habr.com