Joey on SQL Server

Microsoft Build: SQL Server 2025 Is (Almost) Here

At Microsoft Build, SQL Server 2025 enters public preview with major enhancements in AI integration, performance, reliability and developer tools -- reaffirming Microsoft’s continued investment in its flagship on-premises database platform.

• By Joey D'Antoni
• 05/19/2025

Today, at Microsoft Build, Microsoft announced the public preview of SQL Server 2025 -- the latest release of their premier database product. I've been in the private preview program for SQL Server 2025 for several months and wanted to dive deeply into some of the most interesting features.

Given that we are 2025, AI is a significant emphasis. Still, there are many improvements to performance and reliability and an increase in the developer surface area to provide a better overall experience. In the era of cloud database services, you may wonder why Microsoft is making such a significant investment in an on-premises product---that will be part two of this column later this week. Let's dive in.

Of Course, We Have to Talk About AI
I haven't seen a product announcement in the last several years that didn't include an AI component. SQL Server 2025 is no different here.

The big difference with SQL Server, as opposed to many other database-related AI announcements, is that you have complete control of what AI model you use with SQL Server and where you run it. If you want to host your model in your data center or use another cloud provider's AI models, you can do so. The AI models here are external to the database engine -- meaning you don't have to consume resources from your database server if you don't want to.

The AI functionality includes T-SQL syntax and functions, a new vector datatype and Microsoft's DiskANN, which helps improve those large vectors' performance. The database also supports generating embeddings to support the vector search functionality.

Additionally, as part of today's announcements, SQL Server Management Studio 21 is now generally available (GA) and Copilot in SSMS is now in public preview. Note that currently, Copilot in SSMS is limited to Azure OpenAI; I would expect this to expand to other models in the future, but Microsoft has not confirmed anything.

Availability and More
Microsoft introduced the Availability Group (AG) feature to SQL Server in 2012 -- and the feature has gradually evolved to provide better performance and higher levels of reliability. If you aren't familiar, this feature streams the transactions from a database on one server to one or more secondary replica servers to provide high availability and/or disaster recovery.

The feature works (optionally) with the Windows Server Failover Cluster to manage failovers. Still, most of the time, it just works. In some cases, with specific patterns of cascading failures, an AG can end up in a broken state, requiring reboots, support tickets or, in the worst case, reseeding of the AG. SQL Server 2025 is a series of improvements to provide faster failover, improved performance, and better reliability.

Microsoft has also defaulted the Query Store feature to be on by default on secondaries (more on that ahead). Microsoft has also enhanced the ability to do backups on secondary replicas, allowing regular full and differential backups.

One other backup note, Microsoft has introduced the ZSTD backup compression option for backups. It's an open-source compression algorithm that should provider more efficient compression -- this is new to the preview release, so I haven't had a chance to test.

Developer Features
Microsoft built most of these features to better support the AI service offering (which calls them behind the scenes, specifically the REST endpoint feature). However, they will benefit all workloads.

SQL Server has introduced a JSON data type. While you could previously store JSON in the database using the NVARCHAR(MAX) data type, it was an incomplete solution. Now, JSON indexes and some new functions allow the creation of JSON objects and arrays from aggregations.

SQL Server has also added support for regular expressions (regex) and fuzzy string matching. RegEx has long been a developer request -- and required the use of common language runtime (CLR) to implement in the past. Having this native in the database is a significant improvement.

DBAs often face performance challenges when dynamic SQL uses the sp_executesql function. The reason for this is that the database engine sees each of those queries as completely unique, requiring a new execution plan to be generated for each execution, even when they have the same query value. This behavior can lead to excessive CPU utilization just to compile all of those queries. SQL Server 2025 introduces an optional optimized sp_executesql, which allows these invocations to behave like a stored procedure.

Another feature in that has been in Azure SQL Database for a few years is sp_invoke_external_rest_endpoint, which allows you to make calls from your database engine to external APIs. A common question from DBAs is, "Why would you want to call Rest APIs from your database?" Let me try to answer that -- not everything in a database must happen synchronously. If we think back to a feature like Service Broker, it provides a framework to process message queues asynchronously. Calling Rest APIs from the database engine allows the same functionality in a more comfortable and robust developer framework. Also, unsurprisingly, it helps to support the AI functionality in SQL Server.

Standard Developer Edition
While the name of this is awkward, it is probably one of the longest-requested features in SQL Server. Microsoft made SQL Server Developer Edition completely free in 2014 (it was a $50 or "have an MSDN license" before that); however, from a feature and resource utilization perspective, Developer Edition was functionally the same as Enterprise Edition. This gap meant that developers or software vendors writing apps intended to run on Standard Edition had no good way to test their apps without buying a license. While the addition of SQL Server on Docker simplified this problem technically, it didn't ensure license compliance.

One caveat to this edition is that during the preview, Microsoft does not determine resource limits and features for SQL Server until close to GA. This means the following:

• All new features in SQL Server 2025 will work.
• The memory and CPU and other resource limits from SQL Server 2022 will be in place.
• Feature restrictions from SQL Server 2022 will remain in place, for example only Basic Availability Groups will be available.

You can install this edition in the with the preview software but note that installation will default to Evaluation Edition, which is also effectively Enterprise Edition, so if you want to test standard be sure to change that option.

Microsoft has added many performance and stability improvements to the engine. Still, I wanted to highlight a few of my favorites. It's important to understand that most performance features built into recent SQL Server versions depend on the Query Store feature being enabled. The abort_query_execution hint allows DBAs to block future execution of queries that are known to be problematic. While this is an aggressive step, if you have a reporting or query generation tool that is generating a complex query, you can use this feature to preemptively terminate its execution without impacting server resources.

The other two features I wanted to mention are optimized locking (which I covered in my Ignite article last fall) and using resource governor to limit tempdb consumption by a given session -- a common problem in runaway queries with poor execution plans.

SQL Server 2025 has too many features to cover in a single article, but these are the ones I'm personally most interested in. During the summer, I'll take a deeper dive into some of the more interesting features, both here and on my blog. Microsoft has made a big investment in SQL Server 2025, and I think they will pay a lot of dividends to customers. ?