Identify Considerations for Relational Data on Azure · Topic 3 of 3
Azure Relational Data Services
DP-900 Azure relational services: Azure SQL Database, SQL Managed Instance, SQL Server on Azure VMs, Azure Database for PostgreSQL and MySQL, and how to choose.
- 6 min
- Includes a diagram
- Azure SQL Database is PaaS, with single database, elastic pool and serverless options.
- Managed Instance is PaaS with near complete SQL Server compatibility, and is the lift and shift choice.
- SQL Server on Azure VMs is IaaS and gives the most control.
What it is
Azure offers relational databases at several levels of management. The Azure SQL family runs the SQL Server engine in three forms, from fully managed to fully yours. Separate services run the open source engines PostgreSQL and MySQL.
The exam asks you to pick the right service for a short scenario, so the key is knowing how much control each one gives.
How it works
The Azure SQL family
Azure SQL Database is a fully managed platform as a service. Microsoft handles patching, backups and high availability, so you only design the database. It is the best fit for new cloud applications. It comes as a single database, as an elastic pool where several databases share one set of resources, and in a serverless compute option that pauses when idle and bills for use.
Azure SQL Managed Instance is also managed, but it behaves like a whole SQL Server instance. It offers near complete compatibility with SQL Server, including features such as SQL Server Agent and queries across databases. It suits lift and shift moves of existing applications with minimal change.
SQL Server on Azure Virtual Machines is infrastructure as a service. You get a virtual machine with SQL Server installed, with full control of the operating system and the engine, and you manage patching and backups yourself. It suits software that needs operating system access or a specific version.
| Azure SQL Database | SQL Managed Instance | SQL Server on Azure VM | |
|---|---|---|---|
| Service model | PaaS | PaaS | IaaS |
| Compatibility with on premises SQL Server | High | Near complete | Complete |
| OS access | No | No | Yes |
| Patching and backups | Microsoft | Microsoft | You |
| Best for | New cloud apps | Lift and shift | Full control, special needs |
Pricing and protection
Azure SQL Database can be bought on a DTU model, a blended measure of CPU, memory and input and output, or on a vCore model where you choose cores and memory separately. Backups are automatic. A full backup, differential backups and transaction log backups together allow restore to a point in time. The retention for point in time restore is seven days by default and can be set from one to 35 days. Long term retention can keep backups for up to ten years.
Security features include firewall rules, Microsoft Entra authentication, encryption of data at rest by default, and Microsoft Defender for SQL, which finds vulnerabilities and detects threats.
Open source relational services
Azure Database for PostgreSQL and Azure Database for MySQL are managed services for those open source engines. They suit teams that already use them or want to move an open source application to Azure without running servers. Microsoft handles patching, backups and high availability.
Choosing a service
| The requirement | Choice |
|---|---|
| A brand new application, least administration | Azure SQL Database |
| Many small databases with different peak times | An elastic pool |
| Move an existing SQL Server estate with cross database queries | SQL Managed Instance |
| Software that needs operating system access | SQL Server on Azure VM |
| An application built for PostgreSQL | Azure Database for PostgreSQL |
| An application built for MySQL | Azure Database for MySQL |
A worked scenario
A company wants to move a large SQL Server system to Azure quickly. It uses SQL Server Agent jobs and queries that join tables in different databases. Rewriting the system is too expensive. SQL Managed Instance fits, because it keeps those features while Microsoft handles patching and backups. If a vendor application also demanded operating system access, only SQL Server on an Azure virtual machine would suit it.
Service tiers in Azure SQL Database
With the vCore model you also choose a service tier.
| Tier | Aim |
|---|---|
| General Purpose | A balanced choice for most business workloads |
| Business Critical | High performance and low latency, with a built in read only replica |
| Hyperscale | Very large databases that need fast scaling of storage |
The built in replica of Business Critical is a feature called read scale out, and it lets reports run without slowing the main database.
Elastic pools in practice
Suppose a software company runs 50 small databases, one for each customer, and each is busy at a different time of day. Giving every database its own large capacity would waste money, because most sit idle most of the time. An elastic pool lets all 50 share one set of resources, so a database that needs a burst borrows from the pool and then releases it.
High availability and recovery
Azure SQL Database keeps copies of the data and fails over automatically if a node fails, without any work from you. For a regional disaster you can add geo replication or an auto failover group, which keeps a readable secondary database in another region. Backups protect against mistakes, such as deleting a table, by letting you restore to a point in time.
Which questions point to which service?
| Wording in the question | Likely answer |
|---|---|
| Fully managed, pay for what you use, pauses when idle | Azure SQL Database serverless |
| Near 100 percent compatibility, migrate with minimal changes | SQL Managed Instance |
| Install a specific version, access the operating system | SQL Server on Azure Virtual Machines |
| Open source, community edition engine | Azure Database for PostgreSQL or MySQL |
What to remember for the exam
- Azure SQL Database is PaaS, with single database, elastic pool and serverless options.
- Managed Instance is PaaS with near complete SQL Server compatibility, and is the lift and shift choice.
- SQL Server on Azure VMs is IaaS and gives the most control.
- Point in time restore defaults to seven days, and can be set to 35 days.
- PostgreSQL and MySQL have their own managed services.
If a question needs operating system access, the only Azure SQL family answer is SQL Server on Azure Virtual Machines.
How the exam asks about this
- "Least administrative effort for a new app." Azure SQL Database.
- "Many databases sharing resources." An elastic pool.
- "SQL Server Agent and cross database queries." Managed Instance.
- "Backups are your own responsibility." SQL Server on a virtual machine.
Common mistakes
Choosing a virtual machine out of habit. It gives control but adds patching and backup work that the PaaS options remove.
Thinking Managed Instance and Azure SQL Database are the same. Managed Instance is a near complete SQL Server instance. SQL Database is a single database service.
Assuming open source engines need a virtual machine. Both have fully managed Azure services.
Forgetting that PaaS backups are automatic. You configure retention but do not run them.
Check yourself
Which Azure SQL option gives full operating system access?
SQL Server on Azure Virtual Machines.
Which option suits a lift and shift of an app that uses SQL Server Agent?
Azure SQL Managed Instance.
How long does Azure SQL Database keep point in time backups by default?
Seven days.
Topic 3 of 3 in Identify Considerations for Relational Data on Azure
Test yourself on Identify Considerations for Relational Data on Azure
The full Microsoft DP-900 question bank has 503 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.