VMs have a virtualized processor, memory, and storage. Processor and memory factor into the VM type and its choice of size. Storage is influenced by the VM type and size, but has its own parameters. This section will introduce the basics of VM types, sizes, and storage. Performance will be touched upon as this is a crucial element of configuring a VM and will be discussed further in Chapter 5, Performance.
VM types and sizes
This section will contain information about the different types and sizes of IaaS VMs available in Azure.
VM types and series
VMs in Azure come in different types and sizes. Within each major VM type, the size maps to what is known as a series, such as D, E, and G. Each series has different sizes with different specifications. The following table lists the different VM types and their purpose:
Figure 2.8: Different VM types with their recommended usage
VM resources can be reserved and guaranteed by paying for a reserved instance. There is also the option to use dedicated hosts for the VMs, which would isolate performance further. Spot VMs allow you to use capacity in Azure, but if Azure needs those resources, the VM can be evicted. For that reason, a Spot VM is not recommended for permanent production SQL Server workloads. More information on Spot VMs can be found in the documentation in the Use Spot VMs in Azure24 section.
VM size
Within each VM type category, there will be multiple VM sizes available, each with different capacities and limitations. Some VMs may not be available due to regional restrictions, subscription policies and constraints, quotas, and suchlike. The region(s) and VM sizes that you will be able to use may also depend on your company's standards as well as any policies they may put in place that could restrict how, what, and where things are done.
Note
Consult Microsoft Docs25 to learn more about the details of the different VM types, the sizes currently available, the types of processors used for each, and their limitations.
How to choose a VM for SQL Server
Choosing a VM type and size depends on the database workload. This means that you need to know about the application and/or database profiles. Do you currently use a lot of CPU but not as much memory? Do you use more memory but not as much CPU? Do you pound your disks a lot on-premises and need a certain amount of guaranteed IOPS? A mixture of the above? These are the types of questions you will need to answer in order to pick the right type and size. The best way to know is to profile, baseline, and benchmark your application's workload to understand how it is using SQL Server and the underlying server.
Furthermore, a specific VM size will limit the amount of resources and the limitations are a hard cap. Once you hit it, that limitation cannot be upped with that VM size. To increase a limitation, the VM will need to be resized to a larger VM with minimal downtime.
Consider this example: as of the time of writing this chapter, there is a memory-optimized type VM size of Standard_E20s_v3/Standard_E20as_v4 that has 20 vCPUs, 160 GiB of memory, and up to eight virtual network cards with up to 8,000 Mbps speed. Storage aspects will be discussed in the next section.
A VM's number of vCPUs is static. Unlike using a hypervisor on-premises, what you select is what you get. If you need 17 vCPUs, the current minimum size for a memory-optimized VM that could be used would be the Standard_E20s_v3/Standard_E20as_v4. A Standard_E16s_v3/Standard_E16as_v4 has 16 vCPUs; it cannot be configured to have more. Read the descriptions in the link in the earlier side note for descriptions on the underlying CPUs, their speeds, manufacturers, and so on in order to make a correct determination of which type and size of VM to use. For example, while the Ev3 VMs are based on Intel processors, Eav4 VMs feature the AMD EPYC(TM) 7452 processor.
The same is true for memory. If you require 256 GiB of memory for a memory-optimized VM but only need 17 vCPUs, you must step up to the Standard_E32s_v3/Standard_E32as_v4, which has 32 vCPUs and the amount of memory required. You cannot add memory to an E20s/E20as. And if you require 2,000 GiB of memory but you only need 64 vCPUs, the Standard_Ea96s_v4 VM with 96 vCPUs would provide you with the right amount of memory. In both scenarios, to get more memory, a bigger VM must be selected.
The VM of choice must also account for the network throughput required. If you are planning on implementing a feature such as an availability groups for a busy database, you should know how much throughput you will need to ensure that the network will not become a bottleneck.
VMs can be resized to be bigger or smaller; however, downtime will be incurred. Plan accordingly.
Storage
This section will cover storage concepts for IaaS.
Disk types
There are five types of disks that a VM can use:
- Standard HDD
- Standard SSD
- Premium SSD
- Ultra Disk
- Temporary storage
Detailed information about the different types of disks and their limitations can be found in the documentation topic What disk types are available in Azure?26, but their names are self-explanatory.
For SQL Server production workloads, Standard HDD and Standard SSD are generally not recommended. While they are less expensive, they often do not provide enough performance for demanding applications. Most SQL Server workloads will perform well on properly configured VMs using Premium SSD or Ultra Disk. Premium SSD also has a feature called Azure blob cache, which can improve performance. How it works is detailed in the blog post Azure Premium Storage, now generally available 27. Ultra Disk is the fastest and most expensive storage, but may not be available in all regions or for all VM sizes. In addition, it may have to be enabled for the subscription. A sample message is shown in Figure 2.9, where there is some sort of restriction for Ultra Disk:
Figure 2.9: Ultra Disk not available for use
Sometimes, in order to attain the performance needed, multiple disks need to be configured and then, inside the VM, grouped together with Storage Spaces (Windows Server) or using the Linux tools to create a single logical volume from two physical disks presented.
Disk capacity
Disk types are one piece of the storage puzzle. The other is size, or capacity. Each disk type has different sizes of disks to choose from. For example, as of the time of writing this chapter, Premium SSDs have sizes with names such as P30 and P40. Each one has different maximum specifications. For example, a P30 disk today has a maximum size of 1,024 GiB and a P40 2,048 GiB.
If your database size and projected growth exceeds the smaller size, but is less than the next one, you will need to purchase the larger size. This means that if your database is over a terabyte in size (a P30), but less than two (a P40), you will need to consider a P40 if you wish to configure a single disk. There are other ways to achieve capacity greater than a terabyte that will be described in the following sections.
Storage performance
Storage performance is different, but a completely related concept. Part of choosing the right disk configuration is understanding your performance requirements. Also similar to VMs and deciding what type, series, and size to choose for the right processor and memory, you must choose your disk configuration based on the performance required because the limits are rigid. The preceding link documents accurate guidelines for the performance you can expect from each type of disk in certain categories.
Not all parameters are documented for the different disk types. For example, a disk rated at a specific speed will deliver up to that for whatever kind of I/O was tested, but it may not work well with your workload. Always test to ensure that you are getting the performance required before going into production with your workload.
Consider this example: as of the time of writing this chapter, a single P30 can sustain up to 5,000 IOPS at 200 MiB/sec, and the larger P40 disk, 7,500 IOPS at 250 MiB/sec. If you have a database that requires half a terabyte of space but needs 15,000 IOPS or a sustained 450 MiB/sec, you are possibly looking at the equivalent of a P60 if using a single disk or configuring multiple smaller disks (either to look like one disk or spreading the database across those disks).
There is a second and equally important aspect that gates storage performance: the VM type and size. Each VM is rated for storage throughput. A disk with higher throughput ratings can be attached to a VM that has lower throughput, but the storage will only run as fast as allowed by the VM type and size.
Assume that you are using a Standard_E20s_v3 VM. For storage, it can have a maximum of 32 data disks, 320 GiB of temporary SSD storage (which has a ceiling of 40,000 IOPS/320 MBps of throughput with 400 GiB of cache), and a maximum of 32,000 IOPS/480 MBps of throughput for the VM outside of temporary storage.
If your current production workload needs nearly a gigabyte per second of throughput, the E20s will not do it. Assuming you want to stick with an E-series VM, you would be looking at a size of E48s or E64s. The limit of 480 MBps for the E20s is hard; the VM will never achieve more. Looking at the choice of disk, a single P60 would come in at half the amount of IOPS that an E20s VM could achieve. The VM would not let it achieve more than that figure of 480 MBps. This means that the single P60, rated at 500 MBps, could never hit that. You could use two P80 disks to achieve 40,000 IOPS, but would still be capped at a maximum throughput of 480 MBps, even though each P80 disk is rated at 900 MiB/sec. Choosing a VM hardware is about making trade-offs and compromises. Similar considerations would apply to the EAs VM series, even if the throughput information you can find in the documentation will be different.
Another restriction is that certain VM sizes cannot use premium disk types, which also may factor into what VM you use. If you're using the Azure Marketplace, you will see a message similar to the one in Figure 2.10:
Figure 2.10: Error message when trying to use Premium SSD
If you are using a Windows Server-based Marketplace image pre-installed with SQL Server, you can select the type of workload and get some assistance optimizing the storage configuration, as shown in Figure 2.11:
Figure 2.11: Storage configuration pane in the Azure portal
Note that what has been discussed previously is also seen at the bottom in the warning where you may not get the throughput needed and the limitation of the number of disks. Another good aspect to being able to configure this at the time of provisioning the VM is that you can enforce standards for things such as drive letters and data and transaction log file folders.
Ephemeral storage and SQL Server data and transaction log files
Each VM has temporary, also known as ephemeral, storage. Anything configured on this storage is lost if the VM is shut down or rebooted. Therefore, it is not recommended for SQL Server data or transaction log files for application or user databases.
The only potential use for ephemeral storage is TempDB. TempDB is recreated every time SQL Server is restarted, so by the nature of its design, what is in it is not permanent.
There is one caveat if you choose to use this storage for TempDB: the size of the ephemeral disk is fixed and can never be expanded. The only way to make it bigger is to choose a different VM size. That also means that performance cannot be greater than what the VM allows for that disk without resizing. If you know that your TempDB usage meets size and performance requirements, you can consider ephemeral storage since it generally performs better for some things, including 8 KB writes.
Storage-optimized VMs use local non-volatile memory express (NVMe) storage that is ephemeral. If the VM is rebooted, anything configured on the temporary disk is lost. This means that storage-optimized VMs as they are configured as of the time of writing this chapter are not recommended for SQL Server use.
United States
Great Britain
India
Germany
France
Canada
Russia
Spain
Brazil
Australia
Singapore
Canary Islands
Hungary
Ukraine
Luxembourg
Estonia
Lithuania
South Korea
Turkey
Switzerland
Colombia
Taiwan
Chile
Norway
Ecuador
Indonesia
New Zealand
Cyprus
Denmark
Finland
Poland
Malta
Czechia
Austria
Sweden
Italy
Egypt
Belgium
Portugal
Slovenia
Ireland
Romania
Greece
Argentina
Netherlands
Bulgaria
Latvia
South Africa
Malaysia
Japan
Slovakia
Philippines
Mexico
Thailand
