Improving Azure SQL Database reliability with accelerated database recovery in tempdb
We are pleased to announce that in Azure SQL Database, accelerated database recovery is now enabled in the tempdb database to bring instant transaction rollback and aggressive log truncation for transactions in tempdb. The same improvement is coming to SQL Server and Azure SQL Managed Instance.The Microsoft.Build.Sql project SDK is now generally available
Your database should be part of a wholistic development process, where iterative development tools are coupled with automation for validation and deployment. As previously announced, the Microsoft.Build.Sql project SDK provides a cross-platform framework for your database-as-code such that the database obejcts are ready to be checked into source control and deployed via pipelines like any other modern application component. Today Microsoft.Build.Sql enters general availability as another step in the evolution of SQL database development. Standardized SQL database as code SQL projects are a .NET-based project type for SQL objects, compiling a folder of SQL scripts into a database artifact (.dacpac) for manual or continuous deployments. As a developer working with SQL projects, you’re creating the T-SQL scripts that define the objects in the database. While the development framework around SQL projects presents a clear build and deploy process for development, there’s no wrong way to incorporate SQL projects into your development cycle. The SQL objects in the project can be manually written or generated via automation, including through the graphical schema compare interfaces or the SqlPackage extract command. Whether you’re developing with SQL Server, Azure SQL, or SQL in Fabric, database development standardizes on a shared project format and the ecosystem of tooling around SQL projects. The same SQL projects tools, like the SqlPackage CLI, can be used to either deploy objects to a database or update those object scripts from a database. Free development tools for SQL projects, like the SQL database projects extension for VS Code and SQL Server Data Tools in Visual Studio, bring the whole development team together. The database model validation of a SQL project build provides early verification of the SQL syntax used in the project, before code is checked in or deployed. Code analysis for antipatterns that impact database design and performance can be enabled as part of the project build and extended. This code analysis capability adds in-depth feedback to your team’s continuous integration or pre-commit checks as part of SQL projects. Objects in a SQL project are database objects you can have confidence in before they’re deployed across your environments. Evolving from original SQL projects SQL projects converted to the Microsoft.Build.Sql SDK benefit from support for .NET 8, enabling cross-platform development and automation environments. While the original SQL project file format explicitly lists each SQL file, SDK-style projects are significantly simplified by including any .sql file in the SQL projects folder structure. Database references enable SQL projects to be constructed for applications where a single project isn’t an effective representation, whether the database includes cross-database references or multiple development cycles contribute to the same database. Incorporate additional objects into a SQL project with database references through project reference, .dacpac artifact reference, and new to Microsoft.Build.Sql, package references. Package references for database objects improve the agility and manageability of the release cycle of your database through improved visibility to versioning and simplified management of the referenced artifacts. Converting existing projects The Microsoft.Build.Sql project SDK is a superset of the functionality of the original SQL projects, enabling you to convert your current projects on a timeline that works best for you. The original SQL projects in SQL Server Data Tools (SSDT) continue to be supported through the Visual Studio lifecycle, providing years of support for your existing original projects. Converting an existing SQL project to a Microsoft.Build.Sql project is currently a manual process to add a single line to the project file and remove several groups of lines. The resulting Microsoft.Build.Sql project file is generally easier to understand and iteratively develop, with significantly fewer merge conflicts than the original SQL projects. A command line tool, DacpacVerify, is now available to validate that your project conversion has completed without degrading the output .dacpac file. By creating a .dacpac before and after you upgrade the project file, you can use DacpacVerify to confirm the database model, database options, pre/post-deployment scripts, and SQLCMD variables match. The road ahead With SQL Server 2025 on the horizon, support for the SQL Server 2025 target platform will be introduced in a future Microsoft.Build.Sql release along with additional improvements to the SDK references. Many Microsoft.Build.Sql releases will coincide with releases to the DacFx .NET library and the SqlPackage CLI with preview releases ahead of general availability releases several times a year. Feature requests and bug reports for the DacFx ecosystem, including Microsoft.Build.Sql, is managed through the GitHub repository. With the v1 GA of Microsoft.Build.Sql, we’re also looking ahead to continued iteration in the development tooling. In Visual Studio, the preview of SDK-style SSDT continues with new features introduced in each Visual Studio release. Plans for Visual Studio include project upgrade assistance in addition to the overall replacement of the existing SQL Server Data Tools. In the SQL projects extension for VS Code, we’re both ensuring SQL projects capabilities from Azure Data Studio are introduced as well as increasing the robustness of the VS Code project build experience. The Microsoft.Build.Sql project SDK empowers database development to integrate with the development cycle, whether you're focused on reporting, web development, AI, or anything else. Use Microsoft.Build.Sql projects to branch, build, commit, and ship your database – get started today from an existing database or with a new project. Get to know SQL projects from the documentation and DevOps samples.Introducing Azure Database Fleet Manager
Azure SQL Database is powering some of the largest SaaS solutions on the planet sporting millions of tenants. It does that through a combination of multiple multi-tenancy models that support different requirements and use cases. from few users to millions. In this article, we present a new Azure Database Platform capability (in Private Preview) that helps SaaS ISVs and Solution Providers building multi-tenant data apps by removing complexities in managing fleets of 100ks databases and let them focus on finding their optimal price-performance ratio.Seamless end-to-end SQL Server migration to Azure with Azure Arc
Migrating your on-premises SQL Server to Azure used to require multiple tools and involve several disconnected steps. We have addressed these challenges with an integrated all-in-one migration experience for Arc-enabled SQL Servers. Our new solution eliminates the need for additional software or tools, requiring only Arc-enablement of your SQL Server to complete the entire end-to-end migration journey. We refer to this experience as a journey because the migration process can span several days or even weeks. Our solution manages every step along the way, allowing you the flexibility to pick up where you left off at any time. About the solution The Arc-enabled migration integrates all steps of the migration journey into a single, simple-to-use experience. The solution starts by providing an overview of the benefits of Azure SQL services and modernizing your SQL Server in Azure. It offers continuous automated assessments of your SQL Server databases, providing recommendations for migration to various Azure SQL destinations. Based on these recommendations, an appropriate Azure SQL destination is suggested, tailored to your workload needs. Thereafter, you can choose to provision the recommended Azure SQL service in Azure and start the migration process. Throughout the process, you can monitor the ongoing migrations, evaluate data replicated in Azure, and control the cutover point to Azure according to your business requirements. Figure 1: Integrated Arc enabled end to end migrations experience. Note: Functionality, look and feel of preview product experiences are subject to change. This release is limited to migrating SQL Server databases to Azure SQL Managed Instance only using the link feature as one of the best performing minimum downtime migration solutions. It does not provide other migration options or destinations at this time. Hands-on We love hearing back from our customers! Your participation in the private preview and working with the product group can influence the product roadmap. If you're interested in evaluating your SQL Server workloads for migration to Azure or are ready to migrate, please fill out our application form to request an invitation to the private preview: https://aka.ms/arc-migrations-preview Our product team will select candidates on an ongoing basis based on onboarding capacity. Additional resources Migration overview from SQL Server to Azure SQL Server enabled by Azure ArcAzure Data Studio Retirement
We’re announcing the upcoming retirement of Azure Data Studio (ADS) on February 6, 2025, as we focus on delivering a modern, streamlined SQL development experience. ADS will remain supported until February 28, 2026, giving developers ample time to transition. This decision aligns with our commitment to simplifying SQL development by consolidating efforts on Visual Studio Code (VS Code) with the MSSQL extension, a powerful and versatile tool designed for modern developers. Why Retire Azure Data Studio? Azure Data Studio has been an essential tool for SQL developers, but evolving developer needs and the rise of more versatile platforms like VS Code have made it the right time to transition. Here’s why: Focus on innovation VS Code, widely adopted across the developer community, provides a robust platform for delivering advanced features like cutting-edge schema management and improved query execution. Streamlined tools Consolidating SQL development on VS Code eliminates duplication, reduces engineering maintenance overhead, and accelerates feature delivery, ensuring developers have access to the latest innovations. Why Transition to Visual Studio Code? VS Code is the #1 developer tool, trusted by millions worldwide. It is a modern, versatile platform that meets the evolving demands of SQL and application developers. By transitioning, you gain access to cutting-edge tools, seamless workflows, and an expansive ecosystem designed to enhance productivity and innovation. We’re committed to meeting developers where they are, providing a modern SQL development experience within VS Code. Here’s how: Modern development environment VS Code is a lightweight, extensible, and community-supported code editor trusted by millions of developers. It provides: Regular updates. An active extension marketplace. A seamless cross-platform experience for Windows, macOS, and Linux. Comprehensive SQL features With the MSSQL extension in VS Code, you can: Execute queries faster with filtering, sorting, and export options for JSON, Excel, and CSV. Manage schemas visually with Table Designer, Object Explorer, and support for keys, indexes, and constraints. Connect to SQL Server, Azure SQL (all offerings), and SQL database in Fabric using an improved Connection Dialog. Streamline development with scripting, object modifications, and a unified SQL experience. Optimize performance with an enhanced Query Results Pane and execution plans. Integrate with DevOps and CI/CD pipelines using SQL Database Projects. Stay tuned for upcoming features—we’re continuously building new experiences based on feedback from the community. Make sure to follow the MSSQL repository on GitHub to stay updated and contribute to the project! Streamlined workflow VS Code supports cloud-native development, real-time collaboration, and thousands of extensions to enhance your workflows. Transitioning to Visual Studio Code: What You Need to Know We understand that transitioning tools can raise concerns, but moving from Azure Data Studio (ADS) to Visual Studio Code (VS Code) with the MSSQL extension is designed to be straightforward and hassle-free. Here’s why you can feel confident about this transition: No Loss of Functionality If you use ADS to connect to Azure SQL databases, SQL Server, or SQL database in Fabric, you’ll find that the MSSQL extension supports these scenarios seamlessly. Your database projects, queries, and scripts created in ADS are fully compatible with VS Code and can be opened without additional migration steps. Familiar features, enhanced experience VS Code provides advanced tools like improved query execution, modern schema management, and CI/CD integration. Additionally, alternative tools and extensions are available to replace ADS capabilities like SQL Server Agent and Schema Compare. Cross-Platform and extensible Like ADS, VS Code runs on Windows, macOS, and Linux, ensuring a consistent experience across operating systems. Its extensibility allows you to adapt it to your workflow with thousands of extensions. If you have further questions or need detailed guidance, visit the ADS Retirement page. The page includes step-by-step instructions, recommended alternatives, and additional resources. Continued Support With the Azure Data Studio retirement, we’re committed to supporting you during this transition: Documentation: Find detailed guides, tutorials, and FAQs on the ADS Retirement page. Community Support: Engage with the active Visual Studio Code community for tips and solutions. You can also explore forums like Stack Overflow. GitHub Issues: If you encounter any issues, submit a request or report bugs on the MSSQL extension’s GitHub repository. Microsoft Support: For critical issues, reach out to Microsoft Support directly through your account. Transitioning to VS Code opens the door to a more modern and versatile SQL development experience. We encourage you to explore the new possibilities and start your journey today! Conclusion Azure Data Studio has served the SQL community well,but the Azure Data Studio retirement marks an opportunity to embrace the modern capabilities of Visual Studio Code. Transitioning now ensures you’re equipped with cutting-edge tools and a future-ready platform to enhance your SQL development experience. For a detailed guide on ADS retirement , visit aka.ms/ads-retirement. To get started with the MSSQL extension, check out the official documentation. We’re excited to see what you build with VS Code!
Test failover for Azure SQL database
Hi I want to use a failover group to protect an Azure SQL server, for DR purposes, but I'm unsure how to perform a test failover. Can I use a recovery plan perform a test failover, keeping the primary node up and running for production, whilst the secondary is available for DR testing? Cheers AlexManaged Instance link with SQL Server 2017 is now GA
We are announcing the general availability of Managed Instance link feature with SQL Server 2017, which enables near-real time data replication from SQL Server to Azure SQL Managed Instance. Link feature is now supported in all SQL Server versions in the mainstream and extended support, from SQL Server 2016 to SQL Server 2022. To use Managed Instance link feature SQL Server 2017, customers need to install “Azure Connect Pack for SQL Server 2017”. We recommend the latest version of SQL Server Management Studio to create and manage links with SQL Server 2017. To learn more about SQL Server – SQL Managed Instance hybrid capabilities which link feature unlocks, see the feature documentation page.When to Choose Zone Redundancy in Azure SQL Managed Instance
High availability is a critical requirement for modern cloud applications. Azure SQL Managed Instance (SQL MI) offers Zone Redundancy (ZR) for additional protection against a certain class of failures such as datacenter and Availability Zone (AZ) level outages. While these outages are very rare, they can have a significant impact on your business. However, ZR is not always the best option depending on your specific business needs and constraints. This article explores when to choose ZR, compares it to Failover Groups (FOG), discusses key considerations for ZR, and outlines strategies to address its challenges. Important concepts (Terminology) Availability As a service provider, it is our core responsibility to ensure the availability of our service. Azure SQL MI offer availability as a built-in feature, backed by a robust Service Level Agreements (SLA) of 99.99%. Automated backups provide protection from data corruption or accidental deletion. High Availability (HA) In the PaaS database market, the industry standard definition for High Availability within a region has evolved to enabling Zone Redundancy for the database. Availability Zone (AZ) AZs are separate groups of datacenters within a region. Each AZ has independent power, cooling, and networking infrastructure, so that if one zone experiences an outage, then regional services, capacity, and high availability are supported by the remaining zones. Zone Redundancy (ZR) ZR (also known as Multi-AZ) is an HA feature in SQL MI that provides resilience against failures in a specific availability zone within an Azure region. Disaster Recovery (DR) To achieve redundancy across regions, customers enable DR capabilities to quickly recover the instance from a catastrophic regional failure. Options for disaster recovery with Azure SQL Managed Instance are Failover groups and Geo-restore. Failover Group (FOG) A failover group allows all user databases within a managed instance to fail over as a unit to another Azure region in case the primary managed instance becomes unavailable due to a primary region outage. Failover groups are designed to simplify deployment and management of geo-replicated databases at scale. Recovery Time Objective (RTO) The time required for an application to fully recover after an availability incident is known as the RTO. Recovery Point Objective (RPO) RPO is defined as the maximum amount of data – as measured by time – that can be lost after a recovery from an availability incident before data loss will exceed what is acceptable to an organization. Locally Redundant (Default) Configuration If a managed instance is not configured with Zone Redundancy (ZR), it will be deployed with a locally redundant configuration. This configuration provides built-in availability with a 99.99% uptime Service Level Agreement (SLA). Locally redundant availability ensures that your compute nodes and data are stored within a single datacenter in the region, providing protection against localized failures such as minor network disruptions or power outages. However, in the event of a large-scale disaster affecting the whole region, all replicas of a storage account or data on the compute nodes may be lost or rendered unrecoverable. When to Choose a Zone Redundant Configuration ZR configuration enhances resilience by distributing replicas of your SQL MI across multiple Availability Zones the same region. This setup provides protection against datacenter-level failures, ensuring minimal downtime and no data loss. ZR is particularly beneficial when: Applications require high availability with low latency, as all replicas are maintained within the same Azure region. Protection is needed against failures impacting individual datacenters without extending to larger geographic disruptions. Industry or regulatory compliance mandates the use of a ZR configuration, i.e. for applications with stringent SLA that require a 99.995% uptime guarantee. Zone Redundancy vs. Failover Groups: Which One to Choose? Both ZR and FOG provide high availability but serve different purposes. The key differences are presented in the table below. Configuration Locally Redundant Zone Redundant FOG Scope Protection against user and application errors, accidental deletion, and prolonged outages. Additional protection against failures in a specific availability zone within an Azure region. Additional protection against the failure of the entire Azure region. Latency Low Latency Mid latency since replicas are in the same region Higher latency as the secondary replica is in another region Additional cost None (just backup storage) +60% compute +100% storage +0% license +100% compute +100% storage +100% license (0% if passive) Replication Sync Async RPO Non-zero (minutes) 0 (No data loss) Non-zero (seconds) RTO Hours Near-instant Longer (varies by setup) Failover Manual Automatic failover with minimal downtime Manual or semi-automatic failover with possible data loss. Use Case Default Business continuity within a region Disaster recovery across regions ZR and FOG can go together! ZR and FOG are not mutually exclusive and can be combined based on business needs. In case they are combined, customers are not required to make both ends of the Geo-DR link be ZR - it’s their choice. For example, they can use ZR in a zonal region as a primary and also replicate using Geo-DR to a non-zonal region. Other considerations (and How to Mitigate Them or Work around) While ZR provides strong high availability, it comes with some trade-offs: Higher Pricing: ZR configurations require multiple replicas across different Availability Zones, leading to increased costs. Mitigation: Purchasing Reserved Instances can significantly reduce the cost of long-term ZR deployments. Alternatively, consider the non-ZR instance as Azure SQL MI offers excellent protection out of the box. Performance Penalty: ZR configurations introduce latency overhead due to data synchronization across zones. Because zone-redundant instances have replicas in different datacenters with some distance between them, the increased network latency might increase the transaction commit time and thus impact the performance of some OLTP workloads. No mitigation. Single AZ regions: Some regions consist of only one Availability Zone and cannot support ZR configurations. Workaround: Create an instance in another region that supports ZR. Capacity Constraints in Azure Regions: ZR requires additional VMs and storage, leading to higher capacity usage within a region, while every region has limited resources. Modifying your zone redundant instance may be temporarily disabled due to insufficient capacity of the hardware generation in your region. Workaround: To proceed with modifying your zone redundant instance, either select a different hardware generation or disable zone redundancy for the instance. Call to Action It’s easy to enable ZR for your new and existing instances - all it takes is a couple of clicks. The operation to change the ZR configuration is fully online with a failover at the end (see Dynamic Scaling ). You can always return to the single-zone configuration by disabling the zone-redundancy setting. This process is an online operation similar to the regular service tier objective upgrade. At the end of the process, the instance is migrated from a zone-redundant ring to a single-zone ring or vice versa. To get started with zone redundancy for your SQL managed instance, review Configure zone redundancy. Summary In this article, we provided guidance for businesses to make informed decisions about using Zone Redundancy (ZR) in their Azure SQL Managed Instance deployments. We outlined the benefits of ZR, such as protection against datacenter and Availability Zone failures, while comparing it with Failover Groups to help businesses choose the best option for their needs. We also discussed the downsides of ZR, including increased costs and potential performance trade-offs, and suggested strategies to address the challenges. Learn more Enable zone redundancy for Azure SQL Managed Instance. Learn How to initiate a manual failover on SQL Managed Instance For more options for high availability and disaster recovery, see Business Continuity
