SQL on Azure – How clear are your options?

When moving existing workloads to Azure SQL, you’ll find some of the headlines you’ve heard don’t quite work in practice. Workloads should always be looked at on a case-by-case basis. Here’s some guidance to get you started, with tips for those attempting to go it alone.

No straightforward choices

There are two main options for running SQL workloads in Azure – Azure SQL Database (PaaS) or SQL Server on Azure VMs (IaaS). Microsoft has a great article explaining the differences between the two options and what to consider when deciding between them.

Some headlines you might’ve come across…

You should choose Azure SQL Database if:

  • You’re building new cloud-based apps to take advantage of the cost savings and performance optimisation that cloud services provide. This approach has the benefits of a fully managed cloud service, helps lower initial time-to-market and can provide long-term cost optimisation.
  • You want to have Microsoft perform common management operations on your databases and require stronger availability SLAs.

Conversely, the received wisdom is that you should choose SQL Server on Azure VMs if:

  • You have existing on-premises apps you want to migrate or extend to the cloud, or if you want to build enterprise apps larger than 4TB. This approach provides the benefit of 100% SQL compatibility, large database capacity, full control over SQL Server and Windows, and secure tunnelling to on-premises. This approach minimises costs for development and modification of existing apps.
  • You have existing IT resources and can ultimately do your own patching, backups and database high availability. Note, some automated features drastically simplify these operations.

A simple conclusion you could draw is ‘new apps use Azure SQL and existing apps use SQL Server.’ However, it may not be that straightforward. What if you’ve an existing application and want to utilise Azure SQL? After all, who wouldn’t want to take advantage of the cost savings and performance optimisation that the cloud service provides?

This question comes up often when working with customers, which is probably why they’ve recognised they need help from a managed service provider. The fact is, Azure SQL is not a one-size-fits-all solution to your SQL workload woes. It’s also not a good fit for all applications, but it can be a great option for some existing apps. Determining the right approach for your business requires an in-depth workload analysis – I always recommend seeking expert advice to ensure your next move is the best solution for your organisation.

Considerations around your database

Azure SQL is not a full-featured SQL instance managed by Microsoft on your behalf, but rather a T-SQL as a service offering.

This means that not all features of the full on-premise SQL server are available. So, the first consideration is whether your database is using features Azure SQL can support. Microsoft has a feature-parity table outlining the SQL server features and where they’re available on the Azure SQL offering.

It’s vital all the database is supported, or database redevelopment is completed to replace these features to supported ones, before migration.

Making data migration easier

Assuming Azure SQL has the features you’re utilising, the next area to analyse is the database itself. Microsoft has created the Data Migration Assistant (DMA) which completes an analysis of your database and provides a report on any compatibility issues you may face when moving to Azure SQL. In addition, it recommends performance and reliability improvements for your target environment.

It also helps with the migration process of moving from your on-premise SQL server to Azure SQL. We recommend the execution of the DMA tool against the database and remediation of any compatibility issues ahead of migration. In addition, you may find new features that improve your business case for the migration to Azure SQL.

Architecting for Azure SQL

With SQL server on-premise, it’s usually expected that the underlying infrastructure is responsible for achieving the uptime and RTO/RPO requirements. This is usually in the form of a highly available implementation of SQL, whether it be log shipping, mirroring or an AlwaysOn cluster.

The Azure SQL offering has many high availability and DR features available, but these are not all enabled ‘out of the box’. Therefore, it’s vital that you work with an experienced Azure architect to achieve the same uptime and RTO/RPO requirements for the business.

After completing these steps, you should have a good indicator around the suitability, time and effort required to migrate an existing SQL workload running on SQL Server to Azure SQL. If you have any immediate concerns regarding your new or existing Azure application deployments, or are considering Azure for new projects, it’s worth reviewing your plans for SQL.

If you’re currently trying to manage Azure without expert support, feel free to reach out to us with any queries.


Please enter your comment!
Please enter your name here