In the previous article, Migrating SQL workloads to Microsoft Azure: Planning the jump, we discussed the main points that should be checked and considered while drawing your plan to migrate the SQL workload from the on-premises datacenters to Microsoft Azure. In this article, we will go through the different database services that are provided by Microsoft Azure to help you in selecting the proper service that can serve your SQL workload when migrating it to Microsoft Azure.
IaaS or PaaS
Before choosing the suitable Microsoft Azure database service that meets your requirements, you need to specify the suitable Azure platform. Microsoft Azure provides two high-level platform options: Infrastructure as a Services, also known as IaaS and Platform as a Service, also known as PaaS. The platform choice specifies the Azure services that can be used and the control that you can have over the services under that platform.
Choosing the IaaS platform, you are renting the IT infrastructure servers and virtual machines from the cloud provider. This includes storage, networks, and operating systems. With this platform, you are still responsible for and have control over the Operating System layer and all layers over the OS, including the installation of the services, the operating system patching, and so on.
On the other hand, the PaaS platform provides you with the ability of building, testing, and deploying your applications without worrying about the underlying infrastructure management. In other words, you are not responsible for installing an operating system or patching the machine with the latest security and system updates.
The following image shows your responsibilities, in light blue, and the list of layers that you don’t need to worry about, in dark blue, where the cloud service provider, Microsoft for example, is responsible for managing the tasks fall under that layer. You can see that you are responsible for everything when hosting your databases in your datacenter, requiring multiple teams to handle these tasks, which is not possible for the start-up and small companies, as shown below:
Microsoft Azure Database Services
Now we are familiar with the difference between the platforms provided by Microsoft Azure. We need to identify the database services that are provided under each platform.
In the IaaS platform, you can rent a virtual machine and install your SQL Server instance in that machine, where you will be responsible for the Operating System and SQL Server installation and administration tasks under that service.
Moving to PaaS, you can see that Microsoft Azure provides you with different choices based on your workload type. For example, you can use Azure SQL Database or Azure SQL Managed Instance for your transactional SQL workload and use Azure Cosmos DB for your No-SQL transactional workload. For the analytical workload, you can use the Azure SQL Data Warehouse instance, under the Azure Synapse Analytics service.
Let us discuss each SQL database service provided by Microsoft Azure briefly.
SQL Server on Azure VM
The IaaS platform provides you with the ability to install and run your SQL Server instance in a fully managed Azure virtual machine. This option is the best choice when you plan to perform a lift-and-shift from your on-prem environment to Microsoft Azure with the minimal possible changes on your applications and databases schema, providing you with full control over the SQL Server instance and the Operating System management and security configurations, allowing you to host any number of user databases on that SQL Server VM, and provide you with the ability to configure customized high availability and disaster recovery solution.
SQL Server on Azure VM is suitable for you if your company already has IT teams to administrate that virtual machine from OS, networking, and security perspectives. And you will be billed for both the storage used to store your data and the compute operations consumed on that VM.
Rather than waiting for the purchase approval for the new hardware, you can easily, in a few minutes, deploy a new virtual machine in Azure, install a new SQL Server instance using your own license and connect to that SQL Server instance, with the ability to scale it up and down based on your requirements, and stop it during the idle time and resume it again when needed.
Azure SQL Database
Azure SQL Database, categorized under the PaaS platform, is a cloud-computing database service that provides you with the ability to host and use SQL databases in the cloud without worrying about the hardware and the software requirements. Although you are not responsible for the hardware security, the operating system patching and security, and the database files, that are encrypted at rest using the TDE feature, you are still responsible for preventing unauthorized access to the data by limiting the allowed IP addresses from the firewall side and the authorized users from the database access and permissions configuration.
Azure SQL Database provides us with many features, including the ability to automate the backup operation and keep your backup for up to 10 years, create a readable secondary replica to distribute the reporting workload to another datacenter, tune the performance automatically, Point-In-Time Restore, built-in high-availability, and the ability to scale the database resources on the fly up and down, by changing the Database Throughput Unit (DTU) value, and scale-out with no downtime, without the need to wait for any new hardware purchase order as in the on-prems scaling processes, as shown below:
By providing the name of the database and a few other options, your database will be up and running and ready to serve your transactional workload in a few minutes. With no hardware or operating system to buy or manage, you will pay only for what you use. Feel free to use the Azure Total Cost of Ownership Calculator to estimate the cost of your PaaS service usage.
Azure SQL Database can be deployed as a single database, purchased by DTU or vCore models, with its own set of resources managed by a logical SQL Server, that can be used when the database usage is stable. It can be also deployed using an elastic pool, purchased by eDTU or vCore models, that contains a group of databases that share the same set of resources and managed by a logical SQL Server, providing the best choice for the databases with frequently changing usage patterns. If your application surface area scoped at the database level, using the Azure SQL Database is the best choice.
Azure SQL Managed Instance
Azure SQL Managed Instance is the best choice for migrating your transactional SQL workload to Microsoft Azure PaaS platform, with the minimal possible database changes. It provides all the benefits available in the Azure SQL Database with all other features provided by the SQL Server that is hosted on the Azure VM. This includes the SQL Agent that is used to manage, automate and schedule different types of tasks, the MSDTC that is responsible for managing the distributed transactions, Data Quality Service, Master Data Service, Database Mail, Filstream, FileTable, Polybase, and Linked Servers.
Azure SQL Managed Instance fits the applications with surface area scoped at the instance level. Where it provides an isolated environment, configurable backup retention and recovery, advanced workload analysis using Database Advisor and Log Analytics, automatic database tuning and maintenance, encryption of the data in transit and rest, and no patching and version upgrade overhead.
Azure SQL Data Warehouse
Azure SQL Data Warehouse, which is part of the Azure Synapse Analytics that combines the Azure Data Warehouse, the Big Data analytics capabilities, and the data integration for data movements, is a cloud-based Enterprise Data Warehouse solution, that leverages Massively Parallel Processing (MPP) architecture in order to run complex analytical queries across large data quickly, as the data will be stored into the relational tables with columnar storage, reducing the data storage costs and improves query performance from hours to minutes.
Azure SQL Data Warehouse is suitable for the analytical workload that queries large data with the ability to scale the compute and storage resources individually, based on the workload requirements and pauses the computing resources when you don’t need it, to minimize the cost.
Summary
In this article, we went through the SQL database services that are provided by Microsoft Azure, making the decision of choosing the suitable Azure database service as a target for your SQL workload migration easier. In the next article, we will discuss the different tools that can be used to migrate your SQL workload to Azure. Stay tuned!
Table of contents
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021