Introduction to Automating Deployment of SQL Server in Azure IaaS Virtual Machines

In general, Microsoft Azure provides two types of SQL Server based relational data services. So far, we have been focusing on this forum on Azure SQL Database Platform-as-a-Service (PaaS) offering. Its primary benefits are derived directly from its PaaS nature, which eliminates the overhead associated with installation and maintenance of host operating systems and database server instances, leading typically to lower operational costs. On the other hand, this approach imposes a number of limitations and introduces compatibility-related challenges. Some of them are inherent to the underlying characteristics of the service, which precludes the use of SQL Server instance level features (such as, SQL Server Agent, Analysis Services, Integration Services, or Reporting Services), others result from the lack of feature parity with databases hosted by full-fledged instances of SQL Server (although as recent updates clearly indicate, the list of differences between the two is getting consistently shorter). These discrepancies must be addressed when migrating existing on-premises databases to the cloud, which is an important consideration due to the potentially significant effort involved in their mitigation.

The alternative approach involves deploying a SQL Server instance into an Azure Infrastructure-as-a-Service (IaaS) virtual machine, which effectively eliminates these challenges. As a result, you have unrestricted access to the entire SQL Server feature set (although there are some exceptions associated with constraints of the underlying infrastructure, such as limited support for Failover Clustering). In addition, your vertical scaling capabilities significantly exceed those offered by SQL Database (which at the time of writing this article, is capped at 500 GB maximum database size and 735 transactions per second), since they are tied to the largest supported VM sizes, which currently translates into the Standard_G5 tier with 32 cores, 448 GB of RAM and up to 64 of data disks yielding the potential total volume size of up to 64 TB. In addition, you can take advantage of up to 6596 GB of local SSD storage to host tempdb and implement the Buffer Pool Extensions feature of SQL Server 2014, further improving performance and scalability. On the other hand, there are obvious implications in regard to the management overhead associated with IaaS-based solutions, such as the need for deploying and managing the underlying operating system and the DBMS platform. The same obligation applies to facilitating high availability and disaster recovery requirements, which in the case of Azure SQL Database takes advantage of resiliency capabilities built into the service and can be simply turned on or off on an as needed basis, while with IaaS virtual machines, it necessitates resorting to relatively complex implementations of SQL Server specific-features, such as AlwaysOn Availability Groups, Database Mirroring (which incidentally has been deprecated in SQL Server 2014), or Log Shipping. In our article, we will review methods that allow you to minimize this overhead, focusing in particular on deployment tasks.

In general, deployment of a SQL Server in an Azure IaaS virtual machine can be handled in one of several ways:

  • by using one of the natively available Azure Gallery images that contains a SQL Server installation, ranging from SQL Server 2008 R2 SP2 to SQL Server 2014 RTM (including SQL Server 2014 RTM Standard, SQL Server 2014 RTM Enterprise, SQL Server 2014 Enterprise Optimized for Transactional Workloads, and SQL Server 2014 Enterprise Optimized for DataWarehousing Workloads),
  • by using one of the natively available Azure Gallery images that contains a Windows Server installation and provisioning a SQL Server instance after operating system deployment is completed,
  • by uploading a custom operating system image to Azure storage, using it to provision a new virtual machine, and installing a SQL Server instance once the operating system deployment is completed,
  • by uploading or creating a custom operating system image with a SQL Server, including a pre-staged SQL Server installation.

From the perspective of the management overhead, it is rather obvious that the first of these options is simplest to carry out. As a matter of fact, the full deployment process in this case can be easily scripted by using Windows PowerShell. However, this approach has some relatively important drawbacks. For one, it automatically installs most of the SQL Server components (beyond the Database Engine, the same, default instance also hosts Analysis Services, Integration Services, Reporting Services in native mode, Replication, Full-Text and Semantic Extractions for Search, Data Quality Services, and Master Data Services). Effectively, you might be forced to disable or remove the ones that you are not planning on using, introducing manual steps into the initial hands-off setup that pre-created Azure Gallery images were meant to deliver. This option also places system databases on the operating system drive rather than on a data disk, since at the deployment time the virtual machine contains only C: and D: drives. In addition, assuming that your company has an Enterprise Agreement with Microsoft, you will not be able to take advantage of any available SQL Server licenses, and instead incur extra cost built into the per-minute rates associated with running SQL Server Azure Gallery-based images.

One way to eliminate these shortcomings is to employ the second option. In this case, you rely on an Azure Gallery image that contains a Windows Server installation only. Once the virtual machine and its operating system are deployed, you would run SQL Server setup and customize it according to your preferences. The setup can be automated either by relying on the command-line parameters or by using a configuration file. The installation media can be uploaded to Azure blob storage and, subsequently, downloaded to the target IaaS virtual machine and mounted as a separate volume. Alternatively, you might consider uploading the media content to a file share provisioned with Azure file storage, but keep in mind that this service is still in preview.

The third option is a variation of the second one, since, while the sequence of steps is identical, you rely in this case on a custom image uploaded to Azure storage. This could be necessary, for example, if you need to comply with organizational policies that mandate the use of corporate operating system images for all of its Windows systems, both on-premises and in the cloud. When relying on custom images, there are several additional steps that you should incorporate in the deployment process. For starters, add VM Agent into your images (along with relevant VM Extensions), in order to improve manageability of cloud resident virtual machines. In addition, change the default location of the paging file to the D: drive, since this is one of the primary purposes of the local temporary storage in the Azure IaaS virtual machines.

Either of the two options we just presented can be leveraged to implement the fourth one. Such an approach is well suited for scenarios where you want to deploy a custom-configured SQL Server instance to multiple virtual machines with minimal effort and maximum consistency. To accomplish this, you would start by installing Windows Server in a virtual machine (on-premises or in Azure), next, you would run SQL Server setup to prepare an image-based installation, configure the operating system to automatically complete the SQL Server setup during a subsequent restart, and execute the sysprep command line utility with the generalize and shutdown switches (which prepares the operating system for imaging and shuts it down). At that point, you would capture the image and transfer it to Azure blob storage to be used as your personal template. When deploying a new Azure IaaS virtual machine, you would use your custom image (rather a generic Gallery image), which during its initialization would complete the SQL Server setup process in the desired manner.

Regardless of the approach you choose, there are some general guidelines you should keep in mind. Most importantly, add multiple data disks to the virtual machine hosting your SQL Server instance and use them to store database and log files. Ensure that data disks are not stored in a Geo-Replicated storage account (this is related to the fact that there is no guarantee of the consistency of the order in which write operations are applied across multiple, geo-replicated volumes). It is also recommended to turn off disk caching by setting its policy to None. In order to improve performance, when using higher-end virtual machines with SSD storage, move tempdb to the SSD-based disk mounted as the D: drive. Another performance optimization technique that leverages this capability involves Buffer Pool Extensions.

In the upcoming articles published on this forum, we will provide more detail regarding the automated, custom image-based installation of SQL Server 2014 to Azure IaaS virtual machines.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles