Authors: Silvano Coriani, Jasraj Dange, Ewan Fairweather, Xin Jin, Alexei Khalyako, Sanjay Mishra, Selcin Turkarslan Technical Reviewers

Planning a virtual machine configuration in Azure for optimal SQL Server performance

Before you migrate your SQL Server workloads to Azure Infrastructure Services, consider the hardware, design, and architectural differences between your on-premises environment and Azure Infrastructure Services. Knowing these differences will help you identify which workloads are appropriate for this environment. The Microsoft Assessment and Planning (MAP) Toolkit can help you assess your current IT infrastructure for various platform migrations, including Azure Virtual Machines. You should also consider adopting an approach that is well suited to measure the performance of your SQL Server workloads in Azure Infrastructure Services.

Different performance characteristics and considerations for major sub-systems between the cloud and on-premises

I/O sub-system

Storage optimization for SQL Server transactional and analytical workloads is a very important task that requires careful planning and analysis. There is already a tremendous amount of information that explains how to deal with I/O subsystems with different performance characteristics in a traditional on-premises environment, such as spindles, host bus adapters (HBAs), disk controllers, and so on. For more information, see Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications.

Azure disks are implemented as a service, so they do not offer the same range of complex configuration options available in traditional on-premises I/O subsystems. This has both benefits and costs. For instance, Azure disks offer built-in local redundancy and optional geo-redundancy for disaster recovery through the use of replicas. To achieve the same level of redundancy in on-premises deployments, you would need to set up multiple disk arrays in multiple locations and a synchronization mechanism, such as, a storage area network (SAN) replication. On the other hand, the Azure disk performance is not as predictable as on-premises disk I/O subsystem due to several factors:

• 
  Azure Infrastructure Services is a shared, multi-tenant service. Resources like host machines, storage services and network bandwidth are shared among multiple subscribers.
  
• 
  Performance may vary depending upon where and when you provision your virtual machines due to a variety of factors including differences in hardware. Your virtual machine may get moved to a different host due to a hardware replacement necessitated by a failure or lifecycle refresh.
  
• 
  The performance and availability of your virtual machines may be impacted (positively or negatively) by maintenance operations such as platform upgrades or performance and reliability fixes.
  
• 
  When you use cloud-based storage options in Azure, you sacrifice granular control and deep performance optimization options for lower costs, simplicity and out-of-the-box redundancy.
  
• 
  Azure disks are connected to virtual machines via a network infrastructure and that can introduce higher network latency compared to the local attached disks in on-premises environment.
  

For a detailed discussion on different storage configurations, see the Best practices and recommendations for optimizing SQL Server performance in Azure VMs section.

CPU and memory

Although Azure Storage introduces most of the differences between on-premises and Azure deployments, other system components, such as CPU and memory, need to be considered as well when you evaluate performance. In Azure, the only configurable option for CPU is the number of CPU cores assigned to a virtual machine deployment, currently from one shared (A0) to sixteen dedicated in A9 in the Standard Tier, which is subject to increase or change in the future. These CPU cores may not be the same as the SKUs customers can find in expensive on-premises servers. This can lead to significant performance differences in CPU-bound workload and needs to be taken into account during testing and baselining.

For memory, the current offering spans from 768 MB in A0 to 112GB in an A9 Compute Intensive Instance in the Standard Tier. Again, when you compare the performance characteristics of an existing on-premises application, you need to consider the right virtual machine size and its other options to avoid any performance impact due to inadequate memory sizing for buffer pools or other internal memory structures.

Network

Network latency in Azure Infrastructure Services can be higher than that of a traditional on-premises environment, due to several reasons, such as virtualization, security, load balancers, and so on. This means that reducing network round trips between application layers in a cloud solution has a strong positive impact on the performance when compared to on-premises solutions.

For “chatty” applications, where communications between application layers and components are frequent, we recommend that you consolidate multiple application layers on the same virtual machine. This reduces the number of tiers and the amount of communications that your application needs resulting in better performance.

When this is not possible or even counterproductive, consider the following recommendations to optimize network communication:

• 
  Host virtual machines in the same cloud service to enable direct communication between virtual machines via internal IP addresses. For more information, see Tutorial: Connect to SQL Server in the same cloud service.
  
• 
  Use Azure Virtual Network for virtual machines that reside in different cloud services. For more information, see Virtual Networks and Tutorial: Connect ASP.NET application to SQL Server in Azure via Virtual Network.
  
• 
  Load balance multiple virtual machines in the same cloud service via public virtual IP addresses. For more information, see Load Balancing Virtual Machines.
  

Raw storage performance testing

To achieve optimal results in any complex storage environment, you need to follow a systematic, data- driven methodology. This section summarizes the approach that we use internally within Microsoft to develop best practices on how to configure Azure Infrastructure Services and SQL Server for various types of workloads.

To perform I/O benchmarking tests on different Azure disk configurations, we use the Microsoft SQLIO Disk Subsystem Benchmark Tool and analyze the various performance metrics as described in the Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications article through the various performance counters and dynamic management views (DMVs). The primary goal of these tests is to determine the I/O capacity of various configurations. Then, we compare these configurations with different SQL Server workloads (for example, OLTP, point lookup queries, analytical queries with aggregations) and I/O patterns. Our goal is to verify which configuration is most appropriate for each type of workload. The Best practices and recommendations for optimizing SQL Server performance in Azure VMs section contains our findings and recommendations based on these results.

Note that precise I/O performance depends on a number of factors including data center capacity and network utilization. So, you should consider these generalized findings as subject to change. We hope that they will be useful for capacity planning as you consider which SQL Server workloads you want to migrate to Azure environment.

We recommend that you also run your own validation tests. You can see a detailed example of reusable scripts in the Appendix.