Check what type of storage your SQL Server is attached to

Goal

To group the storage of our sql server in 4 main categories that will help us when seeking for information about the speed of the storage, how and if the storage can be increased.

My way of achieving it

Using powershell.

The easiest and more convenient way was to create an oneliner that we can run in powershell and output the desired information. I don’t know how good will work with all the other hardware configurations since the info that I used to create the oneliner was containing only the following information

Disks

  • NETAPP LUN  Multi-Path Disk Device
  • HP LOGICAL VOLUME SCSI Disk Device
  • PowerDevice by PowerPath
  • HP LOGICAL VOLUME SCSI Disk Device
  • SanDisk SD7TB3Q-256G-1006 ATA Device
  • HGST HTS545050A7E380
  • VMware Virtual disk SCSI Disk Device

Controllers

  • QLogic Fibre Channel Adapter
  • Emulex OneConnect OCe11100, iSCSI Storport Miniport Driver
  • QLogic Fibre Channel Adapter
  • EMS Device Bus
  • Microsoft Multi-Path Bus Driver
  • Smart Array P410i Controller
  • Microsoft Storage Spaces Controller

but I will update the script in time.

The onliner is this

Get-WmiObject Win32_DiskDrive | group-object model | foreach {if($_.Name -like “*virtual*”) {“1. Virtual Machine”} else {Get-WmiObject Win32_SCSIController | group-object name |foreach {switch -Wildcard ($_.Name) {“*smart array*” {“2. Smart Array”}; “*fibre*” {“3. HBA’s”}; default {“4. Buit-in Controllers”}}}}} | group-object $_ | select Name |sort-object Name

Below is a picture of running the script on 3 different servers

groupcontrollers

Leave a Reply

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