PowerShell is an essential tool for SQL Server database administrators looking to streamline their workflow and automate repetitive tasks. When it comes to troubleshooting your SQL Server instances, PowerShell is an essential tool for the DBA toolbox.
As a versatile scripting language and command-line shell, PowerShell enables seamless integration of commands, arguments, variables, and modules with an interface and logic that SQL users would find pleasantly familiar. This blog post will guide you through the basics of PowerShell, demonstrate how to implement simple automation for routine database tasks, and explore advanced automation strategies to optimize your database management.
Feel free to open up a PowerShell Integrated Scripting Environment (ISE) on a test system to run some of the code in this blog post. For the later commands where you would install a module, you may need to be in an administrator window.
The Basics of PowerShell
1. Commands
In PowerShell, commands, also known as cmdlets, are akin to SQL Server stored procedures. Each cmdlet performs a specific operation, offering reusability and efficiency in scripting similar to executing stored procedures to interact with databases. For instance, Get-Service cmdlet can be thought of as retrieving data similar to how a stored procedure fetches data from a database. They can also be easily intuited as they are created with a verb-noun structure as seen below in the examples:
Get-ComputerInfo Clear-Host Get-Service Get-InstalledModule Get-Command
2. Arguments
Arguments in PowerShell serve to refine the functionality of commands, much like parameters do in SQL stored procedures. Using arguments with cmdlets allows for tailored command execution, which is crucial when managing databases—similar to how parameters filter results in SQL queries.
Get-ComputerInfo -Property "*windows*" Get-computerinfo -Property "*windows*","*OS*" Get-ComputerInfo -Property "CsName","OsName","OsLastBootUpTime", "OsVersion", "BiosSMBIOSBIOSVersion", "CsDomainRole"
3. Variables
PowerShell variables store data that can be referenced later in scripts, akin to declaring variables in SQL stored procedures. Similarly to SQL, PowerShell requires the declaration of a variable. However, you do not need to specify the type and they remain available throughout the PowerShell session once used, making them ideal for tasks that require persistent data handling. They are typically defined by a “$” preceding them.
Store a list from an external file and use that list as an argument for other scripts:
Get-Content -Path "C:\Users\Administrator\Desktop\sqllist.txt"
Get-Content -Path "C:\Users\Administrator\Desktop\complist.txt"
$SQLList = Get-Content -Path "C:\Users\Administrator\Desktop\sqllist.txt"
$ComputerList = Get-Content -Path "C:\Users\Administrator\Desktop\complist.txt"
$SQLList
$ComputerList
Invoke-Command -ComputerName $ComputerList -ScriptBlock {
Get-ComputerInfo -Property "CsName","OsName","OsLastBootUpTime", "OsVersion", "BiosSMBIOSBIOSVersion", "CsDomainRole"
This example requires you to create a couple of text files with the test environment server and sql instance names so that we can use it as an argument value in the last script. See picture below:
4. Piping
Piping in PowerShell, represented by the | symbol, passes the output of one cmdlet as the input to another, facilitating streamlined data processing. This feature is particularly useful for chaining work on the same dataset, implementing filters, formats, and other actions.
Formatted output of a few OS metrics for a list of hosts:
Invoke-Command -ComputerName $ComputerList -ScriptBlock {
Get-ComputerInfo -Property "CsName","OsName","OsLastBootUpTime", "OsVersion", "BiosSMBIOSBIOSVersion", "CsDomainRole"
} | Format-Table -AutoSize
5. Modules
PowerShell modules are similar to SQL Server’s concept of including libraries of stored procedures, such as the Ola Hallengren SQL Server Maintenance Solution or the First Responder Toolkit from Brent Ozar. Modules enhance PowerShell by encapsulating a collection of cmdlets, functions, and aliases, broadening the scope of what can be accomplished within scripts. If you haven’t heard of https://dbatools.io/ yet, I highly recommend taking a look. This is by far the best PowerShell module for streamlining the work of DBA’s out there.
Get list of installed modules, code to install and import dbatools:
Get-installedmodule Install-Module dbatools Import-Module dbatools
Basic Automation with PowerShell
1. Simple Reports
For database administrators, PowerShell scripts can automate the generation of reports on database health, usage statistics, or performance metrics, providing regular insights into system status without manual querying. From this example, I can see my third instance hasn’t had maintenance rolled out yet. That can be remediated in a later script.
Check to see which “_MAINT_” jobs exist on an instance:
$SQLinstance ='LABSQL1', 'labsql2', 'labsql3' $jobname= '_MAINT_' Invoke-DbaQuery -SqlInstance $SQLinstance -Query " USE msdb; SELECT @@SERVERNAME AS [Server Name], name, enabled FROM sysjobs where name LIKE '%$jobname%' " | Format-Table -AutoSize
2. Scripted Changes and Fixes
Automate common database maintenance tasks, such as adjusting configuration settings or applying patches across multiple database instances. PowerShell scripts ensure consistency and can reduce the potential for human error in repetitive tasks, as well as save time working through the GUI, configuring the CMS group, etc.
Disable or enable a specific Job, return a check to see if it worked:
$SQLinstance ='LABSQL1', 'labsql2', 'labsql3'
$jobname= '_MAINT_DatabaseIntegrityCheck - USER_DATABASES'
$enable = '0' #1 to enable, 0 to disable
Invoke-DbaQuery -SqlInstance $SQLinstance -Query "
USE msdb;
GO
EXEC msdb.dbo.sp_update_job @job_name = N'$jobname',
@enabled = $enable
GO
USE msdb;
SELECT @@SERVERNAME AS [Server Name], name, enabled FROM sysjobs where name LIKE '%$jobname%'
" | Format-Table -AutoSize
3. One-off Scripts – good for DBA tasks and to troubleshoot SQL Server challenges
Creating scripts for one-off tasks, like modifying database schedules or specific data corrections, ensures accuracy and efficiency, allowing for precise control over database operations. The script below helps me avoid creating the one off schedule in the GUI but forgetting to enable it, delaying the catch up job run until I notice it and enable it for the next night.
Add a one-off schedule to a specific job, return a check to see if it was added:
$SQLInstance = 'LabSQL1'
$OneOffSQLJob = '_MAINT_DatabaseIntegrityCheck - USER_DATABASES'
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true
$getdate = Get-Date -UFormat "%m/%d/%Y"
$ActiveStartDate = Get-Date -UFormat "%Y%m%d" #or you can entered the desired future date using the YYYYMMDD format
Invoke-DbaQuery -SQLInstance $SQLInstance -Query "
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'$OneOffSQLJob', @name=N'One-Off added $getdate',
@enabled=1,
@freq_type=1,
@freq_interval=1,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=$ActiveStartDate,
@active_end_date=99991231,
@active_start_time=230000,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
"
Invoke-DbaQuery -SQLInstance $SQLInstance -Query "
SELECT s.name as Job_Name
, ss.name Schedule_name
, ss.date_created
, next_run_date
FROM msdb.dbo.sysjobs s
LEFT JOIN msdb.dbo.sysjobschedules sj
ON s.job_id = sj.job_id
LEFT JOIN msdb.dbo.sysschedules ss
ON ss.schedule_id = sj.schedule_id
WHERE s.name IN ('$OneOffSQLJob')
"
4. Maintenance Rollouts
Deploying maintenance scripts across various database environments using PowerShell can standardize procedures, ensuring all databases adhere to the same configuration and security standards. I wrote a blog post about how to do this a while ago, you can read more there: https://straightpathsql.com/archives/2023/01/ola-maintenance-from-a-jr-dba/.
5. Automation Tip
I’ve used quite a few SQL scripts in these examples. I want to quickly show you something that will make automation a bit easier for repetitive tasks. The tip is the “Script Action to new query window” function built-in to SQL Server Management Studio. The gist is you can make the changes in the GUI and just before you hit OK, you can go to the top left of the window and use the script to new window and then use that code as the basis of your automation. As always it will require a bit of massaging, as in this example “job_id” could be replaced with “job name” and now you have a standard script to enable the job (as seen above), and more.
Advanced SQL Server DBA Automation Techniques Using PowerShell
1. File Management
PowerShell can automate the creation and management of logs or data export files, facilitating the documentation of database activities or the archiving of important data. In this example, I demonstrate how to export results into a csv file that can be reviewed, or loaded into a database, later.
An example of how to export data to a CSV for later review:
$OutputPath = "C:\StraightPath\Reports"
$OutputFile = 'Example_ManagementServer_'+$(get-date -f MM-dd-yy)
$UpdateSession = New-Object -ComObject Microsoft.Update.Session
$UpdateSearcher = $UpdateSession.CreateupdateSearcher()
$Updates = @($UpdateSearcher.Search("IsHidden=0 and IsInstalled=0").Updates)
$ManagementServer = [PSCustomObject]@{
Servername = $env:computername
SSMSVersion = Get-WmiObject -Class Win32_Product | Where-Object name -like "SQL Server Management Studio" | Sort-Object | Select-Object Version -Last 1
DBAToolsVersion = get-installedmodule -name dbatools | Sort-Object | Select-Object Version -Last 1
WindowsOSVersion = [Environment]::OSVersion | Select-Object Version
MostRecentHotfix = Get-Hotfix | Sort-Object | Select-Object hotfixid -last 1
PendingUpdates = $Updates.count
CollectionDate = $(get-date)
}
$ManagementServer | Export-Csv -Path "$OutputPath\$OutputFile.csv" -NoTypeInformation
(get-content "$OutputPath\$OutputFile.csv") -replace "@{ProductVersion=","" -replace "@{Version=","" -replace "}","" -replace "@{hotfixid=","" | Out-File "$OutputPath\$OutputFile.csv" -Force
2. Email Integration
Automate the distribution of database reports or alerts via email by integrating PowerShell scripts with SMTP services. This helps in keeping stakeholders informed about database status or issues promptly.
To send an email with an enabled database mail profile:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'StraightPath_Example,
@recipients = 'David.seis@straightpathsql.com',
@body = 'The database mail configuration was completed successfully.',
@subject = 'Automated Success Message';
GO
3. Scheduling
Schedule PowerShell scripts using Task Scheduler to perform routine database maintenance tasks, backups, or data integrity checks automatically, ensuring optimal database performance with minimal manual intervention. Similar to the above with the “Script to new window” function in SSMS, Task Scheduler in windows has an “Export task” function. Once you’ve designed a working task scheduler job, export it and integrate it into your rollout scripts in the future. When creating files with PowerShell, you will need to learn how to “escape” certain characters, but that is a straightforward, although sometimes tedious, task.
Example of escaping below:
As you can see, the back-tic (`) is used to “escape” a character. This means that PowerShell will ignore it during the run. This is valuable to us in automation because the files we would create, like this XML file use double quotes, which PowerShell uses as well. By escaping the double quotes inside the file that will be created, the script can create the file rather than us having to do it manually for each rollout.
4. SQL Server Database Load
Use PowerShell to automate job or login copying between instance, or to load data from various sources into your databases, streamlining data management tasks and reducing the load on database administrators.
Code to load the data from an excel file into a staging table, and the code to load it into the production table:
$instance = "labsql1"
$database = "ProactiveTeam"
$OutputPath = "C:\StraightPath\Reports"
$Files = Get-ChildItem -Path $OutputPath | Where-Object Name -like "*ManagementServer*"
foreach ($file in $Files) {
Import-DbaCsv -Path $file -SqlInstance $instance -database $database -schema dbo -AutoCreateTable -Delimiter ','
Invoke-DbaQuery -SQLinstance $instance -database $database -Query "
USE $database
UPDATE JumpBox
SET
Beacon_Version = B.Beacon_Version
, SQL_Sentry_Version = B.SQL_Sentry_Version
, SSMS_Version = B.SSMS_Version
, DBA_Tools_Version = B.DBA_Tools_Version
, SQL_Sentry_Monitoring_Job = B.SQL_Sentry_Monitoring_Job
, Monthly_Proactive_Report_Job = B.Monthly_Proactive_Report_Job
, Password_Expiring_Job = B.Password_Expiring_Job
, Windows_OS_Version = B.Windows_OS_Version
, Most_Recent_Hotfix = B.Most_Recent_Hotfix
, Pending_Updates = B.Pending_Updates
FROM JumpBox J
JOIN [dbo].[$((get-item $file).basename)] B
ON J.Machine_name = B.Machine_Name AND J.Client_ID = B.Client_ID
INSERT INTO Jumpbox (Client_ID, Machine_Name, Beacon_Version, SQL_Sentry_Version, SSMS_Version, DBA_Tools_Version, SQL_Sentry_Monitoring_Job, Monthly_Proactive_Report_Job, Password_Expiring_Job, Windows_OS_Version, Most_Recent_Hotfix, Pending_Updates)
SELECT Client_ID
, Machine_Name
, Beacon_Version
, SQL_Sentry_Version
, SSMS_Version
, DBA_Tools_Version
, SQL_Sentry_Monitoring_Job
, Monthly_Proactive_Report_Job
, Password_Expiring_Job
, Windows_OS_Version
, Most_Recent_Hotfix
, Pending_Updates
FROM [dbo].[$((get-item $file).basename)] B
WHERE B.Machine_Name NOT IN (SELECT Machine_Name From JumpBox)
DROP TABLE [dbo].[$((get-item $file).basename)]
"
}
5. Updating SQL tools
Keep your database tools and external software up to date automatically with PowerShell scripts, ensuring you always use the latest features and security updates without manual oversight.
Quietly and quickly uninstall old versions of SSMS and install the newest version. Note: SSMS must be closed for this to work properly:
$DLDir = "C:\Temp"
If(!(test-path -PathType container $DLDir)) { New-Item -ItemType Directory -Path $DLDir | Out-Null }
Write-Host "SSMS: Enumerating SSMS installs..." -ForegroundColor Green
Invoke-Command -ScriptBlock {
Get-WmiObject Win32_product | Where-Object {$_.name -eq "SQL Server Management Studio"} | ForEach-object {
Write-Host "SSMS: Uninstalling SSMS [Version: $($_.Version)]`..." -ForegroundColor Green
$_.Uninstall() | Out-null
}
}
# Define the download URL and the destination
$ssmsUrl = "https://aka.ms/ssmsfullsetup"
$destination = "$DLDir\ssms_installer.exe"
# Download SSMS installer
$WebClient = New-Object System.Net.WebClient
$webclient.DownloadFile($ssmsUrl, $destination)
Write-Host "SSMS: Downloading newest SSMS Installer..." -ForegroundColor Green
# Install SSMS silently
$install_path = "`"C:\Program Files (x86)\Microsoft SQL Server Management Studio 20`""
$params = " /Install /Passive SSMSInstallRoot=$install_path /quiet"
Write-Host "SSMS: Installing SSMS..." -ForegroundColor Green
Start-Process -FilePath $destination -ArgumentList $params -Wait
Remove-Item $destination
#Write-Host "SSMS: Server restart is required to implement changes fully" -ForegroundColor DarkRed
Invoke-Command -ScriptBlock {
Get-WmiObject Win32_product | Where-Object {$_.name -eq "SQL Server Management Studio"} | ForEach-object {
Write-Host "SSMS: Currently Installed Versions of SSMS [Version: $($_.Version)]`..." -ForegroundColor Green
}
}
Conclusion
For database administrators, PowerShell is a robust tool that enhances efficiency and reliability in database management. From performing simple automation tasks to managing complex database environments and deeper SQL Server troubleshooting, PowerShell equips you with the capabilities to handle an array of challenges efficiently. Embrace PowerShell to transform your database administration processes and achieve new levels of operational excellence.
P.S. If you found this overview helpful, I’ll be presenting these topics along with the script demos at SQL Saturday in Jacksonville on May 4th, 2024. It’s a great opportunity for in-depth learning and networking with fellow database professionals. I hope to see you there!
