How to lower the real cost of a SQL Server Virtual Machine in Azure

27 Mar 2020

Azure Tips

Whenever you take out an Azure VM you generally get a single disk which is not that big, when you select a SQL Server based VM (even web edition) you automatically get a 1TB disk, if you are using managed disks you pay for that whole TB even if you are only using 1GB of it. This post will show you how you can lower the price of that virtual machine if you are in this situation

 

What are we stating with?

At the moment what we have is a brand new virtual machine created using the SQL Server 2019 Web on Windows Server 2019 - Gen1 image

 
 

with a default setup using managed disks you get a 1TB Standard SSD which you need to pay for the full usage of each month which if you not using it all is costly (considering you are paying monthly and not using it 😀)

 
 

So how do we make this cheaper?

 

Lowering the cost of a managed disk in Azure

In order for us to achieve this we need to make the disk smaller, making the disk smaller is not super straight forward because you can't shrink a disk but once you know how it’s easy enough to do 😊. So what we are going to need to do is

  1. Add a new managed disk to the virtual machine
  2. Copy data across
  3. Shut down the server
  4. Remove the managed disk from the virtual machine
  5. Power the machine back on
  6. Change the disk letter of the disk we added earlier

So let’s go 😊

From the Azure Portal navigate to your machine and click Disks, then Add data disk, open the name drop list and then Create disk

 
 

Enter a name for the new data disk, the resource group should already be selected, click on Change size and select a new data disk size. Remember that you are able to extend a data disk so if you select something too small you can always expand it later. Click OK and then Create

 
 

Click Save

 

Back in the virtual machine open up Disk Manager by right clicking on the windows button and then Disk Management

 
 

When disk management opens click OK on the Initialize Disk window

 
 

Right click on the new disk and click New Simple Volume...

 
 

You can configure specifics if you have for the new disk, but Next, Next, Next, Next, Finish should be 100% fine 😀.

Open an Admin CMD window and run the following script, this assumes you did next next finish the dialog so the new drive is mapped to drive E. This script disables the SQL Server service, stops it and then copies all the files across

 


 

You'll notice that this takes a very quick assuming this was still a new server otherwise it would take time relative to how much data is on the drive of course to run and will give you an output like below

 


 

At this point we can head to the Azure Portal and stop the virtual machine so we can remove the extra disk, when asked if you want to stop the machine click yes

 
 

On the Disks section click Edit

 
 

Click the ellipse on the old disk and then click Detach and Save

 
 

Back on the Overview tab click Start to start the machine back up

 
 

Connect back to your virtual machine and open Disk Management again. Right click on the drive and then click on Change Drive Letter and Paths...

 
 

Click the Change button, Change the drive letter to F (this was the drive of the previous drive), Click OK and then when you ask if you are sure because some apps may break, click Yes

 
 

Things are all ready to go now. Open and admin CMD window again and run the script below

 


 

this will set the SQL Server service mode back to auto and will start the service with an output like below

 


 

After a reboot to make sure things are all happy you are good to go to use that machine 😁.

While the machine is rebooting remember to delete the old disk, or maybe check that you are happy with how the machine is working and then delete the disk.

 

Conclusion

Admittedly this is another post to remind me how I've done this in the past, I keep on keep notes for it but don't document it in the 1 place I'll be able to find it in the future 😀

I hope this was helpful for you as I know it will be to me in the future 😅