If you decide to migrate your SQL Server data assets on Azure, one of the main questions you have to answer is related to which kind of Azure SQL you need for your workload.

SQL Server is available on Azure in several different forms. You can implement it with an Infrastructure as a Service approach, deploying a VM with a SQL Server instance on board, or you can use it via a Platform as a  Service implementation.

As you can see from the following schema, the PaaS approach reduces the administrative effort and the overall Total Cost of Ownership needed to manage your system, thanks to automated backup and other exciting features. Anyhow, this comes at a cost: you lose some control over the platform since you’re not able to govern all the settings of your environment, or you can’t count on some of the features that are available on the on-premises version of the product.

This article is the first of a series, where I’ll try to explain which aspects can drive you in deciding what kind of implementation you should choose while approaching Azure for SQL Server.

Azure SQL evolution over time

Until a few years ago, while migrating an on-prem database, you had to choose between Azure SQL Database in PaaS or a SQL Server VM in IaaS.

The PaaS was great to reduce the management effort, and to leverage on a highly-available platform that offered useful features, automatically applied or very easy to be implemented. Your focus is at the database level, while the instance is a mere logical object that doesn’t require specific configurations. On the other side, you might need to apply several changes to your application due to lost in functionalities (i.e., you lose the SQL Server Agent, so you must rewrite all your scheduled tasks) or architectural variations. Also, you couldn’t have full control over your infrastructure: i.e., due to platform updates governed by Azure, it was impossible to maintain a specific build for SQL Server, and it might be a problem if your application required a particular version.

Legacy applications were one of the main drivers, which could lead us to the IaaS approach, with SQL Server on a dedicated VM. Additionally, on the VM, you could install the on-premises version of SQL Server, with all its familiar capabilities ready for you: for instance, you could easily extend you on-prem datacenter to Azure, by adding a Disaster Recovery replica of your Always On Availability Group hosted on a VM in the cloud – a kind of configuration that PaaS services can’t still achieve. But this had a price: to manage a VM, and to grant it’s secured, performant and up to date, requires much administrative effort.

In other words, if you could refactor your application, you probably were the right candidate for PaaS benefits; otherwise, for specific features or to do a simple Lift & Shift of your on-prem applications with minimum changes, you had to look for IaaS.

Over time, Azure SQL Database increased the number of features available, almost reaching a significant level of feature parity. In the meanwhile, also the SQL Server database engine increased its retro-compatibility, linking together the database compatibility level to the availability of new features introduced by new SQL Server releases. Nowadays, on recent SQL Server builds, you can set an old compatibility level to block the new cardinality estimator or the most recent query optimizer features and so on: by configuring the compatibility level, you can force your databases to behave as on older SQL Server instances, and you can control it at database level.

Recently, Microsoft introduced a new PaaS service, which brought PaaS in the Lift & Shift world: Azure SQL Database Managed Instances.

With MI, you obtain a full instance, with almost all the features that were missing on Azure SQL Database in respect of the on-premises version, and all the excellent features offered by PaaS. You can:

• Leverage on a feature parity near to 100% of the features you can use in on-premises SQL Server version; this, in addition to the compatibility level behavior change, makes this service ideal for database migration, starting from SQL Server 2005 to more recent editions;
• Reduce management effort for your DBA, since he doesn’t worry about High Availability, backups and so on;
• Contrarily to other Azure SQL deployments, the instance resides inside your network boundaries.

Managed Instances are one of the most valuable scenarios for someone who’s approaching Azure to host his on-prem databases.

Managed Instances are always the best approach?

MIs reduce the gap between the typical PaaS implementation and the IaaS approach, but they cannot fit in every scenario. As mentioned before, SQL VMs are still useful for hybrid scenarios, whereas PaaS implementations – Single Database, Elastic Pool or Hyperscale – may find their place in new developments where TCO or scalability concerns.

During the next articles, I’ll try to go more in-depth on different Azure SQL flavors, explaining their pro and their cons.

How to move the first steps?

To define which kind of Azure SQL service may be more suitable for your needs, you need to know very well the workload you want to host in Azure.

It may be necessary to interview stakeholders or IT people to define what are the dependencies between the database and the rest of the infrastructure, to understand if something else needs to be moved to the cloud along with the DB.

Interviews can be enhanced by data collected in different ways: i.e., you can capture the output of a scheduled execution of the remarkable stored procedure SP_WhoIsActive, by Adam Machanic; if you already have an Azure subscription at your disposal, you may leverage on Service Map – a feature of Azure Monitor – to define a map of interconnection between the instance and all the other relevant system around your network.

You then need to assess your environment, to define capacity requirements, and to find eventual blockers that may drive you to an IaaS or a specific PaaS solution. For this last analysis, you can leverage on Data Migration Assistant tool that can scan your database schema and give you evidence about potential troubles coming from a raise in compatibility level or a migration to a PaaS implementation.

Finally, it’s also essential to review connectivity and security requirements, since these topics may have a weight in your design.

What about other services?

Until now, the discussion was around the database engine, but you can easily migrate or create new implementations that leverage on other typical SQL Server services. The following are PaaS services that may help you in different scenarios:

• For ETL/ELT jobs, you can use Azure Data Factory to execute native SQL Server Integration Services packages, or you can re-design them via its native flows;
• To host your semantic model, you can also evaluate Azure Analysis Services;
• To present your data, you can choose Power BI that, in some of its tiers, add support for paginated reports in addition to its native format.

Whereas the services mentioned above are not suitable for you, you can always deploy an IaaS VM and install the required software on top of it, realizing a hybrid IaaS-PaaS implementation.

This topic, as well as all the others we encountered in this post, will be expanded during the next articles.

Stay tuned!