DP-900 domain - 23% of the exam

Identify Considerations for Relational Data on Azure

Identify Considerations for Relational Data on Azure is 23% of the DP-900 exam. These are the objectives it covers, each with practice questions, with every answer explained.

The domain in numbers

61
Practice questions
2
Objectives
21
Easy
39
Medium
1
Hard

Where people go wrong

  • Putting the patching duty on the wrong side

    The wrong answers move maintenance to the wrong party. They call Azure SQL Managed Instance an IaaS offering in which the customer installs and patches the operating system, reverse the responsibilities between a VM and a PaaS option, or say both are identical. Only SQL Server on Azure Virtual Machines is IaaS, so the customer decides when to update the operating system and SQL Server. The PaaS options automate updates, backups and recovery, and carry a higher guaranteed SLA.

    See it in a sample question
  • Choosing the neighbouring Azure SQL option

    The wrong answers name the adjacent Azure SQL service. Azure SQL Database is given as the answer where instance-level features or operating system control are required, Managed Instance where full administrative rights over the operating system are required, and a VM where Microsoft is meant to handle patching. SQL Server on Azure Virtual Machines gives full OS control. Managed Instance gives the highest SQL Server compatibility of the PaaS options, with automated maintenance. Azure SQL Database fits a brand-new cloud application.

    See it in a sample question
  • Mixing up stored procedures, views, indexes and keys

    The wrong answers hand a job to the wrong database object. An index, a view or a key is offered where a stored procedure is needed, and a stored procedure, an index or a key is offered where a view is needed. A stored procedure stores SQL statements that run on command and accept parameters such as an order ID. A view is a virtual table built from a SELECT query, so it suits a joined result queried like a table. An index only speeds up searches on a column.

    See it in a sample question
  • Moving an application to a different database engine

    The wrong answers send an application to a service that runs another engine. A MySQL web application is offered Azure SQL Database or Managed Instance, a PostgreSQL application is offered the MySQL service, and a LAMP application is offered SQL Server on a VM. Azure Database for MySQL and Azure Database for PostgreSQL are PaaS implementations of their own engines, so an application moves without significant changes and keeps its tooling, such as pgAdmin.

    See it in a sample question
  • Mixing up elastic pool, serverless and Hyperscale

    The wrong answers swap three Azure SQL Database options that each solve a different problem. An elastic pool lets several databases with different peak times share one set of resources and their cost. Serverless allocates and deallocates compute automatically and bills for use. Hyperscale handles very large databases by separating storage and compute. A single database dedicates its own resources, so it cannot share a pool.

    See it in a sample question

Objectives in this domain

Sample questions from this domain

Free sampleIdentify Considerations for Relational Data on Azuremedium

Running SQL Server on an Azure virtual machine is an infrastructure-as-a-service (IaaS) approach, whereas Azure SQL Managed Instance is a platform-as-a-service (PaaS) approach. Which statement correctly describes the difference in management responsibility between the two?

  • AWith SQL Server on an Azure VM the customer manages the operating system and SQL Server, including updates and backups, while a PaaS option has those tasks fully automated by the service. Correct
  • BWith SQL Server on an Azure VM the service automates operating system patching and backups for the customer, while the PaaS option is the one that requires the customer to patch the operating system manually.
  • CBoth the Azure VM option and the PaaS option leave operating system and SQL Server patching entirely to the customer, with no automation in either case.
  • DBoth the Azure VM option and the PaaS option fully automate operating system and SQL Server maintenance, so management responsibility is identical for the customer.
IaaS (SQL Server on a VM) keeps operating system and SQL Server maintenance with the customer; PaaS automates updates, backups, and recovery for you. SQL Server on an Azure VM is an IaaS solution where the customer must manage all aspects of the server, including the operating system and SQL Server updates, configuration, backups, and other maintenance tasks. A PaaS option such as Azure SQL Managed Instance abstracts the underlying hardware and operating system and provides fully automated updates, backups, and recovery, reducing the administrative burden on the customer.

Why A is correct: Correct. SQL Server on an Azure VM is an IaaS solution where the customer must manage all aspects of the server, including the operating system and SQL Server updates, configuration, backups, and other maintenance tasks.

Why B is wrong: This reverses the actual responsibilities. The VM (IaaS) option requires the customer to manage operating system and SQL Server maintenance, and the PaaS option is the one that automates updates and backups.

Why C is wrong: Only the VM (IaaS) option leaves the customer fully responsible for the operating system and SQL Server; the PaaS option provides fully automated updates, backups, and recovery, so the claim about the PaaS side is wrong.

Why D is wrong: The two options differ: the VM (IaaS) option requires the customer to manage the operating system and SQL Server, so responsibility is not identical, even though the PaaS option does automate that maintenance.

Free sampleIdentify Considerations for Relational Data on Azuremedium

An organization wants to move an existing on-premises SQL Server solution that uses advanced instance-level features to the cloud with minimal code changes. Which Azure SQL option is described as offering near-100% compatibility with SQL Server, making it the better choice for this migration?

  • AAzure SQL Database single database, optimized for new cloud-born applications with minimal instance dependencies
  • BAzure SQL Database elastic pool, which shares resources across multiple tenant databases
  • CAzure SQL Managed Instance, a PaaS service with near-100% compatibility with on-premises SQL Server Correct
  • DAzure Database for PostgreSQL, a managed service for the open-source PostgreSQL engine
Managed Instance offers near-100% SQL Server compatibility, the best PaaS fit when advanced on-premises features must move with minimal change. Azure SQL Managed Instance provides a higher degree of compatibility with on-premises SQL Server than SQL Database (near-100%), so it is the better choice for solutions with advanced features moving with minimal changes.

Why A is wrong: SQL Database supports most core capabilities but some features an on-premises app depends on may be unavailable, so it is less compatible than Managed Instance.

Why B is wrong: An elastic pool is a SQL Database deployment option for variable-usage databases, not a higher-compatibility migration target for instance-level features.

Why C is correct: Correct. Azure SQL Managed Instance provides a higher degree of compatibility with on-premises SQL Server than SQL Database (near-100%), so it is the better choice for solutions with advanced features moving with minimal changes.

Why D is wrong: PostgreSQL is a different open-source engine, not the SQL Server compatible option for migrating an on-premises SQL Server solution.

Free sampleIdentify Considerations for Relational Data on Azuremedium

A team wants to package SQL statements that accept a key as a parameter and can be run on command for a common data action. Which database object meets this need?

  • AAn index, which keeps a sorted copy of a column to speed up searches
  • BA view, which acts as a window on rows in one or more underlying tables
  • CA primary key, which marks the column that uniquely identifies each row
  • DA stored procedure, which defines SQL statements that can be run on command Correct
A stored procedure encapsulates SQL statements that run on command and can take parameters to perform common data actions. A stored procedure defines SQL statements that can be run on command and can be given parameters, encapsulating programmatic logic for common actions applied to data based on a specific key or criteria.

Why A is wrong: An index improves query speed but cannot accept parameters or be executed as a unit of logic.

Why B is wrong: A view exposes query results as a virtual table; it does not take parameters or run statements on command.

Why C is wrong: A primary key is a column constraint for uniqueness, not a container for runnable SQL statements.

Why D is correct: Correct. A stored procedure defines SQL statements that can be run on command and can be given parameters, encapsulating programmatic logic for common actions applied to data based on a specific key or criteria.

Free sampleIdentify Considerations for Relational Data on Azuremedium

A company runs a web application on a LAMP stack using the open-source MySQL Community Edition and wants to move it to Azure as a managed service without significant application changes. Which Azure relational service should they use?

  • AAzure SQL Database, the managed PaaS database built on SQL Server capabilities
  • BAzure SQL Managed Instance, providing near-100% compatibility with SQL Server
  • CAzure Database for PostgreSQL, a managed hybrid relational-object database engine
  • DAzure Database for MySQL, a PaaS implementation based on MySQL Community Edition Correct
Azure Database for MySQL is the managed PaaS service that lets existing MySQL applications move to Azure with minimal change. Azure Database for MySQL is a PaaS implementation of MySQL based on the Community Edition, created so organisations using MySQL in on-premises apps can move to Azure quickly without making significant changes to their applications.

Why A is wrong: Azure SQL Database is based on SQL Server, so a MySQL web application would need to be reworked rather than moved without significant changes.

Why B is wrong: Managed Instance targets SQL Server compatibility, not MySQL, so it cannot host a MySQL application unchanged.

Why C is wrong: Azure Database for PostgreSQL runs PostgreSQL, a different engine, so a MySQL application cannot move to it without significant changes.

Why D is correct: Correct. Azure Database for MySQL is a PaaS implementation of MySQL based on the Community Edition, created so organisations using MySQL in on-premises apps can move to Azure quickly without making significant changes to their applications.

Free sampleIdentify Considerations for Relational Data on Azuremedium

A team wants an Azure SQL Database configuration where compute scales automatically and resources are allocated or deallocated as required, so they are billed for usage rather than for resources reserved in advance during idle periods. Which Azure SQL Database configuration is described?

  • AThe default Single Database configuration, where resources are preallocated and you are charged per hour for the resources you have requested whether or not the database is active.
  • BAn Elastic Pool, where multiple databases share a fixed pool of memory, storage, and processing power that you create and size in advance for the group.
  • CThe serverless configuration, where the database automatically scales and resources are allocated or deallocated as required so you are billed for what the database actually uses. Correct
  • DThe Hyperscale service tier, which targets very large databases of up to 100 TB with rapid on-demand scaling and fast backup and restore regardless of data size.
Serverless Azure SQL Database scales compute automatically and allocates or deallocates resources as required, so you pay for usage rather than for idle reserved capacity. The serverless configuration of Azure SQL Database lets the database scale automatically, with resources allocated or deallocated as required. That matches a billing model based on actual usage rather than on resources preallocated and paid for during idle periods, which suits intermittent or unpredictable workloads.

Why A is wrong: By default Single Database resources are preallocated and billed per hour for the requested capacity, so they are not allocated or deallocated automatically as the description requires.

Why B is wrong: An Elastic Pool shares a pool of resources across several databases to smooth their combined load; it does not automatically allocate and deallocate compute for a single database as the description requires.

Why C is correct: Correct. The serverless configuration of Azure SQL Database lets the database scale automatically, with resources allocated or deallocated as required.

Why D is wrong: Hyperscale is aimed at very large databases of up to 100 TB with fast backup and restore; it addresses scale of data, not the automatic allocation and deallocation of compute described here.

Other domains in this exam

See also the DP-900 cert hub, the study guide, and the cheat sheet.

Examworthy is not affiliated with or endorsed by Microsoft. Original, blueprint-aligned practice material only.