In our recent posts published on this forum, we have been discussing characteristics of the SQL Database Platform as a Service (PaaS) offering hosted on the Microsoft Azure platform. As we have pointed out, its convenience, competitive pricing, resiliency, and ease of maintenance (including automated patching as well as monitoring, availability, scalability, and geo-replication capabilities) are partially offset by a lack of support for some of the features available in full-fledged editions of the SQL Server product, such as OLE-DB, Common Language Runtime (CRL) and CRL User Defined Data Types, Transparent Data Encryption, Integrated Full-Text Search, SQL Server Agent, SQL Server Replication, Service Broker, or Business Intelligence-related services, in particular, Integration Services, Analysis Services, and Reporting Services. (For a more comprehensive listing, refer to the Microsoft Azure online documentation). Fortunately, these limitations can be eliminated without relinquishing the cloud presence by taking advantage of the Microsoft Azure Infrastructure as a Service (IaaS) capabilities and installing a SQL Server instance in an Azure-hosted virtual machine (VM). The purpose of this article is to describe principles of such implementation and outline its deployment options.
Employing Microsoft Azure virtual machines (rather than relying on SQL Database) increases management overhead by shifting the responsibility of maintaining the VM guest operating system and SQL Server instances to you. In return, you have the opportunity to realize the feature parity with your on-premise database environment. You obviously need to consider the implications of hosting resources in the cloud – including, for example, network connectivity (with its latency and bandwidth dependencies), security, or backups. Even though some of them are equally applicable to scenarios involving SQL Database, there are numerous others that provide strong differentiation between these two Azure-based solutions, such as the following (for a comprehensive overview of this subject, including detailed analysis of performance-related factors that you should take into account, refer to the MSDN Web site):
- scalability limits – the shared model utilized by an overwhelming majority of cloud technologies tends to have a more significant impact when dealing with PaaS offerings. While the Premium edition of SQL Database provides some guarantees in regard to resource availability (along with 500 GB maximum database size and P3 performance level of 800 Database Throughput Units), the high-end virtual machine options offered at the time of writing of this article (A9) delivers 16 cores, 112 GB of memory, and up to 16 1 TB data disks (their individual size is limited by the Azure storage page blob capacity).
- authentication – SQL Database is restricted to SQL Authentication. With SQL Server instances hosted on Azure virtual machines, you have the ability to use Windows authentication; although in order to facilitate domain credentials, you obviously would need to ensure connectivity to an on-premise or cloud-resident instance of an Active Directory domain controller.
- network configuration – network proximity between Microsoft Azure virtual machines or Azure Cloud Services can be controlled by leveraging virtual networks. In the case of SQL Databases, your abilities in this regard are limited to targeting a particular hosting region. Effectively, the resulting cross-component latency might be an order of magnitude (sub-milliseconds vs. low milliseconds) higher when using the latter in multi-tier Azure hosted deployments.
- resiliency – the reliability of SQL Database benefits from having three redundant copies located in separate fault domains within the same datacenter and Geo-Replication with up to four global replicas (for an extra price associated with the Premium level upgrade). While the similar triple redundant vhd storage (along with its geo-redundancy) arrangement is applicable to Azure virtual machines, increasing the level of high availability leverages typically availability sets and SQL Server specific technologies, such as Database Mirroring, Log Shipping, or AlwaysOn Availability Groups. (For more information regarding this topic, refer to the MSDN Web site).
- backup and recovery – SQL Database supports a scheduled, transactionally consistent, automated export of its content to a storage account (keep in mind though that this methodology has cost implications due to increased storage and network utilization). Starting with SQL Server 2012 SP1 CU2, you have the ability to implement the equivalent functionality in an SQL Server instance by performing its backups directly to Microsoft Azure Blob service.
In order to implement a SQL Server instance in an Azure-resident virtual machine, you will need to start by obtaining a paid for or free-trial Azure Platform subscription (in either case, you are expected to use a Microsoft account). This will allow you to log on to the Azure Management Portal (either the current production version or its most recent preview). At that point, you will have several different options of realizing your plan:
- Deploy a new virtual machine in Azure by leveraging an existing, predefined SQL Server image from the Azure platform gallery, which currently includes each edition of SQL Server 2008 R2 SP2, SQL Server 2012 SP1, and SQL Server 2014 RTM versions hosted, respectively, on Windows Server 2008 R2 SP1, Windows Server 2012, and Windows Server 2012 R2. Product licensing is in this case accounted for in the recurring VM cost.
- Create a new virtual machine on-premise and prepare it for SQL Server installation by using the Image preparation of a stand-alone instance of the SQL Server setup option. Next generalize it using the sysprep.exe utility and (once the sysprep has completed and the virtual machine has been shut down) upload its vhd file to a storage account within your subscription. Subsequently, you can use this vhd as an image (just as you would use a preexisting gallery image) to deploy multiple instances of Azure VMs, each running a customized (during deployment process) installation of SQL Server (as documented on the MSDN Web site). In this case, you will need to ensure that you own relevant product licenses, covering all cloud-resident instances.
- Create a new virtual machine on-premise and perform a standard SQL Server installation on it. After shutting it down, upload its vhd file to a storage account within your subscription. Next, in the management portal, create an Azure disk from the uploaded vhd, which could be subsequently used to deploy an Azure VM, by selecting the newly created disk from gallery (as documented on the MSDN Web site). Effectively, there will be only a single instance of this SQL Server installation running in the cloud (whose licensing you are responsible for).
- Deploy a new virtual machine by leveraging either one of existing, predefined Windows operating system images (which currently include Windows Server 2008 R2 SP1, Windows Server 2012 Datacenter, and Windows Server 2012 R2 Datacenter) or a custom vhd uploaded to a storage account and install an instance of SQL Server within that VM afterwards. Specifics of that installation depend on a number of factors and include the following options:
- If you are currently running a SQL Server 2014 RTM on-premise and your intention is to copy one of the local databases to the cloud, you can combine both the new SQL Server 2014 installation and database copy into one step by running the Deploy Database to a Windows Azure VM wizard directly from the SQL Server Management Studio (refer to the SQL Server Blog for details).
- With an Azure VM in place, it is also possible to invoke the SQL Server setup from an Azure data disk containing a copy of installation media. This will require creating a vhd file on premises, copying SQL Server setup files to it, uploading it to a storage account in your subscription, and attaching it to the VM. Note that you can apply the same approach to database files. Similarly, you can copy files from on-premise to the cloud by leveraging RDP local resource redirection functionality or, if you have established site-to-site IPSec VPN between on-premise and Azure, direct network copy (note that both of these approaches will require opening the appropriate firewall ports on the target server).
- If your intention is to migrate a large, on-premise database to the cloud or you cannot employ any of the standard methods outlined above due to compliance or risk restrictions, then you might have to resort to the Azure Import/Export Service. This involves physical shipment of a BitLocker encrypted disk to one of Microsoft Azure data centers, as described in the How to use the Azure Import/Export Service for SQL Server Files MSDN article.
In our upcoming articles, we will take a closer look at some of these individual scenarios, focusing in particular on automated deployment of a multi-tier configuration involving both IaaS and PaaS components.
