Introducing "Backups on Secondary" for SQL Server Always On Availability Groups with SQL Server 2025
We’re excited to announce a major enhancement for SQL Server Always On Availability Groups Backups on Secondary, in SQL Server 2025. Until SQL Server 2022, you could only perform COPY_ONLY full backups and transaction log backups on a secondary replica of an Always On Availability Group. This enhancement in SQL Server 2025 allows you to offload all types of backups—full, differential, and transaction logs - to a secondary replica, significantly improving performance, resource utilization, and operational flexibility. What Is "Backups on Secondary"? Traditionally, backups in an Always On Availability Group were typically performed on the primary replica, which could lead to resource contention and performance degradation for mission-critical workloads. With this update in SQL Server 2025 to the Backups on Secondary feature, you can now configure your environment to perform all backup operations on a designated secondary replica. This includes: Full backups Differential backups Transaction log backups Key Benefits 🔄 Reduced Load on Primary Replica By offloading backup operations to a secondary replica, you free up CPU, memory, and I/O resources on the primary replica, ensuring that your production workloads run more smoothly and efficiently. 🧩 Flexible Backup Strategies You can now design more flexible and resilient backup strategies by leveraging secondary replicas in different geographic locations or data centers. 🛡️ Enhanced High Availability and Disaster Recovery In the event of a failover, backup operations can continue seamlessly on another secondary replica, ensuring continuous data protection. How It Works The feature is easy to configure using T-SQL or SQL Server Management Studio (SSMS). There are two steps to make backups run on secondary: Configure backups to run on secondary replica via the AUTOMATED_BACKUP_PREFERENCE and BACKUP_PRIORITY parameters – refer to Configure backups on secondary replicas of an Always On availability group for prerequisites and detailed steps. Run the T-SQL BACKUP command using one of the tools such as SSMS or SQL Agent jobs or Maintenance plans. These are the same steps that have been currently in place for SQL Server 2022 and for prior versions. In the prior versions this configuration only allowed COPY_ONLY and transaction log backups. Now, starting with SQL Server 2025, with the same configuration in place, you can perform FULL, DIFFERENTIAL and T-LOG backups on a secondary replica and truly offload the backup overhead on to the secondary replica. You can also query system views like sys.dm_hadr_backup_is_preferred_replica to programmatically determine the preferred replica for backups. Final Thoughts The Backups on Secondary feature is a game-changer for organizations looking to optimize their SQL Server environments for performance, availability, and cost. Whether you're managing a large-scale enterprise deployment or a hybrid cloud setup, this feature gives you the flexibility and control you need to build a more efficient and resilient data platform. This feature is available in SQL Server 2025 CTP 2.0. You can download it and try it out today.Azure 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!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.Running SQL Server 2025 on Linux Containers in WSL
Hey there! Ready to dive into the awesome world of SQL Server 2025? This latest release is packed with cool new features and enhancements that you won't want to miss. Curious to know more? Check out the SQL Server on Linux release-notes for all the exciting details! In this blog post, I will show you how quick and easy it is to get SQL Server 2025 up and running in a container on Windows Subsystem for Linux (WSL). We’ll create a test database, set up some tables, and even play around with a few REGEX functions using the latest SQL Server Management Studio! Running SQL Server in containers on WSL is super flexible for development and testing. It’s perfect for developers who want to experiment with Linux-native features on a Windows machine without needing a separate Linux system. Let’s get started! For detailed instructions on setting up WSL, check out the Install WSL guide. You can also deploy SQL Server as a systemd service by following the SQL Server Installation on WSL guide. First things first, install WSL and a SQL Server 2025 compatible distro. For this demo, I went with Ubuntu 22.04. I’ve already got Docker Desktop installed on my machine to manage the containers. With this setup, you can install any supported SQL Server on the WSL-compatible distro for testing or demo purposes. It’s super handy for any development work too. wsl --install wsl -l -o wsl --install Ubuntu-22.04 Heads up! The above step might need a system restart and will automatically download Ubuntu if it’s not already on your system. Next, let’s deploy SQL Server 2025 on Ubuntu 22.04. I ran a simple docker run command, and ta-da! I can connect to it using SQL Server Management Studio, as shown in below: docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=DontUseThisSmartPwd:)" \ -e "MSSQL_PID=Developer" -e "MSSQL_AGENT_ENABLED=true" \ -p 14333:1433 --name sqlcontainerwsl --hostname sqlcontainerwsl \ -d mcr.microsoft.com/mssql/server:2025-latest I tried out the new SQL Server Management Studio and had some fun testing few REGEX functions: /*Here are some SQL scripts to create some tables with sample records for the demo. These tables contain sample data for testing the regular expression functions.*/ --- 1. **employees Table: For REGEXP_LIKE Example** -- Create employees table with some records DROP TABLE IF EXISTS employees CREATE TABLE employees ( ID INT IDENTITY(101,1), [Name] VARCHAR(150), Email VARCHAR(320), Phone_Number NVARCHAR(20) ); INSERT INTO employees ([Name], Email, Phone_Number) VALUES ('John Doe', 'john@contoso.com', '123-4567890'), ('Alice Smith', 'alice@fabrikam@com', '234-567-81'), ('Bob Johnson', 'bob.fabrikam.net','345-678-9012'), ('Eve Jones', 'eve@contoso.com', '456-789-0123'), ('Charlie Brown', 'charlie@contoso.co.in', '567-890-1234'); GO -- 2. **customer_reviews Table: For REGEXP_COUNT Example** DROP TABLE IF EXISTS customer_reviews CREATE TABLE customer_reviews ( review_id INT PRIMARY KEY, review_text VARCHAR(1000) ); INSERT INTO customer_reviews (review_id, review_text) VALUES (1, 'This product is excellent! I really like the build quality and design.'), (2, 'Good value for money, but the software could use improvements.'), (3, 'Poor battery life, bad camera performance, and poor build quality.'), (4, 'Excellent service from the support team, highly recommended!'), (5, 'The product is good, but delivery was delayed. Overall, decent experience.'); GO -- 3. **process_logs Table: For REGEXP_INSTR Example** DROP TABLE IF EXISTS process_logs CREATE TABLE process_logs ( log_id INT PRIMARY KEY, log_entry VARCHAR(1000) ); INSERT INTO process_logs (log_id, log_entry) VALUES (1, 'Start process... Step 1: Initialize. Step 2: Load data. Step 3: Complete.'), (2, 'Begin... Step 1: Validate input. Step 2: Process data. Step 3: Success.'), (3, 'Step 1: Check configuration. Step 2: Apply settings. Step 3: Restart.'), (4, 'Step 1: Authenticate. Step 2: Transfer data. Step 3: Log complete.'), (5, 'Step 1: Initiate system. Step 2: Check logs. Step 3: Shutdown.'); GO -- 4. **transactions Table: For REGEXP_REPLACE Example** DROP TABLE IF EXISTS transactions CREATE TABLE transactions ( transaction_id INT PRIMARY KEY, credit_card_number VARCHAR(19) ); INSERT INTO transactions (transaction_id, credit_card_number) VALUES (1, '1234-5678-9101-1121'), (2, '4321-8765-1098-7654'), (3, '5678-1234-9876-5432'), (4, '9876-4321-6543-2109'), (5, '1111-2222-3333-4444'); GO -- 5. **server_logs Table: For REGEXP_SUBSTR and Data Cleanup Example** DROP TABLE IF EXISTS server_logs CREATE TABLE server_logs ( log_id INT PRIMARY KEY, log_entry VARCHAR(2000) ); INSERT INTO server_logs (log_id, log_entry) VALUES (1, '2023-08-15 ERROR: Connection timeout from 192.168.1.1 user admin@example.com'), (2, '2023-08-16 INFO: User login successful from 10.0.0.1 user user1@company.com'), (3, '2023-08-17 ERROR: Disk space low on 172.16.0.5 user support@domain.com'), (4, '2023-08-18 WARNING: High memory usage on 192.168.2.2 user hr@office.com'), (5, '2023-08-19 ERROR: CPU overload on 10.1.1.1 user root@system.com'); GO -- 6. **personal_data Table: For REGEXP_REPLACE (Masking Sensitive Data) Example** DROP TABLE IF EXISTS personal_data CREATE TABLE personal_data ( person_id INT PRIMARY KEY, sensitive_info VARCHAR(100) ); INSERT INTO personal_data (person_id, sensitive_info) VALUES (1, 'John Doe - SSN: 123-45-6789'), (2, 'Jane Smith - SSN: 987-65-4321'), (3, 'Alice Johnson - Credit Card: 4321-5678-1234-8765'), (4, 'Bob Brown - Credit Card: 1111-2222-3333-4444'), (5, 'Eve White - SSN: 111-22-3333'); GO /*These tables contain realistic sample data for testing the regular expression queries. You can modify or extend the records as needed for additional complexity. */ /* Let's see the use cases for `REGEXP_LIKE`, `REGEXP_COUNT`, `REGEXP_INSTR`, `REGEXP_REPLACE`, and `REGEXP_SUBSTR` in SQL. These examples are designed to handle real-world scenarios with multiple conditions, nested regex functions, or advanced string manipulations.*/ /* 1. **REGEXP_LIKE to filter based on Complex Pattern** Scenario #1: find all the employees whose email addresses are valid and end with .com */ SELECT [Name], Email FROM Employees WHERE REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.com$'); GO /* Scenario #2: Recreate employees table with CHECK constraints for 'Email' and 'Phone_Number' columns */ DROP TABLE IF EXISTS Employees CREATE TABLE Employees ( ID INT IDENTITY(101,1), [Name] VARCHAR(150), Email VARCHAR(320) CHECK (REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')), Phone_Number NVARCHAR(20) CHECK (REGEXP_LIKE (Phone_Number, '^(\d{3})-(\d{3})-(\d{4})$')) ); INSERT INTO employees ([Name], Email, Phone_Number) VALUES ('John Doe', 'john@contoso.com', '123-456-7890'), ('Alice Smith', 'alice@fabrikam.com', '234-567-8100'), ('Bob Johnson', 'bob@fabrikam.net','345-678-9012'), ('Eve Jones', 'eve@contoso.com', '456-789-0123'), ('Charlie Brown', 'charlie@contoso.co.in', '567-890-1234'); GO -- CHECK Constraints - Ensure that the data fulfills the specified criteria. -- FAILURE - Try inserting a row with INVALID values: INSERT INTO Employees ([Name], Email, Phone_Number) VALUES ('Demo Data', 'demo@contoso.com', '123-456-7890'); GO SELECT * FROM Employees; --- /* 2. **`REGEXP_COUNT` to Analyze Word Frequency in Text** Scenario: Counting Specific Words in Large Text Data Suppose you have a `customer_reviews` table, and you want to count the number of occurrences of specific words like "excellent", "good", "bad", or "poor" to evaluate customer sentiment. */ SELECT review_id, REGEXP_COUNT(review_text, '\b(excellent|good|bad|poor)\b', 1, 'i') AS sentiment_word_count, review_text FROM customer_reviews; GO --- /* 3. **`REGEXP_INSTR to Detect Multiple Patterns in Sequence** Scenario: Identify the Position of Multiple Patterns in Sequence Imagine you have log data where each entry contains a sequence of steps, and you need to find the position of a specific pattern like "Step 1", "Step 2", and "Step 3", ensuring they occur in sequence. */ SELECT log_id, REGEXP_INSTR(log_entry, 'Step\s1.*Step\s2.*Step\s3', 1, 1, 0, 'i') AS steps_position FROM process_logs WHERE REGEXP_LIKE(log_entry, 'Step\s1.*Step\s2.*Step\s3', 'i'); GO --- /* 4. **`REGEXP_REPLACE` for replacing string based on the pattern match** Scenario: Redacting Sensitive Information with Variable Lengths You need to redact sensitive data from a table that contains personal information like Social Security Numbers (SSNs) and credit card numbers. The challenge is that the data might be in different formats (e.g., `###-##-####` for SSNs and `####-####-####-####` for credit cards). */ SELECT sensitive_info, REGEXP_REPLACE(sensitive_info, '(\d{3}-\d{2}-\d{4}|\d{4}-\d{4}-\d{4}-\d{4})', '***-**-****') AS redacted_info FROM personal_data; GO --- /* 5. **REGEXP_SUBSTR to Extract Nested Information** Scenario: Extract Specific Parts of a Complex String Format */ SELECT [Name], Email, REGEXP_SUBSTR(email, '@(.+)$', 1, 1,'c',1) AS Domain FROM employees; GO --- /* 6. **Combining Multiple REGEXP Functions for Data Transformation** Scenario: Log Cleanup and Transformation You have raw server logs that contain noisy data. Your goal is to: 1. Extract the date. 2. Count how many times the word "ERROR" appears. 3. Replace any email addresses with `[REDACTED]`. */ SELECT log_entry, REGEXP_SUBSTR(log_entry, '\d{4}-\d{2}-\d{2}', 1, 1) AS log_date, REGEXP_COUNT(log_entry, 'ERROR', 1, 'i') AS error_count, REGEXP_REPLACE(log_entry, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}', '[REDACTED]') AS cleaned_log FROM server_logs; GO --- --*TVFs* /* 7. **REGEXP_MATCHES - Find all the match in the string and return in tablular format***/ SELECT * FROM REGEXP_MATCHES ('Learning #AzureSQL #AzureSQLDB', '#([A-Za-z0-9_]+)'); /* 8. **REGEXP_SPLIT_TO_TABLE - Split string based on regexp pattern**/ SELECT * FROM REGEXP_SPLIT_TO_TABLE ('192.168.0.1|80|200|Success|192.168.0.2|443|404|Not Found', '\|') There are lot of exciting features in SQL Server 2025! The Linux version includes all engine features from the SQL Server 2025 on Windows. Check out the What’s New for SQL Server 2025 Preview on Linux for all the details. Here are a few highlights: Set custom password policy for SQL logins in SQL Server on Linux Enable and run tempdb on tmpfs for SQL Server 2025 Preview on Linux Connect to ODBC data sources with PolyBase on SQL Server on Linux These features make SQL Server on Linux super versatile and powerful, giving you the same robust experience as on Windows. Conclusion SQL Server 2025 on Linux is a game-changer, offering flexibility and power for developers and IT pros alike. Whether you're developing, testing, or deploying in production, this setup has got you covered. 🔗 References aka.ms/sqlserver2025 https://aka.ms/Build/sql2025blog. https://aka.ms/IntroMirroringSQL http://aka.ms/optimized-hp-blog https://aka.ms/tempdb-rg-blog https://aka.ms/sqlserver2025-ctp-diskann SQL Server on Linux FAQ
Enhanced Server Audit for Azure SQL Database: Greater Performance, Availability and Reliability
We are excited to announce a significant update to the server audit feature for Azure SQL Database. We have re-architected major portions of SQL Auditing resulting in increased availability and reliability of server audits. As an added benefit, we have achieved closer feature alignment with SQL Server and Azure SQL Managed Instance. Database auditing remains unchanged. In the remainder of this blog article, we cover Functional changes Changes Affecting customers Sample queries Call for action Implementation and Notification Time-based Filtering Functional Changes In the current design when server audit is enabled, it triggers a database level audit and executes one audit session for each database. With the new architecture, enabling server audit will create one extended event session at the server level that captures audit events for all databases. This optimizes memory and CPU and is consistent with how auditing works in SQL Server and Azure SQL Managed Instance. Changes Affecting Customers Folder Structure change for storage account Folder structure change for Read-Only replicas Permissions required to view Audit logs One of the primary changes involves the folder structure for audit logs stored in storage account containers. Previously, server audit logs were written to separate folders, one for each database, with the database name serving as the folder name. With the new update, all server audit logs will be consolidated into a single folder which is ‘Master’ folder. This behavior is the same as Azure SQL Managed Instance and SQL Server For Read-Only database replicas, which previously had their logs stored in a read-only folder, those logs will now also be written into the Master folder. You can retrieve these logs by filtering on the new column ‘is_secondary_replica_true’. Please note that the audit logs generated after deployment will adhere to the new folder structure, while the existing audit logs will stay in their current folders until their retention periods expire. Sample Queries To help you adopt these changes in your workflows, here are some sample queries: Current New To Query audit logs for a specific database called "test" SELECT * FROM sys.fn_get_audit_file ('https://testaudit.blob.core.windows.net/sqldbauditlogs/auditpoc/test/ SqlDbAuditing_ServerAudit_NoRetention/2023-01-29/07_06_40_590_0.xel', default, default) SELECT * FROM sys.fn_get_audit_file ('https://testaudit.blob.core.windows.net/sqldbauditlogs/auditpoc/master/SqlDbAuditing_ServerAudit_NoRetention/2023-01-29/07_06_40_590_0.xel', default, default) WHERE database_name = 'test'; To query audit logs for test database from read only replica SELECT * FROM sys.fn_get_audit_file ('https://testaudit.blob.core.windows.net/sqldbauditlogs/auditpoc/test/SqlDbAuditing_ServerAudit_NoRetention/2023-01-29/RO/07_06_40_590_0.xel', default, default) SELECT * FROM sys.fn_get_audit_file ('https://testaudit.blob.core.windows.net/sqldbauditlogs/auditpoc/master/SqlDbAuditing_ServerAudit_NoRetention/2023-01-29/07_06_40_590_0.xel', default, default) WHERE is_secondary_replica_true = 'true'; Permissions Control database on user database Server admin privileges Implementation and Notifications We are rolling out this change region-wise. Subscription owners will receive notifications with the subject “Update your scripts to point to a new folder for server level audit logs” for each region as the update is implemented. It is important to update any scripts that refer to the folder structure to retrieve audit logs based on the database name for the specific region. Note that this change applies only to server-level auditing; database auditing remains unchanged. Call for Action These actions apply only to customers who are using storage account targets. No action is needed for customers using Log Analytics or Event hubs. Folder references: Change the reference for audit logs from the database name folder to the Master folder and use specific filters to retrieve logs for a required database. Read -Only Database Replicas: Update references for audit logs from the Read-Only replica folder to the Master folder and filter using the new parameter as shown in the examples. Permissions: Ensure you have the necessary control server permissions to review the audit logs for each database using fn_get_audit_file. Manual Queries This update also applies to manual queries where you use fn_get_audit_file to retrieve audit logs from the storage account Time-based filtering To enhance your ability to query audit logs using filters, consider using efficient time-based filtering with the fn_get_audit_file_v2 function. This function allows you to retrieve audit log data with improved filtering capabilities. For more details, refer to the official documentation here.ZSTD compression in SQL Server 2025
Introducing ZSTD Compression in SQL Server 2025: A Leap Forward in Data Efficiency With the release of SQL Server 2025 Preview, we are introducing a powerful new feature that promises to significantly enhance data storage efficiency: support for the Zstandard (ZSTD) compression algorithm. This open-source compression technology, originally developed by Yann Collet at Facebook, is now being integrated into SQL Server as a modern alternative to the long-standing MS_XPRESS compression algorithm. What is ZSTD? ZSTD is a fast, lossless compression algorithm that offers a compelling balance between compression ratio and speed. It has gained widespread adoption across the tech industry due to its: High compression ratios — often outperforming legacy algorithms like MS_XPRESS. Fast decompression speeds — ideal for read-heavy workloads. Scalability — tunable compression levels to balance CPU usage and storage savings. Compression in SQL Server: Then and Now SQL Server has long supported data compression to reduce storage costs and improve I/O performance. The MS_XPRESS algorithm, used in row and page compression, has served well for many years. However, as data volumes grow and performance demands increase, a more efficient solution is needed. Enter ZSTD. Key Benefits of ZSTD in SQL Server 2025 Improved Compression Ratios In internal benchmarks, ZSTD has shown up to 30–50% better compression compared to MS_XPRESS, depending on the data type and structure. This translates to significant storage savings, especially for large data warehouses and archival systems. Faster Decompression ZSTD’s optimized decompression path ensures that queries on compressed data remain fast, even as compression ratios improve. Flexible Compression Levels SQL Server 2025 allows administrators to choose from multiple ZSTD compression levels, enabling fine-tuned control over the trade-off between CPU usage and compression efficiency. Seamless Integration ZSTD is integrated into the existing compression framework, meaning you can apply it using familiar T-SQL syntax and management tools. How to Use ZSTD in SQL Server 2025 Using ZSTD is as simple as specifying it in your compression options. Examples: Default compression of MS_XPRESS: Specify ZSTD during BACKUP: BACKUP DATABASE <database_name> TO {DISK|TAPE|URL} WITH COMPRESSION (ALGORITHM = ZSTD) Specify ZSTD and compression level: BACKUP DATABASE <database_name> TO {DISK|TAPE|URL} WITH COMPRESSION (ALGORITHM = ZSTD, LEVEL=HIGH) The default compression level is LOW. Allowed values are LOW, MEDIUM and HIGH. As you would expect, specifying HIGH compression level takes longer compared to LOW compression level. How to verify which compression algorithm was used: You can use the RESTORE HEADERONLY command to verify which compression algorithm was used during the backup. The CompressionAlgorithm column will display the name of the algorithm. Comparing compression levels: A quick look at the file sizes shows the ZSTD does show some savings. Additionally, a higher level of compression is achieved when LEVEL=HIGH is specified. Final Thoughts While the actual savings from the new compression algorithm varies based on the nature of data and amount of resources available, in general we have seen significant improvements in compression. The integration of ZSTD into SQL Server 2025 marks a significant step forward in data compression technology. Whether you're managing terabytes of transactional data or optimizing your data lake for analytics, ZSTD offers a modern, efficient, and high-performance solution. Looking forward to hearing your feedback. SQL Server 2025 brings a ton of new features and capabilities. Refer to Whats new in SQL 2025 for all the excitement. Cheers DinakarFree SQL MI Public Preview Refresh
[Edit May 2025] The free SQL Managed Instance is now Generally Available. Azure SQL Managed Instance is a scalable cloud database service that's always running on the latest stable version of the Microsoft SQL Server database engine and a patched OS with 99.99% built-in high availability, offering close to 100% feature compatibility with SQL Server. PaaS capabilities built into Azure SQL Managed enable you to focus on domain-specific database administration and optimization activities that are critical for your business while Microsoft handles backups, as well as patching and updating of the SQL and operating system code, which removes the burden on managing the underlying infrastructure. Learn more about Managed Instance here. In addition to expanding eligible subscription types, you can also upgrade your managed instance tier from free to production ready with full SLA in 2 clicks. Additionally, you can now see exactly how many free vCore hours you have left. What's new Previously, Free SQL Managed Instance was available to the following subscriptions: Pay-as-you-go (0003P) Azure in CSP (0145P) Today, we’re adding the following subscriptions to the list: Azure Plan (0017G) Enterprise Agreement Support Microsoft Azure EA Sponsorship (0136P) Visual Studio Professional subscribers (0059P) Visual Studio Test Professional subscribers (0059P) Visual Studio Enterpise subscribers (0063P) Pay-As-You-Go Dev/Test (0023P) Enterprise Dev/Test (0148P) Azure in Open Licensing (0111P) To upgrade your SQL Managed Instance to production ready instance with SLA, go to Compute + Storage blade and select Paid offer and click apply: You can see the full cost of your instance in the cost card before you upgrade it so you are informed as to what you will be paying monthly. If you are not ready to upgrade your experience yet, you can track the number of remaining Free vCore hours in the essentials blade: What Do You Get Free SQL Managed Instance details: General Purpose instance with up to 100 databases 720 vCore hours of compute every month (does not accumulate) 64 GB of storage One instance per Azure subscription The instance is automatically stopped when you reach the monthly vCore limit; managed instance starts automatically when credits are available again, or you can manually stop it to preserve free monthly vCore hours How to Get Started Getting started is a breeze. Follow these simple steps: Visit Azure portal: Head to the provisioning page for Azure SQL Managed Instance Apply Free Offer: Look for the "Want to try Azure SQL Managed Instance for free?" banner and click on the "Apply offer" button Select Resource Group: Choose an existing resource group or create a new one Configure Instance: Set up your instance details, including region, authentication method, networking, security, and additional settings Review and Create: Review your settings, then click "Create" to set up your free instance And just like that, you're ready to explore the capabilities of Azure SQL Managed Instance! Connect Seamlessly to Your Instance Connecting to your instance is a breeze, whether you have a public endpoint enabled or disabled: Public Endpoint Enabled: Copy the Host value from the Azure portal, paste it into your preferred data tool, and connect. Public Endpoint Disabled: Choose to create an Azure VM within the same virtual network or configure point-to-site for connection. Did you know? Replicate your on-prem databases to Free SQL Managed Instance via Managed Instance link Your newly created Free SQL Managed Instance starts empty and you might want to add some data to it to proceed with your Managed Instance evaluation. One way to do this is to use Managed Instance link to replicate databases from your SQL Server on-prem or Azure Virtual Machine. Maximize savings with Start/Stop schedule To conserve credits, your free instance is scheduled to be on from 9 am to 5 pm Monday through Friday in your configured time zone. Modify the schedule to fit your business requirements. For more information check out the documentation page.Stream data changes from Azure SQL Managed Instance and Azure SQL Database – private preview of CES
[Edit May 19th, 2025: CES is available in public preview of SQL Server 2025 CTP 2.0 - https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/change-event-streaming/overview?view=sql-server-ver17] We’re excited to share that Change Event Streaming (CES) - the capability to stream data changes directly from your SQL into Azure Event Hubs - is now also available in private preview for Azure SQL Managed Instance. CES enables implementing near real-time data integration and event-driven architectures with minimal overhead, helping you keep systems in sync, power real-time analytics, and track changes as they happen. Whether you’re working with Azure SQL Managed Instance or Azure SQL Database, you can now join the private preview and: Try out the new functionality early. Collaborate with the product team. Share feedback that helps shape the final product. To apply for the private preview today, send an email to sqlcesfeedback [at] microsoft [dot] com or fill in the form at https://aka.ms/sql-ces-signup. More on CES available in the previous blog post. More useful resources: Free Azure SQL Database. Free Azure SQL Managed Instance. Azure SQL – Year 2024 in review. Azure SQL YouTube channel.Unlocking the Power of Regex in SQL Server
Regular expressions, or regex, are powerful tools for text manipulation and pattern matching. They are incredibly useful in various scenarios, such as data validation, extraction, and transformation. SQL Server 2025 introduces native support for regex functions, making it easier to perform complex text operations directly within SQL queries. This Regex support is available in SQL Server 2025 public preview, Azure SQL Database, SQL DB in Fabric, and Azure SQL Managed Instance. The table-valued functions (TVFs) are currently available on SQL Server 2025 public preview however they will follow on other offerings as well in the coming weeks, once deployment is complete. Advantages of using Regex in SQL queries Regular expressions offer several advantages when used in SQL queries: Flexibility in Pattern Matching: Regular expressions allow for complex and flexible pattern matching, making it easier to search for specific text patterns within large datasets. Efficiency in Data Manipulation: Regex functions can efficiently manipulate and transform text data, reducing the need for multiple string functions and simplifying queries. Improved Data Validation: Regular expressions can be used to validate data formats, ensuring that data conforms to specific patterns, such as email addresses, phone numbers, and zip codes. Enhanced Data Extraction: Regex functions can extract specific parts of text data, making it easier to retrieve valuable information from unstructured data. Standardization and Cleaning: Regex can help standardize and clean data by removing unwanted characters, whitespace, and duplicates. Regex functions – REGEXP_LIKE The REGEXP_LIKE function checks if a string matches a pattern. It’s useful for data validation and filtering rows based on specific patterns. This function can be used in the WHERE/ HAVING clause, CHECK constraints, CASE expressions or conditional statements etc. Example: Scenario #1: This query finds all employees with valid email addresses. SELECT [Name], Email FROM Employees WHERE REGEXP_LIKE(Email, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'); Scenario #2: Create Employees table with CHECK constraints on ‘Email’ and ‘Phone_Number’ columns, to ensure data meets the specified criteria. DROP TABLE IF EXISTS Employees CREATE TABLE Employees ( ID INT IDENTITY(101,1), [Name] VARCHAR(150), Email VARCHAR(320) CHECK (REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')), Phone_Number NVARCHAR(20) CHECK (REGEXP_LIKE (Phone_Number, '^(\d{3})-(\d{3})-(\d{4})$')) ); REGEXP_COUNT The REGEXP_COUNT function counts the number of times a pattern appears in a string. It’s useful for analyzing text data and identifying patterns. Example: This query counts the number valid email addresses for each domain. SELECT REGEXP_SUBSTR(Email, '@(.+)$', 1, 1,'c',1) AS Domain, COUNT(*) AS Valid_email_count FROM Employees WHERE REGEXP_COUNT(Email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') > 0 GROUP BY REGEXP_SUBSTR(Email, '@(.+)$', 1, 1,'c',1); REGEXP_INSTR The REGEXP_INSTR function finds the position of a pattern in a string. It’s useful for locating specific substrings within text data. Example: This query finds the position of the @ sign in each employee’s email address. SELECT Name, Email, REGEXP_INSTR(Email, '@') AS Position_of_@ FROM Employees; REGEXP_REPLACE The REGEXP_REPLACE function returns a modified string replaced by a ‘replacement string’, where occurrence of the regular expression pattern found. You can use it to modify or transform text data based on a regex pattern. Example: This query will redact sensitive data of variable length and format from a table that contains personal information like Social Security Numbers (SSNs) and credit card numbers. SELECT sensitive_info, REGEXP_REPLACE(sensitive_info, '(\d{3}-\d{2}-\d{4}|\d{4}-\d{4}-\d{4}-\d{4})', '***-**-****') AS redacted_info FROM personal_data; REGEXP_SUBSTR The REGEXP_SUBSTR function extracts a part of a string that matches a pattern. It’s useful for extracting specific information from text data. Example: This query extracts the domain part of valid email addresses. SELECT Name, Email, REGEXP_SUBSTR(Email, '@(.+)$', 1, 1, 'i', 1) AS Domain FROM Employees WHERE REGEXP_LIKE(Email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'); REGEXP_MATCHES The REGEXP_MATCHES function finds all the matches of a pattern in a string and returns them in a tabular format (i.e. as rows and columns). It’s useful for extracting multiple occurrences of a pattern. It is typically used to find all occurrences of a pattern or to retrieve parts of a string using capture groups, especially when you expect multiple matches per input. Example: This query finds all key-value pairs including substring-matches from the string and returns them in tabular format. SELECT * FROM REGEXP_MATCHES('Name: John Doe; Email: john.doe@example.com; Phone: 123-456-7890', '(\w+):\s*([^;]+)'); Results: match_id start_position end_position match_value substring_matches 1 1 14 Name: John Doe [{“value”:”Name”,”start”:1,”length”:4},{“value”:”John Doe”,”start”:7,”length”:8}] 2 17 43 Email: john.doe@example.com [{“value”:”Email”,”start”:17,”length”:5},{“value”:”john.doe@example.com”,”start”:24,”length”:20}] 3 46 64 Phone: 123-456-7890 [{“value”:”Phone”,”start”:46,”length”:5},{“value”:”123-456-7890″,”start”:53,”length”:12}] REGEXP_SPLIT_TO_TABLE The REGEXP_SPLIT_TO_TABLE function splits a string based on a pattern as the delimiter and returns the fragments as rows in a table. It’s useful for splitting strings with a list of items or breaking down text into words or sentences. Example: This query splits the string into parts based on semicolon and space characters and returns them in a tabular format. SELECT * FROM REGEXP_SPLIT_TO_TABLE('Name: John Doe; Email: john.doe@example.com; Phone: 123-456-7890', '; '); Results: value ordinal Name: John Doe 1 Email: john.doe@example.com 2 Phone: 123-456-7890 3 If your current database compatibility level is below 170 and you have a use-case where you want to leverage the REGEXP_LIKE function but need additional time to perform the complete analysis and testing before switching the compatibility level, you can use REGEXP_COUNT or REGEXP_INSTR functions as an interim solution. Example: SELECT [Name], Email FROM (VALUES('John Doe', 'john@contoso.com'), ('Alice Smith', 'alice@fabrikam.com'), ('Bob Johnson', 'bob@fabrikam.net'), ('Charlie Brown', 'charlie@contoso.co.in')) as e (Name, Email) WHERE REGEXP_COUNT(e.email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') > 0 --OR REGEXP_INSTR(e.email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') > 0; Availability All the intrinsic functions and TVFs are available in SQL Server 2025 public preview. The intrinsic functions are also available in Azure SQL Database, SQL DB in Fabric, and Azure SQL Managed Instance. The TVFs will follow in the future once deployment is completed. To learn more about the feature, please visit – learn page. Conclusion Regular expressions in SQL Server are powerful tools that can make your data tasks easier and more efficient. By bringing regex support natively, SQL Server lets you push more of these tasks into the database layer, which can simplify application code and enable new kinds of queries. Start simple with these functions, test your patterns, and soon you’ll be slicing and dicing strings in SQL with ease. We encourage you to explore these functions and provide your valuable feedback. Stay tuned for more updates and enhancements in the future. Thank you for being a part of our community and helping us make Azure SQL and SQL Server the best platform for your data needs!
