SQL Server storage review using PowerShell

Posted in: Microsoft SQL Server, Technical Track

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

What Output to Expect?

Below is a sample output that you can expect from the script.

StorageReviewSample

Upgrades


I will be working on a v2.0 of this script that will also try and summarize all the findings and make recommendations. I don’t have an ETA yet but it will be available as soon as I am done writing it.

 

When you need to do a similar task more than once you should always think about automating it.
email

Author

Interested in working with Sandeep? Schedule a tech call.

About the Author

Project Engineer at Pythian
This Guy has done stuff. Sandeep stands at the forefront of the fastest moving technology trend: Cloud Services & DevOps. He’s spent the past seven years evangelizing from a role in database administration to trying to automate everything using PowerShell to doing some stuff in DevOps to becoming a Solution Architect in AWS, Azure, and GCP. If he is not watching any video tutorials or helping a customer putting off fires then you can find him at the gym trying to lean out.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *