SQL Server Blog

How to Troubleshoot SQL Server With PowerShell.

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.

Troubleshooting SQL Server

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
powershell command line example

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"
powershell commandlets to filter returned data

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:

powershell variables from an external text file

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
Troubleshoot SQL Server with PowerShell

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
PowerShell enable or disable SQL Server jobs

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')
"
SQL Server scripting  in powershell

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.

Automating DBA tasks with PowerShell

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   
Advanced SQL Server DBA automation in Powershell

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
PowerShell send SQL Server dbmail success message

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!

David Seis
Article by David Seis
David Seis is a Support Technician that joined Straight Path in the summer of 2021. His goal is to work in an area that he believes helps others, and to do it to the best of his ability. He loves to help, solve problems, and make things easy for others to understand. Because of this he is lovingly known as a “Good helper” among friends and family. Outside of work, he has a growing passion for cybersecurity, specifically helping protect those who are most vulnerable to fraud, scams, and phishing. He looks forward to learning more about each of his clients, helping minimize issues, and ensure that any problems are solved quickly.

Subscribe for Updates

Name

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This