The Top 10 Things to Know When Running SQL Server Workloads on Windows Azure Virtual Machines

This article is not up-to-date. For the latest information, please see SQL Server in Azure Virtual Machines and Pricing Details for SQL Server in Azure VMs.

When we announced the availability of the Windows Azure Virtual Machines and Virtual Network previews (we call these two sets of services Windows Azure Infrastructure Services) in June 2012, organizations all over the world began testing their Microsoft SQL Server workloads and pushing the preview to its limits. You can do amazing things with Windows Azure Infrastructure Services. The ability to rapidly deploy virtual machines (VMs) capable of running many different types of SQL Server workloads at a low cost without having to procure and manage hardware has broad appeal. The ability to do complex multi-VM deployments in a virtual network, support for Active Directory (AD), support for SharePoint, and the ability to connect your virtual network back to on-premises networks or remote machines using virtual private network (VPN) gateways makes it even more interesting as an off-premises hosting environment for IT shops and developers alike. Windows Azure Infrastructure Services is a stepping stone that organizations can use to migrate some of their existing workloads to the cloud, as is with no changes, while at the same time taking advantage of more modern "Platform-as-a-Service" capabilities of Windows Azure in a hybrid fashion. We've seen organizations run everything from simple development and test SQL Server workloads to complex distributed mission critical workloads. Here's a few things we've learned from their experiences.

1. Know Your SLA: Before you unplug that server and move your SQL Server workloads to Windows Azure, you need to understand the relevant Service Level Agreements (SLA). The key thing to pay attention to is this statement: "For all Internet facing Virtual Machines that have two or more instances deployed in the same Availability Set, we guarantee you will have external connectivity at least 99.95% of the time." What does this mean from a SQL Server perspective? It means that in order to be covered by this SLA, you will need to deploy more than one VM running SQL Server and add them all to the same Availability Set. See Manage the Availability of Virtual Machines for more details. It also means that you will need to implement a SQL Server High Availability Solution if you want to ensure that your databases are in sync across all of the virtual machines in your Availability Set. The bottom line is that you have to do some work to ensure high availability in the cloud just as you would have to do if these workloads were running on-premises. When properly configured, Availability Sets ensure that your SQL Server workloads will keep running even during maintenance operations like upgrades and hardware refreshes.
2. Know Your Support Policy: The beautiful thing about running SQL Server in a Windows Azure Virtual Machines is that it's very much like running SQL Server anywhere else. It just works. You don't have to change your applications or worry whether various SQL Server features are supported. Most SQL Server features are fully supported when running on Windows Azure Infrastructure Services with a few important exceptions. Let's start with SQL Server version support. Microsoft provides technical support for SQL Server 2008 and later versions on Windows Azure Infrastructure Services. If you are still running workloads on SQL Server 2005 or earlier, you will need to upgrade to a newer version in order to get support. If you are going to upgrade, we suggest you upgrade to SQL Server 2012. It was designed to be "cloud ready" with native support for Windows Azure in the management tools, development tools and the underlying database engine.First, let's talk about high availability. If you don't think you need to worry about implementing a high availability solution for your SQL Server deployments in Windows Azure Infrastructure Services, think again. As mentioned in the previous section, you will need to implement some kind of database redundancy in a virtual machine Availability Set in order to be covered by our SLA. However, there are some limitations that affect SQL Server high availability features. First of all, SQL Server Failover Clustering is not supported. Don't panic, there are plenty of other options if you want to deploy SQL Server in a high availability configuration, such as AlwaysOn Availability Groups, or by using legacy features like Database Mirroring or Log Shipping. We recommend you use the AlwaysOn Availability Groups feature in SQL Server 2012 for high availability, but there are some considerations you should be aware of if you go this route. Availability Group Listeners are not currently supported, but stay tuned as we plan to add support for this in the near future. If you can't wait for Listener support and you still want to use AlwaysOn Availability Groups, there is a work-around. You can use the FailoverPartner connection string attribute instead. You should be aware that this approach limits you to two replicas in your AlwaysOn Availability Group (one primary and one secondary), and does not support the concept of a readable secondary. See Connect Clients to a Database Mirroring Session for more information.

Next, let's talk about some important considerations that apply when configuring storage for your SQL Server databases. In general, we recommend you attach a single data disk to your VM and use it to store all of your data and log files. If you decide to spread your data and log files across multiple data disks to get more storage capacity or better performance, you should not enable geo-replication. Geo-replication cannot be used with multiple disks configurations, because consistent write order across multiple disks is not guaranteed. The final aspect we will cover is the various "distributed" features that SQL Server supports such as Replication, Service Broker, distributed transactions, distributed queries, linked servers, etc. All of these things should work just fine across SQL Server VMs deployed in the same virtual network, but once you start crossing that boundary (either across the public internet or a virtual network VPN gateway), you had better test them thoroughly. These features were designed for use in on-premises data centers, LANs and WANs, not across the public internet. Take some time to read the Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment for more specifics.

• Know Your Licensing: The first (and easiest) way to license your SQL Server deployment on Windows Azure Infrastructure Services is to create a new virtual machine using one of our pre-built SQL Server platform images in the Image Gallery. Using this approach you pay an hourly rate depending upon the edition of SQL Server you choose (Enterprise, Standard or Web). There's no need to worry about product keys, activation, etc. and you can get access to your newly provisioned SQL Server VM in minutes. Be aware that you are charged per minute and there are no minimums. See Provisioning a SQL Server Virtual Machine on Windows Azure for more information. Microsoft doesn't look inside your VMs, so if you de-install SQL Server from a VM that was provisioned using a platform image you will still get charged for SQL Server usage unless you dispose of the VM.The second option is to "bring your own VM". This involves building your own Hyper-V VMs on-premises, installing SQL Server on them, then uploading them to Windows Azure. See Creating and Uploading a Virtual Hard Disk that Contains the Windows Server Operating System for guidance on how to do this. When you bring your own VM, the cost of the Windows Server operating system license is built into your hourly compute charges, but this is not the case for other server products like SQL Server, and it's up to you to make sure that your VMs comply with Microsoft licensing policies. By default, server products like SQL Server are not licensed to run in virtualized configurations or in a hosting environment like Windows Azure Infrastructure Services.   Microsoft offers different SQL Server licensing options for this scenario depending upon whether you are running production workloads or development / test workloads.

  For running production SQL Server workloads, you must purchase software maintenance. See Microsoft License Mobility through Software Assurance for more details. From a licensing standpoint, you will need the same SQL Server edition and number of licenses that you needed on-premises. For instance, if you use SQL Server Standard or Enterprise Edition core-based licenses on-premises, with Software Assurance you can move those core licenses to Windows Azure. A minimum of four core licenses per Virtual Machine applies, so pick an appropriate sized virtual machine. Please note that you will have to wait for 90 days if you choose to reassign your license back to a server on-premises.  For running development / test SQL Server workloads, you should consider purchasing an MSDN Pro, Premium or Ultimate Subscription.  You can install much of the software included with your MSDN subscription (including SQL Server) on your VMs at no additional cost.  See Windows Azure Benefit for MSDN Subscribers for more information. If you do decide to create your own SQL Server VMs, you should be aware of some recent improvements we announced in a cumulative update to SQL Server 2012 SP1 that greatly simplify the preparation of SQL Server VM images using the SysPrep utility. See Expanded SysPrep Support in SQL Server 2012 SP1 CU2 for more information. For SQL Server AlwaysOn Availability Group deployments, please be aware that the "free passive failover instance" licensing benefit does not apply for SQL Server deployments running in Windows Azure Infrastructure Services (or any other hosting environment). This benefit only applies to on-premises deployments, i.e. deployments that do not involve a shared hosting environment. That means all of your replicas will require a fully licensed copy of SQL Server Enterprise Edition. If you want to get up to speed on all of these details, check out the SQL Server 2012 Licensing Guide.

• Know Your Hardware and Storage: In this section we will examine the performance characteristics of Windows Azure Infrastructure Services from a CPU, RAM, I/O and network standpoint. Microsoft is committed to providing great compute and storage performance at a very competitive cost in Windows Azure Infrastructure Services (see Virtual Machine Pricing Details for more information). But you have to understand that the core value proposition of the cloud is to scale out using shared, low cost compute and storage infrastructure, not to scale up on expensive dedicated big iron. Many large organizations have already virtualized some or all of their SQL Server workloads in their own private clouds, yet some hard-core SQL Server stalwarts remain skeptical about performance and reliability. Just for the record, SQL Server virtualization is fully supported and is here to stay. Having said that, you cannot expect to achieve the same level of performance using a VM that is possible when scaling up on big expensive servers and storage subsystems.Windows Azure Virtual Machines are hosted on commodity servers in shared clusters, and Windows Azure Disks (OS and data disks) are implemented using Windows Azure Storage which is a shared storage service with built in redundancy. From a CPU perspective, you pay a price for virtualization. From an IO perspective, you pay a price for shared redundant storage. So before you fork lift that highly tuned mission critical SQL Server OLTP workload to Windows Azure Infrastructure Services, you should do your homework on performance, throughput and latency. If you plan and test thoroughly, the vast majority of typical SQL Server workloads will run just fine in Windows Azure Virtual Machine. But there are a small percentage of performance sensitive "scale-up" workloads that will never be a good fit for this kind of environment.

  Let's start with CPU. The clock speed and other characteristics of our virtual cores may vary somewhat depending upon what kind of host server you land on. You should use at least a medium sized VM instance (A2) because SQL Server needs approximately 4GB of RAM to breathe. A2 and larger VM's offer dedicated virtual cores, so you won't have to share your virtual cores with other VMs on the same host. Depending upon the VM size you choose, you can get anywhere from two virtual cores (A2) to eight virtual cores (extra-large A4 VMs and larger). Next let's look at RAM. Read intensive SQL Server and Analysis Services workloads on big data sets often require lots of RAM to cache all that data in memory for optimal performance. If you see your SQL Server cache hit ratio trending downwards, you might want to move to a larger VM size to get more RAM. You can get anywhere from 3.5 GB RAM in a medium (A2) VM, to 56 GB RAM in a high memory A7 VM. In order to control costs, you should be conservative when sizing VMs. Test your workloads on smaller sized VM's and see if the performance is acceptable. You can always upgrade to a larger VM size later if necessary. Next let's examine storage performance. As we mentioned before, your VHDs (both OS and data disks) are implemented using Windows Azure Disks, which are a special type of Windows Azure Storage page blob that are cached locally on the host server in a shared disk subsystem. Local redundancy is built in, and geo-redundancy is an additional option. The page blobs backing your locally cached VHDs are stored remotely in a shared storage service that is accessed via REST API's over high-speed interconnects. Check out Data Series: Exploring Windows Azure Drives, Disks and Images for more information. By now you should be realizing that the performance characteristics, configuration and behavior of Windows Azure Disks are quite different from locally attached storage or even SANs. So what about storage capacity? First of all, do not store your databases on the OS drive unless they are very small. Do not use the D: temporary drive for databases (including tempdb), data stored on this drive could be lost after a restart and the temporary drive does not provide predictable performance. We recommend you attach a single data disk to your VM and use it to store all of your user databases. See How to Attach a Data Disk to a Virtual Machine for more information. A data disk can be up to 1 TB in size, and you can have a maximum of 16 drives on an A4 or larger VM. If your database is larger than 1 TB, you can use SQL Server file groups to spread your database across multiple data disks. Alternatively, you can combine multiple data disks into a single large volume using Storage Spaces in Windows Server 2012. Storage Spaces are better than legacy OS striping technologies because they work well with the append-only nature of Windows Azure Storage. As discussed previously in section 2, do not enable geo-replication if you intend to use multiple data disks. Input/Output Operations per Second (IOPS) tends to be the key metric used to measure disk performance for SQL Server workloads. So how many IOPS can you do on a Windows Azure disk? The answer is that it depends on the size of IOs and access patterns. For a 60/40 read/write ratio workload doing 8 KB IOs, our target is to provide up to 500 IOPS for a single disk. Need to go higher? Add more data disks and spread your database workload across them. When creating or restoring large databases, you should use instant file initialization to speed up performance. One final point regarding network bandwidth and latency. VMs are allocated a certain amount of network bandwidth based upon size. This allocation can impact the performance of data transfers and backups, so if you plan on moving a lot of data around you should consider using a larger VM size. When connecting to your VMs over a public endpoint or VPN gateway, it's important to remember that your infrastructure is being accessed over the public internet. Your VMs are far away from your physical location and sitting behind sophisticated network infrastructure like load balancers and gateways with advanced security options enabled. This introduces network latency, and requires a different approach for many types of operations that rely on low-latency networks. For example, migrating a large database to the cloud will take much longer, and client applications that were not designed for cloud-style network latency may not behave properly. We'll discuss networking in more detail in the next section. For more information on VM sizes and options, see Virtual Machine Cloud Service Sizes. See Performance Guidance for SQL Server in Windows Azure Virtual Machines for detailed information on performance best practices.

• Plan Your Network First Windows Azure Infrastructure Services offers a full range of network connectivity options for your VM deployments. You should plan your network configuration first before creating VMs to avoid having to start from scratch if you make a mistake. You can use Remote Desktop to connect to individual VMs from your desktop and administer them. See How to Log on to a Virtual Machine Running Windows Server for more information. If you want to allow connections into your VMs from the public internet, you can open up ports using endpoints. See How to Set Up Communication with a Virtual Machine for more details. If you want to administer your SQL Server remotely over the internet, you can create an endpoint that allows access to your VM over the standard SQL Server port 1433, but since this port is well known to hackers we suggest using a random public port for your SQL Server endpoint. You can automatically load-balance incoming connections to your endpoints across a collection of VMs. See Load Balancing Virtual Machines for more information. This is useful for scenarios like scaling out front-end web servers across multiple VMs.In order to establish full connectivity between your VMs you should create a Windows Azure Virtual Network first, then create your VMs inside your new virtual network. Your new VMs will be automatically assigned an IP address using ranges specified in your virtual network configuration, there's no need to implement your own DHCP service. Virtual networks come with a built-in DNS, or you can deploy your own DNS server. See Windows Azure Name Resolution for more information. You should thoroughly test your name resolution before continuing. If you don't have a lot of networking expertise, you might want to have a colleague review your configuration before proceeding so you don't back yourself into a corner.

  Now things start to get really interesting. You can establish site-to-site connectivity between your corporate network and your virtual network using a secure VPN gateway. You need a VPN device on your corporate network to do this. We support both dedicated VPN devices and software based options when establishing a VPN gateway. See Create a Virtual Network for Cross-Premise Connectivity for more information. You can also establish point-to-site connectivity using a VPN connection directly from your computer to your virtual network which is great for developers or administrators who need to access the virtual network from a remote location. See Configure a Point-To-Site VPN in the Management Portal for more information. Most Windows Server deployments usually rely on Active Directory (AD) for identity and security. Windows Azure Infrastructure Services supports a full range of Active Directory deployment options. See Install a new Active Directory forest in Windows Azure if you want a stand-alone AD deployment used only by VMs in your virtual network. If you implement a VPN gateway, you can domain-join VM's in your virtual network to your corporate Active Directory. In this scenario it makes sense to deploy a read-only AD domain controller in your virtual network for improved performance and reliability. See Install a Replica Active Directory Domain Controller in Windows Azure Virtual Networks for guidance on how to do this. Many more powerful AD configurations are also supported, such as Active Directory Federation Services and support for hybrid identity scenarios that span your VMs and other cloud services running in Windows Azure. See Windows Azure Active Directory for more information.

• Set the Time Zone on Your VMs to UTC Consider setting the time zone on your VMs to UTC. Windows Azure Infrastructure Services uses UTC in all data centers and regions. Using the UTC time zone may avoid rare daylight savings related timing issues that could crop up in the future. Clients should of course continue using the local time zone.
• Use Data and Backup Compression SQL Server supports Data Compression and Backup Compression features that can help boost I/O performance with minimal CPU overhead. Compressing your data and backups results in faster I/O operations against Windows Azure Storage and your data will take up less space.
• Back Up to Blob Storage Instead of Disks In SQL Server 2012 Service Pack 1 Cumulative Update 2 we enabled a handy new backup scenario for SQL Server deployments in Windows Azure Infrastructure Services. Instead of having to provision additional data disks to store your backups, you can backup and restore your databases using Windows Azure Blob Storage. Blob storage provides limitless capacity and offers built-in local redundancy and optional geo-redundancy. This frees up precious capacity on your data disks so you can dedicate them to data and log files. See SQL Server Backup and Restore with Windows Azure Blob Storage Service for more information. As an added benefit, you can copy your backup blobs across storage accounts and even regions asynchronously without having to waste precious time and bandwidth performing unnecessary upload and download operations.
• Don't Get Hacked Take the time to properly secure your VMs and SQL Server deployments in Windows Azure Infrastructure Services to protect them from unauthorized access. Hackers are always looking to take over poorly secured machines on the Internet and use them for their own purposes. We recommend that you secure your SQL Server deployments in Windows Azure Infrastructure Services the same way you would secure your on-premises SQL Server deployments behind your network DMZ.   Avoid opening public endpoints for RDP or TSQL. Instead, set up a secure VPN Gateway and administer your database servers directly. Use Windows Authentication for identity and access control. If you must use SQL Authentication, create a different account for SQL Server administration, add it to the sysadmin role, set up a strong password, then disable the sa account. Minimize your attack surface by stopping and disabling services that you don't intend to use. Consider using SQL Server Transparent Data Encryption (TDE) to protect your data, log and backup files at rest. If these files get copied outside of your VM they will be useless.
• Learn PowerShell The Windows Azure Management Portal offers a rich graphical interface for provisioning and managing your VM deployments in Windows Azure Infrastructure Services, but if you have to deploy a lot of virtual machines you should take the time to learn PowerShell. You can save yourself a ton of time and effort by developing a library of PowerShell scripts to provision and configure your VMs and virtual networks. See Automating Windows Azure Infrastructure Services (IaaS) Deployment with PowerShell for more information.

  Now that you are armed with some information, we encourage you to jump in and start identifying which of your SQL Server workloads are ready for Windows Azure Infrastructure Services. Start migrating some of your smaller workloads so you can learn and gain confidence, then move on to more intensive workloads. Keep in mind that besides compatibility, Windows Azure Infrastructure Services offers (and requires) granular control over the configuration and maintenance of your SQL Server deployments. This is a selling point for many IT organizations, but others want to get out of the business of maintaining servers so they can focus more on innovation. Windows Azure SQL Database is built on SQL Server technology and delivered as a service. There's no need to install and manage server software, and advanced features like high availability and disaster recovery are built in. Still others want the best of both worlds by combining the compatibility and granular control offered when running SQL Server on Windows Azure Infrastructure Services to migrate existing workloads, combined with the agility and benefits of a managed service in Windows Azure SQL Database for new workloads. This kind of hybrid usage of Windows Azure is fully supported and has become one of the key differentiating factors in comparison with other public cloud providers. See Data Management for more information on all of these capabilities.   Roger Doherty Sr. Program Manager Microsoft Customer Advisory Team