Why Deprecated Features in SQL Server Can Break Your Upgrade

Migrating a SQL Server database to a newer version is rarely as simple as a straight lift-and-shift. One of the most common causes of post-upgrade failures, unexpected outages, and application breakage is the use of deprecated SQL Server features. These are features that Microsoft has flagged for removal, either in the current version or a future one, and if your code or configuration relies on them, you're carrying risk into every upgrade you do.

The short answer: before any SQL Server upgrade, you must audit your environment for deprecated features and remove or replace them. Skipping this step is one of the fastest ways to turn a planned migration into an emergency incident.

What Are Deprecated SQL Server Features?

Microsoft uses a formal deprecation process to signal that a feature will eventually be removed from the product. There are two categories you need to understand.

Deprecated features are still functional in the current release but are flagged for removal in a future version. SQL Server will often raise a warning through the Deprecation Announcement or Deprecation Final Warning events in Extended Events or SQL Trace. These features may work today but won't be supported indefinitely.

Discontinued features have already been removed. If your application or database relies on one of these, the upgrade will either fail outright or produce runtime errors after the migration completes.

The distinction matters because deprecated features give you a window to act. Discontinued features give you no choice at all.

Which Versions Introduced the Most Breaking Changes?

Every major SQL Server release has removed or deprecated a set of features. The changes between SQL Server 2005 and SQL Server 2008 R2, and then again between 2008 R2 and SQL Server 2012, were particularly significant for organisations that had been running legacy applications for years without touching the underlying T-SQL.

Microsoft maintains a full list of deprecated features for each release in their official documentation. The relevant references are:

If you're planning a migration to SQL Server 2022, there's a corresponding page for that release too. These pages are your first reference point, not an afterthought.

Common examples of deprecated or removed features that have caught organisations off guard include:

  • The BACKUP LOG WITH NO_LOG and BACKUP LOG WITH TRUNCATE_ONLY syntax, removed in SQL Server 2008
  • SET ROWCOUNT affecting INSERT, UPDATE, and DELETE statements, deprecated and eventually superseded by TOP
  • Non-ANSI outer join operators (*= and =*), removed in SQL Server 2012
  • The TEXT, NTEXT, and IMAGE data types, deprecated in favour of VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX)
  • DBCC SHOWCONTIG, removed in SQL Server 2012 in favour of sys.dm_db_index_physical_stats
  • The FASTFIRSTROW table hint, replaced by FAST n in the query hint syntax
  • sp_addlogin, sp_droplogin, and related legacy security stored procedures

This isn't an exhaustive list. The point is that these aren't obscure edge cases. They're features that were commonly used in SQL Server 2000 and 2005 environments, and they've been tripping up migrations for years.

How Do You Identify Deprecated Features in Your Environment?

There are several practical approaches, and you should use more than one.

1. Run the SQL Server Upgrade Advisor / Database Experimentation Assistant

Microsoft has provided tooling to help with this at different points in the product's history. The older SQL Server Upgrade Advisor was used for migrations up to SQL Server 2014. For more recent versions, the Database Migration Assistant (DMA) and the Database Experimentation Assistant (DEA) serve a similar purpose. These tools connect to your existing instance, analyse your databases, and produce a report of compatibility issues and deprecated feature usage.

Download the Database Migration Assistant from: https://learn.microsoft.com/en-us/sql/dma/dma-overview

2. Query the Deprecation DMV

SQL Server tracks deprecated feature usage at runtime through the sys.dm_os_performance_counters DMV. You can also use the following query to check which deprecated features have been hit since the instance last started:

SELECT
    name,
    object_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Deprecated%'
    AND cntr_value > 0
ORDER BY cntr_value DESC;

This query returns any deprecated features that have been executed at least once since the SQL Server service started. A cntr_value greater than zero means something in your environment is actively using that feature. The higher the count, the more frequently it's being called.

This is a quick, low-impact check you can run on any live instance right now.

3. Use Extended Events to Capture Deprecation Warnings

For a more thorough audit, set up an Extended Events session to capture the deprecation_announcement and deprecation_final_support events. This gives you the SQL text and calling context, which makes it much easier to track down exactly which stored procedure, application query, or scheduled job is responsible.

4. Review Your Application Code and Stored Procedures

Automated tooling won't catch everything. A manual review of stored procedures, functions, views, and application-layer SQL is still necessary, particularly for older codebases. Search for known deprecated syntax patterns as part of your pre-migration assessment.

What Should You Do When You Find Deprecated Features?

Finding a deprecated feature is the start of the work, not the end. For each instance you identify:

  1. Determine whether it's deprecated or already discontinued in your target SQL Server version.
  2. Identify the replacement syntax or feature Microsoft recommends.
  3. Test the replacement in a non-production environment before making changes to production.
  4. Update stored procedures, functions, views, and any application-layer SQL that uses the old syntax.
  5. Re-run your deprecation checks after the changes to confirm nothing was missed.
  6. Document what was changed and why, particularly for audit and change management purposes.

Don't leave deprecated features in place with the intention of "fixing them later." Later rarely comes, and the next upgrade cycle will hit the same issues again, except by then the feature may have been fully removed.

Key Takeaways

  • Deprecated SQL Server features are a leading cause of post-upgrade failures and application outages. Audit for them before every migration, not after.
  • Microsoft publishes a full list of deprecated and discontinued features for each SQL Server version. These pages are your baseline reference for any upgrade assessment.
  • The sys.dm_os_performance_counters DMV gives you a quick runtime view of which deprecated features are actively being used on your current instance.
  • The Database Migration Assistant (DMA) is the current Microsoft-supported tool for pre-migration compatibility analysis. Use it as part of every upgrade project.
  • Deprecated features should be remediated before the migration, not deferred. Carrying technical debt across a version upgrade compounds the risk.

If you're planning a SQL Server upgrade and want confidence that your environment is clean before you migrate, DBA Services offers pre-migration health checks and upgrade assessments for SQL Server environments across Australia. We identify deprecated feature usage, compatibility issues, and configuration risks before they become production incidents. Get in touch to find out how we can help make your next migration straightforward.