Windows 2012 disk information – displaying disk information

Goal

To obtain disk information like number, size, partition style, drive label and drive letter

My way of achieving it

Using powershell. I must start by saying that this requires windows 2012. The necessity of having something like this is my desire to create a SQL installation script that eventually will install a SQL Server from one end to the other. The only parameters that the script will require will be the account used for the services and the instance name. Everything else will be discovered automatically. The script from this post is my attempt of discovering the disks available on a server and formatting them accordingly. Till now the script was tested only against simple volumes. The script contains a few functions, one that discover all the disks, another one that discover all available disks (RAW format) , another one that prepare the disk for a stand alone installation of SQL Server and the last one which is preparing the disk for a clustered SQL server installation.

Below is the output generated by the script.

disk_config

As in the case of any actions that change the data, the format functions should be used with caution. The steps for running the script in a safe manner will be to execute the script and call the functions in the following order

  • all_disks
  • av_disks
  • formatclusterdisk or formatclusterdisk with the whatif parameter
  • formatclusterdisk or formatclusterdisk without the whatif only after checking that indeed the disk is RAW.

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