After migrating a municipal 311 database to a newer Azure SQL tier, the team wants modern cardinality estimation and optimizer behavior. Which configuration should they review?

After migrating the municipal 311 database to a newer Azure SQL tier, appropriately raising its database compatibility level is the configuration that enables modern cardinality estimation and optimizer behavior associated with newer SQL engine versions. Compatibility level is the primary database-scoped switch governing the CE model and optimizer features, so aligning it with the migrated platform after testing—not relying on the tier alone—matches the goal. Disabling Query Store removes plan history that is especially useful for comparing pre- and post-migration plans, undermining validation. Setting MAXDOP to 0 only on the reporting replica while ignoring OLTP does not change cardinality estimation for the migrated 311 OLTP database. Removing the database’s foreign keys can weaken integrity and alter some plan choices, but it is not the supported route to modern CE and risks data quality in 311 relationships. Exam caveat: raising compatibility level can improve or worsen plans; test with Query Store, and consider query hints or forcing known-good plans during transition. Compatibility level is distinct from Azure SQL hardware generation or service tier, so a new tier can still use an old level; some features also require both a minimum level and platform support. Practical check: record the current level and baseline Query Store metrics for critical queries, raise the level in a restored test database and compare results, ensure Query Store is enabled before the production change, then raise the level in a controlled window and monitor for regressions, using plan forcing or fixes where needed.