Enhancing your SQL workflow

From a tooling perspective, we are investing heavily in SQL Server Management Studio (SSMS) and the MSSQL extension for Visual Studio Code. SSMS is where we primarily aim to serve data professionals like you including database administrators, data analysts, database developers, data scientists, and data engineers, and we have been doing so for two decades now. The MSSQL extension for Visual Studio Code is where we primarily aim to serve application developers. Additionally, from a web interface perspective, we are investing in the Microsoft Azure portal and Microsoft Fabric web experiences to support Azure and Fabric cross-functional roles and tasks to be done.

In the past year, we’ve modernized SSMS—now based on the latest release of Visual Studio—and brought in numerous customer requests including dark mode, Arm64 support, Fabric support, GitHub Copilot, and more. We also brought rich, AI-assisted experiences to Visual Studio Code with GitHub Copilot Ask and Agent mode support, in addition to many new and improved capabilities in areas around designing schemas and tables, provisioning and getting connected (including Fabric and local containers), query results, and more. In the web, we launched a unified Azure SQL experience in the Azure portal, and shipped SQL database in Fabric, now generally available.

Building the future of SQL development

In addition to delivering quality releases and consistent functionality across these tools and experiences that enable you to efficiently manage and develop with Microsoft SQL Server, we are aiming higher for the future. Our vision is to equip every database with source control and CI/CD integration, streamline trusted and reliable deployments, provide consistent and tailored Copilot experiences, and deliver modern drivers, SDKs, and CLIs as well as a robust data API and MCP Server. We’re also investing in rich experiences that help developers take full advantage of AI capabilities in the SQL engine, making it easier to build and optimize AI-ready applications.

Delivering on that vision requires focus and critical prioritization, a responsibility that we’re taking with deep consideration and increased transparency to you. Full roadmap details across our tools and experiences can be found at the end of this article. If you were using Azure Data Studio or SDK-style SQL projects in Visual Studio 2022 and are impacted by the retirement of these tools, you can still use the original SQL projects in Visual Studio 2026 so that your established solutions can upgrade to the latest Visual Studio version without compatibility conflicts. SDK-style (Microsoft.Build.Sql) projects are generally available in Visual Studio Code with the SQL Database Projects extension and are directly integrated with SQL database in Fabric source control. In the first half of 2026 SDK-style SQL projects will be added to SQL Server Management Studio, empowering more database professionals with foundational tools for database DevOps.

See what’s next and join the conversation

We know that you will have feedback for us, now and as we go forward, and we want to thank you in advance for that, as it is critical that we understand what you need from your SQL tooling. This benefits you, us, and the entire community.

• SQL Server Management Studio roadmap
• SQL Server Management Studio feedback site
• MSSQL extension for Visual Studio Code roadmap
• MSSQL extension for Visual Studio Code feedback
• SQL projects and DacFx feedback
• Microsoft SQL feedback site
• SQL database in Fabric roadmap
• SQL database in Fabric Ideas site

The post How the Microsoft SQL team is investing in SQL tools and experiences appeared first on Microsoft SQL Server Blog.

Continue reading to learn about how three organizations—Thomson Reuters, Hexure, and CallRevu—each jumpstarted their transformation with migration of their on-premises workloads to Microsoft Azure. As a result, organizations were able to improve operational efficiency and accelerate AI-powered innovation. Their stories reveal how fully managed platform-as-a-service solutions like Microsoft Azure SQL Managed Instance helps organizations move from legacy constraints to a scalable and secure AI-ready foundation ready to power future possibilities.

Modernization at scale: Thomson Reuters  

For Thomson Reuters, one of the world’s most trusted providers of tax and accounting solutions, modernization was less of an option and more of a necessity. Supporting over 7,000 firms and 70,000 users during the peak of tax season required an infrastructure that was both robust and scalable. The company previously hosted more than 18,000 databases and over 500 terabytes of data on third-party servers, an approach that came with high costs, operational complexity, and challenges scaling to meet seasonal demand.  

By migrating this massive estate into Azure SQL Managed Instance from another cloud hosting environment, Thomson Reuters achieved modernization at scale. With programs like Microsoft Azure Migrate to support every step of the migration journey, and automation tools like PowerShell and Azure Resource Manager templates, they were able to streamline deployments and maintain performance while minimizing disruptions. Azure’s fully managed platform allowed Thomson Reuters to streamline database administration and automated key tasks like backups and updates. As a result, their teams could focus on delivering value to customers rather than managing infrastructure. Azure Virtual Desktop together with Windows 11 facilitated access to tax preparation applications, reducing complexity and costs.  

The benefits were immediate and significant. Thomson Reuters gained: 

• Consistent performance during seasonal peaks.
• Improved resiliency.
• Reduced support overhead.
• Optimized costs across licensing and infrastructure.  

Thomson Reuters now has a foundation for continued growth and the flexibility to scale its services as demand requires.

Operational efficiency and performance: Hexure  

While Thomson Reuters’ story highlights scale, Hexure’s migration shows the operational efficiency gains that come from moving to a fully managed platform with Azure SQL Managed Instance and Microsoft Azure App Service. Hexure provides digital solutions for insurance and financial services companies—managing sensitive customer information across many databases and applications.  

The company faced challenges with aging infrastructure that slowed down critical processes and demanded heavy manual intervention. Provisioning new customer instances, managing backups, and handling failovers was time-intensive. Processing delays made it harder to serve clients with the speed and reliability customers expect.  

Migrating to Azure SQL Managed Instance changed that equation. Hexure cut processing times by up to 97%, transforming overnight batch jobs into near-instant operations. Migration times were reduced by more than 80% thanks to built-in compatibility and automation. With Microsoft Azure Key Vault, Hexure could better manage cybersecurity and protection of their data. Features like point-in-time restore, automated backups, and geo-replication not only boosted resilience but also ensured compliance with industry regulations.  

Equally important, the move allowed Hexure to:  

• Onboard new customers in minutes versus hours.
• Deliver faster shipping cycles for features and platform improvements.
• Reduce management of infrastructure—including servers.

With migration, Hexure could now focus on innovation and customer service. For an industry where trust and responsiveness are critical, this operational leap forward directly translates into stronger client relationships.

Innovation with AI and insights: CallRevu  

CallRevu’s story illustrates the next frontier: innovation. CallRevu helps automotive dealerships improve lead conversion, follow-up, and customer experience by analyzing phone calls across more than 5,000 locations. Handling this volume of conversational data requires not only advanced analytics, but scalable platform. 

With a fully managed solution built on Azure SQL Managed Instance, Microsoft Azure Kubernetes Service together with Microsoft Azure AI services, CallRevu created a platform that goes beyond storing and managing data. It ensures reliable, scalable performance for call data and transcriptions, while services like Microsoft Azure OpenAI for real-time summaries and insights. This integration allows CallRevu to surface actionable insights in real time—helping dealerships connect marketing to results, improve agent performance, and ultimately drive more sales. 

The company also benefits from the operational simplicity that Azure SQL Managed Instance delivers. By migrating from their on-premises SQL Server environment, they were able to benefit from automated backups, scaling, and monitoring to reduce administrative overhead, while built-in security helps protect sensitive customer interactions. Data is mirrored in Microsoft Fabric allowing Power BI dashboards to generate real-time insights. With a strong and agile data foundation in place, CallRevu can focus on innovating faster—bringing AI-powered capabilities to an industry where customer engagement is a critical differentiator while also:  

• Increasing customer satisfaction by 10%.
• Saving USD500,000 annually in labor costs.
• Increasing lead conversion by 15%.

Take the next step in your transformation journey  

Modernization is not a one-time project—it’s a journey that is different for every organization. For some organizations, the first step is simply migrating off legacy servers. For others, it’s about rethinking how operations can run more efficiently. And for many, it’s about leveraging cloud and AI to create entirely new opportunities.  

The experiences of Thomson Reuters, Hexure, and CallRevu highlight how migration to a platform-as-a-service anchored on database solutions like Azure SQL Managed Instance supports every stage of that journey. By providing a managed, secure, and scalable cloud platform, backed by the tools and programs, organizations can migrate with confidence, operate more efficiently, and innovate faster.

Ready to get started? Here are some free tools you can start trying today: 

• Try Azure SQL Managed Instance free today. 
• Learn how Azure Migrate can help you get started.

Join Microsoft at PASS Data Community Summit 2025 to continue your learning journey and how Azure is making it easier than ever to start your transformation journey. Learn more on our sponsorship and presence.

The post Innovation spotlight: How 3 customers are driving change with migration to Azure SQL appeared first on Microsoft SQL Server Blog.

Adoption gains speed 

We’re seeing incredible momentum with SQL Server 2025 since its public preview debut at Microsoft Build. From lighting up community events like SQL Saturdays to being featured at SQLBits 2025 with CTP 2.1, the excitement is electric. SQL Server 2025 isn’t just keeping pace, it’s setting a new standard. Customers are adopting SQL Server 2025 twice as fast as SQL Server 2022 based on downloads of the public preview.

In the early adoption program, participants were asked to rank the SQL Server 2025 features they were most interested in testing. Built-in AI emerged as one of the top priorities, alongside performance and scalability enhancements. In addition, based on feedback from our preview customers, developer-friendly enhancements—especially the introduction of native JSON support—along with powerful T-SQL additions like regular expression support, have also been positively received—streamlining data processing and boosting developer efficiency. Enterprise customers like Entain, Mediterranean Shipping Company, Kramer & Crew, Schultz, and Bühler are already hands-on, exploring how SQL Server 2025 can power their next-gen applications. 

“As one of the largest SQL Server consulting firms in Brazil, we are excited about the AI features in SQL Server 2025, especially the potential for text processing that can benefit companies of all sizes. AI brings new ways to process and extract insights from data and with SQL Server being the core repository for many businesses, native AI features like embeddings, REST API support, and vector indexes are game changers. They eliminate the need for external vector databases, making AI integration more seamless and efficient.”

Rodrigo Ribeiro Gomes, Head of Innovation, Power Tuning

“SQL Server 2025 introduces seamless Azure and Arc integration and features, enhanced JSON and RegEx capabilities, and enhancements to database engine.”  

Shailesh Panday, Deputy Manager, IT, Buhler AG

Explore capabilities with new preview features

SQL Server 2025 introduces a new preview feature option, giving customers the flexibility to balance production stability with early access to innovation. When turned on, it unlocks access to upcoming features still in preview, enabling developers to test and evaluate new capabilities like vector indexing, improved text chunking, and change event streaming without impacting production workloads (a complete list of preview features is here).  

This opt-in model brings the agility of the cloud to on-premises SQL Server, empowering customers to innovate on their terms. Preview features are provided in alignment with Microsoft’s supportability guidelines. They are intended for evaluation and testing purposes only and are not recommended for use in production environments. The database itself in SQL Server 2025 remains as fully supported and is an essential component of the general availability release. Preview features are optional and designed to operate independently in preview mode. Enabling these features does not impact the stability or supportability of your database.  

SQL Server has traditionally used trace flags to enable or disable specific behaviors within the database engine. The new preview feature switch in SQL Server 2025 is fundamentally different from traditional trace flags. While trace flags are primarily used for debugging and diagnostics, often by DBAs or support engineers to control internal engine behavior, the preview feature switch is designed for developers to explore and test new, user-facing capabilities. Trace flags typically operate at the instance level, affecting the entire server, whereas the preview feature switch is a database-scoped configuration, offering more granular control and safer experimentation without impacting other workloads. Learn more about the preview features in the frequently asked questions.

New feature highlights

As SQL Server adoption on Linux continues to grow, we’re excited to introduce preview support for Ubuntu 24.04, one of the most widely used and trusted Linux distributions. This marks a significant step forward in our commitment to cross-platform flexibility and developer choice. By embracing the latest Ubuntu release, SQL Server 2025 ensures developers and IT teams can build and run modern, cloud-connected applications on a familiar and up-to-date Linux environment. 

PolyBase plays a critical role in enabling analytics scenarios by allowing SQL Server to query external data sources like Microsoft Azure Data Lake or Azure Blob Storage using familiar T-SQL. As many of SQL Server’s modern analytics capabilities are deeply integrated with Microsoft Azure services, secure and seamless access to cloud storage is essential. With preview support for Managed Identity authentication to Azure Storage, SQL Server 2025 takes a step forward in simplifying security and access management. This enhancement aligns with SQL Server’s decade-long track record as the most secure database and reinforces our commitment to enterprise-grade security. By eliminating the need for storing secrets or keys, Managed Identity makes it easier and safer for customers to build scalable, cloud-connected analytics solutions using PolyBase. 

Mirroring in Microsoft Fabric is a game-changing capability that unlocks seamless, near real-time analytics on operational data from SQL Server 2025. To help customers manage compute resources efficiently during the mirroring process, SQL Server now supports creating a dedicated Resource Governor (RG) pool. Each phase of mirroring—such as ingestion, transformation, and synchronization—can be assigned to a specific workload group, giving administrators fine-grained control over resource allocation. These workload groups can be placed in the same or different pools depending on capacity planning needs.  

Discover more 

SQL Server 2025

An AI-ready enterprise database with best-in-class security, performance, and availability.

Download RC0 today

• Download release candidate (RC0). 
• Learn more on Microsoft Learn.
• Discover what’s new in SQL Server 2025 in our latest Mechanics video.

The post Accelerating SQL Server 2025 momentum: Announcing the first release candidate appeared first on Microsoft SQL Server Blog.

End-to-end migration simplified

If you’ve been using SQL Server enabled by Azure Arc, you’re likely familiar with continuous migration assessments that offer target recommendations, technical readiness, and cost estimates. Now, we’re taking the next step forward by introducing automated, end-to-end migration capabilities in SQL Server enabled by Azure Arc.

Once you’ve assessed the Azure readiness of your SQL Server instances, you can now select or provision your Azure targets such as Azure SQL Managed Instance, without jumping between various tools or places in the Azure portal. This streamlined workflow eliminates context switching and simplifies provisioning. You’ll see estimated costs during the provisioning process, giving you clear visibility and confidence to plan ahead. Plus, you can take advantage of the free Azure SQL Managed Instance offer to evaluate at no cost, making it easier to get started. 

Real-time database replication is also integrated into the new migration capability. This new method, built on top of distributed availability groups, enables near real-time database replication from SQL Server to Azure SQL Managed Instance. Setting up real-time replication manually can be a complex multi-step process, but Azure Arc simplifies the entire process while providing best-in-class monitoring. If a customer decides to go back to on-premises, Azure SQL Managed Instance Link also supports seamless failback (if the source SQL Server instance is SQL Server 2022 and above). 

Migrate with confidence 

The new capability empowers customers to migrate with confidence. Before officially cutting over, you can validate that the target Azure SQL Managed Instance meets your business requirements by using the target instance as a read-only replica.  

In addition, the client connection summary feature in SQL Server, enabled by Azure Arc, automatically and continuously captures and displays which clients are connecting to each instance. This replaces the previous manual and time-consuming process of tracing applications to their databases, giving customers clear visibility and helping ensure a smooth transition to Azure.

Get started today 

If you’re looking to simplify and accelerate your migration to Azure, this new connected capability can help you get there faster—with less downtime, lower overhead, and more confidence. 

• Learn more on our Tech Community blog.
• Learn more about the new migration capability in Azure Arc on Microsoft Learn.
• Onboard your SQL Server to Azure Arc today.
• Learn more about continuous migration assessment from SQL Server enabled by Azure Arc.

Frequently asked questions 

1. What is SQL Server enabled by Azure Arc?  

SQL Server enabled by Azure Arc extends Azure services to SQL Server instances hosted outside of Azure: in your data center, in edge site locations like retail stores, or any public cloud or hosting provider. 

Azure Arc enables you to consistently manage SQL Server instances across hybrid and multicloud environments, bringing cloud innovations such as automated updates, unified policy, best practices assessment, and advanced security to SQL Server running anywhere.

2. What’s the continuous migration assessment from Azure Arc?  

Microsoft has announced the general availability of continuous migration assessments for SQL Server enabled by Azure Arc, marking a step forward in simplifying cloud migration planning. This release introduces a redesigned assessment experience that provides deeper insights and more intuitive navigation, especially for single Arc-enabled instances. 

One of the standout features is the integration of retail pricing visibility across all Azure savings options, including Azure Hybrid Benefit (AHB), reserved instances, and Azure savings plans. These pricing insights are now available for Azure SQL Database, SQL Managed Instance, and SQL Server on Azure Virtual Machines (VMs), helping users make informed cost decisions.  

3. What is Azure Database Migration Service? 

Azure Database Migration Service is a fully managed service designed to seamlessly migrate databases to Azure with minimal downtime. It supports both homogeneous migrations (such as SQL Server to Azure SQL) and heterogeneous migrations (such as Oracle to Azure PostgreSQL).

4. What are the migration methods in the new migration capability? 

The following methods are built into the migration process. Azure SQL Managed Instance link enables near real-time database replication using distributed availability groups. Log Replay Service uses SQL Server log-shipping technology and requires a brief planned cutover. Review the Microsoft Learn page to understand the differences between these two migration methods and choose the option that best suits your needs.

The post Accelerate SQL Server Migration to Azure with Azure Arc  appeared first on Microsoft SQL Server Blog.

“We are allowing the cloud provider to handle hardware resource allocation and maintenance so that our staff focus on program development. This strategy ensures that during system operation we no longer need to worry about hardware failures, power instability or information security issues, greatly improving the system’s operational reliability.”

—Ching-Lung Chang, CIO, Library and Information Services Office at YunTech .

Modernize to Azure, a smooth path, a more powerful destination  

Migrating to a cloud platform is an essential step on the journey to modernization, and there are many choices. What makes SQL Server and Microsoft Azure SQL unique is that it’s built on the same engine, no matter where you deploy, which means you can build on your existing SQL experience while gaining the layered security, intelligent threat detection, and data encryption that Azure provides.  

Modernizing to Microsoft Azure SQL Managed Instance offers cost savings, scalability, security, seamless migration, productivity, and always up-to-date features. Now in preview, Azure SQL Managed Instance next generation general purpose delivers improved performance and scalability, making migration and modernization faster and easier across more customer scenarios.  

Azure is the destination, but we know the journey matters just as much. A new SQL Server migration experience is now under preview in Azure Arc. It is powered by Azure Database Migration Service and offers seamless, end-to-end migration capabilities including continuous migration assessments, simplified provisioning, and real-time database replication, assisted by Copilot in Azure. What once took months can now be accomplished in just days, with confidence, continuity, and control.

In-place upgrade to SQL Server 2025  

Another way to stay protected is to upgrade your SQL Server to  SQL Server 2025. Built on SQL Server’s legacy of best-in-class security, performance and availability, SQL Server 2025 empowers you to develop modern AI applications using your data. It provides built-in, extensible AI capabilities, enhanced developer productivity, and seamless integration with Azure and Fabric, all within SQL Server engine using the familiar T-SQL language. 

The upgrade experience has been streamlined. With the retirement of Azure Data Studio and Data Migration Assistant, migration capabilities are now integrated directly into SQL Server Management Studio (SSMS). This eliminates the need for separate tools, reducing complexity and effort. In SSMS 21, a new migration extension allows DBAs and partners to assess and upgrade SQL Server instances from older to newer versions, all within the same management environment. 

Stay protected on-premises or in multi-cloud environments with Azure Arc  

Extended Security Updates for SQL Server 2016 offers an enhanced cloud experience through Azure Arc. With this customer-centric approach, security updates will be natively available in the Azure portal through Azure Arc. Enabling your SQL Server with Azure Arc also unlocks Azure benefits and flexible subscription billing for SQL Server 2016 workloads on-premises or across multi-cloud environments.  

If you enable Extended Security Updates subscription in your production environment through Azure Arc, you have access to SQL Server Extended Security Updates subscription in the non-production environment for free, through SQL Server Developer edition or an Azure Dev/Test subscription.  

We encourage all our customers running SQL Server 2016 to start planning for the end of support. We have migration resources, best practices, and more, as well as a rich ecosystem of partners ready to help. To get started, please visit the following pages to learn more: 

• Why migrate Windows Server and SQL Server to Azure: ROI, innovation, and free offers.
• Get the full return on investment (ROI) and total cost ownership (TCO) reports from IDC: The Business Value of Microsoft Azure SQL Database and Azure SQL Managed Instance Workload and The Business Value of Microsoft Azure for SQL Server and Windows Server Workloads.
• Onboard your SQL Server to Azure Arc.

Frequently asked questions 

What does end of support mean? 

Microsoft Lifecycle Policy offers 10 years of support (five years for mainstream support and five years for extended support) for business and developer products (such as SQL Server and Windows Server). After the end of the extended support period, there are no patches or security updates, which might cause security and compliance issues, and expose your applications and business to serious security risks. 

What do Extended Security Updates include? 

Extended Security Updates include provision of security updates and bulletins rated critical by the Microsoft Security Response Center (MSRC), for a maximum of three years after the end of extended support.  

Extended Security Updates are distributed if and when available. Extended Security Updates don’t include technical support. Customers must purchase a paid support plan (Pay Per Incident, Unified, and Premier Support for Partners) to leverage technical support. Extended Security Updates don’t include new features, functional improvements, nor customer-requested fixes. However, Microsoft might include non-security fixes as deemed necessary. 

Why do Extended Security Updates only offer “critical” updates? 

For end of support events in the past, SQL Server provided only critical security updates, which meets the compliance criteria of our enterprise customers. SQL Server doesn’t ship a general monthly security update.

The post Protect and modernize SQL Server 2016 workloads with Microsoft  appeared first on Microsoft SQL Server Blog.

During recent years, Azure has undergone significant evolution, marked by groundbreaking innovations like Microsoft Fabric and Azure Data Lake Storage. As we continue this journey, it remains imperative to keep evolving our approach on hybrid data storage, ensuring optimal empowerment for our SQL Server customers in leveraging the best from Azure.

Retirement of SQL Server Stretch Database 

On November 16, 2022, the SQL Server Stretch Database feature was deprecated from SQL Server 2022. For in-market versions of SQL Server 2019 and 2017, we had added an improvement that allowed the Stretch Database feature to stretch a table to an Azure SQL Database. Effective July 9, 2024, the supporting Azure service, known as SQL Server Stretch Database edition, is retired. Impacted versions of SQL Server include SQL Server 2022, 2019, 2017, and 2016.  

In July 2024, SQL Server Stretch Database will be discontinued for SQL Server 2022, 2019, 2017, and 2016. We understand that retiring an Azure service may impact your current workload and use of Stretch Database. Therefore, we kindly request that you either migrate to Azure or bring their data back from Azure to your on-premises version of SQL Server. Additionally, if you’re exploring alternatives for archiving data to cold and warm storage in the cloud, we’ve introduced significant new capabilities in SQL Server 2022, leveraging its data virtualization suite. 

The path forward 

SQL Server 2022 supports a concept named CREATE EXTERNAL TABLE AS SELECT (CETaS). It can help customers archive and store cold data to Azure Storage. The data will be stored in an open source file format named Parquet. It operates well with complex data in large volumes. With its performant data compression, it turns out to be one of the most cost-effective data storage solutions. Using OneLake shortcuts, customers then can leverage Microsoft Fabric to realize cloud-scale analytics on archived data.  

Our priority is to empower our SQL Server customers with the tools and services that leverage the latest and greatest from Azure. If you need assistance in exploring how Microsoft can best empower your hybrid data archiving needs, please contact us.

New solution FAQs

What’s CETaS? 

Creates an external table and then exports, in parallel, the results of a Transact-SQL SELECT statement. 

• Azure Synapse Analytics and Analytics Platform System support Hadoop or Azure Blob Storage.
• SQL Server 2022 (16.x) and later versions support CETaS to create an external table and then export, in parallel, the result of a Transact-SQL SELECT statement to Azure Data Lake Storage Gen2, Azure Storage Account v2, and S3-compatible object storage. 

What is Fabric? 

Fabric is an end-to-end analytics and data platform designed for enterprises that require a unified solution. It encompasses data movement, processing, ingestion, transformation, real-time event routing, and report building. Fabric offers a comprehensive suite of services including Data engineering, Data Factory, Data Science, Real-Time Analytics, Data Warehouse, and Databases. 

With Fabric, you don’t need to assemble different services from multiple vendors. Instead, it offers a seamlessly integrated, user-friendly platform that simplifies your analytics requirements. Operating on a software as a service (SaaS) model, Fabric brings simplicity and integration to your solutions. 

Fabric integrates separate components into a cohesive stack. Instead of relying on different databases or data warehouses, you can centralize data storage with Microsoft OneLake. AI capabilities are seamlessly embedded within Fabric, eliminating the need for manual integration. With Fabric, you can easily transition your raw data into actionable insights for business users. 

What is OneLake shortcuts?  

Shortcuts in OneLake allow you to unify your data across domains, clouds, and accounts by creating a single virtual data lake for your entire enterprise. All Fabric experiences and analytical engines can directly connect to your existing data sources such as Azure, Amazon Web Services (AWS), and OneLake through a unified namespace. OneLake manages all permissions and credentials, so you don’t need to separately configure each Fabric workload to connect to each data source. Additionally, you can use shortcuts to eliminate edge copies of data and reduce process latency associated with data copies and staging. 

Shortcuts are objects in OneLake that point to other storage locations. The location can be internal or external to OneLake. The location that a shortcut points to is known as the target path of the shortcut. The location where the shortcut appears is known as the shortcut path. Shortcuts appear as folders in OneLake and any workload or service that has access to OneLake can use them. Shortcuts behave like symbolic links. They’re an independent object from the target. If you delete a shortcut, the target remains unaffected. If you move, rename, or delete a target path, the shortcut can break. 

Learn more 

• Stretch Database
• Deprecated database engine features in SQL Server 2022
• Install an on-premises data gateway
• SQL Server in Microsoft Fabric? Use CETaS to move data!
• OneLake shortcuts
• CREATE EXTERNAL TABLE AS SELECT (CETaS)

Microsoft Fabric

Bring your data into the era of AI

Explore solutions

The post Announcing the retirement of SQL Server Stretch Database appeared first on Microsoft SQL Server Blog.

Azure SQL and SQL Server support building new generative AI experiences that become supercharged when combined with your data. In addition, SQL brings AI assistance to a new level with copilot experiences for both self-help and natural language to SQL capabilities.

In this blog post, I’ll share how you can get started with these new AI experiences—Azure SQL and SQL Server. First, check out our latest story on Microsoft Mechanics:

Responsible AI

Many conversations about AI starts with a statement on responsible AI. Microsoft has established a set of policies, research, engineering efforts, and principles to ensure AI technologies are adopted, implemented, and used in a responsible manner.

These principles include fairness, reliability and safety, privacy and security, inclusiveness, transparency, and accountability. Your data is your data. One promise for Microsoft is that private data of any user, including prompts and responses, are never used to fine tune a model that Microsoft hosts or implements.

Generative AI applications with your data

One of the motivations for generative AI applications is to become more productive, creative, and efficient through the generation of content in all forms: text, audio, and video. Many of today’s examples for generative AI applications involve the user of a natural language prompt and the interaction with a language model. Many of you have probably at some point used an application like ChatGPT or Microsoft Copilot which are great examples of generative AI applications.

Get smarter with your data

While these are great applications, they don’t know about your data. The combination of a generative AI application with your data, for example, stored in a database, can be quite powerful. Generative AI provides methods for smarter searching on your data. A common application pattern is to use language models with a prompt application to “chat with your data.” Using the concept of vector embeddings, language models allow you to get more precision on questions about your data. In addition, responses to questions are more tailored to your users and searches can often be faster because language models allow you to use the power of natural language. Generative AI applications with your data provide unique intelligence in an interactive manner, including conversations. Language models are trained to provide more context on your search, often giving you more (hence generated) content than you might normally get using common searching techniques within a database engine with a language like SQL.

Prompt engineering, Retrieval Augmented Generation fundamentals, and hybrid search

As you investigate how you can take advantage of generative AI with language models, there are two important concepts to understand:

Prompt engineering is the discipline of using high quality and descriptive prompts when interacting with a language model. The concept is simple. The better the prompt, the likelihood of a better response from the model. For example, let’s say you use Microsoft Copilot and type in a prompt like “What are the best steak restaurants in Fort Worth, Texas?” You will get a good list of steak houses in Fort Worth, Texas based on a search by Copilot of rankings across a broad set of searches. But what if you are on a bit of a tight budget? Instead of looking at the results from the prompt and trying to figure out what prices you can afford you could instead ask “I’m on a tight budget but want to eat at a good steakhouse in Fort Worth, Texas.” Now your results are more tailored for what you really want. And since you are interacting with a language model, it understands the phrase “tight budget” means you need choices that are good but affordable.

While this technique can be great if you are interacting with a model that is trained to help you search the internet, what about your own data? One prompt engineering technique to get smarter with your data is called Retrieval Augmented Generation (RAG). The concept of RAG is to search for information from a source of data and use those results to augment the prompt to the model. For Azure SQL and SQL Server, this could mean using standard SQL techniques to search for data using Transact-SQL (T-SQL), taking these results, and sending them along with the original prompt to the language model. This technique is simple and can be an effective way to get smarter with your data, and this can work with almost any type of data you search, not just SQL databases.

For Azure SQL and SQL Server, a more sophisticated technique is called hybrid search. With hybrid search, you can use the power of vector search combined with the query capabilities of your SQL data. Vector embeddings are numerical representations of data that capture semantic meaning and similarities. The key to embeddings with language models is that the model can generate embeddings based on data like text. This means you can take text data inside your SQL database and use a model to generate embeddings and then store these embeddings in your database. Now anytime you want to search for data inside the database, you can send a prompt to a language model which will generate embeddings for the prompt. And then you can use vector search techniques to compare the embeddings from the prompt with the embeddings stored in your database. You can then combine the vector search with other techniques you would normally use in T-SQL to find data in your database: a hybrid search.

There are methods today to use hybrid search completely inside the engine using T-SQL and outside the engine using Microsoft Azure AI Services or frameworks like LangChain or Semantic Kernel.

Get started quickly with Azure AI Services

One approach to get started quickly with no code required is to index your SQL database using Azure AI Search and then use Azure OpenAI Service to build a simple prompt app and “chat with your data” using a hybrid search technique.

You can use Azure AI Search to build an index based on a table in your SQL Server or Azure SQL database. When you build the index, you can apply a skillset to generate embeddings based on your data and store the result in the index. Now you can use Azure OpenAI with a prompt application to perform hybrid searches on your data. One example prompt application to perform simple testing is to use Azure AI Studio. In addition, as you change your SQL data, the index is automatically updated including the embeddings. The figure below shows the basic flow:

You can see this in action from the latest Microsoft Mechanics video or download a deck with demo recordings. One of the interesting aspects of this example is the method of changing the system message to direct the language model to respond in a unique way using the same data. This is also a great example of prompt engineering.

Learn more about Azure SQL in Azure AI Search.

Use hybrid search inside the engine with T-SQL

Let’s say instead of using a separate index, you would like to build generative AI capabilities for your application all inside the engine using T-SQL. You can do this in a very powerful way for Azure SQL Database today using a combination of vector embeddings, vector search, and other T-SQL search methods. This is a true hybrid search because you are using all the power of the SQL query processor together with a vector search. An example my colleague Davide Mauri has developed uses these techniques to help him find the best restaurant for one of this favorite Italian foods, focaccia bread.

Davide built an application that stores reviews from restaurants in the form of vector embeddings using Azure OpenAI Service with Azure SQL Database Representational State Transfer (REST) API inside the engine. With this in place, he can take any prompt to search for the best focaccia bread and use the same technique to generate embeddings for the prompt. Then, he can use a new T-SQL vector_distance function to perform a similarity search. The true power of SQL is possible because Davide built queries to combine this vector search with other criteria from spatial types, the new JSON data type, and the new Regular Expression (RegEx) T-SQL capabilities.

You can see a diagram of how these techniques are combined together below:

You can see this demo in action in our Microsoft Mechanics video or download a deck with demo recordings. You can learn more about the new JSON data type (preview). You can also sign-up to preview the new vector search capabilities and RegEx in Azure SQL Database.

Building generative AI applications using frameworks

There are other methods to build generative AI applications with Azure SQL and SQL Server using frameworks such as:

• LangChain:
  LangChain is an open-source framework to orchestrate AI applications with language models. You can use programming languages such as Python and JavaScript to build your own generative AI application. LangChain supports the SQL Agent Toolkit which allows you to interact with a SQL database using natural language prompts. The toolkit integrates the connection to your database with a language model to generate SQL queries based on natural language prompts. You can see an example of this in the blog post “Building your own DB Copilot for Azure SQL with Azure OpenAI GPT-4.”
• Semantic Kernel:
  Semantic Kernel is an open-source SDK to allow you to build AI applications in C#, Python, and Java, interfacing with many common models in the industry such as OpenAI, Azure OpenAI, and Hugging Face. A library has been built to allow a Semantic Kernel application to interact with Azure SQL Database (and use the new vector search capability) called the SQL Connector.

See a full range of SQL and generative AI examples.

The age of copilots

Microsoft has transformed the industry and how we work and live with a new set of AI assisted experiences called Microsoft Copilot. Copilots are AI companions that work everywhere you do and intelligently adapt to your needs.

Use Copilots where you live

I realize there seem to be copilots everywhere. It is hard to keep track. Microsoft is investing in Copilot experiences in almost every product or service. Use the product or service you normally do and see what Copilot can offer. For example, if you have Microsoft 365, use Copilot for Microsoft 365 naturally within Microsoft Teams or any Office product or service. I personally use Microsoft Copilot in my Edge browser or on the app on my phone for any search experience I need today—web or work related.

Microsoft Copilot in Azure

The primary resource to manage and explore Microsoft Azure is the Azure portal. You can now use Microsoft Copilot in Azure within the Azure portal to manage, deploy, and troubleshoot Azure resources. Azure SQL Database is one of the most popular Azure resources in the world, so we have built two distinct experiences within the Copilot in Azure framework using natural language for self-guided assistance and T-SQL query authoring:

Microsoft Copilot in Azure integration

One of the strengths of SQL Server is the deep built-in telemetry within the engine all accessible through T-SQL. This includes Dynamic Management Views (DMV) and Query Store. These rich, traditional capabilities shine through now in Copilot. For example, you can prompt with Copilot a general statement like “My database is slow” and Copilot, based on your permissions, will access real-time diagnostic data, in the context of your database, to help you quickly navigate difficult, and often vague, performance problems. Here is an example:

You can then continue a conversation with Copilot to tune the query causing the problem. There are many different skills that Copilot can help you all in the context of your database. Learn about all the possibilities of Copilot skills in Azure SQL Database (preview).

Natural language to SQL

The T-SQL query language has so many great capabilities and possibilities. But the open nature of T-SQL also leads to difficulties in crafting queries to meet the need of your application. Along comes a copilot experience to allow you to “chat” with your database using natural language in the context of your database and schema: table, columns, and key relationships. A simple example is being able use a natural language statement to generate a query that typically requires several joins over multiple tables like the following:

Learn more how to use natural language to SQL.

You can see both experiences in action in our Microsoft Mechanics video or download a deck with demo recordings.

Innovations moving forward

We are just beginning with SQL and AI. We have innovations for the future planned for enhancements with AI services, enhancements for deep integration for vector search, and enhanced Copilot experiences for SQL Server. Stay tuned for future blog posts showing all of these innovations.

Learn more today

Here are more resources for you to learn more about SQL and AI:

• Explore the capabilities of Azure SQL Database.
• Keep track of SQL and AI at our one-stop documentation hub.
• Keep track of all of our SQL and AI examples.
• Showcase your own demos using our decks with demo recordings built-in.
• Read our team’s blog on developer experiences for SQL including the latest AI examples at Azure SQL Devs’ Corner.
• To see all this in action, check out our new Microsoft Mechanics video.
• Learn the essentials to building a SQL generative AI application.
• Check out the comprehensive blog post from Sanjay Mishra to explore the power of generative AI applications with Azure SQL Database.
• Learn more about Copilot in Azure SQL Database experiences from Joe Sack’s Data Exposed episode.
• Check out our Microsoft Build 2024 sessions:
  • Power AI apps and develop rich experiences with Azure SQL Database
  • The power of AI and Copilot for Azure Databases

Azure SQL

Migrate, modernize, and innovate with the modern SQL family of cloud database services

Start your journey

The post Getting started with delivering generative AI capabilities in SQL Server and Azure SQL appeared first on Microsoft SQL Server Blog.

We’re excited to announce the public preview of the Premium SSD v2 provisioning experience for SQL Server on Azure Virtual Machines (VMs) deployed in the Azure portal. Premium SSD v2 storage improves performance, reliability, and scalability of your SQL Server workloads while offering robust resource capacity, as you can create a single disk with up to 64 TiBs, 80,000 input/output per second (IOPS), and 1,200 MB/s throughput.

When building Azure SQL VMs in the cloud, DBAs have several storage choices they can consider to give their applications the required performance and capacity their workloads require. In Azure, DBAs have compute options along with affordable storage designed to handle mission critical SQL Server workloads. In this article, we’ll review these options focusing on the latest capabilities of Premium SSD v2 storage and the Ebds_v5 Azure VMs, which are better together—providing the best combination of price performance capabilities in the cloud for SQL Server workloads.

Premium SSD v2 storage in Azure portal

Deploy SQL Server on Azure VMs with Premium SSD v2 disks using the Azure portal.

Learn how

Managed disk storage options

DBAs looking to move their mission critical SQL Server applications from on-premises to the cloud have the managed disk options of Premium SSD (Gen 1), Premium SSD v2, and Ultra Disk for their production workloads where Standard SSDs are often used for dev/test and smaller departmental workloads. The goal of this article is to review the Azure VM storage options for SQL Server and give DBAs the tools and information needed to weigh the possible trade-offs in Azure feature availability and overall costs.

Premium SSD (Gen 1)

For most DBAs looking to build scalable storage with optimal price-performance, they use Premium SSD (Gen 1) managed disks for their storage solutions. Using first generation Premium SSD disks, DBAs can provision their VMs by striping disks of usually Premium SSD P30 or P40 disks in a storage pool. A storage pool allows DBAs to multiply disks up to the VM’s limits providing scalability and maximizing price-performance, while Premium SSD (Gen 1) disks allow DBAs to take advantage of Azure reservations and read-only host-caching.

While Premium SSD (Gen 1) storage exhibits remarkable flexibility and operates with minimal Azure feature limitations in the cloud, along with offering price-performance options through reservations for select disk types, it cannot scale to meet the demands of the latest Ebdsv5 VM series.

Additional differences between Premium SSD and Premium SSD v2 include:

• Premium SSD v2 offers higher disk capacity, up to 64 TiBs per disk, compared to 32 TiBs for Premium SSD.
• Premium SSD v2 provides more flexibility and control over disk performance, as you can independently adjust the disk size, IOPS, and throughput according to your workload needs, while Premium SSD (Gen 1) has fixed performance tiers based on disk size.
• Premium SSD v2 has lower latency and higher reliability than Premium SSD as it uses newer hardware and an improved storage platform.
• Premium SSD v2 supports higher levels of bursting compared to the previous generation, which allows you to achieve additional performance when needed for short periods of time without additional cost.

Ultra Disk

DBAs can also use Ultra Disk to meet the storage demands of the latest generation of Azure VMs as they offer sub-millisecond latency and better performance than Premium SSD (Gen 1). Like Premium SSD v2, Ultra Disk also allows DBAs to dynamically configure and scale the IOPS, throughput, and capacity of their disks independently without having to restart the VM or change the disk size. This makes Ultra Disk an attractive option for data-intensive workloads such as SQL Server that require consistency and high performance with low latency.

However, Ultra Disk also has drawbacks that make it less suitable for some scenarios. For example, Ultra Disk is only available in a limited number of regions and has stricter requirements for VM sizes, zones, and proximity placement groups. Ultra Disk also does not support disk snapshots, disk encryption, Microsoft Azure Site Recovery, or host caching options. Moreover, Ultra Disk is much more expensive than both Premium SSD and Premium SSD v2, especially for larger disk sizes.

Comparing VM and storage deployments

The established guidance for SQL Server VM deployments was to use Premium SSD (Gen 1) in a storage pool configuration with read-only caching for the data files. For the transaction log, we advised using Ultra Disk in cases where DBAs needed lower latency and could handle the limitations. This recommendation was especially the case for our previous hero VM series such as the Edsv4-series which offered the best performance for OLTP workloads at the time.

However, VMs continued to improve and with Azure Boost and other hardware enhancements, the newest Ebs_v5 and Ebds_v5 VMs have proven to be the optimal VM series for SQL Server workloads. The newest Ebs_v5 and Ebds_v5 VMs power higher levels of IOPS and throughput, and now with NVMe storage interface support they can scale well beyond the capabilities of Premium SSD (Gen 1). The Ebs_v5 and Ebds_v5 VMs series and larger VMs on the horizon will require a higher level of storage performance than Premium SSD (Gen 1) was able to provide. A higher level of storage performance is needed to match the capabilities of our newest generation of Azure SQL VMs and to avoid being throttled/capped when your application is pushing higher levels of IOPS/ throughput.

The next generation of Azure VMs will further push well beyond the largest storage needs of our current generation. For example, the largest machine in the previous generation Edsv4-series is the E80ids_v4 which is an Azure SQL VM of 80 vCores, 504 GiBs memory, 80,000 max uncached IOPS, and 1,500 MBps max uncached disk throughput. For a machine of this size, a Premium SSD storage pool would require 16 x P30 disks to provide the same number of IOPS that a single Premium SSD v2 disk could achieve, but with improved latency and less overall cost.

In comparison, the Ebds_v5 series has a VM size of E112ibds_v5 that supports 400,000 max uncached IOPS and 10,000 MBps max uncached disk throughput (Ultra/Pv2-SSD). Premium SSD (Gen 1) would require 80 disks in order to match the IOPs capabilities of this VM, which would exceed the max data disk limit of the VM.

Premium SSD v2 only needs five disks in a storage pool and additionally allows adjusting the IOPS and throughput based on needs for a better overall total cost of ownership (TCO).

Azure SQL VM best practices

Premium SSD v2 has more flexibility than Premium SSDs (Gen 1) and Ultra Disk. You can choose any supported size for a Premium SSD v2 and change the performance parameters without interruption. Premium SSD v2 does not have host caching, but it has much lower latency, which helps with some of the same issues that host caching helps with. The ability to modify IOPS, throughput, and size on demand means you can reduce the management workload of having to combine disks to meet your needs.

To get started, when provisioning a new SQL Server on Azure VM in the Azure portal, you can choose Premium SSD v2 for eligible VMs:

Premium SSD v2 allows you to change disk size, IOPS, and throughput independently to reach your performance targets, making workloads more cost efficient while also adapting to changing performance needs.

With a capacity of 64 TiBs, 80,000 IOPS, and 1,200 MBps of throughput, most environments can benefit from the performance capabilities of a single Premium SSD v2 disk—but for our largest Azure VMs, Premium SSD v2 disks can be combined into a storage pool to provide the performance required for a single logical drive.

When deploying your SQL Server VM image in the Azure portal, Premium SSD v2 is available for the Ebds_v5 and Ebs_v5 Azure VM series which are optimized for high-performance database workloads.

The following table helps visualize some of the performance gains and cost savings when using Premium SSD v2 with your Ebds_v5 and Ebs_v5 VMs:

Learn more about Premium SSD v2 storage for SQL Server on Azure VMs

In summary, Premium SSD v2 offers enhanced performance, granular scalability, and cost-effectiveness for applications demanding sub-millisecond disk response times. While it provides more capabilities, the actual cost difference between Premium SSD, Premium SSD v2, and Ultra Disks depends on factors such as region, disk size, IOPS, and throughput. You can use the Azure pricing calculator to estimate costs based on your specific needs.

If you are deploying SQL Server VMs using the Azure portal and want to utilize Premium SSD v2, note that it is currently limited to the Ebds_v5 or Ebs_v5 series VMs in this public preview phase.

We’re committed to providing our customers with the best possible experience when running SQL Server on Azure VMs. The addition of Premium SSD v2 storage is another step toward that goal.

Try out Premium SSD v2 storage for SQL Server on Azure VMs today and please share your feedback with us. We look forward to hearing from you as we continue to improve our offerings for SQL Server on Azure VMs.

To get started, check out Use Premium SSDv2 storage with your SQL Server on Azure VMs.

You can also keep an eye on the What’s new page for all the latest and greatest updates to SQL Server on Azure VMs and What’s new for Azure storage.

The post Provision Premium SSD v2 Storage for Microsoft SQL Server on Azure Virtual Machines in the Microsoft Azure portal appeared first on Microsoft SQL Server Blog.

• Change Data Capture (CDC) components by Attunity. This is an integrated extract, transform and load (ETL) feature in Enterprise editions of SQL Server 2016, 2017, 2019 and 2022. As an integrated feature, while such functionality will remain for the lifecycle of the SQL Server product, support for this feature will cease on December 13, 2025.
• Change Data Capture (CDC) service for Oracle by Attunity. This is a stand-alone feature in SQL Server Integration Services (SSIS) feature pack for SQL Server 2016, 2017 and 2019.   The support for CDC service for Oracle by Attunity will cease after December 13, 2025.

Customers using these two features are encouraged to modernize to Data Factory in Microsoft Fabric or Azure Data Factory. Customers can use incremental data loading capability from Azure Data Factory. Azure Data Factory can be used for on-premises data sources with a self-hosted integration runtime and is fully compatible with all impacted versions of SQL Server.

Data Factory in Microsoft Fabric enables you to move and transform data from various sources to various destinations. It’s a managed cloud service designed specifically for handling complex hybrid extract-transform-load (ETL), extract-load-transform (ELT), and data integration projects.

If you need any assistance as you plan your CDC modernization please contact Microsoft Support.

Learn more about Data Factory in Microsoft Fabric and Azure Data Factory:

• Incrementally copy data from a source data store to a destination data store—Azure Data Factory | Microsoft Learn
• Incrementally load data from Data Warehouse to Lakehouse—Microsoft Fabric | Microsoft Learn
• Data Pipeline Pricing and FAQ—Data Factory | Microsoft Azure
• Data Factory in Microsoft Fabric documentation—Microsoft Fabric | Microsoft Learn

Frequently Asked Questions

What’s Data Factory in Microsoft Fabric?

Data Factory in Microsoft Fabric is the next generation of Azure Data Factory which provides cloud-scale data movement and data transformation services that allow you to solve the most complex ETL scenarios. It’s intended to make your experience easy to use, powerful, and truly enterprise-grade.  Data Factory empowers you with a modern data integration experience to ingest, prepare and transform data from a rich set of data sources (for example, databases, data warehouse, Lakehouse, real-time data, and more). Whether you are a citizen or professional developer, you will be able to transform the data with intelligent transformations and leverage a rich set of activities. With Data Factory in Microsoft Fabric, we are bringing fast copy (data movement) capabilities to both dataflows and data pipelines. With Fast Copy, you can move data between your favorite data stores blazing fast. Most importantly, Fast Copy enables you to bring data to your Lakehouse and Data Warehouse in Microsoft Fabric for analytics.

What’s Azure Data Factory?

Azure Data Factory is a cloud-based data integration service that allows you to create data-driven workflows in the cloud for orchestrating and automating data movement and data transformation. It is a fully managed, serverless data integration solution for ingesting, preparing, and transforming all your data at scale. With Azure Data Factory, you can visually integrate data sources using more than 90 built-in, maintenance-free connectors. The service enables you to create and schedule data-driven workflows, called pipelines, that can ingest data from disparate data stores. You can build complex ETL processes that transform data visually with data flows or by using compute services such as Azure HDInsight Hadoop, Azure Databricks, and Azure SQL Database.

You can use Azure Data Factory to access and integrate data from on-premises data sources. One way to do this is by using a self-hosted integration runtime, which acts as a bridge between your on-premises data sources and the cloud-based Azure Data Factory service. This allows you to create data-driven workflows that can ingest data from your on-premises data stores and move it to the cloud for further processing and transformation.

How fast can I ingest data in Fabric data pipelines?

Fabric Data Factory allows you to develop pipelines that maximize data movement throughput for your environment. These pipelines fully utilize the following resources:

• Network bandwidth between the source and destination data stores.
• Source or destination data store input/output operations per second (IOPS) and bandwidth This full utilization means you can estimate the overall throughput by measuring the minimum throughput available with the following resources:
  • Source data store
  • Destination data store
• Network bandwidth in between the source and destination data stores Meanwhile, we continuously work on innovations to boost the best possible throughput you can achieve. Today, the service can move 1 TB TPC-DI dataset (parquet files) into both Fabric Lakehouse table and Data Warehouse within five minutes—moving 1 billion rows under one minute; Please note that this performance is only a reference by running the above testing dataset. The actual throughput still depends on the factors listed previously. In addition, you can always multiply your throughput by running multiple copy activities in parallel. For example, using ForEach loop.

Where can I find more training resources to get started?

• Introduction to Azure Data Factory—Training | Microsoft Learn.
• Get started with Microsoft Fabric—Training | Microsoft Learn.
• Create your first Microsoft Fabric dataflow—Microsoft Fabric | Microsoft Learn.
• Create your first data pipeline to copy data—Microsoft Fabric | Microsoft Learn.

The post SQL Server Integration Services (SSIS) Change Data Capture Attunity feature deprecations appeared first on Microsoft SQL Server Blog.

We are excited to share that there are several Accelerated Database Recovery (ADR) enhancements in SQL Server 2022 that further improve the overall availability and scalability of the database, primarily around persistent version store (PVS) cleanup and management.

Overview of Accelerated Database Recovery (ADR) 

ADR improves database availability, especially in the presence of long running transactions, by redesigning the SQL database engine recovery process. ADR is introduced in SQL Server 2019 (15.x) and improved in SQL Server 2022 (16.x). 

ADR is also available for databases in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse SQL. ADR is enabled by default in SQL Database and SQL Managed Instance and cannot be disabled. 

The primary benefits of Accelerated Database Recovery (ADR) are

Fast and consistent database recovery 

With ADR, long running transactions do not impact the overall recovery time, enabling fast and consistent database recovery irrespective of the number of active transactions in the system or their sizes. 

Instantaneous transaction rollback 

With ADR, transaction rollback is instantaneous, irrespective of the time that the transaction has been active or the number of updates that has performed. 

Aggressive log truncation 

With ADR, the transaction log is aggressively truncated, even in the presence of active long running transactions, which prevents it from growing out of control. 

Note: For more details about ADR, please visit this page: and this video for a high-level overview of ADR and its components.

SQL Server 2022

Learn about the new features on security, platform, management, and more.

Read what's new

New ADR improvements in SQL Server 2022

Multi-threaded version cleanup

In SQL Server 2019 (15.x), the ADR cleanup process is single threaded within a SQL Server instance. Beginning with SQL Server 2022 (16.x), this process uses multi-threaded version cleanup (MTVC), that allows multiple databases under the same SQL Server instance to be cleaned in parallel.

MTVC is enabled by default in SQL Server 2022 and uses one thread per SQL instance. To adjust the number of threads for version cleanup, set ADR Cleaner Thread Count with sp_configure.

USE master;
GO
-- Enable show advanced option to see ADR Cleaner Thread Count
EXEC sp_configure 'show advanced option', '1';
-- List all advanced options
RECONFIGURE;
EXEC sp_configure; 
-- The following example sets the ADR Cleaner Thread Count to 4
EXEC sp_configure 'ADR Cleaner Thread Count', '4';
RECONFIGURE WITH OVERRIDE; 
-- Run RECONFIGURE to verify the number of threads allocated to ADR Version Cleaner.
RECONFIGURE;
EXEC sp_configure;

In the above example, if you configure the ADR Cleaner Count to be four on a sql instance with two databases, the ADR cleaner will allocate only one thread per database, leaving the remaining two threads idle.

Note: The maximum number of ADR Cleaner threads is capped at the number of cores used by the SQL Server instance. For example, if you are running SQL Server on an eight core machine, the maximum number of ADR cleaner threads that the engine can use will be eight, even if the value in the sp_configure is set to a greater value.

User transaction cleanup

This improvement allows user transactions to run cleanup on pages that could not be addressed by the regular cleanup process due to lock conflicts. This helps ensure that the ADR cleanup process works more efficiently.

Reducing memory footprint for PVS page tracker

This improvement tracks persisted version store (PVS) pages at the extent level, in order to reduce the memory footprint needed to maintain versioned pages.

Accelerated Data Recovery cleaner improvements

ADR cleaner has improved version cleanup efficiencies to improve how SQL Server tracks and records aborted versions of a page leading to improvements in memory and capacity.

Transaction-level persisted version store

This improvement allows ADR to clean up versions belonging to committed transactions independent of whether there are aborted transactions in the system. With this improvement PVS pages can be deallocated, even if the cleanup cannot complete a successful sweep to trim the aborted transaction map.

The result of this improvement is reduced PVS growth even if ADR cleanup is slow or fails.

New extended event

A new extended event, tx_mtvc2_sweep_stats, has been added for telemetry on the ADR PVS multi-threaded version cleaner.

Summary

In this blog post, we covered all the exciting ADR improvements that we are including with SQL Server 2022 that further improve the overall availability and scalability of your databases.

Stay tuned as we are currently working on further improvements of the multi-threaded version cleaner that will enable parallelizing version cleanup within databases.

Learn more

For more information, and to get started with SQL Server 2022, check out the following references: 

• Manage accelerated database recovery
• Troubleshoot accelerated database recovery
• SQL Server Accelerated Database Recovery

Read What’s new in SQL Server 2022 for all the new features on security, platform, management, and more. 

The post Accelerated Database Recovery enhancements in SQL Server 2022  appeared first on Microsoft SQL Server Blog.