Criando uma VM do Azure SQL Server com menos de 1TB de armazenamento premium

Question

Ainda sou novo no Azure e estou pensando em configurar um SQL Server. Quero usar o armazenamento premium, exceto que não preciso de 1 TB de espaço. Os próprios bancos de dados são relativamente pequenos e eu posso colocar backups em um disco rígido, se necessário.

O 1 TB me coloca no preço do P30 (US $ 164 / mês) quando eu realmente preciso apenas do P10 (US $ 24 / mês).

Eu tentei clicar nas configurações do SQL Server e selecionar "Configuração de armazenamento", mas os controles deslizantes já estão totalmente à esquerda e não vou colocar nada menos que 5000 para IOPS, 96MBps para taxa de transferência ou 1TB para armazenamento . (Isso está em uma VM do DS2 v2)

Existe uma maneira fácil / recomendada de fazer o que eu quero? Um extra de $ 24 / mês para um SSD não é um grande problema, mesmo que não seja estritamente necessário, mas $ 164 / mês é um pouco demais.

Até agora, olhei para redimensionar o disco por meio da API, mas parece que você só pode aumentar o tamanho de um disco e não reduzi-lo. Eu também olhei apenas para criar outro disco, anexá-lo, mover todo o material do SQL para o novo disco e remover o antigo, mas perderia algum dos itens de otimização de armazenamento mencionados na dica de configuração?

    

Answer 1

Falei com o suporte do Azure e passei pela criação da VM em uma sessão remota. Eles finalmente confirmaram que esse é o design intencional.

Se você quiser implantar um SQL Server com armazenamento premium, estará obtendo (e pagando) um P30. Seu raciocínio para isso foi os benefícios de desempenho e otimização.

Editar: eu abri outro ticket diretamente com a equipe do SQL Support e eles puderam fornecer este passo a passo para realizar o que pedimos:

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.

2. 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.

3. Detach the P30 disk if unwanted:

4. Attach the disks you actually want:

   • 
   • Remember – for disks hosting data files enable read caching. Disable caching for disks used for the transaction log files:
     

5. 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):

6. 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:
      
   2. Open the Server Manager Storage Spaces console:
      
   3. Create a new storage pool. After naming, ensure to choose “manual” for the allocation type of each disk you add to your pool.
      
   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:
      
   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 isso também é algo que eles disseram que estavam olhando para mudar no futuro.