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. [code lang="powershell"] PS C:\PS-Scripts>.\Get-SQLStorageReview -computer ServerName -instance ServerName\SQLInstance -report C:\temp\ServerName.html [/code] 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
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.
Share this
Previous story
← Log buffer #546: a carnival of the vanities for DBAs
Next story
Backup strategies in Cassandra →
You May Also Like
These Related Stories
Automate SQL server version and database compatibility report using PowerShell
Automate SQL server version and database compatibility report using PowerShell
Aug 10, 2016
3
min read
SQL server security review using PowerShell
SQL server security review using PowerShell
Jul 26, 2016
20
min read
Powershell error: sqlps.ps1 cannot be loaded because running scripts is disabled on this system
Powershell error: sqlps.ps1 cannot be loaded because running scripts is disabled on this system
Jun 5, 2018
4
min read
No Comments Yet
Let us know what you think