SQL Server Storage Review using PowerShell

Problem

There’s no doubt that storage is one of the key components to SQL Server performance and availability for both large and small SQL Server instances. With today’s increases in server and virtual server processing power and large-scale memory support, storage and I/O can easily become the bottleneck for overall system throughput.

To gain the maximum of our storage configuration, we need to ensure that our SQL Server instance is configured as per Storage Best Practices. I previously followed a checklist for ensuring that my file placement is correct, my auto-growth settings are correct, etc. I don’t know about you, but this was boring and monotonous for me. Now, wouldn’t it be amazing to take that checklist and automate the entire SQL Server storage review?


Solution

The PowerShell Script that can be found on my Public Github Repository will evaluate your Instance storage configuration and generate an HTML report for you, highlighting the pressure points in red.

The script already has help associated with it so you can download and simply use help to figure out how to use the script. Alternatively, you can download it to a PS-Scripts folder and provide parameters as shown below.

PS C:\PS-Scripts\Get-SQLStorageReview -computer ServerName -instance ServerName\SQLInstance -report C:\temp\ServerName.html

Note – please ensure that you are on the server while executing the script. The script currently doesn’t support collecting information for multiple servers at once.

Verified on following platforms

  • PowerShell v2.0 and higher versions
  • Microsoft SQL Server 2008 and higher versions
  • Windows Server 2008 and higher versions

Actual script

Click the below link to download the PowerShell Script.

SQL Server Storage Review Script

How to Execute the Script?

I am keeping things simple by keeping everything on desktop, but you can choose the location of your liking.

  • Copy the file to desktop
  • Open PowerShell console as Administrator and set the execution policy to remotesigned
  • Execute the script with required parameters

Sample Report

Click here to see the sample SQL Server Storage Review Report


Get Help

Get-Help .\Get-SQLStorageReview.ps1 -Detailed

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s