3

I'm still new to Azure and am looking at setting up a SQL Server. I want to use premium storage except I don't need 1TB of space. The databases themselves are relatively small and I can put backups on an HDD if necessary.

The 1TB puts me at the P30 pricing ($164/mo) when I really only need P10 ($24/mo).

I tried clicking on SQL Server settings and selecting "Storage configuration", but the sliders are already all the way to the left and won't let me put in anything less than 5000 for IOPS, 96MBps for Throughput or 1TB for storage. (This is on a DS2 v2 VM)

Azure

Is there an easy/recommended way to do what I want? An extra $24/mo for an SSD isn't a big deal even if it's not strictly necessary, but $164/mo is a bit too much.

So far I've looked at resizing the disk through the API, but it seems like you can only increase the size of a disk and not reduce it. I've also looked at just creating another disk, attaching it, moving all the SQL stuff to the new drive and removing the old one, but would I lose any of the storage optimization stuff that's mentioned in the setup tooltip?

  • 2
    I'm not sure if it's a recent bug or they did it by design. Never been this way. You should open a ticket with support just to make sure. – Bruno Faria Aug 2 '16 at 23:51
  • Are you deploying this out of the market place? – CtrlDot Aug 3 '16 at 3:05
  • @CtrlDot, yes I am. – Brandon Aug 3 '16 at 15:01
  • @BrunoFaria, thanks for the suggestion but unfortunately I'm not sure how to do that. If there is an option to contact support without paying $40/mo on a 6 month commitment, I'm not seeing it. – Brandon Aug 3 '16 at 15:02
  • Contact billing or anything related. They will support you on this case. It's not a technical problem. – Bruno Faria Aug 3 '16 at 15:05
7

I talked to Azure support and walked through the creation of the VM over a remote session. They eventually confirmed that this is the intentional design.

If you want to deploy a SQL Server with premium storage then you're getting (and paying for) a P30. Their reasoning for this was the performance and optimization benefits.

Edit: I opened another ticket directly with the SQL Support team and they were able to provide this walkthrough on accomplishing what we had asked for:

At provision time the GUI does not allow for any premium disk less than the current P30 equivalent when using gallery images of VMs running SQL Server. This isn’t entirely surprising given that the official recommendation is to use 2 or more P30 disks (https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sql-performance/). However, it is possible to work around this GUI design by changing the disk configuration post-deployment. The best part about this is that it can be done on-the-fly with the VM running. I’ll walk you through the how-to and configuration of the StorageSpaces for a StorageSpace with just a couple of disks.

Before we do, keep in mind that if using more than 8 disks per storage space, currently you must do it differently (using PowerShell; example). You should have at least two storage spaces – one for database files and one for transaction log files. The disks for each storage space should all be the same size/performance level. For example, you may have two storage spaces, one containing all P10 disks and the other containing all P30 disks. You should not use a storage space for SQL Server with a mix of P10/P20/P30/etc. You cannot add disks to a storage space at a later time and maintain optimal performance, so know your requirements ahead of time. If adding disks at a later time the storage space would have to be rebuilt, but that’s beyond our scope here. It’s not a bad thing to have multiple storage spaces so you can always add more disks in a new storage space if you want. If you really wanted you could have two or more storage spaces for each database – one or more for the data file(s) and one for the transaction log file.

  1. Create your Azure VM from a gallery image with SQL Server. In the storage options, leave the default configuration (unless you’d like to have P30 disks, then increase accordingly). NOTE: VM size does govern IO throughput and can result in achieving less throughput than what your disk configuration may otherwise be capable of achieving. For example, the DS3 VM governs IO throughput to 12,800 IOps/128MBps but can have 8 attached data disks. This means that even though 3 P10 disks can theoretically otherwise achieve 300MBps, they would be governed to 128MBps if attached to a DS3 machine. So choose a VM size that meets your CPU, memory, and IO throughput requirements.

Create VM

  1. After the VM has been created, go ahead and connect to the VM. In file explorer (This PC) you should see 3 volumes – OS drive (C), Temp Storage (D), and the SQL Data (F). We’ll get rid of that SQL Data drive shortly. If you check, you should see that all of the system databases actually reside on the OS drive.

  2. Detach the P30 disk if unwanted: Detach P30 disk

  3. Attach the disks you actually want:

    • Attach disk
    • Remember – for disks hosting data files enable read caching. Disable caching for disks used for the transaction log files:
      Host caching option
  4. After creating as many disks as you want, actually get rid of the one you don’t want (You may have used other names for your resource groups/storage accounts): Remove disk Remove disk 2

  5. Create your storage space(s)/volume(s):

    1. In your RDP session to the VM, open up the Disk Management console and initialize/online the disks:
      Run cmd Bring disk online
    2. Open the Server Manager Storage Spaces console:
      Server manage storage spaces
    3. Create a new storage pool. After naming, ensure to choose “manual” for the allocation type of each disk you add to your pool.
      New pool Manual
    4. Create a new virtual disk. Be sure to choose an interleave size of 64K for OLTP workload and 256K for data warehousing workloads. Fixed provisioning is also best. The number of columns should be equal to the number of disks in the pool:
      New virtual disk configuration columns
    5. When this is done, it will automatically pop-up the new volume wizard. That on is fairly self-explanatory so I’ve omitted screenshots here.

FYI this is also something that they said they were looking at changing in the future.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.