SQL Server 2016 end of support: map your dependencies before you migrate

Why dependency mapping comes before migration planning

The instinct with a migration project is to start with infrastructure: identify the target (Azure SQL Database, SQL Managed Instance, SQL Server 2022), run the Database Migration Assessment, and plan the lift. That instinct skips a step that causes the most project failures: understanding the internal dependency structure of the database itself.

On a SQL Server 2016 database that's been running for ten years, stored procedures, views, functions, and triggers have been layered on top of each other across multiple teams, project timelines, and developers who may no longer be with the organization. One view might be called by 40 stored procedures. One stored procedure might depend on a cross-database reference that the migration plan doesn't account for. A single function rename cascades silently through 15 callers.

The dependency map tells you three things that the Database Migration Assessment doesn't:

• Which objects have the most callers (highest risk to change)
• The correct migration sequence (objects with no callers first, highest-dependency objects last)
• Cross-database references that would break silently if the source database moved

The catalog view SQL Server has been maintaining for you

SQL Server has been tracking the full object call graph since 2008 via sys.sql_expression_dependencies. It records every reference one object makes to another — stored procedure to view, view to table, function to table, cross-database — automatically, without any setup.

Run this query on your SQL Server 2016 database right now:

SELECT
    referenced_entity_name      AS object_name,
    COUNT(*)                    AS caller_count
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NULL
GROUP BY referenced_entity_name
ORDER BY caller_count DESC;

The result is every object in your database ranked by how many other objects call it. The top of that list is where your migration risk lives. An object with 50 callers is one you migrate last, after every one of its dependents has been validated in the new environment. An object with 0 callers can be migrated in any order.

Finding your migration sequence

A dependency-first migration sequence works like this: migrate leaf nodes first (objects nothing depends on), work up the dependency tree, and migrate your highest-dependency objects last — after all of their callers are already stable in the new environment.

To find your leaf nodes — objects that aren't called by anything else in the database:

-- Objects with no internal callers (safe to migrate first)
SELECT DISTINCT o.name AS object_name, o.type_desc
FROM sys.objects o
WHERE o.type IN ('P', 'V', 'FN', 'IF', 'TF')  -- procs, views, functions
  AND o.name NOT IN (
      SELECT referenced_entity_name
      FROM sys.sql_expression_dependencies
      WHERE referenced_database_name IS NULL
  )
ORDER BY o.type_desc, o.name;

And to identify your highest-risk objects — the ones to migrate last and test most thoroughly:

-- Top 20 highest-dependency objects (migrate these last)
SELECT TOP 20
    referenced_entity_name      AS object_name,
    COUNT(*)                    AS caller_count
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NULL
GROUP BY referenced_entity_name
ORDER BY caller_count DESC;

Cross-database references: the silent migration killer

SQL Server 2016 databases commonly use three-part naming (OtherDatabase.dbo.vwSomeView) to reference objects in sibling databases on the same instance. These references work transparently on-premises but require explicit planning when migrating to Azure SQL, which doesn't support cross-database queries in the same way SQL Managed Instance and SQL Server do.

Find every cross-database reference in your database:

SELECT
    referencing_entity_name         AS calling_object,
    referenced_database_name        AS target_database,
    referenced_schema_name          AS target_schema,
    referenced_entity_name          AS target_object
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
ORDER BY referenced_database_name, referencing_entity_name;

If this query returns rows, those are migration blockers that need a resolution plan before you can move to Azure SQL Database. Options include:

• Consolidating the referenced objects into a single database
• Replacing cross-database references with synonyms
• Migrating to Azure SQL Managed Instance, which supports cross-database queries
• Rewriting the procedures to use linked server equivalents or APIs

The assessment no tool gives you automatically

Microsoft's Database Migration Assessment (DMA) and Azure Migrate are excellent tools for identifying compatibility breaks between SQL Server versions — deprecated syntax, unsupported features, incompatible data types. What they don't produce is an internal dependency map of your own objects.

That's because it's a different kind of analysis. Compatibility assessment is about SQL Server features. Dependency mapping is about your code and the relationships between your objects — knowledge that only lives in your database's own catalog views.

The two are complementary. Run DMA to find compatibility issues. Run the queries above to understand your internal dependencies and set a migration sequence. Both are prerequisites before the first object moves.

What a dependency map looks like in practice

On a typical SQL Server 2016 database that's been in production for several years, the dependency analysis usually surfaces a few surprises:

• A shared lookup view that dozens of procedures depend on, but nobody thought to include in the migration plan
• Cross-database references to a reporting database that was migrated separately last year — now broken
• A utility function with no apparent callers that's actually called from dynamic SQL in three procedures (dynamic SQL won't appear in the catalog view)
• Objects that haven't been called by anything in years but are still referenced by stale procedures that are themselves no longer called

None of these surface in a compatibility assessment. All of them cause incidents on cutover day if they're not found first.

Extended Security Updates as a bridge

If your migration timeline extends past July 2026, Microsoft offers Extended Security Updates (ESUs) for SQL Server 2016 through July 2029. ESUs provide critical security patches only — no new features, no bug fixes. They're a bridge, not a destination.

ESUs for SQL Server 2016 are available free of charge when the database is hosted on Azure (Azure Virtual Machines with the SQL Server IaaS extension, or Arc-enabled SQL Server). For on-premises instances, ESUs require purchase through Software Assurance or a standalone contract.

Using the ESU window to complete a thorough dependency analysis and migration plan is a better use of that time than rushing a migration without one.

Migration targets for SQL Server 2016 workloads

The right target depends on how your workload uses SQL Server:

Start before you plan

Dependency mapping is the kind of task that gets pushed to "later in the project" and then causes the last-week scramble. Run the queries above this week, before your migration kickoff, before you've committed to a timeline, before you've estimated effort. What you find will change all three.

The dependency structure of a 10-year-old SQL Server database is usually more complicated than anyone on the team remembers. sys.sql_expression_dependencies is the fastest way to find out exactly how complicated — in under a minute, on a running production database, with no impact on workload.

← Back to blog · Deep dive: sys.sql_expression_dependencies →