Joey on SQL Server

SQL Server 2025 Announced at Microsoft Ignite 2024

Expect the next generation of Microsoft's database platform to show up in preview sometime next year.

• By Joey D'Antoni
• 11/19/2024

At today's kickoff for Microsoft Ignite, the company announced that SQL Server 2025 is finally coming.

No timing was announced for the release, but based on prior versions, I would expect to see a publicly available preview in the spring of next year. Many of the features that Microsoft has announced have been in preview or even GA in Azure SQL Database, but there are some net new features that should make database administrators happy. I had a chance to speak with some colleagues on the SQL Server team to get some more detail around some of these features.

One of the more challenging configurations with SQL Server is Always On Availability Groups (AGs). Microsoft introduced the feature in SQL Server 2012. The SQL team significantly increased the functionality and performance in SQL Server 2016, but AGs are still complex distributed systems, which can prove problematic. The feature streams transaction log data from a primary database(s) to one or more target replicas. When availability groups work well, they provide high availability and disaster recovery. However, when failures happen, like a node going down, or logs falling out of sync, the recovery process can be challenging for most DBAs and may often require a rebuild, reducing availability and frustrating users. Microsoft is introducing algorithmic changes to the availability group code stack that should reduce the need for restarting nodes, or worse reseeding databases to recovery from transient failures.

It wouldn't be a conference in 2024 if there wasn't a mention (or 125 mentions) of artificial intelligence (AI). Microsoft has announced AI features in SQL Server 2025 and SQL Server Management Studio (SSMS) (more on that later). Microsoft has added support for both cloud-based and locally hosted AI models. This means you can call Azure Open AI, but you can also call local services like Ollama, KServe, and vLLM. Microsoft is adding functionality across the SQL landscape to handle model definitions and make REST API calls based on that, but is also adding support in SQL Server 2025 for the sp_invoke_external_rest_endpointprocedure, which allows direct interaction with REST APIs from the database. This feature has been part of Azure SQL DB for a couple of years now and will be extremely useful for developers.

SQL Server 2025 is also introducing a native JSON datatype, which should improve performance over the current approach of storing JSON data in the VARCHAR(MAX) field. This datatype will also help simplify the interaction with the REST APIs of the Azure OpenAI service. In addition to the data type, Microsoft plans to add native JSON indexing across both Azure SQL and SQL Server, which should also help the performance of those JSON queries.

There are a lot of developer surface area improvements in this release, and one of the more interesting ones is called Change Streaming, which will allow developers to consume change data from the database’s transaction log and use event streaming systems like Kafka to provide real-time analytics on transactional data. I was involved with a project to attempt to do this a couple of years ago, which ultimate faltered because it relied on unsupported system commands, and we could not guarantee the order of transactions. I can see a lot of use for Change Streaming in analytical environments.

Another Arc-enabled feature Microsoft mentioned is support for Azure Entra-managed identities. I suspect this will allow for virtual machine (VM) managed identities to connect to Azure storage and other Azure resources. SQL Server has supported backing up to Azure blob storage since a late release of SQL Server 2012. Still, it has always relied upon access keys or shared access signatures for authentication. Using managed identity for those connections (and I also presume for PolyBase) simplifies this configuration and makes them more secure. If you aren't familiar with Entra-managed identities you can read more here,

Microsoft also called out the optimized locking feature in Azure SQL Database since early 2023. Optimized locking is one of those great performance features that simply works and makes your workloads run more effectively with zero changes to your configuration and code. While the internals of how database latches, locks and lock escalation work are esoteric topics, they are vital to maintaining a relational database's transactional consistency. Locks are a common source of performance issues -- usually related to poor data structures and the use of triggers but occasionally associated with the database engine itself. Optimized locking reduces the overall number of locks taken by the database engine. It avoids lock escalations through transaction ID labels, which reduces the number of rows locked in the first place, and an optimization called lock after qualification (LAQ) evaluates the queries WHERE clause to use the last committed version of a row without acquiring a lock. In addition to those features, optimized locking releases locks more quickly than in the old model. You may notice the mention of row versioning here -- optimized locking is built on the technology used by Advanced Database Recovery (ADR) and requires ADR to be enabled. Optimized locking also sees maximal benefit from using the read committed isolation level.

The other bit of big news in recent weeks was the announcement of the preview of SQL Server Management Studio (SSMS) 21. SSMS 21 comes with a new logo, optional Copilot functionality, and probably most importantly to SQL developers and DBAs DARK MODE!!! If you haven’t worked in this space, you might not but dark mode for SSMS has been one of the most requested features in the history of SQL Server. You should note that dark mode is not enabled for all dialogs, but that is something the team continues to work through. SSMS 21 is Visual Studio 2022-based application (there is no need to install Visual Studio to use SSMS 21). The use of a 64-bit VS shell means you can return the entirety of that 4 GB table, but it doesn't mean that you should.

The other commonly requested feature that SSMS now supports is Git integration. The end goal of SSMS is to bring support for SQL projects to SSMS. This process is not complete, as the SDK-style database projects require work to be fully supported in SSMS.

Copilot, which is currently in private preview, will be an optional extension to SSMS once released publicly. Like Github Copilot, it will help you write T-SQL based on native language prompts. In SSMS, Copilot will use the context of your connection and your database metadata to help you write T-SQL queries. Copilot can also assist with fixing and explaining existing queries. 

It is the very early days of SQL Server 2025, but like I speculated last May, Microsoft has quietly been putting a lot of impressive features in Azure SQL Database, and I expected them to land in the next version of SQL Server. While PaaS services like Azure SQL DB and Managed Instance do dominate marketing efforts, a lot of organizations still like the flexibility of SQL Server, whether it is in a cloud VM or on-premises. SQL Server 2025 brings together AI and performance and functionality improvements to core engine, while SSMS delivers the feature everyone has been asking for: dark mode.