SQL Server – map drives to disk numbers

Goal

To obtain the disk number of all the drives together with drive size and free space.

My way of achieving it

Using powershell and CIM_StorageExtent.

For a long period now I tried different methods and approaches in order to map the drives in Windows to their respective disk numbers. Today starting from what Granger Godbold said here, https://rvdnieuwendijk.com/2012/05/29/powershell-function-to-get-disk-scsi-lun-number/ , I finally managed to have that information. The class that I used is CIM_StorageExtent. I started with this Get-WmiObject CIM_StorageExtent  and after I filtered the output to not show me the Memory like objects I noticed that maybe the objects there appears in a specific order and I checked my assumption

name|size|freespace
Disk #1, Partition #0|214748364800|
Disk #6, Partition #0|214748364800|
Disk #7, Partition #0|214748364800|
Disk #8, Partition #0|214748364800|
Disk #9, Partition #0|214748364800|
Disk #10, Partition #0|214748364800|
Disk #11, Partition #0|268435456000|
Disk #12, Partition #0|53684994048|
Disk #13, Partition #0|53684994048|
Disk #14, Partition #0|53684994048|
Disk #15, Partition #0|53684994048|
Disk #2, Partition #0|429498826752|
Disk #16, Partition #0|53684994048|
Disk #17, Partition #0|53684994048|
Disk #18, Partition #0|53684994048|
Disk #19, Partition #0|53684994048|
Disk #20, Partition #0|161059176448|
Disk #21, Partition #0|53684994048|
Disk #22, Partition #0|161059176448|
Disk #23, Partition #0|53684994048|
Disk #24, Partition #0|53684994048|
Disk #25, Partition #0|53684994048|
Disk #3, Partition #0|644249288704|
Disk #26, Partition #0|53684994048|
Disk #27, Partition #0|53684994048|
Disk #28, Partition #0|107372085248|
Disk #29, Partition #0|53684994048|
Disk #30, Partition #0|161059176448|
Disk #31, Partition #0|53684994048|
Disk #32, Partition #0|53684994048|
Disk #33, Partition #0|161059176448|
Disk #34, Partition #0|53684994048|
Disk #35, Partition #0|966376030208|
Disk #4, Partition #0|214748364800|
Disk #36, Partition #0|966376030208|
Disk #37, Partition #0|1234814631936|
Disk #38, Partition #0|966376030208|
Disk #39, Partition #0| 1070596096|
Disk #5, Partition #0|214748364800|
Disk #0, Partition #0|209715200000|
Disk #0, Partition #1| 90248839168|
T:\SQLData\Data01\||214480257024
T:\SQLData\Data04\||36030709760
T:\SQLData\Data05\||336353427456
T:\SQLData\Data06\||183024680960
T:\SQLData\Data07\||141081640960
T:\SQLData\Data08\||214481960960
T:\SQLData\Data09\||214639247360
T:\SQLData\Data10\||214639247360
T:\SQLData\Tempdb\Data01\||212532592640
T:\SQLData\Data02\||23798415360
T:\SQLData\Data03\||70669697024
T:\SQLData\Tempdb\Log01\||53051457536
T:\SQLData\Log01\||32302104576
X:\SQLData\Tempdb\Log01\||53051981824
U:\SQLData\Data01\||53576269824
U:\SQLData\Log01\||53576269824
Y:\SQLData\Tempdb\Data01\|51477938176
…….

 

It looks like all the drives, the ones containing “\” appears in the following order:

  • disk order , partition 0
    • disk 0, partition 0
    • disk 1, partition 0
    • disk 39, partition 0
  • disk order, partition order
    • disk 0, partition 1
    • disk 0, partition 2
    • disk 0, partition n
    • disk 1, partition 1
    • disk 1, partition n
    • …..

This is the order that I noticed when I had ONLY SIMPLE VOLUMES. Maybe the order will be differently in case we have spanned volumes.

But because usually the servers in my environment are not using spanned volumes I decided to make a script to take advantage of what I just saw. The output of the script looks like below

The script can be downloaded from here

SQL Server Instance Information – physical location, volume name, disk name and number, disk information

Goal

Obtaining information about the SQL Server physical location where the databases are and displaying for those locations the volume name, disk name and disk information

My way of achieving it

Using SSMS, xp_cmdshell and DISKPART.

In order to not use detail keyword in the script when constructing the xp_cmdshell and diskpart command I decided to us the varbinary representation of it in the script.
SELECT CONVERT(VARBINARY(MAX), ‘detail’) AS [VARBINARY], ‘varbinary representation of DETAIL keyword’ AS Info
SELECT CONVERT(VARCHAR(MAX), 0x64657461696C)AS [VARCHAR], ‘varchar representation of 0x64657461696C’ AS Info

VARBINARY – 0x64657461696C – varbinary representation of DETAIL keyword
VARCHAR – detail – varchar representation of 0x64657461696C

Before any comments about the script I want to say how important is to verify the script before execution and to run it first only on a test server or virtual machine and to run it on other systems only after you create an encrypted version of it.

Sometimes when

– we have to increase a disk where SQL Server databases are, we need to provide information about that disk to other team in order for them to increase the disk

– we have to find more information about a disk from SAN team in case we have some performance issues and those issues are tracked back to I/O latency

– we want to just display the information of disk, volumes, type, size, free in only one window from inside SSMS

The idea of having something like this came to live after I was searching how to map disks and volumes in powershell and I found the script posted by Adam Conkle ( Thanks for the script ) , here, https://gallery.technet.microsoft.com/DiskPartexe-Powershell-0f7a1bab. I realized that I can use the same approach that he uses to do the same but from inside SQL Server.

The script that I am using to obtain the output below can be downloaded from the link provided at the end of this post.

mapvtodandsqll

The problem is that diskpart requires administrators privileges which means that the SQL Service account must be a member of local administrators group on the server in order for the script to function or the implementation of a proxy account that has administrative privileges at OS level. More than that is advisable to have the script created as a SP and encrypt it in order to be sure that no one will replace those keywords, detail disk or detail volume with other ones like delete which can compromise or in other words to destroy the server. I have tried to replace the keywords to its varbinary representation because I considered the script much safer than having the script using the DETAIL keyword.

The script can be downloaded from here