SQL Server instance metadata inventory with PowerShell and SMO

The purpose of this article is to introduce a PowerShell script that I wrote and published on GitHub. The script is a quick and practical way to grab instance‑level configuration from a bunch of servers and dump it into CSV files you can actually use. Instead of clicking through SSMS on every box, you run one PowerShell script, let SMO do the heavy lifting, and end up with three inventory files you can filter, pivot, or feed into whatever reporting you already have.

The approach builds on the ideas in a longer and older post, SQL Server Metadata using Powershell ,but this version is aimed at people who just want a reliable tool they can run on a schedule and hand to the rest of the team

---

Scope

The scope of the  PowerShell script in the GitHub repo SQL Instance Metadata Export Script  is intentionally narrow: it focuses on and gathers only instance‑level metadata, and not database or objects level metadata. The goal is a simple inventory you can trust, not a full CMDB.

---

Output Files

The script writes three CSV files, each one covering a different slice of instance‑level metadata across all the servers you pass in.

Info CSV:  Contains attributes from Server.Information (SMO), including version, edition, collation, operating system details, and other identifying properties for each instance.

Settings CSV:  Contains attributes from Server.Settings (SMO), including instance-level configuration values that would otherwise need to be obtained from SSMS or dynamic management views (DMVs).

Config CSV: Contains attributes from Server.Configuration (SMO), providing a view of sp_configure style options, including both ConfigValue and RunValue. The script outputs two rows per instance, identified by ValueType = ConfigValue or RunValue, to distinguish persisted values from those currently in effect..

All three files also include:

• ServerInstance
• HostName
• IPAddress
• NumberOfDatabases

That extra context is there so that when you’re doing baselines, you don’t have to join back to another inventory just to figure out which host you’re looking at.

---

How the script behaves

Under the covers, the script does roughly this:

• Loads the SqlServer PowerShell module so SMO is available.
• Loops through every value you pass into -ServerInstance (or via the pipeline).

• For each instance:

• Connects using Windows auth by default, or SQL auth if you pass -SqlCredential.
• Pulls Server.Information, Server.Settings, and Server.Configuration.
• Builds a wide PSCustomObject for each category using ordered hashtables, so the CSV columns are stable.

• Collects all rows in memory and writes three CSVs once at the end.

---

Usage

Here are the core patterns you’re likely to use day‑to‑day.

Single instance, default file names

.\Export-InstanceMetadata.ps1 -ServerInstance "ProdSql01"

This creates 3 csv files into your current folder:

• SQLInstanceMetadata_Info.csv
• SQLInstanceMetadata_Settings.csv
• SQLInstanceMetadata_Config.csv

Multiple instances, specific output folder

.\Export-InstanceMetadata.ps1 `
    -ServerInstance "SQL01","SQL02\INST1","SQL03" `
    -OutputFolder "C:\SQL\Metadata" `
    -Verbose

Same three files, but every instance you passed in is represented. -Verbose gives you enough noise to see which instances connected cleanly and which ones failed without drowning you in debug.

Custom prefix for per‑environment baselines

.\Export-InstanceMetadata.ps1 `
    -ServerInstance "ProdSql01","ProdSql02" `
    -OutputFolder "C:\SQL\Inventory" `
    -FileBaseName "SQLProd"

Now your files are:

• SQLProd_Info.csv
• SQLProd_Settings.csv
• SQLProd_Config.csv

This makes it easy to keep separate snapshots for Prod, Test, DR, etc. without renaming files after the fact.

Using SQL authentication

$cred = Get-Credential  # SQL login
.\Export-InstanceMetadata.ps1 `
    -ServerInstance "VendorHostedSql" `
    -SqlCredential $cred `
    -OutputFolder "C:\SQL\Metadata"

Same behavior, but you’re not relying on domain auth. Handy for vendor‑hosted or isolated environments where Windows auth isn’t an option.

Feeding instance names from a file

Get-Content .\instances.txt |
    .\Export-InstanceMetadata.ps1 `
        -OutputFolder "C:\SQL\Metadata" `
        -FileBaseName "SQLInventory"

If you already manage your SQL Servers list somewhere (CMS, text file, whatever), this lets you reuse that list instead of editing the script every time.

---

Why it’s useful for senior DBAs

This script is mainly about giving you cheap, repeatable visibility:

• Baselines: Capture a snapshot before and after a change window and stick the CSVs in source control or with the CAB artefacts.

• Drift: Load the Config CSV into Power BI or Excel and you can spot weird max server memory, max degree of parallelism, or other oddball settings across the organization in a few clicks.

• Inventory Questions: “How many instances are still on 2016?” “Where are all the Developer Edition installs?” These become filters instead of ad‑hoc T‑SQL in random RDP sessions.

---

Import CSVs into one Excel workbook with separate tabs

There is no official Microsoft PowerShell module for Excel files, except for using the Excel COM Object. Therefore, I have used CSV files as the output format so far.

However, if you prefer, you can use PowerShell code to combine the 3 separate CSVs into a single Microsoft Excel workbook with tabs. This approach uses a PowerShell module called ImportExcel, which is specifically designed for working with Excel files. Although it is a third-party module and not an official Microsoft product, it is widely adopted and recommended by Microsoft for PowerShell automation.

Install-Module ImportExcel -Scope CurrentUser

# Import CSVs into one Excel workbook with separate tabs
Import-Csv "C:\Path\SQLInstanceMetadata_Config.csv" | Export-Excel "C:\Path\SQLServerMetaData.xlsx" -WorksheetName "Config"
Import-Csv "C:\Path\SQLInstanceMetadata_Info.csv" | Export-Excel "C:\Path\SQLServerMetaData.xlsx" -WorksheetName "Info"
Import-Csv "C:\Path\SQLInstanceMetadata_Settings.csv" | Export-Excel "C:\Path\Documents\SQLServerMetaData.xlsx" -WorksheetName "Settings"

---

See also

• SQL Server Metadata using Powershell
• Powershell script to get list of databases on a server
• Powershell one liner to export data directly from SQL Server to Excel
• GitHub repository script

Track SQL Server Configuration Changes Using the Error Log

If you work with SQL Server long enough, you or someone will eventually want to know, “Did anyone change that setting?” And if you cannot prove it one way or the other, well you might just be in luck. 

The good news is that SQL Server already tracks configuration changes in a few places. You just need to know where to look and what each option can (and cannot) do for you. 

The easiest option: standard reports

The simplest place to start is the SSMS GUI. Right‑click the server → Reports → Standard Reports, and look for the one that shows configuration history.

This standard report reads from SQL Server’s default trace, which being the "default" setting, would have been already running. That means you don't need to setup and enable it. It is just there ready to view, unless of course it was purposely disabled/stopped.

However, the default trace comes with an important limitation: it is small by design. It is typically about 20 MB total, spread across up to 5 rollover files. You cannot change that size, so older entries will eventually roll off as new activity fills the trace.

If you want your own custom tracking, Extended Events is usually a better choice than building a new trace. Microsoft has largely moved the ecosystem toward Extended Events, and that’s where you should focus any new work.

When you need better control: Use Auditing and Extended Events features

SQL Server Auditing and Extended Events can also capture configuration changes, but there is one big catch: you must set them up before the change happens.

If you only think about tracking after the fact, auditing and Extended Events won’t help you. Once a setting is changed, they cannot retroactively tell you when it happened (unless they were already recording).

That is why many DBAs still start with the default trace or the SQL Server error log. Those options are already there, and they can answer the question right now, even if you did not plan for it.

The SQL Server error log

Another powerful, often overlooked place to check is the SQL Server error log. Every time a configuration option is changed with sp_configure, SQL Server usually writes a message into the error log that includes the phrase “Configuration option”.

Because of that, the error log becomes a built‑in change history for key settings, as long as the log files are still present. You can search this log in several ways:

• In SSMS, by opening the SQL Server logs.

• In a text editor, by opening the .log files directly.

• Programmatically, using sys.xp_readerrorlog from T‑SQL.

The sys.xp_readerrorlog procedure is especially handy because it lets you search and filter the log without leaving SQL Server. Microsoft documents it here: sp-readerrorlog

Please note: You may notice that Microsoft documents sp_readerrorlog as the supported way to read SQL Server error logs, while this post uses sys.xp_readerrorlog instead. In practice, both procedures read the same underlying log files and return the same core information; the main difference is that sp_readerrorlog is a wrapper that first checks your permissions and then calls xp_readerrorlog under the hood. The supported version also accepts a slightly simpler parameter list, while xp_readerrorlog exposes extra parameters such as a start date, end date, and sort order, which makes it more flexible for ad‑hoc troubleshooting and filtered searches. Because of this extra flexibility, many DBAs, including examples in this blog post, continue to rely on xp_readerrorlog in day‑to‑day work, even though it remains an undocumented extended stored procedure.

---

Examples: 

Here is a basic example that searches the current SQL Server error log for configuration change:

USE master;
GO
EXEC sys.xp_readerrorlog
    0,          -- LogNumber: 0 = current error log
    1,          -- LogType: 1 = SQL Server error log
    N'Configuration option', -- SearchString1: text to search for
    NULL,       -- SearchString2: additional text filter (none)
    NULL,       -- StartTime: no start‑time filter
    NULL,       -- EndTime: no end‑time filter
    N'desc';    -- Sort order: newest entries first

This returns all entries in the current log file that contain Configuration option, ordered newest first.

If you want to narrow it by time, you can pass a start‑time filter:

USE master;
GO
EXEC sys.xp_readerrorlog
    0,          -- LogNumber: 0 = current error log
    1,          -- LogType: 1 = SQL Server error log
    N'Configuration option', -- SearchString1: text to search for
    NULL,       -- SearchString2: additional text filter (none)
    '2026-05-10 16:00:00', -- StartTime: start datetime filter
    NULL,       -- EndTime: no end‑time filter
    N'desc';    -- Sort order: newest entries first

Now you only see configuration‑change lines from that specific point onward.

You can also search for a specific setting by adding a second text filter. For example, this looks for configuration messages that also contain the word Optimize:

USE master;
GO

EXEC sys.xp_readerrorlog
    0,          -- LogNumber: 0 = current error log
    1,          -- LogType: 1 = SQL Server error log
    N'Configuration option', -- SearchString1: primary text to search for
    N'Optimize', -- SearchString2: secondary text to search for
    NULL,       -- StartTime: no start‑time filter
    NULL,       -- EndTime: no end‑time filter
    N'desc';    -- Sort order: newest entries first

These patterns make it easy to quickly corner the exact change you are investigating.

What happens after a restart

Here is an important detail that trips people up: every time SQL Server restarts, it starts a new error log file. The previous file is renamed to errorlog.1, the one before that becomes errorlog.2, and so on.

By default, SQL Server keeps up to 7 of these archived log files, but you can configure it to keep more (up to 99). If needed, you can also manually rotate the log without restarting by running sp_cycle_errorlog. This is useful if you want to “start fresh” and still keep older logs around.

Because of this cycling behavior, older configuration changes may move into errorlog.1, errorlog.2, and so on. The good news is that sys.xp_readerrorlog can read those archived logs too. The bad news is that the oldest logs will eventually be removed unless you copy that data somewhere else.

So while the error log is a great tool for recent history, it is still not a permanent, long‑term auditing solution.

Usefulness beyond configuration changes

sys.xp_readerrorlog is not only useful for tracking configuration changes. You can also use it to search for errors, warnings, startup messages, and other events.

For example, say you want to check for severity 16 errors containing a specific error number over the last 24 hours:

USE master;
GO

DECLARE @start_time datetime = GETDATE() - 1;

EXEC sys.xp_readerrorlog
    0,          -- LogNumber: 0 = current error log
    1,          -- LogType: 1 = SQL Server error log
    N'Severity: 16', -- SearchString1: primary text to search for
    N'41145',  -- SearchString2: secondary text to search for
    @start_time, -- StartTime: start datetime filter (yesterday)
    NULL,       -- EndTime: no end‑time filter
    N'desc';    -- Sort order: newest entries first

This kind of pattern is very handy when troubleshooting a server and you want to quickly focus on a specific kind of error.

---

The bottom Line:

Here is how I usually think about this in practice:

Need to do a quick check?    Use the Standard Reports in SSMS. They read from the default trace and give you an easy, visual way to see recent changes.

Need more detail, but don’t want to set up a new system?  Use sys.xp_readerrorlog against the SQL Server error log. It is fast, already available, and works great for recent configuration changes.

For long‑term auditing and compliance: Use the Extended Events or SQL Server Auditing. These tools require planning and setup, but they are the right choice if you need reliable, long‑lasting change history.

So if someone tells you that a setting was never touched, check the default trace first. If that has already rolled off, search the error log. Only then, if you need a permanent answer, invest in a more structured auditing solution.

Beware of Generic SQL Server License Keys (What to Use Instead)

I (think) realize that content here is probably more applicable to mid-to-large organizations than small enterprises or solo DBAs. Here's why:

• Regular compliance reporting to Microsoft (VLSC/EA) is standard for Enterprise Agreements
• They often utilize scanning tools like Flexera One, SAM etc. for core usage across VM farms
• Software Assurance and virtualization rights management across multiple hosts
• Audit preparedness with dedicated licensing specialists or consultants
• Focus on Always On clusters and high-VM-density scenarios
• Most importantly, they have the budget to cover these licensing costs, and they often pass them on to customers with a healthy markup!

---

Microsoft products, including SQL Server, provide publicly available generic keys for evaluation or trial use (these are different from the free evaluation versions offered through the official Microsoft Evaluation Center).. 

These generic keys never expire, that's the trap. They give you fully functional production SQL Server forever... without any licensing proof. Your cores run happily until audit day.

They're legit for testing, 100% illegal for production. Don't be the DBA who turns 'temporary' into 'audit nightmare.

Microsoft never publishes these exact keys in docs, but they do get leaked online through channels like evaluation ISOs, partner training, and OEM installers. Scammers harvest them and sell 'free trial' as 'lifetime production licenses.

Search Google for SQL Server Public License Keys

---

It can be very tempting to grab generic, public license keys you find online when you need to spin up a SQL instance fast. For example 2Q48Q-PB48J-DRCVN-GB844-X2H4Q for SQL Server 2022 works like a charm. Hey, we're legit and honest, we'll report those cores in our next VLSC submission. No big deal, right? 

These setup keys still install SQL Server perfectly. Something like HX782-X7RHN-BVHGT-8HB24-2KGXG for SQL Server 2025  floating around sketchy sites or message boards will activate your install, but they don't prove any core entitlements. It's just a public setup key. It'll get you running, but it leaves you non-compliant.

There are scam sites that sell these SQL Server licenses using public keys for as little as $5-$500 as "lifetime licenses." They work, until Microsoft audit compliance/telemetry (not same as SQL Server Telemetry - CEIP Service) flags them and your scanned core usage doesn't match any legitimate VLSC entitlements.

Even worse, some shady ISOs have these keys pre-baked. If you download and use that ISO, it auto-fills everything. Just check "I have Software Assurance," click Next... and dig your into even deeper hole..

Microsoft's compliances/telemetry picks these up, and when your scanned usage reports don't match legitimate license keys/entitlements from your Volume Licensing Service Center (VLSC), audits get messy. The right move is to login into the Microsoft M365 admin center , and downloading edition-specific keys tied directly to your EA. They come as a CSV with product IDs that align perfectly with what you're entitled to.

Ideal solution, is to do regular scanning plus VLSC reporting to get audit-proof. The free scanning Microsft MAP Toolkit days are gone (unsupported, but you can still download it from Microsoft site), so Flexera One (not cheap) or something similar is often being used for enterprise needs. But VLSC keys? That's your real compliance bedrock.

Bottom line: Never use generic keys on production servers.

PowerShell Remoting for SQL DBAs: WinRM + SSH Guide (Updated 2026)

Originally published in 2023, this post never made it into Google's index, so I'm republishing it with key updates for PowerShell 7+ cross-platform support, HTTPS WinRM best practices (port 5986), and other modern enhancements.

With that said, PowerShell remoting lets you run commands and scripts on remote computers as if you were sitting in front of them. For DBAs and system admins, it's one of the most powerful tools for automating routine management across many servers. 

But I wouldn't call it a core or required skill for DBAs. Essential skills like query tuning, backup/restore, Always On AGs, and HA/DR rank higher. PowerShell remoting matters more for enterprise DBAs managing hundreds of servers, but it's not a must-have for everyone.

This guide introduces PowerShell remoting using both WinRM (classic Windows remoting) and SSH (cross­-platform), with some examples, security notes, and troubleshooting tips.

---

What PowerShell Remoting Is

PowerShell remoting is a mechanism that sends PowerShell commands and scripts from your machine to one or more remote machines, executes them there, and returns results to you.

There are two main transports you will see today:

WinRM-based remoting:

• Uses Windows Remote Management (WinRM), HTTP/HTTPS on ports 5985/5986.
• Built into Windows PowerShell 2.0+ and supported in PowerShell 7+ on Windows.

SSH-based remoting:

• Uses OpenSSH (TCP port 22 by default).
• Cross­-platform: Windows, Linux, macOS.
• Supported natively in PowerShell 7+.

Both approaches allow:

• 1 to 1 interactive sessions.
• 1 to many fan­out (run once, execute on many machines).
• Session reuse for multiple commands, which is important for performance.

Why Even Care About SSH Remoting?

Windows-only SQL Server DBAs usually stick with WinRM + PowerShell ISE, and that works great in domain environments. No argument there.

But here's why PowerShell 7+ SSH deserves a look, even for pure Windows shops:

• Port 22 sails through firewalls (unlike WinRM's 5985/5986)  
• Perfect for jump boxes and Azure Arc SQL  
• No GPO fights over WinRM config  
• PowerShell 7+ brings faster module loading and true parallel `Invoke-Command`, but VS Code's learning curve can feel steep coming from SSMS's polished GUI.

The Reality: SQL Server on Linux is real now, accordingly to some resources, ~20-30% of new deployments are Azure SQL, containers, RHEL/Ubuntu etc.. 

Bottom line: Use WinRM/PowerShell 5.1 for classic Windows domains. SSH/PS7+ for everything else. Use what matches your environment.

---

Requirements and Versions

For PowerShell versions 5.1 and below

• Installed by default on Windows Server and Windows 10.
• Uses WinRM remoting by default.

For PowerShell versions 7 and up

• Cross­-platform and installed separately.
• Supports both WinRM and SSH remoting.
• Has its own remoting endpoints and configuration, independent from Windows PowerShell.

Additionally, for WinRM-based remoting to work:

• WinRM must be enabled and configured on the remote computer.
• The firewall must allow WinRM (HTTP/HTTPS) traffic.
• Authentication must be possible (domain trust, certificates, or explicit credentials).
• Name resolution must work (DNS/hosts), or you must use IP plus TrustedHosts.

---

Enabling WinRM Remoting on Servers (Windows Server)

On Windows Server versions, WinRM is usually installed and often already configured, especially in domain environments. To explicitly configure:

# Run in an elevated PowerShell session on the server
Enable-PSRemoting -Force

What this does:

• Starts and configures the WinRM service.
• Creates the default PowerShell endpoint.
• Configures HTTP listener (port 5985).
• Creates firewall rules for WinRM.

If your organization enforces WinRM policies via Group Policy, those settings may override this configuration. In that case, work with your AD/GPO administrators.

Enabling WinRM Remoting on Clients (Windows 10/11)

On Windows 10/11, WinRM is present but often disabled by default:

# Run as Administrator
Enable-PSRemoting -Force

If this fails or is restricted, you can also check with:

winrm quickconfig

This tool can help configure the WinRM service level, but PowerShell remoting endpoints still require PowerShell configuration.

Quick Test: Ping a Remote Endpoint

Before running real commands, verify that remoting is working on the remote computer:

Test-WsMan -ComputerName SERVER1

---

Basic WinRM Remoting Examples

Example 1: Run a Simple Command

Invoke-Command -ComputerName SERVER1 -ScriptBlock {
    Get-Service -Name WinRM
}

-ComputerName can accept a single name or an array of names. -ScriptBlock contains the code that runs on the remote machine.

Example 2: Run on Multiple Servers

$servers = 'SERVER1', 'SERVER2', 'SERVER3'

Invoke-Command -ComputerName $servers -ScriptBlock {
    Get-Service -Name Spooler
}

This is one of the biggest wins of remoting: a single command targeting many servers.

Example 3: Use Explicit Credentials

$cred = Get-Credential

Invoke-Command -ComputerName SERVER1 -Credential $cred -ScriptBlock {
    Get-Process
}

This prompts for a username and password, which PowerShell uses to authenticate to the remote system.

Using Persistent Sessions

Creating and reusing a PowerShell session (PSSession) is more efficient than setting up a new connection for every command. It is also necessary for some workflows, like copying files or maintaining state.

Example 4: Create and Use a PSSession

$session = New-PSSession -ComputerName SERVER1

Invoke-Command -Session $session -ScriptBlock {
    Get-ChildItem C:\Temp
}

# Run another command in the same session
Invoke-Command -Session $session -ScriptBlock {
    Get-Date
}

# Always clean up when done
Remove-PSSession $session

A PSSession keeps the remote environment alive across multiple commands.

Example 5: Copy a File to a Remote Server

$session = New-PSSession -ComputerName SERVER1

Copy-Item -Path "C:\Scripts\Deploy.ps1" `
          -Destination "C:\Admin\Deploy.ps1" `
          -ToSession $session

Remove-PSSession $session

This uses the WinRM channel to transfer the file rather than SMB, which can be useful in locked-down environments.

Example 6: Enter an Interactive Remote Session

Enter-PSSession -ComputerName SERVER1

# Prompt changes to something like:
# [SERVER1]: PS C:\Users\Admin>

# Run commands directly on SERVER1
Get-Process
Get-Service

# Exit the remote session
Exit-PSSession

This is a powerful replacement for RDP in many administrative scenarios.

---

Example 7: Check Database Backup Status Across SQL Servers

# Shows last full backup date for every database on every SQL Server.
$servers =  @("SQL01\Instance01", "SQL02\Instance02", "SQL03\Instance03")

foreach($server in $servers) {
    $status = Invoke-Sqlcmd -ServerInstance $server -Database "msdb" -Query @"
        SELECT 
            database_name AS DatabaseName,
            MAX(backup_finish_date) AS LastFullBackup
        FROM msdb.dbo.backupset 
        WHERE type = 'D'
        GROUP BY database_name
"@

    $status | Select-Object @{Name="SQLServer";Expression={$server}}, 
              DatabaseName, 
              @{Name="LastFullBackup";Expression={$_.LastFullBackup -f "yyyy-MM-dd HH:mm"}}
}

Example 8: Restart Stuck SQL Agent Jobs Remotely

#Finds failed Agent jobs from today, restarts them automatically.
$SQLServerInstance = "SQL01\Instance01"
$failedJobs = Invoke-Sqlcmd -ServerInstance $SQLServerInstance -Database "msdb" -Query @"
    SELECT j.name AS JobName
    FROM msdb.dbo.sysjobs AS j
    INNER JOIN msdb.dbo.sysjobhistory AS h ON j.job_id = h.job_id
    WHERE h.run_status = 0
    AND h.step_id = 0
    AND h.instance_id = 
         (SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
            WHERE job_id = h.job_id)
    AND h.run_date >= CONVERT(INT, CONVERT(VARCHAR(8), GETDATE() - 1, 112));
"@

foreach($job in $failedJobs) {
    Write-Output "Starting Job: $($job.JobName)"
    Invoke-Sqlcmd -ServerInstance $SQLServerInstance -Database "msdb" -Query @"
        EXEC msdb.dbo.sp_start_job @job_name = '$($job.JobName)'
"@
    Write-Output "Started Job: $($job.JobName)"
}

---

Common Issues and Troubleshooting

1. Access Denied

Symptoms:

• Access is denied.
• The client cannot connect to the destination specified in the request.

Checks:

• Are you running the console as Administrator?
• Does the account have permissions to connect via WinRM on the remote machine?
• Are there group policies restricting WinRM or PowerShell endpoints?

2. Network or Name Resolution Problems

Symptoms:

• WinRM cannot complete the operation.
• The client cannot connect to the destination specified in the request.

Checks:

• Test basic reachability:
  

  Test-Connection SERVER1
  Test-WsMan SERVER1

• Check DNS: does the hostname resolve correctly?
• Inspect firewall rules on both client and server.

3. TrustedHosts and Workgroup Machines

On non­domain machines, or when using IP addresses, you may need to configure TrustedHosts:

Set-Item WSMan:\localhost\Client\TrustedHosts -Value "SERVER1,SERVER2" -Force

Use wildcards or ranges only when you understand the security implications.

4. Double-Hop Problem

If a remote session needs to access another network resource (for example, SQL Server or a file share from within the remote script), Kerberos may not delegate credentials by default. Solutions include:

• Using CredSSP (with caution).
• Using Kerberos constrained delegation.
• Using alternate credentials explicitly within the script for downstream access.
• Running the script from a system that has direct access instead of hopping.

---

Security Considerations

Security is critical when enabling remoting.

Key points:

• Prefer HTTPS for WinRM (port 5986) in untrusted or internet­facing scenarios.
• Use Kerberos authentication within a domain whenever possible; it provides mutual authentication and strong security.
• Limit who can use remoting:
  
  • Use groups instead of individual user accounts.
  • Use constrained endpoints or Just Enough Administration (JEA) so users only have the rights they need.
• Be careful with CredSSP: it can solve double­hop issues but introduces credential­delegation risk. Use it only when necessary and within tightly controlled environments.

If you must use HTTP (5985), keep traffic on internal trusted networks and consider IPsec or VPN protections.

---

PowerShell Remoting Over SSH

While WinRM is excellent for Windows domain environments, SSH-based remoting is often easier across platforms and through firewalls.

Requirements

• PowerShell 7 or later on both client and server.
• OpenSSH server configured on the remote host.
• PowerShell 7 set as a subsystem or command in SSH configuration.

On Windows, install OpenSSH server and enable it, then configure sshd_config to allow a PowerShell endpoint, for example:

# In sshd_config (simplified example)
Subsystem powershell C:/Program Files/PowerShell/7/pwsh.exe -sshs -NoLogo -NoProfile

On Windows the sshd_config file is located at C:\ProgramData\ssh\sshd_config.
On Linux/Unix/macOS, the file is located /etc/ssh/sshd_config.

Restart the SSH service after changes:

# Linux/macOS

sudo systemctl sudo systemctl restart sshd

# Windows

Restart-Service sshd

Example 9: SSH Remoting with PowerShell 7

$cred = Get-Credential  # user on the SSH server

$session = New-PSSession `
    -HostName "linux01.contoso.com" `
    -UserName $cred.UserName `
    -SSHTransport

Invoke-Command -Session $session -ScriptBlock {
    $PSVersionTable
}

Remove-PSSession $session

On Windows-to-Windows over SSH, the pattern is the same—just change the host.

SSH remoting allows you to use the same PowerShell syntax to manage Linux and Windows, which is especially attractive for hybrid SQL/Oracle shops.

---

Best Practices for Modern Environments

• Standardize on PowerShell 7+ for new automation where possible, especially in mixed OS environments.
• Use HTTPS WinRM listeners and/or SSH for secure remote management.
• Use PSSessions and run multiple commands per session instead of single-use remoting calls.
• Incorporate logging and auditing via transcript (Start-Transcript) and central logging solutions.
• Start small: test remoting on a lab machine, then roll out to production via GPO or configuration management.

Fix Slow, Bloated MSDB: Purge Old History And Add Missing Indexes

After tempdb, msdb is often the most abused system database, growing unchecked until it tanks your backup reporting and job monitoring.

I've watched MSDB performance degrade across multiple SQL Server instances. It's not optimized out of the box and doesn't get much care, so as it balloons to 100GB+, metadata queries crawl, showing up as top offenders in Activity Monitor.

Another indicator of MSDB performance problems: missing indexes in MSDB showing at the top of the missing indexes DMV results.

In the past, I'd just find and add missing indexes. But MSDB tuning gets often gets overlooked even by the best of us. 

---

I have my own reasons to keep MSDB lean and fast:

• I regularly query MSDB to get job status, backup history, and missing backups (manual + automated)

• Our enterprise backup tool frequently queries the backup history tables, about every 5 minutes. These queries have become resource-intensive and slow, significantly hindering performance on several SQL Servers. Yes, imagine that, your backup tool spends more time querying backup meta data than time it takes to do transaction log backups.

• Bloated MSDBs (50-100GB). While this may concern some, it doesn't bother me per se..

Solution: 1) Index MSDB tables, 2) Purge old history, 3) Configure job history retention.

The missing index DMVs make the first step easy. 

Get-MissingIndexScripts.sql

Here's what they recommend on one of my production servers:

Most out-of-the-box MSDBs would usually show 3-5 missing index recommendations. 

---

Manage MSDB Size Through Purging

I've written a SQL script that complements the indexing strategy by regularly purging old history records from MSDB. It uses SQL Server's built-in system procedures for the heavy lifting, all you specify is @DaysRetention, and older records are automatically removed from key history tables. View/download it from GitHub:

Purge_MSDB_History_Cleanup.sql

---

Configure job history retention

You can configure automated purging of job history tables through the SSMS: SQL Server Agent → Right-click → Properties.

Go to the History page and tweak the values per your needs:

Click OK button at the right-bottom corner to save the changes.

That's it. Add indexes + regular history purging + job retention settings. These three steps transformed my 100GB monsters into snappy, responsive MSDB database..

Questions, comments or tweaks? Drop them in the comments!