DP-900 Practice Question: Identify considerations for relational data on Azure
A company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database has a large fact table that is partitioned by date (monthly partitions) to improve query performance and simplify data archiving. The company wants to maintain the same partitioning strategy in Azure to avoid rewriting queries. Which feature in Azure SQL Managed Instance should they use to achieve this?
⚠ Common exam trap
It's easy for candidates to confuse table partitioning with sharding or index partitioning, assuming any form of data distribution will work, but only table partitioning with partition functions and schemes preserves the exact same structure and query semantics in Azure SQL Managed Instance.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Table partitioning with partition functions and schemes
Azure SQL Managed Instance supports table partitioning using partition functions and partition schemes, which is the same feature available in SQL Server. This allows you to define monthly partitions on the fact table using a date column, preserving the existing partitioning strategy and query logic without modification. The partition function maps rows to partitions based on the date boundary values, and the partition scheme assigns those partitions to filegroups.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Table partitioning with partition functions and schemes
Why this is correct
In Azure SQL Managed Instance, table partitioning is fully supported using the same T-SQL syntax as on-premises SQL Server: you create a partition function to map rows to partitions based on boundary values, then a partition scheme to assign those partitions to filegroups. When migrating, the restored database retains its partition metadata, so existing queries, partition switches, and partition-aligned indexes continue to work without redesign. This is the only option that preserves the original partition design rather than replacing it with a different architecture.
- ✗
Sharding across multiple Azure SQL Managed Instances
Why it's wrong here
Sharding across multiple Azure SQL Managed Instances is a horizontal scaling pattern that splits logical data across independent databases, each with its own schema and connection endpoint. It requires application changes for routing and does not replicate the single-database partition function and scheme used in your on-premises SQL Server design. Table partitioning, by contrast, remains inside one database and is transparent to queries. Treating sharding as a substitute would over-complicate the migration and break the existing partitioning logic.
- ✗
Index partitioning only
Why it's wrong here
Index partitioning only is insufficient because a partitioned index can only exist on a partitioned table; the index's partition columns must align with the table's partition function and partition scheme. Creating indexes on the source table does not create the table's partition function or partition scheme, so the rows themselves are not stored according to the original design. Without the underlying table partition metadata, you cannot implement sliding-window maintenance or partition-level switching, and the migration will fail to preserve the existing partitioned schema.
- ✗
Federated tables
Why it's wrong here
'Federated tables' refer to an older design where data is distributed across multiple SQL Server instances and accessed through distributed partitioned views or linked servers, not to native SQL Server table partitioning. Azure SQL Managed Instance does not support a federated-table partitioning mechanism, and this approach would not recreate the partition function and partition scheme from your on-premises database. To migrate faithfully, you must keep the existing table partitions inside the managed instance rather than spreading them across nodes with federation.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
Key term
SQL Managed Instance
SQL Managed Instance is a cloud database service from Azure that gives you most of the features of a full SQL Server instance without you having to manage the underlying hardware or software patches.
About these practice questions
This DP-900 question is part of Courseiva's 820-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.