Upgrading to SQL Server 2025: Three Lessons Learned

Problem

We recently upgraded multiple systems to SQL Server 2025. The engine upgrade itself was smooth, but three unexpected issues surfaced in our lower environments as we planned out production. None of these issues prevented the upgrade from completing, but all three could easily derail an otherwise smooth in-place upgrade to SQL Server 2025. What were these issues, and how can you avoid hitting them?

Solution

There were two specific features affected by changes you’ll only expect if you thoroughly read the release notes, and a new behavior in setup that definitely caught us by surprise.

Linked Servers

Most people dislike linked servers; we use them mostly for internal cross-server operations, like running maintenance tasks from a central utility instance. Our linked servers were originally set up using SQL Native Client (SQLNCLI), which tended to ignore or suppress many TLS validation errors. Many environments still don’t have proper certificates configured, often because it’s more complicated than simply setting TrustServerCertificate=Yes (also known as “just trust me”).

You have probably seen this setting; in earlier versions of SQL Server, as various parts of the ecosystem have been made more secure, you’ve been forced to add it to your application connection strings and to check the equivalent box in Management Studio’s connection dialog.

SQL Server 2025 tightens up TLS behavior even more, and the providers now enforce strict certificate validation. This might bite you after the upgrade, or potentially even in a side-by-side upgrade if you just script the linked servers as is and create them on the new SQL Server 2025 instance.

Linked Server Errors

After upgrading in our dev environment, we started seeing a variety of TLS-related failures, including:

Msg 7303, Level 16, State 1
Cannot initialize the data source object of OLE DB provider "MSOLEDBSQL" for linked server "<linked server name>".
TCP Provider: The certificate chain was issued by an authority which is not trusted.
Msg 10054, Level 20, State 0  
A transport-level error has occurred when receiving results from the server.
Msg 17832, Level 20, State 18
The login packet used to open the connection is structurally invalid; the connection has been closed.
[SQL Server]The target principal name is incorrect

To protect yourself before the upgrade, you can do one of two things:

  1. Make sure all your servers have proper certificates installed, and that connections between them work without the blind “just trust me” setting (this may also involve fixing or re-creating service principal names). You can find thorough details in these tips and documentation:
  2. Re-create the linked servers using MSOLEDBSQL and the “just trust me” setting. While this setting works, it should be treated as a convenience flag as opposed to a permanent security strategy.

Right-click the linked server in Object Explorer and choose Script Linked Server As > Drop and Create To. Here is an example, which also specifies MultiSubnetFailover in case you are linking to an availability group listener. The script should include any linked server logins (though you’ll need to supply passwords for any SQL authentication logins, as those are not scripted for you), as well as any non-default server options you’re using (like data access, rpc in/out, and collation compatible).

EXEC master.dbo.sp_dropserver 
  @server     = N'', 
  @droplogins = N'droplogins'; 
 
EXEC master.dbo.sp_addlinkedserver 
  @server     = N'', 
  @srvproduct = N'', /* must be an empty string */
  @provider   = N'MSOLEDBSQL', 
  @datasrc    = N'', 
  @provstr    = N'TrustServerCertificate=Yes;MultiSubnetFailover=Yes;'; 
/* don’t forget sp_addlinkedsrvlogin and sp_serveroption calls */

In either case, test each linked server both before and after the upgrade. (This doc suggests you can use trace flag 17600 to maintain older behavior, but I didn’t try that… and would consider it an inferior choice.)

Full-Text Search

We only use full-text search sparingly but, after the upgrade, all queries using relevant functions like CONTAINS stopped working.

The underlying problem is that SQL Server 2025 introduces a new full-text index version, but existing catalogs remain on version 1 (the only version since SQL Server 2005) unless you upgrade them manually. It doesn’t seem to matter which full-text option you choose during setup; existing catalogs remain on version 1 unless you upgrade them manually. And when the engine tries to access an older versioned index, it fails with:

Msg 30010, Level 16, State 2
An error has occurred during the full-text query. Common causes include: word-breaking errors or timeout, 
FDHOST permissions/ACL issues, service account missing privileges, malfunctioning IFilters, communication 
channel issues with FDHost and sqlservr.exe, etc. If recently performed in-place upgrade to SQL2025, 
For help please see https://aka.ms/sqlfulltext.

(As an aside, that URL in the error message should point here: Full-Text queries and populations fail after upgrade.)

The easiest fix is to just rebuild those indexes, but be aware that rebuilding large catalogs can be time-consuming and impact CPU and I/O. Test this rebuild outside of production before upgrade day.

First, make sure your database supports the newer index version:

SELECT value 
  FROM sys.database_scoped_configurations
 WHERE name = N'FULLTEXT_INDEX_VERSION';

If the value is 1 and not 2, change the database-level setting to use version 2:

ALTER DATABASE SCOPED CONFIGURATION
  SET FULLTEXT_INDEX_VERSION = 2;

Then rebuild each catalog:

ALTER FULLTEXT CATALOG <full-text catalog name> REBUILD;

If you have a lot of databases and a lot of full-text indexes, yes, this will be tedious. And unfortunately, it’s not something you can do prior to the upgrade, like you can with linked servers. (The documentation suggests a quicker fix: finding the 2005 binaries and copying them to the new binn folder. But this is much more complicated, and smells of technical debt.)

Setup is more disruptive

When I’m upgrading or patching manually using the setup UI, I like to prep everything out and answer all the dialogs, stopping right at the last step where I can simply click “Update” or “Upgrade” when I’m ready. I can no longer advocate doing that as it is more intrusive than it used to be. The upgrade from SQL Server 2022 to SQL Server 2025 intentionally restarts the service right here, during the health check phase:

The point during rule checks that I discovered setup had restarted the engine.

Reviewing the setup logs (Detail.txt), I saw this sequence:

...
(28) <ts> Slp: Evaluating rule        : Engine_IsLPIMEnabledForX64
...
(28) <ts> SQLEngine: --SqlServerServiceSCM: Stopping SQL Server Service and dependents
...
(28) <ts> Slp: Sco: Attempting to stop service with wait MSSQLSERVER, timeout 600, stop dependents True
...
(28) <ts> Slp: Sco: Service MSSQLSERVER stopped in less than 3 seconds
...
(28) <ts> SQLEngine: --SqlServerServiceSCM: Starting SQL via SCM ()... 
(28) <ts> Slp: Sco: Attempting to start service MSSQLSERVER
...
(28) <ts> Slp: Sco: Service MSSQLSERVER started
...
(28) <ts> Slp: Evaluating rule        : Engine_SqlEngineHealthCheck
(28) <ts> Slp: Rule running on machine: <server\instance name>
(28) <ts> Slp: Rule evaluation done   : Succeeded
(28) <ts> Slp: Rule evaluation message: The SQL Server service can be restarted; or for a clustered instance, the SQL Server resource is online.
(28) <ts> Slp: Send result to channel : RulesEngineNotificationChannel
...

In our local, dev, and test environments, this wasn’t a huge deal, but it was unexpected. It’s possible this has always been the case and I didn’t notice, or it may be that this behavior has changed, but gone are the days when I could be proactive. I would typically get the upgrade or CU to the very last step on a bunch of machines, well in advance, or while waiting for Windows Updates to apply, without any fear of affecting SQL Server until I pressed that last button.

I also found a minor typo, not a huge deal unless you’re parsing logs for this text:

(28) <ts> Slp: Sco: Service SQLSERVERAGENT aleady at stop state

In addition, on most instances, the upgrade restarted the service a total of three times! In addition to the above during the rule check, twice more after clicking the button:

...
(01) <ts> SQLEngine: --SqlEngineSetupPrivate: Upgrade: ShutdownInstance : Engine Configuration (NotClustered)
...
(01) <ts> SQLEngine: --SqlService: Stopping SQL Server Service
...
(01) <ts> Slp: Sco: Service MSSQLSERVER stopped in less than 3 seconds
...
... two minutes pass ...
...
(01) <ts> Slp: Sco: Service MSSQLSERVER started
...
(01) <ts> SQLEngine: : Checking Engine checkpoint 'WaitSqlServerStartEvents'
(01) <ts> SQLEngine: --SqlServerServiceSCM: Stopping SQL Server Service and dependents
...
(01) <ts> SQLEngine: : Checking Engine checkpoint 'GetSqlServerProcessHandle_4'
(01) <ts> Slp: Sco: Attempting to stop service with wait MSSQLSERVER, timeout 600, stop dependents True
...
(01) <ts> Slp: Sco: Service MSSQLSERVER stopped in less than 3 seconds
...
(01) <ts> Slp: Sco: Service MSSQLSERVER started
...

Just something to watch out for, especially if you are patching availability group secondaries and have sensitive monitors on connections established between the different replicas. And again, maybe this is something that always happens (two restarts during upgrade), and I never noticed. It didn’t seem to affect the upgrade process itself, but it was surprising to me when I found this out after reviewing the logs.

Bonus: New settings and features to test

I’m a big fan of a clean upgrade where you don’t change anything else – so that any failures can be precisely directed at the upgrade, as opposed to, “oh, and I changed these other 15 things too.”

Before you upgrade, though, you should test some of these very appealing switches and knobs in an upgraded environment below production, so you can know which ones you might want to turn on after your upgrade is complete.

Compatibility Level

  • Set compatibility level to 170 to automatically take advantage of query processor fixes and IQP improvements (examples here) – after testing, of course, and reviewing these differences.

Optimized Locking

  • This feature dramatically reduces blocking and lock contention; I blogged about how this feature works when it was first introduced in Azure SQL Database. While this is enabled by default on new databases created in SQL Server 2025, you need to explicitly enable it for upgraded databases. This is the one that will likely require the most testing for compatibility with your applications and any vendor tools that monitor locking, blocking, and deadlocks. It is also the one that will be the most frustrating to enable – while it doesn’t require exclusive access to change, like some other settings, it does wait until there are no active transactions. This may be effectively impossible in certain high-volume environments.

Backup compression

Query Store for readable secondaries

  • This lets you use query store and DOP feedback where all your queries run, not just the queries that only run on primary. If you are on SQL Server 2022 and have enabled trace flag 12606, which let you use this not-supported-in-production feature in production, you can disable it.

Resource Governor

  • You may have ignored this feature in the past, but it’s worth another look, particularly if you are on finally-supported Standard Edition. (I’ve been arguing for years that Enterprise Edition customers don’t need this feature; it’s Standard Edition customers who can’t afford to throw unlimited hardware at resource and contention problems.) In SQL Server 2025, you can now use resource governor to manage tempdb usage, which Brent Ozar talks about here.

Next Steps

Review the following tips and other resources:

Related Posts

One comment

  1. Thank you Aaron. Big heads ups. Linked Server and FTS, we do have heavy dependency. So, complete pre-Prod evaluation should help. 🫡🤝

Leave a Reply

Your email address will not be published. Required fields are marked *