So you are looking at moving to the cloud and taking advantage of Microsoft Azure SQL services.  The problem I have seen many companies face is selecting the best option for their unique situation.  The goal of this blog post is to briefly outline each offering as well as point out the strengths and weaknesses of each.  The offerings available will be placed in two categories, Infrastructure as a Service (IaaS) and Platform as a Service (PaaS).  The figure below shows an overview of the options that will be discussed.

Azure SQL IaaS:

The Azure IaaS solution can be thought of nearly identical to running SQL on a virtual machine in your company datacenter or on-prem.  You do receive the benefits of using the Microsoft Azure back end infrastructure, meaning the vm is on on a Hyper-V Failover cluster and all the redundancy that comes with it.  I will not spend much time on the IaaS solution since with this option your team is still responsible to manage the virtual machine operating system and SQL.

The IaaS solution can be loaded from a Virtual Machine template including SQL, or you can start with a base Windows Server template and manually install SQL.  Worth noting is that with the builtin template that includes SQL you will start being charged monthly for the expensive SQL license.  Recently, Microsoft released a script to convert this SQL install and take advantage of the BYOL hybrid benefits found here.  

I typically use IaaS as a last resort when trying to move SQL to a cloud solution.  One recent example that made sense was to place an IaaS SQL server into a SQL always-on cluster.  SQL always-on clustering is not compatible with any of the PaaS solutions, so this situation was necessary and designed for taking backups from the Azure node.  Another time you may need to use an IaaS solution is if an application or custom code needs to run on the actual SQL server, this configuration is not possible with a PaaS solution as you cannot login to the server OS.

Backups with IaaS can be done with Azure Backup or setting up standard SQL backup jobs as you would currently on-prem.

Azure SQL (PaaS):

Azure offers three SQL PaaS solutions defined as Single, Elastic Pool and Managed Instance.  Microsoft will manage the updates and upgrades of the SQL platform and backend Operating System for each PaaS solution.  Yes! no more upgrading to that latest OS or SQL version, planning down times or weekend work it all happens seamlessly by Microsoft.  Backups are also done automatically with some additional options we will get into later.

 Azure SQL (Single):

The single database option offered by Microsoft uses an isolated set amount of compute, memory, and storage resources.  Keep in mind you do not have access to the back end server for management.  The offering comes in three options including Basic for less demanding workloads, Standard for typical performance requirements and Premium for IO-intensive workloads.  Microsoft has a v-Core based and DTU based purchasing model.

SQL PaaS (Single) Sizing:

Single does have some limitations with size, currently sizing is set at:

*Note there is a Hyperscale offering in preview that is said to support 100TB.

SQL PaaS High Availability (HA):

Microsoft has built all the PaaS offerings with a 99.99% uptime SLA.  The main differences with HA comes when you look at the tiers of service offered, the Basic and Standard tiers are built on a HA failover cluster.  Stepping up to the Premium option, you get the added HA of the SQL backend being part of an Always-On SQL Cluster.  Microsoft claims that even the Basic and Standard options should not see any downtime with patching or updates with any application with a builtin retry.  More detail here.

SQL PaaS Backups:

Microsoft has the Azure SQL Single offering setup to automatically create full, differential, and transaction log backups to allow point-in-time restores (PITR).  The default retention is set to 7 days for the v-Core based purchasing model.  The default for the DTU model is 7 days for basic and 35 days for standard and premium tier.  A minimum storage amount equal to 100% of database size is provided at no extra charge. Additional consumption of backup storage will be charged in GB/month.

Note that if you decrease the retention older backups will no longer be available.

There is also a Long-Term Retention option to send backups up to 10 years to blob storage.

Scalibility:

Just want to point out all PaaS offerings allow you to dynamically add more resources to your database with minimal downtime.  This can be done manually or programmatically.

Overview:

The Single option is a great solution and often the most cost effective.  It is perfect for smaller back end application databases that do not need much administration overhead as it is the most limited in features.  A comparative list of the SQL features and Azure PaaS Single can be found here.  In my experience link servers seem to be one reason I have had to abandon the single offering and look into the Managed Instance we will discuss shortly.

The best advantage to Azure SQL single is that it is a true platform as a service and takes all the infrastructure management and backup managing headaches away.  It is also scalable so can grow with your company or workload.

Azure SQL (Elastic Pool):

SQL Database Elastic Pools are an offering very similar to the single option above.  The distinct difference is that the Elastic Pool will allow you to add many databases to utilize shared resources including compute, memory, and storage.  This option can allow you to save money by having several databases sharing the same resources and is perfect for mixing non intensive workloads.  Elastic Pools have the same HA, Backup and Scalability options.  There are some changes in the sizing model for Elastic Pools, here are the details.

SQL PaaS (Elastic Pool) Sizing:

Elastic Pool currently sizing is set at:

Overview:

Elastic Pools are a great option to mix several non-critical workloads together.  I say non-critical because there is always the risk that one database will pull most of the resources and starve another critical application.  Cost and Risk must be evaluated here to help with the decision making process.

Azure SQL Managed Instance:

Azure SQL Managed Instance was designed to help bridge the gap between giving customers a PaaS solution and keeping as much of the features and management intact.  Microsoft still manages the updates and upgrades for the OS and SQL on the back end.  The managed instance allows you to place several databases on a single SQL instance in Azure.  A comparative list of the SQL features and Azure PaaS Managed Instance can be found here.

SQL PaaS High Availability (HA):

Microsoft has built all the PaaS offerings with a 99.99% uptime SLA.  The main differences with HA comes when you look at the tiers of service offered, the General Purpose tier is built on a HA failover cluster.  Stepping up to the Business Critical option, you get the added HA of the SQL backend being part of an Always-On SQL Cluster.  More detail here.

Scalibility:

PaaS offering allow you to dynamically add more resources to your database with minimal downtime.  This can be done manually or programmatically.

SQL PaaS (Elastic Pool) Sizing:

Single does have some limitations with size, currently sizing is set at:

SQL PaaS Managed Instance Backups:

Microsoft has the Azure SQL Single offering setup to automatically create full, differential, and transaction log backups to allow point-in-time restores (PITR).  The default retention is set to 7 days for the v-Core based purchasing model.  The default for the DTU model is 7 days for basic and 35 days for standard and premium tier.  A minimum storage amount equal to 100% of database size is provided at no extra charge. Additional consumption of backup storage will be charged in GB/month.

Note that if you decrease the retention older backups will no longer be available.

Long-Term Retention is not yet available for SQL Managed Instance.  You can setup backup jobs inside of SQL as a work around.

Overview:

Azure SQL Managed Instance offers the most features for a PaaS solution and the easiest transition moving from on-prem to the cloud.  It comes at an increased cost but in my experience  has been the easiest transition for database administrators to adopt. Another big advantage to the managed instance is being able to add it to a VLAN for local IPs and for example an expressroute connection.