Enough has been written about the new features of SQL Server 2025. This piece is about the question you have to answer as a DBA, BI developer or IT manager: what does the upgrade gain you, and what does it cost in work and risk?

I have worked with Microsoft’s BI stack for twenty years. I moved a data warehouse environment with SSIS, SSAS, SSRS and MDS to SQL Server 2022 myself. My short answer: go to 2022, not to 2025 yet. For some readers 2025 is still the better choice, and I say for whom.

Why this matters now

Version Mainstream support up to and including Extended support up to and including
SQL Server 2016 13 July 2021 14 July 2026
SQL Server 2017 11 October 2022 12 October 2027
SQL Server 2019 28 February 2025 8 January 2030
SQL Server 2022 11 January 2028 11 January 2033
SQL Server 2025 6 January 2031 6 January 2036

Microsoft’s Lifecycle pages show the day after, at 06:59 Pacific Time. That is the moment support stops. The date in the table is the last full day.

Mainstream support brings fixes and improvements. Extended support brings security updates only. For SQL Server 2016 that is over too. If you still run it, you get no more security updates, unless you buy Extended Security Updates (ESU). They run for at most three years, until July 2029. You get them through Azure Arc or through volume licensing. A SQL Server 2016 instance in an Azure VM no longer gets them for free. That only applied to 2014.

For 2017, learn.microsoft.com shows no ESU offer yet. Do not count on one.

Advice per version

SQL Server 2016: upgrade now

Support is over. Every month without ESU is a month without security updates. If you buy ESU as a bridge, give the upgrade project a fixed end date. Otherwise you end up paying three years of ESU for a server you wanted gone long ago.

SQL Server 2017: plan now, be done in 2027

Extended support stops on 12 October 2027. That is less than a year away. Count back from that date, with time for testing and an acceptance round. Start the inventory this quarter.

SQL Server 2019: plan

Mainstream support stopped in February 2025. You only get security updates, and that stays so until January 2030. There is no rush, but do put the upgrade in your budget for the next two years.

SQL Server 2022: wait

You have extended support until 2033. Wait until 2025 has proven itself in practice and your vendors support it. Do keep in mind that mainstream support stops on 11 January 2028.

2022 or 2025 as the target version?

For most BI environments I pick 2022.

Master Data Services and Data Quality Services no longer exist in 2025. If you run MDS, 2022 is the last version where you can. I wrote about the options for MDS earlier: Microsoft MDS end of life.

Reporting Services has no 2025 version. SSRS 2022 is supported until 11 January 2033. If you pick 2025, you migrate your reports to Power BI Report Server. That is a separate project.

Not every vendor supports 2025 yet. For every application with its own database, ask whether 2025 is in the support matrix. If it is not, you run outside their support.

The downside of 2022 is the clock. Mainstream support stops in fourteen months. After that you only get security updates. For a data warehouse I do not see that as a problem. For an environment that may really need a fix from Microsoft, that is different.

Pick 2025 if you do not run MDS or DQS, your vendors support 2025, and you need one of these gains: more cores or memory on Standard, larger databases on Express, or Power BI Report Server without Enterprise.

In-place or side-by-side

An in-place upgrade runs setup on the existing server. Setup overwrites the old instance. It no longer exists afterwards. You can go in-place to 2025 from SQL Server 2014 SP3, 2016 SP3, 2017, 2019 and 2022.

The risk is in the way back. You cannot restore a backup made on a newer version to an older version. If it goes wrong, you install the old version again and restore the backups from before the upgrade. Everything that changed in the database after that is lost.

With side-by-side you set up a new server and restore the databases on it. The old server stays as a fallback. You can take new hardware and a new Windows version in the same move. The price is extra work: logins, SQL Server Agent jobs and server triggers live in master and msdb. You move those yourself.

For a data warehouse with SSIS, SSAS and SSRS I pick side-by-side. With SSRS you have no choice: there is no in-place upgrade from SSRS to Power BI Report Server. In-place is fine for a development or test server, or a small instance where an evening of downtime hurts nobody.

Compatibility levels: do not raise them on day one

SQL Server 2025 has compatibility level 170. A database you restore or upgrade keeps its old level. A database at level 130 therefore stays at 130, also on the new server. The lowest level 2025 allows is 100. If a database is lower, SQL Server sets it to 100 automatically.

That is good news. Microsoft writes that query plans do not change at the upgrade, but only when you raise the level yourself. So you can separate those two steps. You should.

In my upgrade to 2022 a few queries got slower. That is exactly the risk of a new optimizer. Microsoft documents a standard procedure for it with Query Store:

  1. Upgrade the server and leave the level as it is.
  2. Turn Query Store on.
  3. Let Query Store run for a full business cycle. For a data warehouse that is at least one month-end close.
  4. Raise the level.
  5. Look in Query Store for queries that got slower. Force the old plan for those queries, or set the level back.
-- Welk level heeft elke database?
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id > 4
ORDER BY name;
-- Stap 2: Query Store aan, vóór je het level wijzigt
ALTER DATABASE [DWH] SET QUERY_STORE = ON;
ALTER DATABASE [DWH] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
-- Stap 4: na de baseline naar het level van SQL Server 2025
ALTER DATABASE [DWH] SET COMPATIBILITY_LEVEL = 170;
-- Stap 5: een trage query terug naar zijn oude plan
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;
-- Of de hele database terug naar het oude level, bijvoorbeeld 160
ALTER DATABASE [DWH] SET COMPATIBILITY_LEVEL = 160;

One warning: the compatibility level does not protect you against functionality Microsoft removed. The old word breakers for full-text, for example, are gone at level 130 too.

SSIS, SSAS, SSRS and MDS

In my upgrade to 2022 this part turned out fine. SSIS, SSAS, SSRS and MDS all went better than I expected beforehand. With 2025 that is different. Microsoft removes components here, and most English articles mention that in one sentence.

SSIS

You still install SSIS through setup, under Shared Features. Express has no SSIS. This is what changes in 2025:

  • The CDC components by Attunity and the CDC service for Oracle by Attunity are gone.
  • The Microsoft Connector for Oracle is gone.
  • The Hadoop Hive Task, the Hadoop Pig Task and the Hadoop File System Task are no longer in SSIS 2025.
  • The old Integration Services Service, which you see in SSMS, is deprecated.
  • 32-bit is deprecated. SSMS 21 and SQL Server Integration Services Projects 2022 only work 64-bit.
  • The SqlClient Data Provider connection type is deprecated. Microsoft recommends the ADO.NET connection type, which now supports the Microsoft SqlClient Data Provider.
  • If you use the .NET API Microsoft.SqlServer.Dts.Runtime with an Execute SQL Task, you must update the references and rebuild your project.

The migration path starts with searching. You do a first round with PowerShell in the folder with your .dtsx files:

Terminal window
Get-ChildItem -Recurse -Filter *.dtsx |
Select-String -Pattern 'Attunity|Oracle|Hadoop' -List |
Select-Object Path

Every package that shows up here gets a new source or a new task before you go to 2025. If a package still runs in 32-bit, find out why. If an old driver is behind it, replace that first.

After the upgrade you update the SSISDB catalog. Back up SSISDB first. In SSMS, right-click SSISDB and choose Database Upgrade. The wizard only works on a local instance. If SSISDB is in an availability group, take it out first and put it back after the upgrade.

SSAS

For Analysis Services the list is shorter:

  • The Power Pivot for SharePoint mode is no longer in the installer.
  • The provider is now called Microsoft.Data.SqlClient. According to Microsoft you may need to update your model definitions to that name.
  • HTTP connections through msmdpump.dll are off by default. Anyone who reached a cube with Excel that way must move to HTTPS.
  • On a failover cluster, SSAS must run under a domain account. A local account no longer works there.

Multidimensional cubes get no new restriction in 2025. Data mining has been gone since 2022. If you come from 2016 or 2017 with old tabular models at compatibility level 1100 or 1103, you must bring those to 1200 first. Those levels have been discontinued since 2022.

Microsoft gives no separate migration path for SSAS. In practice it means: test the connections, and process every model once on the new server.

SSRS

This is the biggest change. Microsoft releases no new version of SSRS. From 2025, Power BI Report Server (PBIRS) is the on-premises reporting server. PBIRS can do everything SSRS can, and your existing RDL reports work in it.

There is no in-place upgrade from SSRS to PBIRS. You migrate:

  1. Back up the encryption key.
  2. Back up the ReportServer and ReportServerTempDB databases.
  3. Restore those backups on the SQL Server where PBIRS will use them.
  4. Install PBIRS and connect it to the restored databases.

If you come from SSRS 2022, you need PBIRS from May 2025 or later. If you use SSRS in SharePoint mode, you move the reports by hand with rs.exe.

On licensing, Standard wins. Up to and including 2022, you only got PBIRS with Enterprise cores with Software Assurance. With SQL Server 2025, PBIRS comes with both Enterprise and Standard, with the same product key.

If you do not want to migrate now, you do not have to. SSRS 2022 is supported until 11 January 2033, and Microsoft allows the SSRS 2022 catalog to sit on a newer database engine. So you can move the engine to 2025 and the report server later.

MDS and DQS

Microsoft removed Master Data Services and Data Quality Services from 2025. Microsoft still supports them in 2022 and earlier versions, and offers no migration path.

Watch out with DQS. If DQS is installed, the upgrade to 2025 stops at the Feature Rules step. Microsoft’s fix is the parameter /IACCEPTDQUNINSTALL. With it, you accept that DQS disappears.

For MDS you have two ways. You leave the MDS server on 2022 and upgrade the rest. Or you first move your models to another MDM tool and upgrade after that.

One example is Primentra. It runs on your own SQL Server, Express included, and works with models, entities and staging tables like MDS. A migration from MDS is built in. The comparison with the other options and a query to check your servers for MDS are in my article on MDS end of life.

Licensing and editions

In 2025, Standard got more room:

SQL Server 2022 SQL Server 2025
Standard: compute 4 sockets or 24 cores 4 sockets or 32 cores
Standard: buffer pool 128 GB 256 GB
Express: maximum database size 10 GB 50 GB
Resource Governor Enterprise only Standard too
Power BI Report Server Enterprise with SA Enterprise and Standard

Optimized locking is in Standard in 2025 too. Express now has everything Express with Advanced Services had. The columnstore cache and the memory for memory-optimized data stay at 32 GB on Standard. Express stays at 4 cores and 1,410 MB buffer pool.

The Web edition no longer exists in 2025. You upgrade a Web server to Standard or Enterprise. There is a new edition: Standard Developer. It has everything in Standard and is meant for development and testing. You can finally test on the same edition as production.

When do you no longer need Enterprise? When your server stays within 32 cores and 256 GB buffer pool, and you mainly took Enterprise for Resource Governor or for Power BI Report Server. If you need more cores, more memory or a larger columnstore cache, you stay on Enterprise. CE feedback, one of the newer optimizer features, is also Enterprise only. Put your own features next to Microsoft’s edition table before you switch.

Checklist for the upgrade

  1. Note the version, edition and installed features of every server: SELECT @@VERSION;.
  2. Find MDS and DQS databases. The query is in my MDS article.
  3. Ask every vendor whether the target version is in the support matrix.
  4. Search your SSIS packages for Attunity CDC, the Oracle connector, Hadoop tasks and the SqlClient Data Provider connection type.
  5. Find packages that run in 32-bit.
  6. Check whether anyone uses SSAS through msmdpump.dll over HTTP.
  7. For SSRS, decide: migrate to PBIRS, or leave SSRS 2022 in place.
  8. Back up the SSRS encryption key, ReportServer and ReportServerTempDB.
  9. Check linked servers with OLE DB Driver 19. Because of the new default for encryption, they need a valid certificate.
  10. Check replication with a remote distributor and the log shipping monitor. Those need a trusted certificate too.
  11. Plan the rebuild of your full-text indexes. 2025 no longer has the old word breakers and filters.
  12. Turn Query Store on and let it run for a full business cycle.
  13. Make full backups of all databases, including master and msdb.
  14. Choose in-place or side-by-side. With side-by-side: move logins, Agent jobs and server triggers.
  15. Leave the compatibility level as it is on the day of the upgrade.
  16. Update the SSISDB catalog with the upgrade wizard.
  17. Raise the compatibility level per database, after the baseline. Keep an eye on Query Store.
  18. Write down the fallback route before you start, with who decides and when.

Sources

Frequently asked questions

How long is SQL Server 2016 still supported?

Extended support for SQL Server 2016 ran up to and including 14 July 2026. After that, security updates only come if you buy Extended Security Updates. They run for at most three years, until July 2029, and you get them through Azure Arc or volume licensing.

What compatibility level does SQL Server 2025 have?

SQL Server 2025 has compatibility level 170. A database you restore or upgrade keeps its old level, as long as that is at least 100. A lower level moves to 100 automatically. New query plans only come when you raise the level yourself.

Is Reporting Services still part of SQL Server 2025?

No. Microsoft releases no new version of SSRS. From SQL Server 2025, Power BI Report Server is the on-premises reporting server, also with Standard. Existing RDL reports work in it, but there is no in-place upgrade from SSRS to Power BI Report Server.

Is Master Data Services still part of SQL Server 2025?

No. Microsoft removed Master Data Services and Data Quality Services from SQL Server 2025 and offers no migration path. SQL Server 2022 is the last version with MDS. If DQS is installed, the upgrade to 2025 stops at Feature Rules until you pass /IACCEPTDQUNINSTALL.

How many cores can SQL Server 2025 Standard use?

SQL Server 2025 Standard uses at most 4 sockets or 32 cores, whichever comes first. In 2022 that was 24 cores. The buffer pool can grow to 256 GB, up from 128 GB. Express allows databases up to 50 GB in 2025, instead of 10 GB.

Comments

Your email address is not shown. A photo comes from Gravatar, if you have one.