When we first started with Azure, we used the P70 tier for all the data disks attached to our SQL servers. We have quite a few SQL Servers and each SQL servers will have 3 to 15 Data disks attached, with at least one other node to form a cluster. The second node also has the same amount of data disks attached. Upper management realizes that the cost for these data disks are too high and we will need to resize the data disks to a lower tier.
At the time of this article, Azure does not allow a disk to “downsize” to lower tier. Azure portal only supports increasing the disk size / performance tier.
To work around this, we need to attach additional disk (with the performance tier that we would need) and move the data over. Here are the steps that I followed to complete this task. I have already created and attached the new disks prior to step 1.
- Stop and disable all SQL related services. To do so, I ran the following two PowerShell cmdlets.
#Get all the services with Display Name that contains 'SQL' and stop the service with the Force flag Get-Service -DisplayName "*SQL*" | Stop-Service -Force #Get all the services with display name that contains 'SQL' and disable them Get-Service -DisplayName "*SQL*" | Set-Service -StartupType "Disabled"
We need the -Force flag to stop any services that are dependent to other service. You may need to modify the -DisplayName filter if you have any service that does not contain ‘SQL’ as part of its name.\
- In this example, I am moving the data as follows:
- R: (LOGS) -> U:(LOGS)
- S: (DATA1) -> V: (DATA1)
- T: (DATA2) -> W: (DATA2)
I used the following Robocopy command to move the data one by one.
Robocopy R:\ U:\ *.* /j /e /sec /Xd "System Volume Information" "$RECYCLE.BIN" /Xo Robocopy S:\ V:\ *.* /j /e /sec /Xd "System Volume Information" "$RECYCLE.BIN" /Xo Robocopy T:\ W:\ *.* /j /e /sec /Xd "System Volume Information" "$RECYCLE.BIN" /Xo
The syntax is as follows:
Robocopy R:\ U:\ *.* /j /e /sec /Xd "System Volume Information" "$RECYCLE.BIN" /Xo
- R: – old disk
- U: – new disk
- *.* – copy every file using any extension
- /j – copy using unbuffered I/O
- /e – include all empty sub-folders
- /sec – to copy the data/attributes/timestamps/DACLs
- /Xd – exclude the following directories (in this case, System Volume Information and Recycle Bin was excluded)
- /Xo – do not overwrite existing data (this should be safe to omit, since we are copying to a brand new disk)
Here is an example of the result from running these commands.
- Once the copying is completed, we will need to work on swapping the drive letters for the disks. We will remove the drive letters from the old disks and add the drive letter to the new ones. Open Disk manager and highlight the old LOGS disk. Right click to “Change drive letter and Paths” and remove “R” from LOGS. We will remove the drive letter on each of the old disks, and change the drive letters on the new disks to match the original layout.
- R: (LOGS) will be removed -> U:(LOGS) will be changed to R:
- S: (DATA1) will be removed -> V: (DATA1) will be changed to S:
- T: (DATA2) will be removed -> W: (DATA2) will be changed to T:
When everything is complete, we will start the SQL related services again. You should keep the old disks available for a short period of time before detaching them from the VM and delete them for good.