Migration interviews are really testing one thing underneath all the specific tool names: can you move a production database to Azure without breaking the business along the way? That’s a very different skill from knowing what a feature does — it’s about sequencing, risk management, and knowing exactly what breaks if you skip a step.
This list is split into Beginner (1–17), Intermediate (18–35), and Advanced (36–50), written the way you’d actually explain it to someone across the table — not a tool feature list, but the reasoning behind why you’d pick one migration path over another for a specific situation.
Migration interviews love the phrase “it depends on your downtime tolerance.” That’s not a dodge — it’s genuinely the first question that determines almost every other decision in a migration plan, and a good answer explains what changes based on the answer, not just states the phrase.
Beginner Level
1. What are the main target options when migrating an on-premise SQL Server database to Azure? Three main destinations: Azure SQL Database (PaaS, single database or elastic pool, least admin overhead but some feature limitations), Azure SQL Managed Instance (PaaS, near-100% SQL Server compatibility, best for lift-and-shift), and SQL Server on Azure VM (IaaS, full control, closest to what you already have on-prem).
2. How do you decide which target to migrate to? It comes down to how much your application depends on features Azure SQL Database doesn’t support — SQL Agent, cross-database queries, linked servers, CLR — and how much operational overhead you’re willing to keep managing yourself. Full compatibility need with minimal re-engineering points to Managed Instance; cloud-native flexibility and lowest admin burden points to Azure SQL Database; needing something Azure SQL just can’t do at all (specific OS-level software, unsupported features) points to a VM.
3. What is the Data Migration Assistant (DMA), and when do you use it? A free Microsoft tool that assesses your on-prem database for compatibility issues and deprecated features before you migrate — things that simply won’t work the same way, or at all, on the target. You run it early, during the assessment phase, specifically so you find out about blockers weeks before a migration weekend, not during one.
4. What’s the difference between an assessment and an actual migration? Assessment is the “will this even work” phase — checking compatibility, sizing, and cost, using tools like DMA or the Azure Migrate service, without touching production. Migration is the actual data movement and cutover. Skipping assessment and jumping straight to migration is one of the most common and most painful beginner mistakes.
5. What is the Azure Database Migration Service (DMS)? A managed service purpose-built for moving databases into Azure, supporting both offline (one-time cutover, downtime during copy) and online (near-zero-downtime, continuous sync) migration modes, depending on the source/target combination and your downtime tolerance.
6. What’s the difference between an offline and an online migration? Offline means you take an initial full copy, and the source is effectively unavailable (or at least frozen from writes) for the duration of that copy and cutover — simpler, but the database is down while it happens. Online means the initial copy happens while the source stays live and serving traffic, followed by continuous synchronization of ongoing changes, so the actual downtime window shrinks down to just the final cutover — more complex, but far less disruptive for a system that can’t afford a long outage.
7. What is a BACPAC file, and when would you use it for migration? A BACPAC is an export format containing both schema and data for a database, packaged into a single file. It’s a straightforward way to migrate a smaller database with some downtime tolerance — export from on-prem, import into Azure SQL Database — but it’s not designed for very large databases or near-zero-downtime scenarios, since it’s fundamentally a point-in-time, offline-style export/import.
8. What is transactional replication, and how is it used in migration? A SQL Server feature that continuously replicates changes from a publisher (source) to a subscriber (target) database. In migration, it’s one way to achieve a near-zero-downtime cutover for scenarios DMS’s online mode doesn’t directly cover — you set up replication ahead of time, let it catch up, then cut the application over to the target once replication lag is effectively zero.
9. What are the typical compatibility issues DMA flags for Azure SQL Database migrations? Dependencies on SQL Server Agent jobs, cross-database queries and joins, linked servers, certain deprecated T-SQL syntax, and CLR usage — all things that either don’t exist or work differently in Azure SQL Database’s more restricted PaaS model compared to full SQL Server or Managed Instance.
10. What’s the first thing you should check before planning any migration? Feature and compatibility gaps between your source and intended target, using DMA — because everything else in the plan (which target to choose, what downtime window is realistic, what code changes are needed) depends on knowing what actually won’t work as-is, before you commit to a specific migration path.
11. How do you migrate SQL Server Agent jobs when moving to Azure SQL Database? Since Azure SQL Database doesn’t have SQL Agent, jobs need to be re-implemented using Elastic Jobs, Azure Automation runbooks, or Azure Data Factory pipelines, depending on what the job actually does — a T-SQL maintenance job might map cleanly to Elastic Jobs, while a more complex ETL job might be better suited to Data Factory.
12. What’s the simplest migration path for a small, low-traffic database that can tolerate some downtime? Export a BACPAC from the on-prem database and import it directly into Azure SQL Database via the portal, SSMS, or sqlpackage — straightforward, minimal tooling, appropriate specifically because the downtime tolerance and database size make the simplicity worth the trade-off versus a more complex near-zero-downtime approach.
13. What is Azure Migrate, and how does it relate to database migration specifically? Azure Migrate is a broader Azure service for discovering and assessing on-prem infrastructure (servers, databases, web apps) for migration readiness, including a dedicated SQL Server assessment tool that estimates target sizing (which Azure SQL tier and size fits your current workload) alongside compatibility findings — often used as the earliest, broad-discovery step before drilling into DMA for database-specific detail.
14. What is right-sizing, and why does it matter during migration? Right-sizing means picking a target service tier/compute size that actually matches your real workload, rather than either wildly over-provisioning (wasting money) or under-provisioning (causing performance problems on day one). Azure Migrate’s assessment can recommend a starting size based on captured on-prem performance data, though it’s still worth validating against real Azure Monitor metrics after cutover rather than treating the recommendation as final.
15. What should you test before cutting over an application to the migrated database? Application functionality end-to-end against the new database, query performance under realistic load (not just “does it connect”), and a validated rollback plan in case something’s wrong post-cutover — skipping the rollback plan specifically is a common beginner mistake that turns a minor issue into a crisis.
16. What’s the risk of migrating without first running a compatibility assessment? You might discover, mid-migration or worse, after cutover, that something critical — a linked server dependency, a SQL Agent job nobody remembered was important, a deprecated feature the application quietly relies on — simply doesn’t work on the target, forcing an emergency rollback or a scramble to re-engineer under pressure instead of on your own schedule.
17. What is the general high-level sequence of a migration project? Discover and assess (what do you have, what will and won’t work), plan (choose target, choose migration method, choose cutover window), migrate/sync (move schema and data, keep source and target in sync if doing an online migration), validate (test functionality and performance on the target), and cutover (switch the application to the target, with a rollback plan ready) — roughly in that order, though assessment and planning often loop back and forth a few times before you’re confident enough to move to the migrate phase.
Intermediate Level
18. Walk through how you’d plan a near-zero-downtime migration of a business-critical database using Azure Database Migration Service. Start with DMA to clear compatibility blockers well ahead of time. Provision the target (Managed Instance or Azure SQL Database, depending on compatibility needs) and set up DMS in online mode — it performs an initial full data copy while the source stays live, then continuously applies transaction log changes to keep the target in sync. I’d monitor replication lag until it’s consistently near zero, schedule the actual cutover for a low-traffic window anyway even though downtime is minimal, do a final validation pass, then switch the application’s connection string over — with the source kept available and in sync a while longer as a rollback safety net before fully decommissioning it.
19. What are the key differences in migration approach between targeting Azure SQL Database versus Azure SQL Managed Instance? Azure SQL Database migrations require resolving every compatibility gap DMA flags — there’s no fallback if the application depends on something the target simply doesn’t support. Managed Instance migrations are generally more forgiving because of near-full SQL Server compatibility, meaning less application re-engineering, but you’re trading that for a PaaS offering with somewhat different performance characteristics and cost model than Azure SQL Database, and a migration process that has its own quirks (like a typically longer initial provisioning time for the instance itself).
20. How would you handle SQL Server Agent job migration when moving to Azure SQL Managed Instance versus Azure SQL Database? Managed Instance supports SQL Server Agent natively, so most jobs migrate with little to no change — a genuine advantage for lift-and-shift scenarios with a lot of existing job infrastructure. Azure SQL Database has no Agent at all, so every job needs to be re-implemented — simple maintenance jobs often map to Elastic Jobs, while more complex orchestration or ETL logic is often better re-platformed onto Azure Data Factory or Azure Automation, which is real migration effort that’s easy to underestimate if you assume jobs are a minor detail.
21. What’s your approach to migrating a database with heavy reliance on linked servers? First determine whether the target actually needs to solve this the same way — Managed Instance supports linked servers, so if compatibility need justifies that target choice, linked servers may migrate close to as-is. For Azure SQL Database, which doesn’t support linked servers in the traditional sense, I’d evaluate whether Elastic Query can serve the same cross-database query pattern, or whether the actual right answer is re-architecting that specific integration (via an application-layer call or a data pipeline) rather than trying to force a direct feature-for-feature replacement that doesn’t exist on that target.
22. How do you validate data integrity after a migration, beyond just “the row counts match”? Row counts are a starting sanity check, not sufficient validation on their own — I’d also compare checksums or hash values on key tables between source and target, validate referential integrity and constraint behavior actually matches, and specifically test edge cases like NULL handling, collation-sensitive comparisons, and datetime precision, since subtle data type or collation mismatches between source and target can silently produce different query results even when every row technically “migrated.”
23. What’s the significance of collation differences between on-prem SQL Server and Azure SQL during migration? If the source database’s collation differs from the target’s default (or from what you configure), string comparisons, sorting order, and case-sensitivity behavior can change in ways that break application logic silently — a query that worked correctly against Latin1_General_CI_AS might behave differently against a different collation. I’d explicitly set the target database’s collation to match the source at creation time rather than accepting whatever default gets applied, and specifically test string-comparison-heavy logic post-migration.
24. How would you approach migrating a very large database (multiple terabytes) with a limited downtime window? Online migration via DMS is the starting point regardless of size, since it decouples the bulk of data movement from the actual cutover downtime. For genuinely enormous databases, I’d also evaluate whether Hyperscale as a target changes the calculus — its storage architecture makes post-migration operations like backup and further scaling much faster regardless of database size, which matters for ongoing operations even if it doesn’t change the initial migration approach itself. I’d also seriously consider network bandwidth between source and Azure as a potential bottleneck worth testing early, not assuming it’ll be fine.
25. What’s the role of a pilot or test migration before the production cutover? A pilot migration — running the full process against a copy of production data in a non-production Azure environment — surfaces problems (compatibility issues DMA missed, performance surprises, application bugs against the new target) while there’s no real risk, and gives you an accurate time estimate for how long the actual cutover will take, which is critical for planning a realistic maintenance window rather than guessing and potentially running over.
26. How do you handle application connection string changes during cutover with minimal application-side risk? Where possible, I’d use a stable abstraction — a failover group’s listener endpoint, or a DNS CNAME the application already points to — so the actual cutover is a single DNS or configuration change rather than a code deployment across every application instance. I’d also make sure the application has reasonable connection retry logic, since even a well-planned cutover has a brief window where connections might transiently fail during the switch.
27. What’s the difference between migrating via transactional replication versus using DMS online mode? Both achieve continuous sync for near-zero-downtime cutover, but DMS is purpose-built for migration specifically — it handles the initial seeding and ongoing sync as one integrated, managed workflow with built-in monitoring for migration progress. Transactional replication is a more general-purpose SQL Server feature you’re repurposing for migration, giving you more manual control (and more manual setup) — useful in scenarios DMS doesn’t directly support, but generally more operational overhead for a straightforward migration DMS could otherwise handle end-to-end.
28. How would you plan for and test a rollback if a post-cutover issue is discovered? I’d keep the source database intact and, ideally, still receiving a light sync (or at minimum, not yet decommissioned) for a defined grace period after cutover specifically so rollback is a real option, not just a plan on paper. I’d also predefine what actually triggers a rollback decision — specific error rates, specific broken functionality — before cutover, so that decision isn’t made under panic in the moment, and I’d have tested the rollback mechanism itself beforehand, not just assumed it would work.
29. What’s your approach to migrating databases with heavy use of CLR (Common Language Runtime) objects? Check target support first — Managed Instance supports CLR (with some restrictions), Azure SQL Database has limited/no support for custom CLR assemblies. For Azure SQL Database as a target, I’d evaluate whether the CLR logic can be reimplemented in native T-SQL, or moved to the application layer entirely, treating this as a genuine re-engineering task to scope early rather than something to discover mid-migration.
30. How do you handle security and permissions migration alongside the data itself? Logins and users don’t migrate automatically the same way data does — especially moving from SQL Authentication on-prem toward a target using Azure AD/Entra ID authentication, which is a genuine identity model change, not just a data copy. I’d map out the on-prem login/permission structure explicitly, decide the target’s authentication approach deliberately (not just replicate SQL logins by default), and recreate users/role memberships and granular permissions on the target as an explicit step in the plan, then validate application connectivity under the new identity model before cutover.
31. What’s the significance of testing query performance on the target before cutover, not just after? A query that performed fine on-prem, with local storage and a fixed hardware profile, can behave differently against Azure SQL’s different I/O characteristics, service tier resource limits, or a slightly different optimizer version — discovering this after cutover means users are already experiencing it. I’d run representative production-like query workloads against the target in the pilot phase specifically to catch performance regressions while there’s still time to fix them without cutover pressure.
32. How would you handle migrating a database that uses features not supported in Azure SQL Database, when the business insists on that target for cost reasons? I’d present the actual trade-off clearly rather than silently working around it — quantify the re-engineering effort required to replace each unsupported feature (a linked server dependency, an Agent job, CLR logic) against the cost delta of choosing Managed Instance instead, since sometimes the “cheaper” target ends up costing more in total migration effort and risk than the pricier target that requires no re-engineering at all. That’s a business decision to make with accurate numbers, not one to make silently by just picking the cheaper option and hoping the gaps aren’t a real problem.
33. What monitoring would you put in place immediately after cutover, and why right away rather than “eventually”? Azure Monitor metrics for the core resource dimensions (CPU, storage, connections, DTU/vCore), Query Store enabled from day one to establish a performance baseline on the new target, and alerting for connection failures specifically, since a subtle post-migration connectivity or authentication issue is exactly the kind of problem that’s easy to miss if nobody’s actively watching in the first 24–48 hours after cutover, when the system is least proven under real production load.
34. How do you approach migrating a database with an existing high-availability setup (like an on-prem Always On Availability Group) to Azure? Rather than trying to replicate the exact on-prem HA topology, I’d map the requirement (RPO/RTO, read-scale-out needs) onto the equivalent Azure-native capability — Business Critical tier’s built-in HA architecture largely replaces the need for a manually configured AG, and failover groups replace a manually configured DR setup across sites. It’s usually the wrong instinct to try to recreate the exact on-prem architecture instead of using what the target platform already provides natively for the same purpose.
35. What’s your process for estimating a realistic migration timeline for a moderately complex production database? I’d break it into assessment (DMA/Azure Migrate findings and remediation of any blockers), a pilot migration to get a real, measured time estimate for the data movement itself rather than a guess, application testing time against the pilot target, and a buffer for whatever the pilot surfaces that needs fixing before the real cutover — and I’d always present that as a range, not a single date, until the pilot migration has actually run once, because until then any estimate is still partly a guess.
Advanced Level
36. Design a migration strategy for a 10TB, mission-critical OLTP database with a maximum tolerable downtime of 5 minutes. I’d use DMS in online mode targeting Managed Instance or Azure SQL Database (Business Critical or Hyperscale, depending on further sizing analysis), with the initial full data copy running well ahead of the cutover date while the source stays fully live. I’d monitor replication lag continuously in the days before cutover to confirm it consistently stays near zero under real production load, not just during quiet periods, and schedule cutover for the lowest-traffic window regardless. The actual 5-minute budget covers: stopping new writes on the source, confirming final sync completion, switching the application’s connection endpoint, and validating the first few real transactions succeed — everything else (data copy, ongoing sync, testing) happens well before that window, because trying to fit terabyte-scale data movement into a 5-minute window is never the right approach; only the final cutover step should be time-boxed that tightly.
37. How would you handle migrating a database with Always Encrypted columns from on-prem to Azure SQL? The encrypted data itself migrates as ciphertext without issue via standard migration tooling, since the database engine never needs to decrypt it during a normal data copy. The genuinely tricky part is key management — the column master key needs to be accessible to the target environment (typically moving from a local certificate store on-prem to Azure Key Vault), and I’d plan that key migration as its own explicit workstream, tested independently before the main data migration, since a failure here doesn’t show up as a migration error — it shows up as the application being unable to decrypt data post-cutover, which is a much worse discovery moment.
38. What’s your approach to migrating a database with extensive use of SQL Server Integration Services (SSIS) packages? Azure offers Azure-SSIS Integration Runtime within Azure Data Factory specifically to lift-and-shift existing SSIS packages with minimal rework, which I’d evaluate first for packages that are fundamentally sound and just need a new home. For packages that were already fighting against SSIS’s model, or that depend on on-prem-only connectivity/resources, I’d treat migration as an opportunity to re-platform onto native Data Factory pipelines instead — a case where “lift and shift” isn’t automatically the right call just because a lift-and-shift path technically exists.
39. How would you design a migration approach for a database using cross-database transactions, given Azure SQL Database doesn’t support them the same way? First confirm the target — if Managed Instance is viable given other constraints, cross-database transactions largely continue to work, avoiding this problem entirely. If Azure SQL Database is the required target, I’d analyze whether the cross-database transactional coupling is genuinely required for correctness, or whether it’s incidental coupling that formed because the databases happened to be co-located on the same on-prem instance — often the real fix is architectural: either consolidating what should have been one database into one, or accepting eventual consistency via an application-level saga pattern or Elastic Transactions for the specific cases that truly need distributed transaction guarantees.
40. Explain how you’d approach a phased, application-by-application migration for an organization with many interconnected on-prem databases, rather than a single big-bang cutover. I’d map the actual dependency graph between databases and applications first — which databases share transactions, which are read dependencies only, which are genuinely independent — because that graph, not organizational convenience, should drive migration sequencing. I’d migrate the most independent, lowest-risk databases first to build confidence and refine the process, explicitly identify any pair of databases with tight coupling (like cross-database transactions or heavy synchronous integration) as candidates that likely need to migrate together in the same wave rather than split across phases, and keep a working hybrid-connectivity bridge (VPN/ExpressRoute plus linked-server-equivalent patterns) for the transitional period where some databases are migrated and others aren’t yet.
41. How would you handle migrating a database with heavy dependency on specific SQL Server version features (like a particular compatibility level’s query optimizer behavior) that the business is nervous about changing? I’d set the target database’s compatibility level to match the source explicitly at migration time, rather than accepting whatever the newest default is, specifically to avoid an unplanned optimizer behavior change landing at the same time as the migration itself — two simultaneous risks instead of one. I’d treat upgrading compatibility level as a deliberate, separate, later change with its own testing cycle, using Query Store’s plan comparison capability to validate the impact before committing to it, once the migration itself is stable and no longer the active risk.
42. What’s your strategy for validating that a migrated database’s performance characteristics genuinely match or exceed the source, not just “seems fine”? I’d capture a real baseline from the source using Query Store or extended events before migration — actual query durations, resource consumption, and wait stats under real production load, not synthetic benchmarks — then run the same captured, representative workload against the migrated target and do a direct, query-by-query comparison rather than a vague overall impression. Any query that regressed gets investigated specifically (a missing index recommendation not yet applied, a statistics difference, a genuinely different optimizer behavior) before cutover, not discovered afterward through a user complaint.
43. How would you approach migrating a database subject to strict data residency requirements, where the on-prem data center and the required Azure region combination complicates the migration path? I’d confirm early — before any technical planning — exactly which Azure regions satisfy the residency requirement, since this can eliminate certain migration approaches entirely (some methods route through intermediate storage that itself needs to respect residency constraints, not just the final target). I’d also verify that DMS or whichever tool is chosen doesn’t stage data through a non-compliant region during the migration process itself, since a residency violation during transit is still a violation even if the final resting location is fully compliant.
44. Describe how you’d handle a migration where the source database’s transaction log growth rate during migration threatens to overwhelm network bandwidth to Azure. I’d first quantify actual sustained transaction log generation rate against measured available bandwidth to confirm this is a real constraint and not a theoretical worry, since guessing here leads to over-engineering a problem that might not exist at the actual scale. If it’s genuinely a bottleneck, options include ExpressRoute for higher, more reliable bandwidth than a standard VPN, scheduling the initial bulk data seed during a lower-write-activity window to reduce the log volume DMS needs to catch up on afterward, or in extreme cases, physically shipping an initial seed via Azure Data Box and letting DMS handle only the ongoing incremental sync from that point forward rather than the full initial copy over the network.
45. How would you design a security-conscious migration plan that avoids exposing sensitive data during the transition period itself, not just before and after? I’d ensure the migration pipeline itself — DMS, any staging storage, network path — uses encryption in transit throughout, confirm TDE is active on the target from the moment data lands (it’s on by default, but I’d verify rather than assume), and specifically avoid any migration approach that stages a full unencrypted export (like a plain BACPAC) in a location with broader access than the source database itself had. The migration process is itself part of the security surface for that data, not a gap where normal data protection standards get temporarily relaxed for convenience.
46. What’s your approach to migrating stored procedures and functions that rely on deprecated or removed T-SQL features? DMA flags most of these during assessment, but I’d treat that list as a work item to scope and estimate individually, not just a checkbox to clear — some deprecated feature usage has a trivial modern equivalent, while others (certain legacy full-text search syntax, older XML handling patterns) require genuine rewrite effort. I’d prioritize fixing based on how business-critical and how frequently-executed each affected procedure is, rather than treating every flagged item as equally urgent.
47. How would you handle post-migration decommissioning of the on-prem source, balancing cost savings against rollback safety? I’d define an explicit, time-boxed grace period upfront (commonly 2–4 weeks, adjusted for the specific system’s criticality) during which the source stays available, ideally still lightly synced, purely as a rollback safety net — and I’d get explicit stakeholder sign-off on when that period ends, rather than letting “we’ll decommission it eventually” drift indefinitely and either costing money unnecessarily or getting decommissioned prematurely by someone unaware it was still a safety net.
48. Explain how you’d approach performance tuning differences discovered post-migration that stem from Azure SQL’s different I/O and memory architecture compared to on-prem hardware. I’d start from wait statistics on the target rather than assuming the same tuning approach that worked on-prem transfers directly — Azure-specific waits like LOG_RATE_GOVERNOR simply don’t exist on-prem and point to a genuinely different kind of constraint (service tier throughput limits, not raw hardware capacity) requiring a different fix (reduce log-generating activity or scale tier, not add local disk). I’d treat “why is this slower on Azure” as its own diagnostic question with Azure-specific root causes, rather than assuming the on-prem tuning playbook applies unmodified.
49. How would you structure a migration runbook so that it’s executable by someone other than the person who wrote it, during an actual cutover window? Every step needs to be a concrete, verifiable action with an explicit success criterion — not “verify replication is caught up” but “confirm DMS dashboard shows 0 pending changes for 3 consecutive minutes” — plus an explicit decision point and rollback trigger condition at each major step, so whoever’s executing it isn’t making judgment calls under pressure that the plan should have already made for them. I’d have it reviewed and, ideally, dry-run by someone who wasn’t involved in writing it, specifically to catch the assumptions the author didn’t realize they were making.
50. Walk through a genuinely difficult migration you’ve handled or would expect to handle, and your end-to-end decision process. This is intentionally open-ended, because interviewers are evaluating judgment and sequencing, not a memorized script. A strong structure to demonstrate regardless of the specific scenario: (1) assess honestly before committing to an approach — real compatibility findings and real sizing data, not assumptions carried over from the on-prem environment, (2) choose the target and migration method based on the actual downtime tolerance and compatibility findings, not habit or what’s fastest to set up, (3) pilot the approach against realistic data and load before it’s the real cutover, (4) execute cutover against a runbook with explicit rollback triggers defined in advance, not decided in the moment, and (5) validate performance and functionality against a real pre-migration baseline afterward, not a vague impression that “it seems fine.” Interviewers remember candidates who can describe what they’d do when the pilot reveals a problem — because that, not the happy path, is what actually distinguishes migration experience from migration theory.
A Closing Thought
Almost every advanced answer above comes back to the same discipline: assess honestly, test before you commit, and always know your rollback path before you need it. That’s not a coincidence — migrations are one of the few places in database work where a mistake is genuinely hard to undo cleanly, which is exactly why interviewers probe so hard on process and sequencing rather than just tool names.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
