Microsoft is making it increasingly easy to run SQL Server 2014 on an Azure virtual machine in one of Microsoft's seventeen Azure data centers. You can run a preconfigured virtual machine with a preconfigured SQL Server 2014 instance from the Azure gallery on your choice of any size Azure virtual machine. One of the choices from the gallery is "SQL Server 2014 Enterprise Optimized for Transactional Workloads" running on Windows Server 2012 R2. One nice thing about using a preconfigured gallery image is that you do not have to pay for any SQL Server 2014 licenses. You simply pay the hourly cost for the edition of SQL Server and virtual machine size that you choose.
SQL Server 2014 Configuration Options
Microsoft explains that "This Enterprise Edition image is optimized for OLTP workloads and is intended for VM sizes including A4, A7, A8 and A9. Once deployed, the VM comes with Windows Storage Spaces pre-configured." Microsoft also does some instance-level configuration work on SQL Server 2014, although they do not go far enough with what I would consider to be standard best practices.
They create eight tempdb data files that are all 25600MB in size, with an autogrow increment of 1024MB, which is a good default choice. They also enable TF1117 and TF1118 as start-up trace flags, which are also good choices for SQL Server. Finally, Microsoft also enables instant file initialization and lock pages in memory in the operating system, which I also agree with.
I would prefer that Microsoft also made some changes to these instance-level configuration options:
- backup compression default
- cost threshold for parallelism
- max degree of parallelism
- max server memory (MB)
- optimize for ad hoc workloads
Backup compression should be enabled by default in most cases. Cost threshold for parallelism often should be raised to a higher value than the default of 5, depending on your workload. Max degree of parallelism usually should be changed to a non-default value based on the number of cores in a NUMA node. This setting also depends on your workload. Max server memory should be set to a non-default value based on the amount of RAM in the virtual machine and what you are running (besides the SQL Server database engine) on the VM. Finally, I think optimize for ad hoc workloads should be enabled, pretty much in all cases.
In Microsoft's defense, it would be difficult to make a satisfactory configuration choice for some of these items without knowing (in advance) the details of your VM size and expected database server workload. That leaves the task up to you, just like with an on-premises SQL Server instance.
Azure Virtual Machine Sizing
Even though you can choose anything from an A0 Basic to an A9 Standard machine, Microsoft recommends that you choose either an A4 Standard, A7 Standard, A8 Standard, or A9 Standard size virtual machine for production usage. Pricing details for SQL Server virtual machines are listed here.
Looking at the comparative specifications for these recommendations in Table 1, it is hard to understand why you would want to choose an A4 Standard machine, since it costs the same amount per hour as the larger A7 or A8 Standard machines. Looking at the online documentation, it is not initially very clear what the actual difference is between an A7 and an A8 Standard machine. Digging a little deeper, the A8 Standard machine is considered a Compute Intensive instance, which is supposed to use a faster 2.6GHz Intel Xeon E5-2670 processor, along with two network adapters (one 10Gbps and one 32Gbps RDMA capable).
The A7 Standard virtual machine uses a somewhat slower 2.2GHz Intel Xeon E5-2660 processor, while the network connectivity appears to be standard 1Gbps Ethernet. While this sounds like a significant difference in processor and network performance, it is not really the main issue with the A-series virtual machines for SQL Server usage.
| VM Size | SQL Standard Rate | SQL Enterprise Rate | Core Count | RAM Amount |
|---|---|---|---|---|
| A4 Standard | $0.80/hr | $3.00/hr | 8 | 14GB |
| A7 Standard | $0.80/hr | $3.00/hr | 8 | 56GB |
| A8 Standard | $0.80/hr | $3.00/hr | 8 | 56GB |
| A9 Standard | $1.60/hr | $6.00/hr | 16 | 112GB |
Table 1: A-Series SQL Server Virtual Machine Information
The main problem with all of the A-series virtual machines is the pretty miserable I/O subsystem performance, even though Microsoft has pre-configured the disk subsystem with Windows Storage Spaces to get best performance possible given the inherent performance limitations of the A-series virtual machines and hosts. Figure 1 shows the CrystalDiskMark results for the E: drive from an A4 Standard machine from the East US Azure data center, which is meant for transaction log files.
Figure 1: A4 Standard CrystalDiskMark Results
A much better alternative for SQL Server are the D-series virtual machines. These virtual machines cost the same per hour as the comparably sized A-series virtual machines, and they have local SSD storage that should only be used for tempdb and/or for buffer pool extensions (BPE) files, since they are not persistent. Some relevant specifications for D-series virtual machines are shown in Table 2.
| VM Size | SQL Standard Rate | SQL Enterprise Rate | Core Count | RAM Amount |
|---|---|---|---|---|
| D4 Standard | $0.80/hr | $3.00/hr | 8 | 28GB |
| D13 Standard | $0.80/hr | $3.00/hr | 8 | 56GB |
| D14 Standard | $1.60/hr | $6.00/hr | 16 | 112GB |
Table 2: D-Series SQL Server Virtual Machine Information
The D4 Standard machine costs the same as an A4 Standard machine, but it has twice the RAM and some local SSD storage. The D13 Standard machine costs the same as an A7 or A8 Standard machine, but with the benefit of local SDD storage. The D14 Standard machine costs the same as an A9 Standard machine, but also has the benefit of local SSD storage. Given this information, it makes little sense to use a A-series virtual machine for SQL Server.
Unfortunately, the permanent drives for your SQL Server data and log files also have pretty substandard I/O performance in CrystalDiskMark, as shown in Figures 2 and 3.
 Figure 2: D14 Standard CrystalDiskMark Results |
 Figure 3: D14 Standard CrystalDiskMark Results |
The local SSD performance is related to the size of the Azure virtual machine, with larger sizes getting better local SSD performance. The CrystalDiskMark performance results for a D14 Standard machine in the East US Azure data center are shown in Figure 4.
Figure 4: D14 Standard CrystalDiskMark Results for Local SSD Storage
The F: drive (for SQL Server data files) has slightly better results than the E: drive, but both drives have a very low level of performance for SQL Server.
Conclusion
It seems pretty clear that the D-series machines are better for SQL Server usage than the A-series machines. It also makes sense to pay close attention to the sizing and pricing of the virtual machine you decide to provision for SQL Server, since you can get more RAM at the same hourly cost. The two best choices from a performance perspective are the D13 or D14 Standard virtual machines.
The preconfigured SQL Server 2014 instances from the Azure gallery can save you a lot of money in SQL Server licensing costs, and they have much of the necessary configuration work already complete in the base image. You should still go in and make a few final configuration changes based on your preference and workload. Finally, you should take the time to run some performance benchmarks on your virtual machine so that you understand the level of performance that it can deliver.
Why do the persistent disks have only 12MB/sec sequential read speed? I know of no Azure storage technology that is this slow. Blobs should be at 80MB/sec (per blob).
I'd love to see these benchmarks for the new Azure Premium storage (persistent SSD).
http://azure.microsoft.com/blog/2014/12/11/introducing-premium-storage-high-performance-storage-for-azure-virtual-machine-workloads/
Have you spec'd out the new G series virtual machines? I believe the storage on those is SSD.
SSD storage on the G-series are not durable. You still need to allocate storage if you want it to be durable which means $$$.
FYI The SQL Server pricing you listed and wondered why it was the same on some instances – that's just the SQL Server cost. You have to add that cost to the Windows Server Instance cost.
So for an A4 Standard it's $566 / month you're paying for the Windows Server VM + $628 / month for SQL Server license. So A4 Standard running SQL Server Standard is $1194.
Bump it up to A7 and while SQL doesn't change in price the windows server VM does – now the total cost is $1570 / month.
You missed this I think on the SQL Server pricing page on azure:
"The prices below apply to all versions of SQL Server. Per-minute rates for SQL Server images don’t include the cost of the Windows Server virtual machine it runs on. See the Windows Server rates.."
"Looking at the online documentation, it is not initially very clear what the actual difference is between an A7 and an A8 Standard machine"
I don't agree
https://www.pluralsight.com/blog/it-ops/what-is-hypervisor
Not sure I follow.
This article was written in 2010, and comments about any documentation were relevant to what was available from Microsoft at that time.
The link you posted is to a 2013 blog post on the basics of what a hypervisor is, and has no details whatsoever about A7, A8, or really anything Microsoft offers at all. Perhaps you posted the wrong link?