Load balancing for database files among the mount points

Goal

To load balancing the database files among the number of mount points or paths that we have available for this database.

My way of achieving it

Using tsql, xp_cmdshell and powershell command. From time to time, because databases grow, more databases are created we need more and more space for storage and because of this we end up adding more disks or mount points (the case that this script, post will address) but these are not used evenly by the databases from our instance. Some of the databases that were created  in the beginning will use only the mountpoints available when the database was created. Because of this from time to time we need a cleaning method or a method to load balancing the files among the mountpoints. The script below is my attempt to load balancing those files. By default is displaying information for databases that have 4 database files. You must be aware that the script is not checking for the available free space at destination. The script is only displaying the information without executing anything. The output of the script for a test database will look like below

loadbalancingdbfiles_17July2016

The code can be downloaded from here

SQL Server Instance – database files location, disk, mount point, size, free space, xp_fixeddrives looklike

Goal

To obtain information about the locations were database files are, disk, mountpoint and to display the capacity, free space and space occupied on that drive by the database files

My way of achieving it

Using xp_cmdshell and powershell. The reason behind this was the fact that although xp_fixeddrives is great sometimes we need more information. This script is nothing more than an attempt to create something that can be used when we have to answer questions like

  • where my files for my databases are stored
  • the location is a mount point or not
  • what is the size of the drive where my database files are
  • how much in percentage the database files occupies from that drive

The output of the script looks like below

dltompandsizes

For me this type of information is useful when we have to show to other teams that actually the SQL Server is not the major consumer of that location or when we have to identify the disk number where the database files are in case we have to ask the storage team performance logs for it.

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