เริ่มต้นใช้งานคำสั่ง PowerShell Inrigg-SQLCmd
ไม่ต้องสงสัยเลยว่าการจัดการเซิร์ฟเวอร์ SQL ในแพลตฟอร์ม GUI นั้นใช้งานได้ดี แต่หากคุณเชี่ยวชาญการสืบค้น T-SQL แบบดั้งเดิมมากกว่า คุณจะพบว่า PowerShell Intake-SQLCmd cmdlet น่าเพลิดเพลิน
Invoke-SqlCmd cmdlet มอบประสบการณ์ที่มุ่งเน้น PowerShell มากขึ้นสำหรับการทำงานกับฐานข้อมูล SQL Server และในบทช่วยสอนนี้ คุณจะเริ่มจัดการฐานข้อมูล SQL ของคุณด้วย Intake-SqlCmd cmdlet
เจาะลึกและเริ่มปรับปรุงการจัดการฐานข้อมูล SQL ของคุณ!
ข้อกำหนดเบื้องต้น
ก่อนที่จะข้ามไปที่คำสั่ง PowerShell Invoke-SQLCmd ตรวจสอบให้แน่ใจว่าคุณมีสิ่งต่อไปนี้เพื่อปฏิบัติตามพร้อมกับบทช่วยสอนนี้:
- คอมพิวเตอร์เพื่อเรียกใช้ PowerShell - บทช่วยสอนนี้ใช้ Windows Server 2022 พร้อม PowerShell v7.3.X
- Microsoft SQL Server พร้อมฐานข้อมูลที่มีอยู่ - บทช่วยสอนนี้ใช้ SQL 2022 Standard edition พร้อมฐานข้อมูลชื่อ EmployeesDB
- ผู้ใช้ SQL Server ที่มีสิทธิ์ในการเชื่อมต่อกับเซิร์ฟเวอร์และดำเนินการ
การติดตั้งโมดูล SqlServer
ด้วย Invoke-SqlCmd cmdlet คุณสามารถดำเนินการคำสั่ง SQL และสคริปต์ภายในคอนโซล PowerShell ได้ ด้วยเหตุนี้ คุณสามารถทำให้งานการจัดการฐานข้อมูลเป็นอัตโนมัติและรวม SQL Server เข้ากับคำสั่งและโมดูล PowerShell อื่นๆ ได้ และใช่ โดยไม่ต้องสลับระหว่างเครื่องมือและอินเทอร์เฟซต่างๆ
แต่เช่นเดียวกับ cmdlet อื่นๆ เพื่อใช้ประโยชน์จาก Invoke-SQLCmd cmdlet คุณต้องติดตั้งโมดูล SqlServer โมดูลนี้รองรับคุณสมบัติ SQL ล่าสุดที่ช่วยให้คุณพัฒนาฐานข้อมูลและการดูแลเซิร์ฟเวอร์ได้โดยอัตโนมัติ
1. เปิด PowerShell ในฐานะผู้ดูแลระบบ และเรียกใช้คำสั่ง Install-Module ต่อไปนี้เพื่อดาวน์โหลดและติดตั้งโมดูล SQLServer จากแกลเลอรี PowerShell
Install-Module SQLServerป้อน Y แล้วกด Enter เมื่อได้รับแจ้งให้ดำเนินการติดตั้งต่อ
2. เมื่อติดตั้งแล้ว ให้รันคำสั่ง Import-Module ด้านล่าง ซึ่งไม่ได้ให้เอาต์พุต แต่เปิดใช้งานคำสั่งทั้งหมดที่เกี่ยวข้องกับโมดูล SqlServer
Import-Module SQLServer3. ตอนนี้ ให้รันแต่ละคำสั่งด้านล่าง ซึ่งไม่ได้สร้างเอาต์พุตแต่ตั้งค่าตัวแปรที่มีข้อมูลที่เหมาะสมสำหรับชื่อเซิร์ฟเวอร์ ชื่อผู้ใช้ และรหัสผ่าน การทำเช่นนี้ช่วยให้คุณปรับปรุงขั้นตอนการทำงานและหลีกเลี่ยงการป้อนข้อมูลที่ใช้บ่อยด้วยตนเองเมื่อจำเป็น
การใช้ตัวแปรเหล่านี้อย่างสม่ำเสมอตลอดทั้งสคริปต์และคำสั่งของคุณจะช่วยประหยัดเวลาและลดข้อผิดพลาดที่เกิดจากการพิมพ์ผิดหรือข้อผิดพลาดอื่นๆ
# Set the SQL server name
$serverName = "mssql-vm"
# Set the SQL server credentials
$adminSqlLogin = "vmsilvamolina"
$password = "S3cur3P4ss@1"บางทีคุณอาจวางแผนให้ตัวแปรเหล่านี้คงอยู่ หากเป็นเช่นนั้น ให้ตั้งค่าเป็นตัวแปรสภาพแวดล้อมที่คุณสามารถเรียกใช้ได้เมื่อจำเป็น
การเชื่อมต่อกับ SQL Server ผ่าน PowerShell Inrigg-SQLCmd
เมื่อติดตั้งโมดูล SqlServer แล้ว คุณก็พร้อมที่จะเริ่มต้นใช้งาน PowerShell Invoke-SQLCmd cmdlet cmdlet นี้ช่วยให้คุณเชื่อมต่อกับเซิร์ฟเวอร์ SQL ของคุณและเรียกใช้แบบสอบถาม
ก่อนที่คุณจะสามารถสืบค้นเซิร์ฟเวอร์ SQL ของคุณได้ ตรวจสอบให้แน่ใจว่าคุณมีตัวแปรที่คุณตั้งไว้ในขั้นตอนสุดท้ายของส่วน “การติดตั้งโมดูล SqlServer” ไว้ใกล้มือ
รันคำสั่ง Invoke-SQLCmd ต่อไปนี้ ซึ่งไม่ได้จัดเตรียมเอาต์พุต แต่ดำเนินการต่อไปนี้:
- เชื่อมต่อกับเซิร์ฟเวอร์ SQL ของคุณ (
$serverName) - อนุญาตการเชื่อมต่อกับข้อมูลรับรองที่ระบุ (
$adminSqlLoginและ$password) - เรียกใช้
-Queryไปยังเซิร์ฟเวอร์ SQL ของคุณเพื่อPRINTข้อความHello world
Invoke-SQLCmd -ServerInstance $serverName -Username $adminSqlLogin -Password $password -Query "PRINT 'Hello world'"โปรดทราบว่าพารามิเตอร์
-ชื่อผู้ใช้สามารถระบุได้เฉพาะบัญชี SQL เท่านั้น ไม่ใช่บัญชี Windows
ไม่มีอะไรเกิดขึ้นเหรอ? ไม่อย่างแน่นอน หากคุณไม่เห็นข้อผิดพลาดทุกอย่างเป็นไปด้วยดี
ตอนนี้ ให้รันคำสั่งเดียวกันในขั้นตอนที่หนึ่ง แต่คราวนี้ ให้ใช้พารามิเตอร์ -Debug เพื่อแสดงข้อมูลเพิ่มเติมเกี่ยวกับการดำเนินการที่ดำเนินการโดยคำสั่ง
Invoke-SQLCmd -ServerInstance $serverName -Username $adminSqlLogin -Password $password -Query "PRINT 'Hello world'" -Debugสมบูรณ์แบบ! ในผลลัพธ์ด้านล่าง คิวรีจะแสดงผลลัพธ์โดยไม่มีข้อผิดพลาด ป้อน Y แล้วกด Enter เพื่อยืนยันการดำเนินการ
การเชื่อมต่อกับ SQL Server ด้วยรหัสผ่านเป็น SecureString
คุณได้เชื่อมต่อกับเซิร์ฟเวอร์ SQL ของคุณสำเร็จแล้วโดยใช้ตัวแปรที่เก็บข้อมูลประจำตัวของคุณ ซึ่งใช้งานได้อย่างไม่ต้องสงสัย แต่โปรดทราบว่าฮาร์ดโค้ดหรือใส่รหัสผ่านของคุณตามที่เป็นให้กับตัวแปรจะทำให้เกิดความเสี่ยง ทำไมไม่ลองแปลงรหัสผ่านของคุณเป็น SecureString ดูล่ะ
PowerShell นำเสนอโมดูลการจัดการความลับ หากคุณต้องการเข้ารหัสรหัสผ่านของคุณแทนเพื่อเพิ่มความปลอดภัย
เรียกใช้คำสั่งต่อไปนี้เพื่อดำเนินการต่อไปนี้:
ConvertTo-SecureString- แปลงค่าสตริงของตัวแปร$passwordของคุณให้เป็นสตริงที่ปลอดภัยซึ่งจัดเก็บไว้ใน$secStringPasswordNew-Object- สร้างวัตถุ PSCredential ด้วยชื่อผู้ใช้ ($adminSqlLogin) และรหัสผ่าน ($secStringPassword)
# Convert the $password variable's value to SecureString
$secStringPassword = ConvertTo-SecureString $password -AsPlainText -Force
# Create a credential object
$credObject = New-Object System.Management.Automation.PSCredential ($adminSqlLogin, $secStringPassword)ตอนนี้ ให้รันคำสั่งต่อไปนี้เพื่อเชื่อมต่อกับเซิร์ฟเวอร์ SQL ของคุณและเรียกใช้คำสั่งเดียวกันในการพิมพ์ข้อความ Hello world คราวนี้ ให้ใช้ออบเจ็กต์ข้อมูลรับรองที่คุณสร้างในขั้นตอนก่อนหน้า ($credObject) เพื่ออนุญาตการเชื่อมต่อ
Invoke-SQLCmd -ServerInstance $serverName -Credential $credObject -Query "PRINT 'Hello world'" -Debug
การเชื่อมต่อกับ SQL Server ในฐานะผู้ใช้ Windows
เมื่อทำการสืบค้นข้อมูล โดยทั่วไปคุณจะต้องดำเนินการคำสั่งด้วยสิทธิ์และระดับการเข้าถึงที่เหมาะสม สมมติว่าคุณต้องรันคำสั่งของคุณในฐานะผู้ใช้ Windows หากเป็นเช่นนั้น คุณต้องเปิดเซสชัน PowerShell ใหม่ในฐานะผู้ใช้ Windows นั้นๆ
หากต้องการสืบค้นข้อมูลในฐานะผู้ใช้ Windows โดยไม่ต้องอาศัยพารามิเตอร์ -Username:
เรียกใช้คำสั่งต่อไปนี้เพื่อเริ่มงานพื้นหลัง PowerShell (Start-Job) ภายใน -ScriptBlock
งานเบื้องหลังรันไปยังข้อมูล -Query จากตาราง sys.databases ในฐานะผู้ใช้ Windows () ที่ได้รับการตรวจสอบสิทธิ์ด้วยข้อมูลรับรอง
Start-Job -ScriptBlock { `
Invoke-SQLCmd -ServerInstance $serverName -Query "select * from sys.databases"`
} -Credential (Get-Credential -Credential <USERNAME>)จดชื่อของงาน (เช่น งาน20) ดังที่แสดงด้านล่าง คุณจะต้องมีชื่องานเพื่อตรวจสอบงานในขั้นตอนต่อไปนี้
ตอนนี้ รันคำสั่งด้านล่างเพื่อรับ (Get-Job) และรับ (Receive-Job) งานพื้นหลัง PowerShell ทั้งหมด ตรวจสอบให้แน่ใจว่าคุณเปลี่ยน ด้วยชื่องานเดียวที่คุณจดบันทึกไว้ในขั้นตอนก่อนหน้า
พารามิเตอร์ -Keep ช่วยให้คุณสามารถดึงข้อมูลสตรีมที่รวบรวมได้อีกครั้งหลังจากการดูครั้งแรก โดยพิมพ์ในรูปแบบตาราง (Format-Table)
Get-Job -Name <jobName> | Receive-Job -Keep | Format-Tableการสร้างและการสืบค้นข้อมูลจากฐานข้อมูล
เนื่องจากขณะนี้คุณสามารถเชื่อมต่อกับเซิร์ฟเวอร์ SQL ของคุณได้ จึงถึงเวลาเริ่มทำงานอย่างลึกซึ้งยิ่งขึ้นด้วยพารามิเตอร์ของคำสั่ง Invive-SQLCmd ขั้นแรก คุณจะต้องเชื่อมต่อกับฐานข้อมูลเฉพาะและสร้างตารางที่มีข้อมูลที่จะสืบค้น
1. รันคำสั่งต่อไปนี้เพื่อตั้งชื่อฐานข้อมูลให้เป็นตัวแปร ($databaseName) และรัน -Query เพื่อยืนยันการเข้าถึงฐานข้อมูล
# Add a new variable for the database name
$databaseName = "EmployeesDB"
# Run a query to confirm the access
Invoke-SQLCmd -ServerInstance $serverName `
-Database $databaseName `
-Credential $credObject `
-Query "select Count(*) from sys.tables"แบบสอบถามจะส่งกลับจำนวนตารางที่สร้างขึ้นบนฐานข้อมูล ดังที่แสดงด้านล่าง:
2. ถัดไป รันคำสั่งต่อไปนี้เพื่อรัน -Query เพื่อ CREATE TABLE ที่เรียกว่า EmployeeData และ INSERT สองรายการ (บันทึก)
คำสั่งเหล่านี้ไม่มีเอาต์พุต แต่สร้างตารางที่มีคอลัมน์ชื่อ EmployeeID, EmployeeName และ EmployeeHireDate
$query = @"
CREATE TABLE EmployeeData (
EmployeeID int,
EmployeeName varchar(255),
EmployeeHireDate varchar(255)
);
INSERT INTO EmployeeData(EmployeeID, EmployeeName, EmployeeHireDate)
VALUES (1, 'Victor', '2023-01-01');
INSERT INTO EmployeeData(EmployeeID, EmployeeName, EmployeeHireDate)
VALUES (2, 'Manuel', '2023-01-01');
"@
Invoke-SQLCmd -ServerInstance "$serverName" `
-Database $databaseName `
-Credential $credObject `
-Query $query3. หลังจากสร้างตารางแล้ว ให้รันคำสั่งด้านล่างเพื่อดึงข้อมูลจากตารางที่สร้างขึ้นใหม่ (EmployeeData) ข้อมูลจะถูกจัดเก็บไว้ใน $EmployeesDB ซึ่งเป็นออบเจ็กต์ PowerShell ที่คุณสามารถเข้าถึงและจัดการได้เหมือนกับออบเจ็กต์ PowerShell อื่นๆ
คำสั่งนี้ไม่สร้างเอาต์พุต แต่คุณจะเข้าถึงข้อมูลในขั้นตอนต่อไปนี้
$EmployeesDB = Invoke-SQLCmd -ServerInstance "$serverName" `
-Database $databaseName `
-Credential $credObject `
-Query "SELECT * FROM Employees;"คุณได้รับข้อผิดพลาดหรือไม่? ข้อผิดพลาดเกิดขึ้น ณ จุดหนึ่งซึ่งคุณต้องเรียนรู้วิธีจัดการ และด้านล่างนี้คือข้อผิดพลาดบางส่วน:
- ข้อผิดพลาดด้านล่างปรากฏขึ้นเนื่องจากจำเป็นต้องแก้ไขชื่อตารางที่สอบถาม แก้ไขชื่อตารางที่ระบุจาก
พนักงานเป็นEmployeeDataและแบบสอบถามจะสำเร็จ
- บางทีคุณอาจต้องการยกเลิกการดำเนินการค้นหาทันทีหลังจากได้รับข้อผิดพลาด หากเป็นเช่นนั้น ให้ใช้พารามิเตอร์
-AbortOnErrorเช่นเดียวกับในภาพหน้าจอด้านล่าง คุณจะเห็นข้อมูลเกี่ยวกับข้อผิดพลาด
- หรือคุณสามารถระงับข้อความแสดงข้อผิดพลาดไม่ให้ปรากฏขึ้นด้วยพารามิเตอร์
-OutputSqlErrorsที่มีค่า$falseดังที่แสดงด้านล่าง โปรดทราบว่าพารามิเตอร์นี้ยอมรับเฉพาะค่าบูลีนเท่านั้น
4. สุดท้าย เรียกใช้คำสั่ง foreach ด้านล่างเพื่อเข้าถึงและส่งออกข้อมูลในออบเจ็กต์ $EmployeesDB ไปยังคอนโซล PowerShell ของคุณ
# Print the queried information from EmployeesDB
foreach ($Employee in $EmployeesDB) {
Write-Output "Employee ID: $($Employee.EmployeeID)"
Write-Output "Employee Name: $($Employee.EmployeeName)"
Write-Output "Employee Hire Date: $($Employee.EmployeeHireDate)"
Write-Output ""
}ดังที่คุณเห็นด้านล่าง ด้วยคำสั่ง Write-Output ข้อมูลที่ดึงออกมาจะถูกพิมพ์ไปยังคอนโซล
การแทรกข้อมูลลงในฐานข้อมูลจากไฟล์ SQL
การแทรกและสืบค้นข้อมูลสำเร็จรู้สึกดีใช่ไหม แต่เมื่อคุณต้องแทรกหลายรายการ การเขียนคิวรีสำหรับแต่ละรายการอาจเป็นเรื่องที่น่าเบื่อ แต่ไม่ต้องกังวล พารามิเตอร์ InputFile พร้อมด้วยคำสั่ง Inrigg-SQLCmd จะมีประโยชน์มาก
แทนที่จะเขียนแบบสอบถามด้วยตนเอง ให้ใช้ไฟล์ SQL ที่มีแบบสอบถาม INSERT หนึ่งรายการและค่าหลายค่าของแต่ละรายการ ดังนี้:
1. เปิดตัวแก้ไขข้อความ/โค้ดที่คุณต้องการ เติมข้อความค้นหาต่อไปนี้ลงในไฟล์ และบันทึกเป็น run.sql โปรดสังเกตด้านล่างว่าคุณจะต้องเน้นไปที่การเขียนค่าสำหรับแต่ละรายการเท่านั้น
ข้อความค้นหาด้านล่างจะแทรกพนักงานใหม่ลงในตาราง EmployeeData ของคุณ
INSERT INTO EmployeeData (
EmployeeID,
EmployeeName,
EmployeeHireDate
)
VALUES
(
3,
'Jhon',
'2023-01-02'
),
(
4,
'Mike',
'2023-01-02'
),
(
5,
'Peter',
'2023-01-02'
);2. ถัดไป รันคำสั่งต่อไปนี้ ซึ่งไม่มีเอาต์พุต แต่รันการสืบค้นจากไฟล์ SQL ของคุณ (C:\run.sql)
$EmployeesDB = Invoke-SQLCmd -ServerInstance "$serverName" `
-Database $databaseName `
-Credential $credObject `
-InputFile "C:\run.sql"3. ตอนนี้ ให้รันคำสั่ง foreach ด้านล่างเพื่อส่งออกข้อมูลจากตาราง EmployeesDB ของคุณ
foreach ($Employee in $EmployeesDB) {
Write-Output "Employee ID: $($Employee.EmployeeID)"
Write-Output "Employee Name: $($Employee.EmployeeName)"
Write-Output "Employee Hire Date: $($Employee.EmployeeHireDate)"
Write-Output ""
}ด้านล่างนี้ คุณสามารถดูข้อมูลจากตาราง EmployeesDB ของคุณ รวมถึงข้อมูลพนักงานที่แทรกใหม่สามรายการผ่านไฟล์ run.sql
หากคุณต้องการระบุจำนวนวินาทีก่อนที่แบบสอบถามจะหมดเวลา ให้ใช้พารามิเตอร์
-QueryTimeoutดังที่แสดงด้านล่าง ตามค่าเริ่มต้น เมื่อคุณเรียกใช้คำสั่งInvoke-SQLcmdโดยไม่มีพารามิเตอร์การหมดเวลา แบบสอบถามจะไม่หมดเวลา พารามิเตอร์-QueryTimeoutระบุว่าคำสั่งต้องรอนานแค่ไหนเพื่อให้คำขอ SQL ดำเนินการเสร็จสิ้น
Invoke-SQLCmd -ServerInstance "$serverName" `
-Database $databaseName `
-Credential $credObject `
-InputFile "C:\run.sql" `
-QueryTimeout 5การจัดการผลลัพธ์แบบสอบถาม
โดยรวมแล้ว คุณได้เห็นแล้วว่าคำสั่ง Invoke-SQLCmd ของ PowerShell มีความหลากหลายและมีประสิทธิภาพเพียงใดสำหรับการเรียกใช้คำสั่ง SQL และสคริปต์ แต่ทราบหรือไม่ว่าคุณสามารถปรับแต่งเอาต์พุตของคำสั่ง Invoke-SQLCmd ให้เหมาะกับความต้องการเฉพาะของคุณได้ ใช่ โดยการต่อท้ายพารามิเตอร์ -OutputAs
คุณสามารถรวมเอาต์พุตที่คุณกำหนดเองเข้ากับคำสั่งและโมดูล PowerShell อื่นๆ เพื่อทำให้งานการจัดการฐานข้อมูลของคุณเป็นแบบอัตโนมัติและปรับปรุงประสิทธิภาพได้
หากต้องการดูวิธีจัดการเอาต์พุตคิวรี ให้ทำดังนี้
1. เรียกใช้คำสั่งต่อไปนี้เพื่อ -Query ข้อมูลจากตาราง EmployeeData และจัดเก็บเอาต์พุตเป็น DataRows ไปยัง $EmployeesDB. พารามิเตอร์นี้ช่วยให้คุณแสดงผลลัพธ์การสืบค้นเป็นรูปแบบที่เหมือนตาราง
คำสั่งนี้ไม่ได้พิมพ์เอาต์พุตไปยังคอนโซล แต่คุณจะตรวจสอบข้อมูลที่สืบค้นดังต่อไปนี้
# Store data in a variable
$EmployeesDB = Invoke-SQLCmd -ServerInstance "$serverName" `
-Database $databaseName `
-Credential $credObject `
-Query "SELECT * FROM EmployeeData;" `
-OutputAs DataRows2. ถัดไป เรียกตัวแปร $EmployeesDB เพื่อตรวจสอบข้อมูลที่เก็บไว้
<รหัส>$พนักงานDB
หรือเพิ่มจุดต่อท้าย แล้วกด TAB ต่อไปเพื่อวนดูฟังก์ชันต่างๆ ที่คุณอาจใช้ ดังที่แสดงด้านล่าง
3. เรียกใช้คำสั่งเดียวกันด้านล่างเหมือนกับในขั้นตอนก่อนหน้า แต่คราวนี้ ให้แทนที่ค่า DataRows ด้วย DataSet ค่าพารามิเตอร์นี้ช่วยให้คุณสามารถบันทึกผลลัพธ์การสืบค้นลงในออบเจ็กต์ที่มีรูปแบบ XML ($xmlEmployeeData)
เช่นเดียวกับตัวอย่างก่อนหน้านี้ คำสั่งนี้ไม่ได้สร้างเอาต์พุตไปยังคอนโซล แต่คุณจะต้องตรวจสอบเอาต์พุตในขั้นตอนถัดไป
$xmlEmployeeData = Invoke-SQLCmd -ServerInstance "$serverName" `
-Database $databaseName `
-Credential $credObject `
-Query "SELECT * FROM EmployeeData;" `
-OutputAs DataSet4. สุดท้าย ให้เรียก $xmlEmployeeData และตรวจสอบวัตถุที่มีรูปแบบ XML
$xmlEmployeeDataหรือเรียกใช้คำสั่งด้านล่างเพื่อดูข้อมูลในรูปแบบตาราง (.Tables) เช่นเดียวกับในขั้นตอนที่สอง
$xmlEmployeeData.Tablesบทสรุป
ตลอดบทช่วยสอนนี้ คุณได้ทำตามขั้นตอนแรกเพื่อเรียนรู้คำสั่ง PowerShell Inrigg-SQLCmd คุณได้เชื่อมต่อกับฐานข้อมูล SQL สำเร็จแล้ว เรียกใช้คำสั่งต่าง ๆ และจัดการผลลัพธ์ผลลัพธ์
การทำงานภายใน GUI เช่น SQL Server Management Studio ทำให้การจัดการฐานข้อมูลเป็นเรื่องที่น่าท้อใจน้อยลงอย่างแน่นอน แต่ถ้าคุณรู้จักคำสั่ง Invoke-SQLCmd เหมือนอย่างหลังมือ คุณสามารถทำงานให้สำเร็จได้มากขึ้นด้วย PowerShell
ทำไมไม่ทำให้งานประจำวันเป็นแบบอัตโนมัติด้วยคำสั่ง Inrigg-SQLCmd หรือดึงข้อมูลจากเซิร์ฟเวอร์ SQL ของคุณเพื่อจัดการกับ PowerShell ไม่ว่าคุณจะเลือกขั้นตอนใด ขอให้โชคดีในการเดินทางสู่การเป็นผู้เชี่ยวชาญ PowerShell หรือ DBA!














