recommend-migration-path

# Skill: Recommend Migration Path These claims were checked on 2026-09-15 against the bundled `skill-contract.yml`, decision rules, input/output schemas, and knowledge base v3.14. ## Description Help the user get a **preliminary recommendation / recommended assessment path** for a SQL Server migration to Azure. This skill is a **discovery and pre-selection assistant**, not a final architecture decision. Every recommendation remains **provisional** until validated by assessment tooling and an architect. When auditing a recommendation, trace it to the knowledge base [`references/knowledge-base.md`](https://github.com/fredgis/sql-migration-advisor/blob/main/docs/sql-server-to-azure-migration.md) and the bundled `references/decision-rules.md`. ## When to Use Invoke this skill when the user: - wants to migrate or modernize a SQL Server estate to Azure and no assessment data exists yet; - asks which Azure target, migration method or tool fits their workload; - needs the blockers and the evidence to collect before committing to a path; - asks in any language for a SQL Server to Azure migration recommendation. Use a different skill when: - **the instance is already Arc-connected with an assessment uploaded** — read that assessment instead of running the interview. In `microsoft/sql-migration-agent` that is the `get-migration-assessment` skill, which queries Azure Resource Manager directly. Measured evidence beats an interview every time, and this skill exists for the estates that have none yet; - the user wants to execute a migration rather than choose one; - the question is about a database engine other than SQL Server. ### What the source version and edition change Skills use APIs and features whose availability varies with the version and edition of SQL Server, and with whether the instance is Arc-enabled. **On older versions, and on SQL Server 2008 in particular, many skills cannot run at all**, because the interfaces they depend on do not exist there. This skill is an exception by design: it interviews a person rather than an instance, so it works for every version from 2008 onward. What changes with an old version is not whether the assessment runs, but what it can conclude — fewer migration methods qualify, more of them depend on an intermediate upgrade, and a recommendation that would be routine on SQL Server 2019 may be blocked outright on 2008. Say so when it applies. A user on an old version should learn that the constraint comes from the source, not from the target they were hoping for. ## User Inputs **The field and option catalogue lives in [`references/input-contract.md`](references/input-contract.md).** It owns the canonical fields, their allowed values, the stable IDs, which rules consume each field, and what happens when a field is unknown. Do not restate it here: two copies of a vocabulary drift, and this one already did. **Asking rules — how to call `ask_user`. These apply to every question, Tier 1, Tier 2 and structured inputs alike, and each one records a failure seen in a real session:** - **Never use a multi-select.** Use single-selects, free text and typed values only. Every multi-select this interview has shipped came back empty in real sessions, including one where the user had just chosen “let me select them” and afterwards said which item they had picked, while every single-select in those same sessions returned its value. Whichever layer drops the selection, an interview must not depend on a control that discards the answer without saying so. - **Never let an empty answer carry meaning.** “I have none of these” and “I have not checked” are opposite answers: one clears a blocker, the other raises one. Ask a single-select that names the intent (`None, confirmed` / `Let me list them` / `Not checked yet`) before asking for the list itself. - **Ask each question at most once.** If the answer comes back empty, or the user declines or cancels, **do not re-ask it, and never re-ask it in a stricter form.** - **If an answer still arrives empty**, resolve it to `UNKNOWN` and never to `NONE_CONFIRMED`: record the unknown, carry it into `unknowns[]` and `evidenceRequired[]`, keep the recommendation `provisional`, and continue without re-asking. - **A free-text list that matches nothing is `UNKNOWN`, not `NONE_CONFIRMED`.** The user committed to listing; failing to recognise their words is our problem, not evidence of absence. - If the user explicitly answers `None, confirmed`, that is a definite answer — record `NONE_CONFIRMED` and move on. - If a decision-driving field ends up unknown, say so once in the output rather than blocking the interview to chase it. ### Compact profile first Before asking anything, check what the user has already given. If they can supply a profile in prose or structured form, take it, normalise it against the input contract, **show the normalised profile back**, and then ask only for the fields that are both missing and capable of changing a surviving candidate. A full interview runs to twenty turns or more. Most users who already know their estate should not have to walk it. ### Tier 1 — Triage (provisional recommendation) Ask one at a time. Show the human label, record the **stable ID**. The rules match on IDs only, never on displayed wording: a label can be translated or reworded, an ID cannot drift. If an answer arrives as free text, map it to an ID before applying any rule; if it maps to none, the field is `UNKNOWN`. 1. **Scope** — “How big is this migration?” - `Single database` **`SINGLE_DB`** · `A few databases (2–10)` **`FEW_DATABASES`** · `Large estate (10+ servers/DBs)` **`LARGE_ESTATE`** - `LARGE_ESTATE` ⇒ Azure Migrate discovery/business case + `Az.DataMigration` automation; then profile representative groups. 2. **Source location** — “Where does the source SQL Server run today?” - `On-prem` **`ON_PREM`** · `Azure VM` **`AZURE_VM`** · `AWS EC2` **`AWS_EC2`** · `AWS RDS for SQL Server` **`AWS_RDS`** · `GCP Compute Engine` **`GCP_COMPUTE`** · `GCP Cloud SQL` **`GCP_CLOUD_SQL`** - `AWS_RDS` and `GCP_CLOUD_SQL` ⇒ MI Link and transactional replication are out; LRS/DMS via backup upload to Blob; native restore only indirectly. 3. **Source version** — “Which SQL Server version is the source?” - `2008/2008 R2` **`SQL2008`** · `2012` **`SQL2012`** · `2014` **`SQL2014`** · `2016` **`SQL2016`** · `2017/2019` **`SQL2017_2019`** · `2022` **`SQL2022`** · `2025` **`SQL2025`** - Drives MI Link, LRS, native restore, replication, Arc portal, and ESU choices. 4. **Migration intent / readiness** — “What outcome do you need now?” - `Move to Azure now` **`MIGRATE_NOW`** · `Modernize in place / not ready to move yet (assess first)` **`MODERNIZE_IN_PLACE`** · `Assessment only` **`ASSESSMENT_ONLY`** · `Rehost first, modernize later` **`REHOST_FIRST`** - `MODERNIZE_IN_PLACE` and `ASSESSMENT_ONLY` unlock **SQL Server enabled by Azure Arc** as the control-plane path. `REHOST_FIRST` ranks IaaS above PaaS without eliminating either. 5. **Primary driver** — “What is the main reason?” - `End-of-support / ESU pressure` **`EOS_ESU`** · `Cost optimization` **`COST`** · `App modernization` **`APP_MODERNIZATION`** · `Data-center exit (VMware estate)` **`DATACENTER_EXIT`** · `Analytics / Fabric unification` **`FABRIC_ANALYTICS`** · `Sovereignty / edge` **`SOVEREIGNTY_EDGE`** 6. **Management model** — “How much control do you need?” - `Fully managed PaaS` **`MANAGED_PAAS`** · `Need OS / file-system / engine control` **`OS_CONTROL`** · `Need Kubernetes on-prem / edge / multi-cloud` **`KUBERNETES`** - If `KUBERNETES`: ask unlock question **6a**. 6a. **Kubernetes engine model** — only if Q6 = `KUBERNETES` — “Do you want Microsoft to run the engine on your cluster, or do you want to own it end to end?” - `Managed engine (Arc data controller: auto patch/backup/HA)` **`ARC_MANAGED_ENGINE`** · `Full DIY container (we own HA/patch/backup)` **`DIY_CONTAINER`** - Decides Arc-enabled SQL MI vs SQL Server container. 7. **Feature dependencies** — “Do you know which SQL Server features the workload uses: FILESTREAM/FileTable, PolyBase, DTC, cross-DB queries, SQL CLR, linked servers, SQL Agent jobs, Service Broker?” - Single-select: `None of them, confirmed` → **`NONE_CONFIRMED`** · `Let me list them` → **`LIST_FEATURES`** · `Not checked yet` → `Not sure` - `NONE_CONFIRMED` records `None` and clears the PaaS feature blockers. `Not checked yet` records `Not sure` and holds SQL MI and SQL DB at `unknown_requires_assessment` until a dependency discovery runs. Only `LIST_FEATURES` opens **7a**. 7a. **Which dependencies** (free text, only after `LIST_FEATURES`) — “List the ones it uses, separated by commas: FILESTREAM/FileTable, PolyBase, DTC, cross-DB queries, SQL CLR, linked servers, SQL Agent jobs, Service Broker.” - Normalise the answer into `feature_dependencies`. If nothing in it matches a known dependency, record `Not sure` rather than `None`. - If PolyBase: ask **7b**. If DTC: ask **7c**. If CLR is listed or unknown and MI/SQL DB remain candidates, ask Tier 2 CLR permission set. 7b. **PolyBase qualifier** — only if PolyBase is used — “What does PolyBase actually query — files in Azure/cloud storage, or an external database like Oracle or Teradata?” - `Cloud files only (Blob/ADLS Gen2 Parquet/CSV)` · `External RDBMS connector` · `S3 / Delta / pushdown required` · `Not sure` 7c. **DTC qualifier** — only if DTC/distributed transactions are used — “Are those distributed transactions only between SQL Servers, or do they span a non-SQL database?” - `SQL-to-SQL only (MI↔MI or MI↔SQL Server)` · `Heterogeneous / third-party RDBMS` · `Not sure` 8. **Largest DB size** — “How large is the biggest database?” - **`UNDER_150_GB`** `< 150 GB` · **`FROM_150_GB_TO_4_TB`** `150 GB – 4 TB` · **`FROM_4_TB_TO_128_TB`** `> 4 TB – 128 TB` · **`OVER_128_TB`** `> 128 TB` · `Not sure` - The classes do not overlap. Until v2.4 the question offered both `> 4 TB` and `> 128 TB`, so a 200 TB database matched two answers and the reader picked which one it meant. - `OVER_128_TB` is above the Hyperscale ceiling: no Azure SQL target holds it as a single database, so it forces sharding or SQL Server on Azure VM. Without this option the rule could never fire. 9. **Downtime tolerance** — “How much cutover downtime can the business accept?” - **`NEAR_ZERO`** `Near-zero (minutes)` · **`MINIMAL`** `Minimal (tens of minutes to a couple of hours)` · **`OFFLINE`** `Offline planned window` · `Not sure` 10. **Bandwidth** (`network_bandwidth`) — “What is the network path to Azure?” - **`GOOD_BANDWIDTH`** `Good ExpressRoute / high bandwidth` · **`LIMITED_WAN`** `Limited WAN` · **`VERY_LARGE_MULTI_TB`** `Very large multi-TB move` · `Not sure` 10a. **MI Link ports** (`mi_link_ports`) — ask only while MI Link is still a candidate: “Can ports 5022 and 11000–11999 be opened in both directions?” - **`PORTS_CONFIRMED_OPEN`** `Confirmed open in both directions` · **`PORTS_BLOCKED`** `5022 or 11000–11999 blocked` · `Not sure` - `PORTS_CONFIRMED_OPEN` is the only answer that lets MI Link be **confirmed**. Without it a user could declare them blocked but never declare them open, so MI Link could only ever be un-refuted. 10b. **Blob reachability** (`blob_https_reachability`) — ask whenever a backup, BACPAC or Data Box path is a candidate: “Is HTTPS upload to Azure Blob confirmed and tested?” - **`BLOB_HTTPS_CONFIRMED`** `Confirmed, upload tested` · **`BLOB_HTTPS_BLOCKED`** `Blocked by proxy, firewall or policy` · **`BLOB_HTTPS_UNKNOWN`** `Not verified` - One question used to mix bandwidth, MI Link ports (5022 and 11000–11999) and Blob access. They gate different things, and a session once reported a backup/restore gate as `passed` while the Blob path was never verified. `BACKUP-BLOB-PATH` consumes this field. 11. **Compliance / sovereignty** — “Any data residency, sovereign, or edge constraints?” - **`STANDARD_COMMERCIAL`** `Standard commercial` · **`EU_DATA_BOUNDARY`** `EU data boundary` · **`GOVERNMENT_SOVEREIGN`** `Government / sovereign` · **`EDGE_AIR_GAPPED`** `Edge / air-gapped` · `Not sure` 11b. **Preview services** (`preview_acceptable`) — ask when a preview capability would change the answer: “Can this project take on a service still in preview, or generally available only?” - **`PREVIEW_ACCEPTED`** `Preview is acceptable` · **`PREVIEW_REFUSED`** `Generally available only` · `Not sure` - `MI-TIER` reads it for zone redundancy and `COPILOT-AGENT` for the control-plane branch. Both rules consumed it while the profile had no field to carry it, so a decision they made could not be reproduced from a valid input. Unanswered leaves preview options unavailable rather than granting them. 11c. **Recovery model and log chain** (`recovery_model`, `log_chain_status`) — ask when a minimal-downtime method is still in play: “What recovery model do these databases run in, and is the log backup chain unbroken?” - `recovery_model`: **`FULL`** `Full` · **`BULK_LOGGED`** `Bulk-logged` · **`SIMPLE`** `Simple` · `Not sure` - `log_chain_status`: **`CHAIN_INTACT`** `Unbroken` · **`CHAIN_BROKEN`** `Broken` · `Not sure` - `DMS-MODE` refuses to assume online DMS without `FULL` and an unbroken chain. Both are separate facts: a database can sit in `FULL` and still have had its chain cut. The rule demanded them while the profile could not carry them, so an online DMS decision could not be reproduced from a valid input. Unanswered holds the online mode at `unknown_requires_assessment`, never granted. - The two answers are not one fact. Log shipping accepts `BULK_LOGGED` where the Log Replay Service is `FULL` only, so the field carries what the user said and each rule reads it on its own terms. 12. **Ancillary services and security** — “Anything around the database to bring along?” - Single-select: `Nothing, confirmed` → **`NONE_CONFIRMED`** · `Let me list them` → **`LIST_SERVICES`** · `Not sure` - Only `LIST_SERVICES` opens **12a**. 12a. **Which ancillary services** (free text) — “List them, separated by commas: SSIS packages, SSRS reports, SSAS models, TDE-encrypted DBs, many SQL Agent jobs, Windows logins.” 13. **Tier-selection inputs** — ask compactly when SQL MI or SQL DB remains eligible: - “Any tier drivers?” Single-select: `No particular driver, confirmed` → **`NONE_CONFIRMED`** · `Let me list them` → **`LIST_TIER_DRIVERS`** · `Not sure` - Only `LIST_TIER_DRIVERS` opens **13a**. - `Not sure` holds the tier at `unknown_requires_assessment`; it must never fall back to General Purpose. 13a. **Which tier drivers** (free text) — “List them, separated by commas: low-latency writes, high IOPS/log throughput, strict SLA / zone redundancy, read-scale replicas, intermittent usage, many tenants / variable demand.” - Consumed by GP vs Business Critical vs Hyperscale vs Serverless vs Elastic Pool. ### Tier 2 — Confirmation (only when decision-driving) Ask only questions that can change candidates still in play, or that the architect will need for their own sign-off. Do not ask about elastic-pool tenancy if SQL DB is already eliminated. | Confirmation input | Ask when | Consumed by | | --- | --- | --- | | Source OS (`source_os`): **`WINDOWS_SERVER_2012_OR_LATER`** · **`WINDOWS_SERVER_BELOW_2012`** · **`WINDOWS_CLIENT`** · **`LINUX`** | **MI Link is in play**, or VM/AVS/Arc/container or licensing/ESU are | MI Link requires a host OS supported by that SQL Server version: SQL Server 2016 is Windows Server only, Linux is supported from SQL Server 2017 onwards, and Windows hosts must be Windows Server 2012 or later. Windows client editions cannot host the availability groups the link depends on | | Source edition (`source_edition`): **`ENTERPRISE`** · **`STANDARD`** · **`DEVELOPER`** · **`EXPRESS`** · **`WEB`** | Same as above | MI Link requires Enterprise, Standard or Developer. Also drives compatibility, HA/DR support, AHB/ESU and patching responsibility | | Compatibility level | SQL DB, Fabric SQL DB, or modernization candidate | refactoring effort and compatibility scoring | | Current HA/DR topology: FCI, AG, log shipping, none | near-zero/minimal downtime or VM/AVS/MI Link in play | method feasibility, rollback, resilience | | `rpo` and `rto`, separately and in the customer's own units | any production migration | method ranking and DR design. Never inferred from the chosen method | | Peak log generation / change rate | MI Link, LRS, replication, log shipping, Data Box seed | catch-up feasibility and downtime risk | | `performance`: CPU, memory, IOPS, latency peaks | any PaaS target or tier choice | sizing and tier-selection rules | | Authentication (`authentication`): **`SQL_LOGINS_ONLY`** · **`WINDOWS_LOGINS`** · **`ENTRA_ID`** · **`MIXED_AUTH`** | SQL DB/MI or cross-domain source | login remediation, AD/Entra dependencies | | CLR permission set (`clr_permission_set`): **`CLR_SAFE`** · **`CLR_EXTERNAL_ACCESS`** · **`CLR_UNSAFE`** | CLR present or unknown and PaaS remains possible | `CLR-PERMISSION`. **`CLR_SAFE` is not a clearance**: under `clr strict security` the engine treats SAFE and EXTERNAL_ACCESS as UNSAFE unless signed or hash-trusted | | TDE status (`tde_status`): **`TDE_ENABLED`** · **`TDE_NOT_ENABLED`** | a backup-based method is a candidate | the server certificate must exist in the target before a restore | | Source permissions (`source_permissions`): **`SYSADMIN_AVAILABLE`** · **`LIMITED_RIGHTS`** | an orchestrated method or the SSMS 22 Migration Component is recommended | tooling prerequisites and AG endpoints. SSMS 22 requires `sysadmin` on the source; say so when recommending it |