Azure Database Support Blog articles

Getting Started with Azure SQL Data Sync Using User-Assigned Managed Identity (UAMI)

Mohamed_Baioumy_MSFT — Tue, 18 Aug 2026 09:12:37 GMT

Azure SQL Data Sync is a powerful service that enables data synchronization across multiple Azure SQL Databases. Traditionally, Data Sync relied on SQL Authentication, requiring administrators to manage usernames and passwords for both hub and member databases.

With the introduction of User-Assigned Managed Identity (UAMI) support, Azure SQL Data Sync now provides a more secure and modern authentication model built on Microsoft Entra ID. This enhancement reduces credential management overhead, eliminates password storage concerns, and helps organizations strengthen their security posture.

In this article, we'll explore how to onboard Azure SQL Data Sync with UAMI, configure the required permissions, create synchronization components, and review best practices for long-term management.

Why Use UAMI with Azure SQL Data Sync?

User-Assigned Managed Identity offers several advantages over traditional SQL authentication:

Eliminates password storage and rotation requirements
Reduces credential exposure risks
Integrates with Microsoft Entra ID authentication
Supports centralized identity management
Improves compliance and security posture
Simplifies operational maintenance

Whether you're deploying a new synchronization topology or modernizing an existing Data Sync environment, UAMI provides a secure cloud-native alternative to password-based authentication.

Architecture Overview

A typical UAMI-enabled Azure SQL Data Sync deployment looks like this:

Authentication Method:

Microsoft Entra ID via User-Assigned Managed Identity

The same managed identity can be used by Data Sync to authenticate against all participating databases.

Prerequisites

Before configuring Azure SQL Data Sync with UAMI, ensure the following prerequisites are completed:

1. Enable Microsoft Entra Authentication

The Azure SQL Server hosting both Hub and Member databases must have Microsoft Entra authentication enabled and an Entra administrator configured.

2. Create a User-Assigned Managed Identity

Create a UAMI within Azure and collect the following information:

Managed Identity Name
Client ID
Resource ID

These values will be required during configuration.

3. Install the Required PowerShell Module

UAMI support requires the Azure SQL preview PowerShell module:

Install-Module Az.Sql -RequiredVersion 6.6.0-preview ` -AllowPrerelease ` -Force ` -AllowClobber

Or a later version that includes Data Sync UAMI support.

Phase 1: Configure Database Access

Before Data Sync can use a managed identity, the identity must be granted access to all databases participating in synchronization.

Connect to each Hub and Member database using a Microsoft Entra administrator account and create a user for the managed identity.

Example:

DECLARE @MSIname SYSNAME = '<UAMI_NAME>'; DECLARE @dbName SYSNAME = DB_NAME(); DECLARE @clientId UNIQUEIDENTIFIER = '<CLIENT_ID>'; -- Create User -- Grant db_datareader -- Grant db_datawriter -- Grant CONTROL permissions

The UAMI must be created and granted permissions on every database participating in synchronization.

Phase 2: Create the Sync Group

After permissions are configured, create the Sync Group using UAMI authentication.

Example PowerShell:

New-AzSqlSyncGroup ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $hubDatabase ` -Name $syncGroup ` -HubDatabaseAuthenticationType UserAssigned ` -ResourceId $identityResourceId

Key Parameters

Parameter	Description
HubDatabaseAuthenticationType	Authentication method for Hub Database
UserAssigned	Enables UAMI authentication
ResourceId	Full ARM Resource ID of the UAMI

Phase 3: Add Sync Members

Once the Sync Group is created, add member databases and specify UAMI authentication.

Example:

New-AzSqlSyncMember ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $hubDatabase ` -SyncGroupName $syncGroup ` -Name $memberName ` -MemberDatabaseAuthenticationType UserAssigned ` -ResourceId $identityResourceId

At this stage, both Hub and Member databases are configured to authenticate using the managed identity.

Phase 4: Configure Synchronization

Refresh Hub Schema

Refresh the schema metadata before selecting tables for synchronization.

Update-AzSqlSyncSchema ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $hubDatabase ` -SyncGroupName $syncGroup

Configure the Synchronization Schema

Data Sync requires an explicit schema definition specifying which tables and columns will participate in synchronization.

Example schema:

{ "Tables": [ { "QuotedName": "[dbo].[contacts]", "Columns": [ { "QuotedName": "[id]" }, { "QuotedName": "[name]" } ] } ] }

Apply the schema:

Update-AzSqlSyncGroup ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $hubDatabase ` -Name $syncGroup ` -HubDatabaseAuthenticationType UserAssigned ` -ResourceId $identityResourceId ` -SchemaFile "C:\schema.json"

Trigger Synchronization

After configuration is complete, trigger the initial synchronization.

Start-AzSqlSyncGroupSync ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $hubDatabase ` -SyncGroupName $syncGroup

Phase 5: Rotating UAMIs

Over time, organizations may need to replace an existing managed identity.

UAMI_v1 → UAMI_v2

Only one UAMI can be assigned to a Sync Group or Sync Member at a time.

To rotate identities:

Grant required database permissions to the new UAMI.
Remove the current UAMI.
Assign the new UAMI in the same operation.

Update Sync Group

Update-AzSqlSyncGroup ` -HubDatabaseAuthenticationType UserAssigned ` -ResourceId $newUamiResourceId ` -RemoveIdentityResourceId $oldUamiResourceId

Update Sync Member

Update-AzSqlSyncMember ` -MemberDatabaseAuthenticationType UserAssigned ` -ResourceId $newUamiResourceId ` -RemoveIdentityResourceId $oldUamiResourceId

Common Pitfalls

During implementation, the following issues are commonly encountered:

Pitfall #1: Missing Entra Administrator

If the SQL Server does not have an Entra administrator configured, UAMI authentication will fail.

Pitfall #2: Missing Database Permissions

The managed identity must be created and granted permissions in every participating database.

Pitfall #3: Multiple UAMIs Assigned

Azure SQL Data Sync supports only one UAMI per Sync Group or Sync Member at a time.

Pitfall #4: Identity Included During PATCH Updates

When updating schemas through REST APIs, avoid re-submitting the identity block if the managed identity is already assigned. This may result in a DataSyncMultipleIdentities error.

Validation Checklist

Before moving to production, verify the following:

Microsoft Entra administrator configured
UAMI created successfully
UAMI user created in all Hub and Member databases
Required permissions granted
Sync Group configured with UserAssigned authentication
Sync Members configured with UserAssigned authentication
Schema refreshed successfully
Synchronization schema applied
Initial synchronization completed successfully
Data synchronized correctly between Hub and Member databases

Best Practices

For secure and scalable deployments:

Prefer UAMI over SQL Authentication for new deployments.
Use dedicated managed identities for Data Sync workloads.
Follow least-privilege access principles where possible.
Test configuration changes in non-production environments first.
Maintain documentation for identity ownership and rotation procedures.
Monitor synchronization health after identity updates.
Standardize naming conventions for managed identities.

References

The following Microsoft resources provide additional details for implementing Azure SQL Data Sync with User-Assigned Managed Identities:

1. Configure Microsoft Entra Authentication for Azure SQL Database

Configure an Entra administrator for Azure SQL Database, which is a prerequisite for UAMI-based authentication.

https://learn.microsoft.com/azure/azure-sql/database/authentication-aad-configure

2. Manage User-Assigned Managed Identities

Learn how to create and manage User-Assigned Managed Identities in Azure. Managed identities eliminate the need to manage credentials in code and can be reused across multiple Azure resources. Manage user-assigned managed identities using the Azure portal explains the process and prerequisites. [Manage use...soft Learn | Learn.Microsoft.com]

3. Connect to Azure SQL Using Microsoft Entra Authentication

Configure and validate Entra-based connectivity for Azure SQL Database before granting permissions to the managed identity.

https://learn.microsoft.com/azure/azure-sql/database/authentication-microsoft-entra-connect-to-azure-sql

4. Azure SQL Data Sync PowerShell Documentation

Review PowerShell cmdlets used to create Sync Groups, Sync Members, refresh schemas, and trigger synchronization.

5. Azure SQL REST API Documentation

Use REST APIs for automation and Infrastructure-as-Code deployment scenarios involving Azure SQL Data Sync.

Conclusion

User-Assigned Managed Identity support represents a significant security enhancement for Azure SQL Data Sync. By eliminating dependency on stored credentials and leveraging Microsoft Entra authentication, organizations can improve security, simplify operations, and reduce administrative overhead.

Whether you're building a new synchronization topology or migrating from SQL Authentication, UAMI provides a modern, scalable, and cloud-native authentication model for Azure SQL Data Sync.

As cloud environments continue to adopt identity-first security practices, implementing UAMI for Azure SQL Data Sync is an important step toward a more secure and manageable data platform.

Azure SQL Data Sync Fails with "Cannot Insert NULL": Understanding the Root Cause

Mohamed_Baioumy_MSFT — Tue, 18 Aug 2026 07:55:05 GMT

Azure SQL Data Sync is a powerful service that enables data synchronization across multiple Azure SQL Databases. While synchronization failures are relatively uncommon, one error that administrators occasionally encounter is SQL Server Error 515, indicating that a NULL value cannot be inserted into a non-nullable column.

At first glance, this appears to be a straightforward data-quality problem. However, in many cases, the actual root cause lies elsewhere: inconsistencies between Azure SQL Data Sync tracking metadata and the underlying source data.

This article explains:

Common causes of Error 515 during synchronization
How to troubleshoot the issue
How to identify invalid tracking records
Safe mitigation approaches to restore synchronization

The Error

A synchronization operation may fail with an error similar to the following:

SqlException Error Code: -2146232060

SqlError Number: 515

Message:

Cannot insert the value NULL into column 'column_name',

table 'dbo.table_name';

column does not allow nulls.

INSERT fails.

SqlError Number: 3621

The statement has been terminated.

Although the error references a NULL value being inserted into a destination table, the root cause is not always missing data. In many cases, the issue originates from synchronization metadata maintained by Azure SQL Data Sync.

How Azure SQL Data Sync Tracks Changes

Azure SQL Data Sync relies on internal tracking tables to detect and replicate data changes between Hub and Member databases.

Whenever rows are inserted, updated, or deleted, synchronization metadata is recorded in tracking tables. Data Sync uses this metadata to determine what changes need to be propagated to other databases.

If the tracking metadata becomes inconsistent with the actual source table contents, Data Sync may attempt to synchronize invalid records, resulting in failures such as:

Cannot insert the value NULL into column...

Common Root Causes

Scenario 1: Schema Mismatch Between Databases

One of the most common causes of synchronization failures is a schema mismatch between synchronized databases.

For example:

Database	Column Definition
Hub	NULL Allowed
Member A	NOT NULL
Member B	NOT NULL

If Data Sync replicates a row containing a NULL value from the Hub database, synchronization will fail when the destination database does not allow NULL values.

Areas to Validate

Ensure the following are identical across all synchronized databases:

Column nullability (NULL vs NOT NULL)
Data types
Column length
Constraints
Primary key definitions

Even small schema differences can cause synchronization failures.

Scenario 2: Invalid Tracking Metadata

A less obvious but frequently encountered scenario involves orphaned records in Data Sync tracking tables.

This can occur when:

Primary key values are updated directly
Data is modified outside expected application workflows
Historical tracking records become disconnected from source data
Synchronization metadata references rows that no longer exist

When Data Sync processes these stale entries, synchronization may fail with Error 515 even though the source data itself appears valid.

Troubleshooting Process

Step 1: Verify Column Definitions

Begin by examining the affected table and column identified in the error message.

For example:

sp_help 'dbo.table_name'

Review the schema on both Hub and Member databases and verify that:

The affected column has the same definition everywhere
NULL settings are identical
Data types and lengths match

If discrepancies exist, align the schemas across all synchronized databases before proceeding.

Step 2: Review the Table Schema

If the schema appears consistent, review the complete definition of the affected table.

Pay particular attention to:

Primary key columns
Identity columns
Constraints
Nullable settings

Identifying the primary key is especially important for the next validation step.

Step 3: Check for Orphaned Tracking Records

Run the following query against both Hub and Member databases.

Replace:

table_name
primary_key

with the actual table and primary key column names.

SELECT COUNT(*) FROM DataSync.table_name_dss_tracking t WHERE sync_row_is_tombstone = 0 AND NOT EXISTS ( SELECT * FROM dbo.table_name s WHERE t.primary_key = s.primary_key );

For tables with composite primary keys, include all key columns in the comparison.

How to Interpret the Results

Result > 0

One or more orphaned tracking records exist.

This indicates that the tracking table contains entries that reference records no longer present in the source table.

This is a strong indicator that invalid synchronization metadata is causing the failure.

Result = 0

No orphaned records were detected.

If the synchronization error persists, further investigation should focus on schema consistency, data quality, and additional synchronization diagnostics.

Mitigation Option 1: Correct Schema Differences

If schema inconsistencies are found:

Align the table definition across all synchronized databases.
Ensure NULL and NOT NULL settings are consistent.
Verify primary key definitions match.
Reinitialize synchronization if necessary.

After schema alignment, synchronization can typically resume successfully.

Mitigation Option 2: Clean Invalid Tracking Data

If orphaned tracking records are identified, remove the invalid synchronization metadata.

Important: Always validate and test cleanup operations in a non-production environment before executing them in production.

The following query removes tracking entries that no longer correspond to records in the source table:

DELETE FROM DataSync.table_name_dss_tracking WHERE sync_row_is_tombstone = 0 AND NOT EXISTS ( SELECT * FROM dbo.table_name s WHERE DataSync.table_name_dss_tracking.primary_key = s.primary_key );

Replace:

table_name
primary_key

with the appropriate values for your environment.

After cleanup, Data Sync can rebuild valid change tracking information and synchronization typically returns to a healthy state.

Additional Validation Query

The following query can help identify historical deletion records that exist in tracking tables:

SELECT tr.id1 FROM DataSync.table2_dss_tracking tr LEFT JOIN dbo.table2 orig ON tr.id1 = orig.id1 WHERE tr.sync_row_is_tombstone = 1 AND orig.id1 IS NULL AND tr.last_change_datetime > DATEADD(day, -20, GETUTCDATE());

This can provide additional insight into how synchronization metadata is tracking deleted records.

Understanding the Underlying Cause

The most important takeaway is that the NULL value reported in the synchronization error is often not the actual problem.

A common sequence looks like this:

A primary key value is modified directly.

UPDATE dbo.table_name SET primary_key = new_value;

Data Sync tracking metadata continues to reference the original key value.
The source table and tracking table become inconsistent.
During synchronization, Data Sync attempts to process the stale tracking record.
The synchronization operation fails and surfaces a "Cannot insert the value NULL into column" error.

In these scenarios, cleaning invalid tracking records resolves the inconsistency and restores successful synchronization.

Best Practices to Prevent Recurrence

To minimize the likelihood of synchronization failures:

Keep schemas identical across all synchronized databases
Avoid updating primary key values whenever possible
Use surrogate keys for synchronized tables
Validate schema consistency before deploying schema changes
Periodically investigate Data Sync tracking tables when troubleshooting synchronization failures
Test schema modifications in non-production environments before deployment

Conclusion

When Azure SQL Data Sync reports a:

Cannot insert the value NULL into column...

error, it is important not to assume that the problem is caused by missing data in the source table.

A structured troubleshooting approach should include:

Verifying schema consistency across synchronized databases
Reviewing primary key definitions
Investigating Data Sync tracking tables for orphaned records
Cleaning invalid synchronization metadata when appropriate

In many real-world cases, stale tracking records are the true root cause. Identifying and removing these invalid entries can restore synchronization quickly and avoid unnecessary application or schema changes.

Have you encountered similar Azure SQL Data Sync issues in your environment? Share your experience and troubleshooting techniques in the comments below.

Closing the SQL Data Sync Monitoring Gap: Building Proactive Alerts with Azure Logic Apps

Karunakar_Kotha — Tue, 18 Aug 2026 17:02:30 GMT

Microsoft Azure SQL Data Sync will be retired on September 30, 2027

Introduction

One of the most common lessons we've learned while working with customers running distributed applications is that data synchronization failures rarely happen suddenly.

Most synchronization issues leave breadcrumbs long before a full outage occurs. A warning here.

A failed row there. A transient connectivity issue that appears harmless today but becomes tomorrow's production incident.

For organizations still leveraging Azure SQL Data Sync, there is an important operational challenge:

SQL Data Sync does not expose synchronization health as an Azure Monitor metric.

This means teams cannot simply create an Azure Monitor alert rule and be notified when synchronization health begins to degrade.

Recently, while working in my own Azure lab environment, We explored a practical way to close this observability gap. The goal was simple:

Detect Data Sync warnings and errors early and automatically notify operations teams before synchronization failures impact business applications.

The result was a lightweight, serverless monitoring solution built entirely with Azure Logic Apps and Managed Identity, requiring minimal operational overhead.

The Hidden Monitoring Challenge

Many Azure services integrate seamlessly with Azure Monitor and emit metrics that can be used for alerting, dashboards, and incident management.

SQL Data Sync is different.

Although synchronization activity is recorded, the health information resides within the Sync Group Logs API, rather than being surfaced directly as alertable Azure Monitor metrics.

As a result:

Errors can go unnoticed until synchronization stops completely.
Warning events may be ignored because they're not visible in operational dashboards.
Support teams often learn about issues only after a downstream application reports stale or inconsistent data.

What's particularly concerning is that:

Row-level warnings frequently appear well before a hard synchronization failure.

These warning entries can serve as valuable leading indicators, giving operations teams time to investigate before users experience impact.

Building a Proactive Monitoring Approach

To address this challenge, I built a lightweight monitoring workflow using Azure Logic Apps.

Instead of waiting for failures to be reported, the Logic App proactively queries the Sync Group Logs API every 15 minutes and looks for:

Error events
Warning events

If any matching entries are found, an email notification is automatically sent to the designated operational contact or on-call distribution list.

High-Level Architecture

This design is:

Serverless
Low cost
Simple to deploy
Secure through Managed Identity
Easily customizable

Solution Architecture Walkthrough
Step 1: Scheduled Monitoring
The Logic App runs every 15 minutes.To avoid missing events that occur between execution intervals, the solution queries the previous 20 minutes of logs.

Why a 20-Minute Lookback on a 15-Minute Schedule?
Every 15 Minutes ▼ Query Last 20 Minutes ▼ 5-Minute Overlap ▼

Key Benefit:
The 20-minute lookback intentionally overlaps the 15-minute execution schedule, ensuring that warning and error events are not missed due to execution delays, transient platform issues, or timing gaps between runs. This design improves monitoring reliability and early issue detection

Step 2: Managed Identity Authentication

Security was a key design principle.

Rather than storing credentials, secrets, or service principal passwords, the Logic App uses a system-assigned managed identity.

The managed identity is granted the SQL DB Contributor role on the Hub SQL Server.

Benefits include:

- No credential management
- Automatic token acquisition
- Improved security posture
- Native Azure RBAC integration

Step 3: Querying the Sync Group Logs API

The Logic App executes an HTTP GET request against the SQL Data Sync Logs endpoint.

Example endpoint:

https://management.azure.com/subscriptions/<subscriptionId>/resourceGroups/<resourceGroup>/providers/Microsoft.Sql/servers/<HubServer>/databases/<HubDatabase>/syncGroups/<SyncGroup>/logs?api-version=2020-11-01-preview&startTime=<startUtc>&endTime=<endUtc>&type=All

The request is authenticated using Managed Identity against the Azure Resource Manager endpoint.

Step 4: Parsing the Response

The API returns log entries as a JSON array.

Important fields include:

Field	Description
timestamp	When the event occurred
type	Error, Warning, or informational event
source	Component generating the event
details	Detailed event information
tracingId	Correlation identifier
operationStatus	Result of the operation

After parsing the response, each field becomes available for workflow processing.

Step 5: Filter and Prioritize Actionable Events

Not every log entry warrants operational attention. To reduce noise and focus on meaningful health signals, the workflow filters events to retain only those classified as Error or Warning.

A Filter Array action is used to evaluate each log entry and keep only actionable events:

By filtering out informational and non-critical messages, operations teams can focus on events that may indicate synchronization issues, service degradation, or potential failures requiring investigation.

Or( equals(item()?['type'], 'Error'), equals(item()?['type'], 'Warning') )

Step 6: Notify Operations Teams

After filtering, the workflow determines whether any actionable events were identified.

Condition:

length(body('Filter_array')) > 0

When one or more warning or error events are detected, the Logic App automatically generates an email notification to the designated operations or support team.

The alert includes key diagnostic information such as:

Event details
Warning or error classification
Tracing ID
Timestamp information

Example Subject

SQL Data Sync Alert: MySyncGroup - 3 Warning/Error Events Detected

Example Notification

Type: Warning Timestamp: 2026-07-28T12:15:00Z Details: Failed row synchronization detected Tracing ID: abc123xyz

Providing this information upfront enables engineers to begin troubleshooting immediately, reducing the time required to identify and remediate synchronization issues.

Why This Matters

In many organizations, data synchronization is a business-critical workload. Consider scenarios such as:

Application databases synchronizing across multiple regions
Distributed retail platforms
Financial transaction processing systems
Operational reporting environments
Legacy SQL Data Sync deployments supporting core business processes

In these environments, early detection can significantly reduce operational risk. The difference between investigating a warning today and responding to a synchronization failure tomorrow may be the difference between a routine operational activity and a customer-impacting incident.

Key Benefits of the Solution

Improved Visibility:
Surfaces health signals that would otherwise remain hidden within SQL Data Sync logs, providing operations teams with greater insight into synchronization activity.
Early Detection
Identifies warning conditions and row-level synchronization issues before they escalate into service-impacting failures.
Security-First Architecture
Leverages Managed Identity for authentication, eliminating the need to store or manage credentials.
Cost Efficiency
Built on the Azure Logic Apps Consumption Plan, keeping operational costs low while delivering continuous monitoring capabilities.
Operational Simplicity
Requires no custom services, virtual machines, or ongoing infrastructure maintenance, making deployment and management straightforward.

Recommendations and Operational Best Practices

Based on testing and operational experience, consider the following best practices:

Treat Warnings as Actionable Signals

Many organizations focus exclusively on errors; however, warnings often provide the earliest indication of synchronization degradation. Investigating warning events proactively can help prevent future outages and service disruptions.

Maintain an Up-to-Date On-Call Distribution List

Ensure alerts are delivered to the appropriate responders. An outdated distribution list can be just as ineffective as having no alerting mechanism in place.

Use Overlapping Monitoring Windows

Always query a slightly larger time interval than the Logic App execution schedule. This approach helps prevent missed events caused by execution delays, scheduling drift, or transient service interruptions.

Review Trends Regularly

Even when warnings do not immediately impact synchronization, recurring patterns may reveal underlying issues such as:

Connectivity challenges
Schema inconsistencies
Permission and access problems
Data quality concerns

Periodic trend analysis can help teams identify and address systemic issues before they affect business operations.

Conclusion

Although Azure SQL Data Sync does not currently expose native Azure Monitor metrics for synchronization health, organizations are not limited to reactive monitoring approaches.

By combining:

Azure Logic Apps
Managed Identity
SQL Data Sync Logs API
Automated Email Notifications

organizations can implement a lightweight yet powerful monitoring solution that surfaces hidden warning and error signals before they evolve into production incidents.

In modern cloud operations, resilience is not defined solely by how quickly teams respond to failures. It is equally determined by their ability to identify weak signals early and take proactive action before customers are impacted.

This Logic App-based approach demonstrates how a relatively small amount of automation can significantly improve operational visibility, accelerate issue detection, and reduce the Mean Time to Detect (MTTD) for Azure SQL Data Sync environments.

Lessons Learned #551: Azure SQL Connection Timeouts: Three Things to Check

Jose_Manuel_Jurado — Wed, 05 Aug 2026 18:21:05 GMT

An application starts reporting intermittent timeouts when connecting to Azure SQL Database. Some requests succeed, others fail, and a test from a developer’s laptop works perfectly. The database appears online, no recent deployment seems related, and the natural reaction is to ask:

Is Azure SQL unavailable?
Is the firewall blocking the connection?
Should we increase the connection timeout?
Should we change the driver or scale the database?

Those are reasonable questions, but they may lead the investigation in the wrong direction. The most important lesson is simple: A timeout tells us how long the application waited. It does not tell us what the application was waiting for.

Not every “SQL timeout” happens inside Azure SQL

From the application’s point of view, opening a database connection may involve several operations:

Resolving the server name.
Reaching the SQL endpoint.
Obtaining a Microsoft Entra access token.
Waiting for an available pooled connection.
Completing the SQL login.
Executing the first command.

When all these operations are reported through the same application method or log entry, it can look as though Azure SQL took thirty seconds to accept the connection. In reality, only part of that time may have been spent connecting to the database.

In one anonymized support scenario, the application experienced problems mainly on its first connection. Network tests were successful and no corresponding SQL connection failure was identified. The investigation eventually showed that access-token acquisition was consuming a significant part of the available time. Increasing the SQL timeout or changing the firewall would not have addressed the real delay.

Check 1: Capture the complete error and the exact time

A screenshot containing only “Connection Timeout Expired” is rarely enough.

Capture:

The complete exception and inner exception.
The operation being performed.
The driver and version.
The authentication method.
The exact timestamp in UTC.
Whether the issue affects every connection or only some of them.

The wording around the timeout matters. For example, a timeout while obtaining a connection from the pool points toward the application’s pooling and concurrency behavior. A pre-login or TLS error belongs to a different investigation. A command timeout after the connection was established is usually a query-performance problem rather than a connection problem.

Check 2: Measure the application timeline

The application should record important operations separately. A simple timeline can completely change the investigation:

10:14:20.100 Token acquisition started

10:14:28.400 Token acquired

10:14:28.405 SQL connection started

10:14:29.050 SQL connection established

The complete operation took almost nine seconds, but Azure SQL connection establishment took less than one second.

Useful measurements include:

Token-acquisition duration.
Time waiting for a pooled connection.
SQL connection-open duration.
SQL command duration.
Number of retry attempts.

Applications using Microsoft Entra authentication must obtain an access token before authenticating to Azure SQL. Measuring that operation separately helps distinguish an identity delay from a database connectivity problem.

This is particularly useful when the issue appears:

On the first connection after startup.
After a token expires.
Only with Managed Identity or Workload Identity.
Intermittently, while SQL authentication connections remain unaffected.

Check 3: Test from the application environment

A successful connection from a laptop does not validate the path used by an application running in:

Azure App Service.
Azure Functions.
Azure Kubernetes Service.
A virtual machine.
An on-premises application server.
A container or integration runtime.

The laptop and the application may use different DNS servers, routes, firewalls, proxies and identities. Connectivity and DNS tests should therefore be performed from the environment that is actually failing.

This becomes especially important when Private Endpoint is used. The application should continue connecting with: <server>.database.windows.net

It should not use the Private Endpoint IP address or the privatelink.database.windows.net hostname directly. Direct login attempts using the private IP or the private-link FQDN fail; the normal logical-server FQDN must remain in the connection string.

From the affected environment, confirm that:

The expected DNS server answers the request.
The server FQDN resolves to the expected private IP.
The Private Endpoint connection is approved.
The Private DNS zone is linked correctly.
The resolved address is reachable through the intended route.

A test from an unrelated machine is still useful for comparison, but it does not prove that the application path is healthy.

Observed symptom	Likely investigation area
Timeout while obtaining a connection from the pool	Application connection pooling
Server name cannot be resolved	DNS
TCP connection to the endpoint cannot be established	Network path, firewall or routing
Error during the pre-login handshake	TLS, driver, network interruption or pre-login processing
Authentication or access-token error	Microsoft Entra authentication, identity or token acquisition
Timeout during the post-login phase	Login completion, session initialization or server-side processing
Execution or command timeout after connecting	Query execution and database performance

Avoid changing several things at once

During a production incident, it is tempting to:

Increase the timeout.
Add firewall rules.
Change the connection policy.
Upgrade the driver.
Restart the application.
Clear connection pools.

Applying several changes together makes it difficult to determine which one helped, and some may only hide the symptom.

A better approach is to define one hypothesis: We believe DNS in the application environment is resolving the public endpoint instead of the Private Endpoint.

Then define:

The evidence supporting the hypothesis.
One controlled change.
The expected result.
How the result will be measured.
How the change will be reverted.

Azure SQL supports Proxy and Redirect connection policies, which determine how traffic flows after reaching the Azure SQL gateway. The policy is configured for the logical server, so it should be verified before making firewall assumptions or changes.

What should we collect before opening a support request?

A small but precise evidence package can avoid several rounds of questions:

Complete error and inner exception.
Exact UTC timestamps.
Application platform and location.
Public or Private Endpoint.
Server FQDN used by the application.
Driver and version.
Authentication method.
Token, pool, connection and command durations.
DNS result from the affected environment.
Whether the issue is constant, intermittent or limited to the first connection.
Recent application, network, identity or configuration changes.

Understanding Azure SQL Long-Term Retention Immutability Configuration with Terraform and AzAPI

Mohamed_Baioumy_MSFT — Sat, 01 Aug 2026 17:35:42 GMT

Executive Summary

Organizations using Azure SQL Database Long-Term Retention (LTR) backup policies may encounter failures when attempting to disable backup immutability while also specifying an immutability mode in the same request. In the investigated scenario, the customer observed that the operation succeeded when performed through ARM templates but failed when executed through Terraform using AzAPI-based resources.

The investigation determined that the Azure SQL resource provider enforces validation rules that prevent TimeBasedImmutabilityMode from being specified when TimeBasedImmutability is set to Disabled. The issue was not caused by the Azure SQL service itself, but rather by how configuration values were being submitted through Terraform and AzAPI resource updates.

The recommended mitigation is to ensure that immutability mode is omitted or explicitly set to null when disabling time-based immutability. This allows the request to comply with the resource provider's validation requirements.

Introduction

Azure SQL Database supports immutable Long-Term Retention (LTR) backups to help organizations meet compliance, governance, and data protection requirements. These policies allow administrators to control whether retained backups can be modified or deleted.

During an investigation involving Terraform and AzAPI deployments, a customer reported inconsistent behavior when attempting to disable backup immutability. While equivalent ARM template operations completed successfully, Terraform-based deployments generated validation errors.

This article explains the observed behavior, the investigation findings, the confirmed root cause, and the recommended mitigation.

Issue Description

Reported Symptoms

The customer reported the following behavior:

Enabling and managing Long-Term Retention backup immutability worked successfully.
Configurations involving immutability mode settings could be applied successfully under certain conditions.
Attempts to disable immutability through Terraform resulted in failures.
Similar operations appeared to succeed when performed using ARM templates.

Technical Environment

The discussion confirmed the following components:

Azure SQL Database Long-Term Retention (LTR) backup policies
Backup immutability configuration
Terraform deployments
AzAPI resources and AzAPI resource updates
ARM template deployments
Azure SQL Resource Provider validation logic

Expected Behavior

When administrators disable backup immutability, the configuration update should be accepted and the policy should transition to a disabled state.

Actual Behavior

Requests submitted through Terraform/AzAPI included both:

TimeBasedImmutability = Disabled
TimeBasedImmutabilityMode = Unlocked

The Azure SQL resource provider rejected this configuration, returning an error indicating that an immutability policy mode cannot be specified when backup immutability is not enabled.

Investigation and Troubleshooting

1. Initial Customer Question

The customer sought clarification on whether enabling, disabling, locking, and unlocking backup immutability should all be possible through AzAPI resources and whether a product issue existed.

2. Review of Azure SQL Resource Provider Behavior

The support team reviewed requests submitted to the Azure SQL resource provider and compared successful and unsuccessful operations. The investigation focused on configuration differences between ARM template deployments and Terraform-driven updates.

Confirmed Finding

When ARM templates disabled immutability, the request contained:

TimeBasedImmutability = Disabled

and did not include an immutability mode parameter.

Confirmed Finding

When Terraform attempted to disable immutability, the request included both:

TimeBasedImmutability = Disabled
TimeBasedImmutabilityMode = Unlocked

This resulted in a validation failure from the Azure SQL resource provider.

3. Validation of Error Behavior

The team verified that the error was generated by the Azure SQL resource provider and was reproducible outside Terraform, including equivalent testing through ARM deployments when the conflicting parameter combination was supplied.

Confirmed Error

The resource provider returned an error equivalent to:

Cannot set immutability policy mode when backup immutability is not enabled.

4. Assessment of Terraform and AzAPI Behavior

The investigation identified an important behavioral difference.

Terraform itself did not yet expose dedicated Time-Based Immutability parameters in its SQL modules. As a result, the customer was using AzAPI resources to perform direct REST-based operations.

The team discovered that:

azapi_resource behaved as expected.
azapi_resource_update could retrieve and reuse an existing property value when no value was explicitly provided.

This behavior caused the immutability mode value to persist unexpectedly during updates.

5. Reproduction and Verification

The engineering discussion included review and validation of the reported behavior. Testing confirmed that requests containing immutability mode while immutability was disabled were expected to fail due to platform validation.

Root Cause

Confirmed Root Cause

The failure occurred because the update request attempted to disable backup immutability while simultaneously providing a value for TimeBasedImmutabilityMode.

Azure SQL validation rules require immutability mode to be associated only with an enabled immutability configuration. When immutability is disabled, an immutability mode must not be supplied.

An additional contributing factor was the behavior of azapi_resource_update, which could retain a previously configured immutability mode value when no new value was explicitly provided. Consequently, requests unintentionally included an immutability mode even though the intent was to disable immutability entirely.

The available evidence supports this conclusion through:

Comparison of successful ARM template requests and failing Terraform requests.
Reproduction of the same validation behavior by the Azure SQL resource provider.
Validation of the AzAPI update behavior involving retained values.

Mitigation and Resolution

Recommended Mitigation

When disabling backup immutability:

Set TimeBasedImmutability to Disabled.
Do not provide TimeBasedImmutabilityMode.

Terraform/AzAPI Workaround

The investigation determined that explicitly setting:

TimeBasedImmutabilityMode = null

prevents the previous value from being reused and allows the request to be processed correctly.

Configuration Matrix Discussed

The support team identified the following expected behavior:

Operation	Immutability Mode Requirement
Locking backups	Mode should be set to Locked
Unlocking while remaining enabled	Mode may be supplied and is recommended for clarity
Disabling immutability	Mode should not be supplied

This guidance was explicitly discussed during the investigation.

Validation

After applying the mitigation:

The disable operation should complete without the immutability mode conflict.
Requests should no longer trigger the Azure SQL validation error related to immutability mode usage.

Recommendations and Best Practices

Recommendations Supported by the Investigation

Ensure that immutability mode is not included when disabling backup immutability.
Review Terraform templates for dynamically generated properties that may continue to emit previously populated values.
When using AzAPI update resources, explicitly manage nullable properties where supported to avoid unintended value persistence.

Important Considerations

Behavior may vary depending on:

Azure SQL API version
Terraform provider version
AzAPI provider implementation details
Existing Long-Term Retention backup state
Whether previously locked backups exist

Always validate deployment behavior in a non-production environment before applying configuration changes broadly.

Conclusion

This investigation demonstrated that the inability to disable Azure SQL Long-Term Retention backup immutability was not caused by a platform defect in Azure SQL. Instead, the failure occurred because requests attempted to specify an immutability mode while immutability itself was being disabled.

The issue was further influenced by AzAPI update behavior that could preserve previously configured values unless explicitly cleared. Setting the immutability mode to null, or removing it entirely when disabling immutability, resolved the problem.

The key technical takeaway is that TimeBasedImmutabilityMode and TimeBasedImmutability must be configured consistently with Azure SQL resource provider validation rules, particularly during infrastructure-as-code deployments.

Public Documentation

Troubleshooting SQL Authentication Failures on Azure SQL Database Geo-Secondary Replicas

hudajazmawi — Thu, 30 Jul 2026 14:12:46 GMT

Scenario Background

During a recent investigation, a customer reported that SQL Authentication was failing when connecting to an Azure SQL Database geo-secondary replica.

The customer confirmed the following:

SQL Authentication worked successfully against the primary replica.
The geo-secondary database was healthy and synchronized.
The replicated database user existed in the secondary database.
Microsoft Entra authentication continued to work successfully.

Because the database was available and replication health was normal, the initial assumption was that the issue might be related to replication or database accessibility.

After reviewing the security configuration, the issue was identified as a mismatch in SQL login configuration between the primary and secondary logical servers.

This scenario highlights an important security consideration when designing Azure SQL Database disaster recovery solutions:

Active Geo-Replication replicates user databases, but server-level objects such as SQL logins stored in the master database are not automatically replicated.

Common Symptoms

You may encounter one or more of the following symptoms:

SQL Authentication succeeds against the primary replica but fails against the geo-secondary.
Microsoft Entra authentication continues to work successfully.
Replication health appears normal and synchronized.
The database user exists in the secondary database.
Connection attempts return errors such as:

Login failed for user '<login_name>'.

Invalid username or password

These symptoms can occur even when the secondary database has successfully replicated and is fully accessible through other authentication methods

Understanding the Architecture

Azure SQL Active Geo-Replication replicates user databases to the secondary region, but it does not automatically replicate server-level objects stored in the logical server's master database, including SQL logins. Configure Security for Disaster Recovery - Azure SQL Database | Microsoft Learn

Because SQL Authentication depends on a server-level login, a replicated database user alone is not sufficient. The corresponding login must also exist on the secondary logical server and be correctly mapped. Tutorial: Geo-replication & failover in portal - Azure SQL Database | Microsoft Learn

For SQL Authentication to work successfully:

The SQL login must exist on the target logical server.
The login SID must match the SID of the corresponding login on the primary server.
The database user SID must correctly map to the SQL login SID.

The relationship can be summarized as:

Figure 1. SQL Login and Database User Synchronization Behavior in Azure SQL Geo-Replication

Even though the database user is replicated successfully, SQL Authentication can fail because login validation occurs before database access is granted

Understanding the Root Cause

When SQL Authentication is used, Azure SQL first validates the server-level login.

Authentication failures can occur when:

The SQL login does not exist on the secondary logical server.
The login exists but has a different SID than the corresponding login on the primary server.
The login-user mapping is broken because the replicated database user references a different SID.

Microsoft documentation specifically calls out that when using SQL logins and database users, the same logins must be present on the target server as part of geo-replication disaster recovery planning.

For more details: Configure Security for Disaster Recovery - Azure SQL Database | Microsoft Learn

Validate Login Configuration

Start by comparing the SQL logins on both logical servers:

SELECT name, sid FROM sys.sql_logins WHERE type_desc = 'SQL_LOGIN';

Verify:

The login exists on both servers.
The SID matches between the primary and secondary servers.
The login is not disabled.

Resolution Option 1: Use Contained Database Users

Contained users eliminate the dependency on server-level logins because authentication information is stored inside the user database.

Example:

CREATE USER AppUser WITH PASSWORD = 'StrongPassword';

Because contained users are part of the user database, they are replicated together with the database and can continue working after failover without additional login configuration.

For more information:

Contained User Access to Contained Databases - SQL Server | Microsoft Learn

Configure Security for Disaster Recovery - Azure SQL Database | Microsoft Learn

Resolution Option 2: Create the Missing Login on the Secondary Server

If SQL logins are required, retrieve the SID from the primary server's master database:

SELECT name, sid FROM sys.sql_logins WHERE name = 'AppLogin';

Then create the login on the secondary server's master database using the same SID:

CREATE LOGIN AppLogin WITH PASSWORD = 'StrongPassword', SID = 0x010600000000006400000000000000001E104AE44775E14EBED5F8248FEF66EC;

Microsoft also uses this same SID-preservation approach when configuring geo-replication security

Tutorial: Geo-replication & failover in portal - Azure SQL Database | Microsoft Learn

Resolution Option 3: Recreate the Login if the SID Does Not Match

If the login already exists on the secondary server's master database but uses a different SID, drop and recreate it using the SID from the primary server.

DROP LOGIN AppLogin; GO CREATE LOGIN AppLogin WITH PASSWORD = 'StrongPassword', SID = 0x010600000000006400000000000000001E104AE44775E14EBED5F8248FEF66EC; GO

This ensures the login is correctly mapped to the replicated database user.

Alternative Validation and Remediation Using PowerShell

In our scenario, the customer was using the Azure Portal Query Editor and was unable to validate the configuration through traditional tools such as SQL Server Management Studio (SSMS) or Azure Data Studio (ADS). PowerShell provides an alternative method to both validate connectivity and apply the required login configuration on the secondary server

In our case, the customer was primarily testing connectivity through the Azure Portal Query Editor and did not always have access to traditional management tools such as SQL Server Management Studio (SSMS) or Azure Data Studio (ADS).

PowerShell can be used both to validate connectivity and to apply the required login configuration on the secondary server.

Step 1: Connect to Azure

Connect-AzAccount Set-AzContext -SubscriptionId "<SubscriptionID>" $token = (Get-AzAccessToken -ResourceUrl "https://database.windows.net").Token

Step 2: Check whether the login exists on the secondary logical server

Connect to the master database on the secondary server and review the existing SQL logins:

Invoke-Sqlcmd -ServerInstance "tcp:<server-name>.database.windows.net,1433" -Database "master" -AccessToken $token -Query "SELECT name, sid FROM sys.sql_logins;"

Step 3: Create the Missing Login on the Secondary Server

If the login does not exist on the secondary logical server, connect to the secondary server's master database and create it using the SID from the primary:

Invoke-Sqlcmd -ServerInstance "tcp:<server-name>.database.windows.net,1433" -Database "master" -AccessToken $token -Query "CREATE LOGIN AppLogin WITH PASSWORD = 'StrongPassword', SID = 0x010600000000006400000000000000001E104AE44775E14EBED5F8248FEF66EC;"

Step 4: Recreate the Login if the SID Does Not Match

If the login already exists but has a different SID than the primary, recreate it using the SID from the primary server

Invoke-Sqlcmd -ServerInstance "tcp:<server-name>.database.windows.net,1433" -Database "master" -AccessToken $token ` -Query " DROP LOGIN AppLogin; CREATE LOGIN AppLogin WITH PASSWORD = 'StrongPassword', SID = 0x010600000000006400000000000000001E104AE44775E14EBED5F8248FEF66EC; "

Step 5: Validate connectivity

After creating or correcting the login, test the connection by authenticating with the SQL login against the secondary replica.

This approach is especially useful when administrators cannot use SSMS or Azure Data Studio and need to validate or remediate the configuration directly through PowerShell

Invoke-Sqlcmd ` -ServerInstance "myserver.database.windows.net" ` -Database "MyDatabase" ` -Username "AppLogin" ` -Password "StrongPassword" ` -Query "SELECT DB_NAME();"

A successful result confirms that SQL Authentication is working and that the login can access the target database.

Key Takeaways

Active Geo-Replication replicates user databases, not SQL logins stored in the master database.
A replicated database user alone does not guarantee successful SQL Authentication.
Always validate login existence and SID consistency across primary and secondary logical servers.
Contained database users can simplify authentication management in geo-replicated environments.

References

Lessons Learned #550: From a Support Case to Reusable Knowledge

Jose_Manuel_Jurado — Wed, 29 Jul 2026 10:05:15 GMT

Reaching Lessons Learned #550 is an important milestone for me. However, the value of this series is not only the number of articles published. Each article started with a technical question, an unexpected behavior, a support investigation, or a scenario that required additional testing and analysis.

Some cases resulted in a configuration change. Others required a query, a script, a workaround, a product clarification, or a different troubleshooting approach. Over time, I have learned that resolving the immediate issue is only one part of the work.

A support case becomes even more valuable when the knowledge gained during the investigation can help another engineer or customer facing a similar situation. Every support case may contain a lesson. The challenge is to identify it, validate it, and make it reusable.

Identify the Reusable Lesson

Not every detail from a support case needs to become an article. The first step is to identify the part of the investigation that may be useful outside the original scenario.

This could be:

an unexpected product behavior;
a common misunderstanding;
a diagnostic query;
a troubleshooting method;
a configuration requirement;
a limitation that may not be immediately visible;
a way to interpret a metric or error message;
a test that helped confirm the technical explanation.

For example, the specific customer environment may be unique, but the method used to distinguish CPU pressure from Data IO pressure may be useful in many other investigations. Similarly, the original application architecture may be complex, but the test used to isolate a network path may be simple and reusable.

The objective is not to reproduce the complete support case. The objective is to extract the lesson that may help others.

Explain the Symptom Clearly

A useful technical article should begin with a behavior that readers can recognize.

For example:

Connections fail only from one application instance.
Query duration increases after a service-tier migration.
CPU reaches a high percentage, but the workload remains constrained by another resource.
A failover restores normal operation without fully explaining the original cause.
A monitoring result appears different from what was initially expected.

A reader should be able to determine quickly whether the scenario resembles a problem they are investigating.

Describe How the Conclusion Was Reached

A solution is more useful when the reader understands how it was validated.

For that reason, I normally try to explain:

what was initially observed;
which evidence was reviewed;
which possibilities were considered;
which tests were performed;
what result supported the conclusion;
which limitations remained.

The objective is to provide enough context for the reader to understand why the conclusion is reasonable and under which conditions it applies.

Separate Mitigation from Explanation

A mitigation may restore service without fully explaining the technical cause.

For example:

restarting an application may reset the connection pool;
a failover may disconnect blocking sessions;
scaling may increase several resource limits simultaneously;
recompiling a query may temporarily produce a better execution plan;
reverting a deployment may remove the immediate impact.

These actions can be valid and necessary.

However, when converting the case into reusable knowledge, it is important to distinguish between:

what restored normal operation;
what was confirmed as the contributing condition;
what remained unconfirmed.

This distinction helps prevent a successful recovery action from being interpreted as a complete root-cause explanation.

Include Something Practical

The most useful articles normally provide something the reader can apply.

This may be:

a query;
a script;
a checklist;
a sequence of tests;
a monitoring recommendation;
a comparison table;
a list of questions to ask;
an example of the expected and unexpected results.

Even a short article can be valuable if it gives the reader a practical next step.

For example, a troubleshooting article may suggest comparing:

affected and unaffected periods;
successful and unsuccessful connections;
current and previous execution plans;
CPU, Data IO, and log write utilization;
the original and alternative network paths;
behavior before and after one controlled change.

The practical element is what transforms an explanation into a reusable resource.

Document the Boundaries of the Conclusion

A technical conclusion is more reliable when its limitations are clearly described.

During a support investigation, the available evidence may not allow us to determine every detail.

For example:

the historical telemetry may be limited;
the behavior may not be reproducible;
the exact application request may not be identifiable;
the test environment may differ from production;
an internal implementation detail may not be externally visible.

In these situations, it is useful to explain both what was confirmed and what could not be confirmed.

For example:

The behavior was reproduced only through the affected network path. The same endpoint and authentication method worked successfully through an alternative path. The tests confirmed that the network path was a relevant condition, although the available evidence did not identify the specific component responsible.

This type of conclusion is precise, useful, and transparent.

A Simple Model I Normally Follow

When deciding whether a support investigation can become reusable knowledge, I normally consider the following sequence:

Observe: What behavior was reported or measured?
Clarify: What was the exact scope and impact?
Investigate: Which evidence was relevant?
Reproduce: Could the behavior be tested under controlled conditions?
Validate: Which result supported or challenged the explanation?
Mitigate: What action reduced the immediate impact?
Conclude: What did the available evidence allow us to confirm?
Share: Which part of the investigation may help someone else?

Not every case follows these steps in the same order, and not every investigation provides a complete answer.

However, this approach helps transform an individual technical experience into something that can be understood and reused.

Questions That Help Identify a Lessons Learned Article

Before writing an article, I normally consider questions such as:

Was the behavior unexpected or difficult to interpret?
Could the same question affect other Azure SQL users?
Was there an important difference between the initial assumption and the final conclusion?
Did the investigation produce a useful query, script, or test?
Is there a limitation or condition that should be better understood?
Can the scenario be explained without customer-specific information?
What should another engineer or customer do when facing the same behavior?

If the investigation provides a useful answer to one or more of these questions, it may contain a lesson worth sharing.

Conclusion

After 550 Lessons Learned articles, the most important lesson may be that technical support knowledge should not remain only inside an individual service request. A support case starts with an immediate need: understand the behavior, reduce the impact, and identify the appropriate next action.

However, once the investigation is complete, we have an opportunity to go one step further. We can extract the reusable part of the experience, explain how the conclusion was reached, document its limitations, and provide something practical for the next person facing a similar situation.

That is how an individual support case can become shared technical knowledge. Resolving a case helps one specific situation. Sharing the validated lesson may help many others avoid starting the same investigation from zero.

Lessons Learned #549: Reproduce, Challenge, and Validate – Testing Technical Assumptions

Jose_Manuel_Jurado — Wed, 29 Jul 2026 09:50:09 GMT

During an Azure SQL investigation, the available evidence may point to a technically reasonable explanation.

For example:

A network change may appear to explain a connectivity issue.
High CPU may appear to explain a performance degradation.
A query plan change may appear to explain a longer execution time.
A failover may appear to explain why the behavior disappeared.

These explanations may be correct. However, before considering them confirmed, I normally try to reproduce the behavior and validate the conditions under which it occurs. A reasonable explanation is still a hypothesis until the available evidence supports it consistently.

Reproduce the Smallest Useful Scenario

A reproduction does not always need to recreate the complete production environment. In many cases, a smaller test provides a clearer result.

For a connectivity issue, the test may require only:

one client;
one database endpoint;
the same authentication method;
the relevant network path;
a clearly identified timestamp.

For a performance issue, it may require:

one representative query;
the same parameters;
the relevant database configuration;
a controlled execution period.

The objective is to isolate the behavior being investigated while reducing unnecessary variables. The smaller the scenario, the easier it normally becomes to understand why the result changes.

Define the Expected Result

Before running a test, I try to define what result I expect. For example:

Hypothesis: The connection behavior depends on a specific network path.
Expected Result: The behavior should occur when the connection uses that path and should not occur when an alternative path is used.
Result That Would Challenge the Hypothesis: The same behavior occurs independently of the network path.

The same approach can be used for performance investigations.

Hypothesis: The query slowdown is caused by Data IO saturation.
Expected Result: Query duration should increase when Data IO reaches its service-level limit.
Result That Would Challenge the Hypothesis: The same slowdown occurs while Data IO remains low.

Defining the expected result before the test helps avoid interpreting every outcome as confirmation of the initial explanation.

Change One Variable at a Time

When several conditions are changed simultaneously, it may be difficult to determine which one affected the result.

For example, suppose a performance test includes:

a larger service objective;
updated statistics;
a new index;
lower concurrency.

If performance improves, the result is positive, but it may not clearly identify which change produced the improvement.

A more useful approach is to test each relevant change separately.

Test	Change
Test 1	Original configuration
Test 2	Updated statistics only
Test 3	New index only
Test 4	Higher service objective only

This does not mean that every investigation requires an extensive test matrix.

The objective is simply to avoid changing several important variables at the same time when we need to understand which one explains the behavior.

Compare Where the Behavior Occurs and Where It Does Not

Understanding where a behavior does not occur can be as useful as reproducing where it does.

For example:

Does the connection fail from one application server but succeed from another?
Does the query perform normally with different parameters?
Does the issue occur only through one network path?
Does the previous execution plan perform better?
Does the behavior disappear when concurrency is reduced?

These comparisons help define the boundaries of the problem.

They may not immediately identify the complete root cause, but they help determine which conditions are relevant and which ones are less likely to explain the result.

Challenge the Explanation

Once a test appears to support a hypothesis, I normally try to challenge it. For example, if performance improves after scaling the database, it may be tempting to conclude that CPU was insufficient.

However, scaling may also provide:

more memory;
higher Data IO capacity;
greater transaction-log throughput;
additional workers;
different resource limits.

The improvement is important evidence, but additional information may still be required to identify which resource was actually limiting the workload.

Similarly, if a failover restores normal performance, it may also have:

disconnected blocking sessions;
refreshed application connections;
caused query recompilation;
reset a temporary condition.

The action that restores normal operation may not, by itself, fully explain the original cause.

Document the Result and Its Limitations

Not every test produces a definitive answer.

A useful conclusion should describe:

which conditions were tested;
what result was expected;
what result was observed;
whether the result was repeatable;
which differences from the original scenario remained;
what the evidence did and did not allow us to confirm.

For example:

The behavior was reproduced only when the connection used the affected network path. The same authentication method and database endpoint worked successfully through an alternative path. Based on these tests, the network path was confirmed as a relevant condition. The available evidence did not identify the specific network component responsible for the behavior.

This conclusion is useful because it clearly separates what was validated from what remains unknown.

Conclusion

Reproduction is one of the most valuable troubleshooting tools available to an engineer.

However, its purpose is not simply to make an error occur again.

A useful reproduction should help us understand:

which conditions are required;
which conditions are not relevant;
what result supports the hypothesis;
what result challenges it;
how consistently the behavior can be observed.

The most effective tests are often simple:

reproduce the smallest useful scenario;
define the expected result;
change one variable at a time;
compare affected and unaffected conditions;
document the limitations.

Reproduction does not only confirm that a behavior exists. It helps define the conditions under which the technical conclusion can be trusted.

Lesson Learned #548: From Symptoms to Evidence – How I Approach an Azure SQL Investigation

Jose_Manuel_Jurado — Wed, 29 Jul 2026 09:37:18 GMT

After working on many Azure SQL support cases, I have learned that the initial service request details are essential, although they may not always provide sufficient information to understand the complete technical situation.

When a service request is initially created, the available information may be limited to a brief description such as:

The database is running slowly.
CPU utilization is high.
Connections are failing.
The issue started after a deployment.
Performance improved after a failover.
The workload became slower after a service-tier migration.

These details provide an important starting point for the investigation. However, the initial description may not yet include all the relevant timestamps, metrics, logs, configuration details, or historical context required for a complete technical assessment.

This is completely understandable. At the time the service request is created, the immediate priority is normally to describe the observed behavior and its impact. Additional technical context can then be collected progressively during the investigation.

For that reason, I normally use the initial service request details to understand the reported symptom and determine which additional information may be required. The first objective is to clarify the observed behavior, define the affected scope, and identify the evidence that may help us evaluate the different possible explanations.

In this article, I would like to share the approach I normally follow when moving from an initial service request description to an evidence-based technical conclusion.

Clarifying the Observed Behavior

The first thing I normally try to understand is the exact impact.

For example:

Was the application completely unavailable, or was it slower than usual?
Did the behavior affect all users or only a specific group?
Did it affect all queries or only one process?
Was the issue continuous or intermittent?
Did it affect one database, several databases, or the complete logical server?
Did the problem occur only from a particular network location?
Did existing connections continue working while new connections failed?

These questions may appear simple, but the answers can significantly change the direction of the investigation. For example, the initial service request details may indicate:

Connections to Azure SQL Database are failing. After reviewing the scenario, we may find that:

other applications can connect successfully;
only one application instance is affected;
existing connections continue working;
only new connections are failing;
the issue occurs from one specific network path.

With this additional information, the situation may no longer appear to be a general Azure SQL connectivity issue. The investigation may instead need to focus on areas such as:

application connection pooling;
authentication;
DNS resolution;
token renewal;
network routing;
firewall rules;
a specific application instance;
client-side resource pressure.

Similarly, a service request may indicate that the database is slow. Before looking for the cause, I normally try to understand what “slow” means in that specific situation.

For example:

Are all queries slower?
Is only one stored procedure affected?
Has execution time increased from seconds to minutes?
Is the delay occurring while opening the connection?
Is the delay occurring while executing the command?
Is the application waiting on Azure SQL Database or on another dependency?

Before reviewing metrics, logs, or execution plans, it is important to convert the initial description into a precise and measurable technical symptom.

Distinguishing Observations from Possible Explanations

One of the most useful habits I have developed is to separate what has been observed from what still needs to be validated.

Consider the following statement: The application became slow when database CPU reached 95%.

From this description, we may have two observations:

the application experienced a slowdown;
database CPU reached 95%.

However, the relationship between those two observations still needs to be validated. High CPU may have caused the slowdown, but it could also be part of a larger chain of events. For example, CPU utilization may have increased because:

the application started sending more requests;
blocking caused requests to accumulate;
a query execution plan changed;
application retries generated additional workload;
a scheduled process started running;
concurrency increased;
a query began processing more data;
data distribution changed;
statistics changed;
a maintenance task started.

In other words, high CPU may be the cause of the performance issue, but it may also be the result of another condition. I have seen similar situations after failovers, application restarts, and scaling operations. For example, the available details may indicate: Performance returned to normal after a failover.

The failover is an important part of the investigation, but it may not, by itself, fully explain the original cause.

A failover may also:

disconnect blocking sessions;
cause queries to compile again;
refresh application connections;
reset a temporary condition;
clear some cached state;
coincide with a reduction in workload.

For this reason, I normally try to distinguish between:

what was initially reported;
what has been confirmed by telemetry;
what is currently considered a possible explanation;
what has already been validated.

This distinction helps prevent an early assumption from becoming the final conclusion before sufficient evidence is available.

Building a Timeline

When timestamps and historical information are available, I normally try to build a timeline. A timeline is often one of the most useful parts of an investigation because several events may initially appear related until they are placed in the correct order. Consider the following example:

Time	Event
10:00 UTC	Application deployment completed
10:05 UTC	Active sessions started increasing
10:10 UTC	Query duration increased
10:12 UTC	Data IO reached 100%
10:15 UTC	CPU reached 95%
10:20 UTC	Application timeouts were reported
10:30 UTC	Application service was restarted
10:35 UTC	Session count returned to normal

If the initial service request description mentions only the CPU peak and the application timeouts, CPU may appear to be the most likely starting point.

However, the timeline shows that the number of active sessions and Data IO utilization increased before CPU reached 95%. This does not immediately confirm the root cause, but it changes the questions that should be asked.

For example:

Why did the number of sessions increase?
Did requests start taking longer because of IO pressure?
Did application retries contribute to the workload increase?
Was the CPU peak the initial cause, or was it a consequence of the accumulating workload?
Did the application deployment change request volume or execution patterns?

Depending on the situation, the timeline may include information from:

Azure Monitor metrics;
Query Store;
application logs;
deployment history;
audit events;
scaling operations;
failovers;
configuration changes;
maintenance processes;
network changes.

The objective is not to collect every piece of information available. The objective is to identify the sequence of events that may explain what happened before, during, and after the reported incident.

Using Each Data Source for the Right Question

Another lesson I have learned is that no single source of telemetry normally explains the complete situation.

Different data sources answer different questions.

Azure Monitor

Azure Monitor can help identify whether the database reached limits related to:

CPU;
Data IO;
log write;
sessions;
workers;
storage;
connection failures;
deadlocks.

These metrics are very useful for identifying when resource pressure occurred. However, a database-level metric may not directly identify the query, application, or operation responsible for that resource usage.

Query Store

Query Store may help identify:

changes in query duration;
increased CPU consumption;
changes in execution count;
increased logical reads;
execution-plan changes;
query regressions.

It can be especially useful when a database-level metric needs to be correlated with specific query activity.

Dynamic Management Views

Dynamic Management Views may provide information about:

active requests;
waits;
blocking;
sessions;
open transactions;
memory grants;
current resource consumption.

This information is especially valuable while the issue is occurring.

However, some of the data may no longer be available after the event has ended.

Application Logs

Application logs may help identify:

connection timeouts;
command duration;
retry behavior;
connection-pool exhaustion;
authentication errors;
dependency failures;
changes in request volume.

These logs often provide context that may not be visible from the database side.

Deployment and Configuration History

Deployment and configuration history may help explain why the behavior began at a particular time.

This may include:

application releases;
schema changes;
index operations;
compatibility-level changes;
connection-string updates;
service-tier changes;
network changes;
security configuration changes.

The important point is to choose the evidence according to the question being investigated.

For example, if I am investigating whether a workload is limited by Data IO rather than CPU, reviewing only CPU percentage may not provide sufficient information.

I would also want to understand:

when Data IO reached its limit;
whether query duration increased during the same period;
which queries generated the highest number of reads;
whether the workload started reading more data;
whether the same degradation occurred when Data IO remained below its limit;
whether additional IO capacity changed the result.

The investigation becomes more effective when every piece of telemetry is connected to a specific technical question.

Collecting more data does not automatically produce a better conclusion.

Keeping More Than One Possible Explanation Open

It is easy to identify one technically reasonable explanation and begin searching only for evidence that supports it. I normally try to avoid doing this, especially during the first stages of the investigation. For example, imagine that a database is migrated from a DTU-based service tier to a vCore-based service tier and the workload subsequently performs more slowly.

One possible explanation is that the new environment does not provide sufficient CPU.

However, other possibilities may include:

lower Data IO capacity;
lower transaction-log throughput;
different memory availability;
an execution-plan change;
statistics changes;
a compatibility-level difference;
increased concurrency;
a change in request volume;
a change in data size or distribution;
an application change unrelated to the migration.

Each possible explanation requires different evidence. If CPU remains moderate while Data IO repeatedly reaches its limit, increasing the number of vCores without reviewing the storage characteristics may not address the main constraint.

If Query Store shows a plan regression immediately after the migration, the selected service tier may not be the primary cause. If execution count doubled after an application deployment, the database may simply be processing more work than before. Keeping several possible explanations open does not make the investigation less decisive. It reduces the risk of reaching a conclusion before the relevant evidence has been reviewed.

Defining What Would Challenge the Hypothesis

One of the most useful questions I ask during troubleshooting is: What result would demonstrate that my current hypothesis may not be correct?

Suppose the current hypothesis is: The performance degradation is caused by Data IO saturation.

Evidence supporting this hypothesis may include:

Data IO reaches 100% during the affected periods;
query duration increases at the same time;
the affected queries perform a high number of physical reads;
CPU remains below its limit;
performance improves when the workload runs with additional IO capacity.

However, I should also look for results that may challenge the hypothesis.

For example:

Does the same slowdown occur while Data IO remains low?
Are there periods with high Data IO but normal application performance?
Are queries with very few reads also affected?
Does additional IO capacity consistently improve the workload?
Does changing another variable produce a larger improvement?
Does the issue occur in an environment where the same IO pressure is not present?

If an investigation only searches for supporting evidence, almost any initial theory may appear correct.

Actively looking for evidence that challenges the current explanation makes the final conclusion stronger and more reliable.

Conclusion

After working on many support investigations, one of the most important lessons I have learned is that the initial service request details and the final technical conclusion naturally serve different purposes. The initial description provides the first available information about the observed behavior and its impact. At that stage, some of the relevant logs, metrics, timestamps, configuration details, or historical context may not yet be available.

A reliable investigation begins with the initial service request details and develops progressively as additional evidence and technical context become available.

Working Around the Lack of Partition Switching in Microsoft Fabric SQL Database

hudajazmawi — Wed, 29 Jul 2026 06:16:46 GMT

Overview

One of the current limitations of SQL Database in Microsoft Fabric is the lack of support for partition switching.

For DBA who use partition switching in SQL Server or Azure SQL Database to implement sliding-window retention patterns, archival processes, or large-scale data cleanup operations, this limitation can create challenges when migrating workloads to Fabric.

This article describes a practical approach that significantly improved delete performance in a real-world scenario where partition switching was not available.

The Challenge

A customer needed to remove a subset of data from a large table based on a specific business filter (filter_id= 2).

In traditional SQL Server environments, partition switching is often used for this type of workload because it is a metadata-only operation and can move or remove large volumes of data almost instantly.

However, SQL Database in Microsoft Fabric currently does not support partition switching, as documented in the Microsoft Learn article below:

Limitations for SQL database - Microsoft Fabric

Switch partition is a limitation in SQL Database in Fabric

As a result, the customer had to rely on a standard DELETE operation.

Symptoms

Although only a relatively small number of rows were being removed, the delete operation consistently required more than an hour to complete.

Investigation showed that the issue was not caused by the delete predicate itself. Instead, the target table was referenced by multiple foreign key relationships.

During the delete operation, SQL had to validate referential integrity across all dependent tables, introducing significant overhead and causing the query to run much longer than expected.

Root Cause

The performance degradation was due to the presence of multiple foreign key constraints referencing the table, which required validation during the DELETE operation. See this picture for the FKs tables:

This resulted in:

Row-by-row constraint validation across multiple dependent tables
Additional overhead during data modification operations
Increased execution time for DELETE queries.

Alternative Approach

Since partition switching was unavailable, the following strategy was used:

Temporarily disable or remove foreign key constraints on dependent tables. We used this command:

-- Disable all FK constraints referencing the table ALTER TABLE dbo.ChildTable1 NOCHECK CONSTRAINT FK_ChildTable1_sales_orders; -- ... repeat for all the constraints (Foreign Keys)
Delete related data from child tables first.

-- Perform DELETE DELETE FROM dbo.ChildTable1 WHERE order_id = 2;
Execute the delete operation on the parent table.

-- Perform DELETE DELETE FROM dbo.SalesOrders WHERE order_id = 2;
Re-enable and validate the foreign key constraints.

-- Re-enable constraints ALTER TABLE dbo.ChildTable1 WITH CHECK CHECK CONSTRAINT FK_ChildTable1_sales_orders;

This approach reduced the amount of referential integrity work performed during the delete process.

Results

After the foreign key dependencies were removed from the execution path, the delete operation completed in seconds instead of more than an hour.

While this approach does not provide the same metadata-only efficiency as partition switching, it can be an effective workaround for large data cleanup operations in Fabric SQL Database when foreign key enforcement becomes the primary bottleneck.

Key Takeaways

Partition switching is currently not supported in SQL Database in Microsoft Fabric.
Workloads that rely on sliding-window retention patterns may require alternative implementation strategies.
Foreign key constraints can have a significant impact on delete performance.
For controlled maintenance activities, temporarily disabling or dropping foreign key constraints, performing the cleanup, and then restoring and validating the constraints may dramatically improve performance.
Always validate the impact on application behavior and data integrity before implementing this approach in production.

Conclusion

The absence of partition switching in SQL Database in Microsoft Fabric does not prevent large-scale data cleanup operations, but it may require a different design approach. By identifying foreign key validation as the true performance bottleneck and restructuring the delete process accordingly, substantial performance improvements can often be achieved while maintaining data integrity.

This can serve as a practical alternative until native partition switching support becomes available in Fabric SQL Database.

Enabling DB Auditing in Azure SQL DB hangs for a long time

Tancy — Wed, 29 Jul 2026 06:13:36 GMT

Issue

An issue was brought to our attention recently where an azure SQL DB was generating an error when the customer was trying to enable database level Auditing. The first error was observed when the customer was trying to enable auding with a storage location as the destination. Here’s the error message he received:

The portal would just hang (For hours) while saving the enable Audit related changes:

The customer experience was the same even while enabling the sql server auditing via portal. The portal would hang in a similar manner as shown above.

The Second issue reported was that after fixing the storage issue reported above, when the customer was trying to enable database level Auditing using their Devops tools, while Auditing was successfully enabled, the audit files (**.xel) created in the storage location (Audit folder) were empty. This is despite the customer trying DML events to trigger the Auditing to capture the events. Here’s how the storage location looked:

No Audit logs were observed even at the portal:

Error

Here’s the first error message while cx tried enabling Auditing using a DevOps tool:

The resource write operation failed to complete successfully, because it reached terminal provisioning state 'Failed'.\n\tCode: ResourceDeploymentFailure\n\tMessage: The resource write operation failed to complete successfully, because it reached terminal provisioning state 'Failed'.\n\tTarget: Exception Details: (InternalServerError) An unexpected error occured while processing the request. Tracking ID: '227365fe-a3cc-4ea8-b097-014fe57584c5' Code: InternalServerError Message: An unexpected error occured while processing the request. Tracking ID: '227365fe-a3cc-4ea8-b097-014fe57584c5'

Workaround/Fix:

To fix the first issue on the Azure portal hanging while saving the DB audit settings, after in-depth investigations, we recommended the customer, When configuring immutable blob storage for auditing, ensure that Allow protected append writes is set to either Append blobs or Block and append blobs. The None option isn't supported. We had noticed that this setting was set to None initially.

To enable it to true:

Azure portal -> storage account page -> search for containers -> choose sqldauditlogs container -> access policy blade -> edit immutable blob storage policy and select the value of ""Allow protected append writes to" to Append blobs.

Here’s a snapshot below:

Our public documents clearly state that: When configuring immutable blob storage for auditing, ensure that Allow protected append writes is set to either Append blobs or Block and append blobs. The None option isn't supported. For time-based retention policies, the storage account's retention interval must be shorter than the SQL Auditing retention setting. Configurations where the storage policy is set, but SQL Auditing retention is 0, aren't supported.

Workaround for 2^nd issue ( Auditing not capturing events)

We analyzed the json file of the devops tool shared by the customer and found the following settings:

From the audit setting shown above, we can see that the end customer has used the predicate expression

"predicateExpression":"object_name = 'SensitiveData'

in the audit configuration.

This means that the operations that object SensitiveData will only be captured in the audit records.
The operations on other tables/objects will not be captured.

Once the customer made changes to the json file/Auditing configuration, the auditing started capturing events as expected.

The customer also understood both the scenarios involved in this troubleshooting exercise.

References

Auditing - Azure SQL Database and Azure Synapse Analytics | Microsoft Learn

Overview of immutable storage for blob data - Azure Storage | Microsoft Learn

Extended Server Blob Auditing Policies - Create Or Update - REST API (Azure SQL Database) | Microsoft Learn

Manage access to Log Analytics workspaces - Azure Monitor | Microsoft Learn

SQL Data Sync Retirement: Migration Insights and Modern Alternatives

Karunakar_Kotha — Sat, 25 Jul 2026 00:46:49 GMT

What started as a routine customer discussion quickly evolved into a strategic modernization conversation. A service that had quietly synchronized business-critical data for years was approaching retirement, prompting an important question: What should organizations do next?

Every service retirement is an opportunity to reassess architecture, reduce technical debt, and build for the future.

When a Retirement Notification Becomes a Business Conversation

Recently, while working with a customer, we reviewed their Azure SQL Database architecture and discovered a critical dependency on SQL Data Sync

For years, the service had reliably synchronized data across multiple databases, enabling applications, reporting workloads, and distributed business processes.

Like many organizations, the customer viewed Data Sync as infrastructure that simply worked in the background. However, the discussion took a different turn when we reviewed Microsoft's retirement announcement:

Azure SQL Data Sync will be retired on September 30, 2027.

What initially appeared to be a migration challenge quickly became an opportunity to modernize the customer's data movement architecture and align with Microsoft's future investments in data integration, analytics, and cloud-native services.

Understanding SQL Data Sync

Azure SQL Data Sync was designed to synchronize selected data between Azure SQL Databases and, in some cases, between Azure and on-premises databases.

Organizations have commonly used Data Sync for:

Hybrid data synchronization
Distributed application architectures
Globally distributed applications
Bi-directional data synchronization

While the service has served customers well, organizations should begin evaluating alternative solutions now to ensure sufficient planning, testing, and adoption time before retirement.

Because both databases were already in Azure, the discussion quickly moved toward identifying strategic alternatives.

Customer's Setup

This particular customer had a simple, familiar layout: one Azure SQL Database feeding another, both fully in Azure, connected by SQL Data Sync. No on-premises leg, no complicated topology — just two cloud databases that needed to stay aligned.

Their requirements were equally straightforward, and honestly, the kind every team asks for:

Reliable, dependable synchronization
Low operational overhead — nobody wanted a new system to babysit
Something with a real future, not another service on a retirement countdown
Room to scale as data volumes grow
An Azure-native fit, not a bolt-on third-party tool

Because both databases already lived in Azure, the conversation moved quickly toward the platform's own native tooling — starting with the option that ended up being the strongest fit.

Option 1: Azure Data Factory (Recommended Strategy)

For customers running Azure SQL Database to Azure SQL Database synchronization, Azure Data Factory (ADF) emerged as the strongest strategic recommendation.

Why ADF?

Azure Data Factory provides:

Fully managed Azure-native data movement
Enterprise-grade monitoring
Flexible orchestration
Scalability from development through production environments
Long-term Microsoft investment and support

The migration pattern we typically recommend looks like:

Phase1 :Initial Full Load

Before anything can stay in sync, both sides need to start from the same place. Phase 1 is a one-time bulk copy: Azure Data Factory reads everything from the source database and writes it into the target, establishing a clean baseline.

Phase 1: Initial Full Load, establishing baseline parity between Database A and Database B

Phase2 :Incremental Synchronization

Once both databases match, you don't need to keep copying everything — just what's changed. This is where Change Tracking (CT) or Change Data Capture (CDC) comes in: SQL Server-native features that flag which rows were inserted, updated, or deleted since the last run. ADF's incremental pipeline reads only those deltas and applies them downstream, on whatever schedule the business needs — minutes, hours, or daily.

Phase 2: Incremental Synchronization using Change Tracking / Change Data Capture, running on a repeating schedule

Phase 3: Scheduling and Monitoring

Once both phases are live, ADF takes over the operational side: scheduling pipeline runs, monitoring their health, retrying failures automatically, and alerting your team when something needs attention. That's a level of visibility SQL Data Sync's built-in sync groups never really offered

ADF handles:

Scheduling
Pipeline execution
Monitoring
Retry mechanisms
Alerting

This model often delivers greater visibility and operational control than traditional SQL Data Sync implementations.

When You Don't Need Synchronization — You Need a Copy

Partway through the engagement, one question reframed the whole discussion: do we actually need two-way synchronization, or do we just need a readable copy of the database somewhere else? That distinction matters more than it sounds, and it points to three other options worth knowing.

Option 1: Active Geo-Replication

If the goal is disaster recovery, serving read traffic closer to users, or keeping the business running through a regional outage, Active Geo-Replication is usually a better fit than rebuilding sync logic from scratch. It gives you a continuously updated, readable secondary — not a bi-directional sync target.

Best fit

Disaster recovery scenarios
Read-intensive applications
Global user distribution
Secondary readable databases

Less ideal for

Complex data transformations
Bi-directional updates

Option 2: Database Copies and Read Replicas

Some organizations do not require continuous synchronization at all.

In those cases:

Read Replicas
Database Copy

Good Use Cases

Reporting databases
Analytics environments
Refreshable staging systems
Read-only workloads

This approach significantly reduces architectural complexity while still meeting many business requirements.

Option 3: Microsoft Fabric Mirrored Databases

As Microsoft Fabric adoption grows, another interesting alternative is Fabric Mirrored Databases.

This option is particularly attractive for organizations already investing in:

Microsoft Fabric
OneLake
Real-time analytics
AI and data platform modernization

Benefits include:

Near real-time data availability
Simplified analytics architecture
Integration with Fabric workloads
Reduced data silos

For customers modernizing both operational and analytical platforms, this can be an excellent opportunity to rethink data architecture beyond simple synchronization.

Option 4: Azure Functions for Event-Driven Synchronization

Not every customer requires a large orchestration platform.

For lightweight or application-specific synchronization logic, Azure Functions may offer a more agile approach.

Example Use Cases

Event-driven updates
Custom business rules
Microservices architectures
Low-volume synchronization requirements

The tradeoff is that customers assume additional development and operational responsibilities

Lessons Learned from the Customer Engagement

This engagement reinforced several important lessons:

Don't Wait Until 2027

Although retirement is over a year away, large organizations often require significant planning, testing, governance approvals, and deployment cycles.

Starting early reduces risk.

There Is No Universal Replacement

The right solution depends on:

Latency requirements
Read versus write workloads
Synchronization direction
Operational complexity
DR requirements
Budget limitation

Different use cases require different migration paths.
3. Migration Is an Opportunity

Rather than simply replacing SQL Data Sync, organizations should evaluate:

Data architecture modernization
Observability improvements
Operational simplification
Fabric adoption opportunities
Long-term cloud strategy

Final Recommendations

For most customers currently using Azure SQL Database → Azure SQL Database synchronization:

Requirement	Recommended Solution
Ongoing synchronization	Azure Data Factory + CDC/Change Tracking
Read-only replica	Active Geo-Replication
Simple duplication	Database Copy or Read Replica
Analytics modernization	Fabric Mirrored Databases
Event-driven custom logic	Azure Functions

In my customer's use case, Azure Data Factory with incremental changes (CDC) emerged as the preferred strategic path because it was Azure-native, scalable, supported long-term, and aligned with Microsoft's future direction for data movement and integration.

Closing Thoughts

Technology retirements often create urgency, but they also create opportunity. retirement of SQL Data Sync is not merely a migration project. It is an opportunity to reassess data movement architecture, improve resiliency, reduce technical debt, and embrace modern Azure-native services.

If your organization is currently using SQL Data Sync, now is the right time to inventory your sync groups, identify dependencies, and begin evaluating alternative architectures before September 30, 2027.

References

Lessons Learned #547:Some SQL DB Statistics Remain Outdated While Others Are Automatically Updated

Jose_Manuel_Jurado — Thu, 23 Jul 2026 09:47:54 GMT

During the analysis of a SQL Server performance case, we observed an interesting statistics update pattern on a large table.

Several statistics had recently been updated at different times, while a group of automatically created _WA_Sys_ statistics still showed:

An older last_updated date.
A high modification_counter.
A row count significantly lower than the current number of rows in the table.

At first glance, this could suggest that AUTO_UPDATE_STATISTICS was not working correctly. However, a closer review showed that this pattern can be completely consistent with the expected behavior of SQL Server.

1. Types of statistics in SQL Server

SQL Server can maintain several types of statistics.

Automatically created column statistics

When AUTO_CREATE_STATISTICS is enabled, SQL Server can automatically create a single-column statistic when the Query Optimizer needs cardinality information for a column used in a query predicate. These statistics normally use names such as: _WA_Sys_00000002_47A6D5E5

The name can be interpreted as: _WA_Sys_<column_id in hexadecimal>_<object_id in hexadecimal>

For example: 00000002 hexadecimal = column_id 2 47A6D5E5 hexadecimal = table object_id

The most reliable way to identify the associated column is not to decode the name manually, but to query the SQL Server catalog views:

DECLARE @TableName sysname = N'dbo.CustomerTransactions'; SELECT s.stats_id, s.name AS statistics_name, sc.stats_column_id, c.column_id, c.name AS column_name, s.auto_created, s.user_created, s.no_recompute FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id INNER JOIN sys.columns AS c ON c.object_id = sc.object_id AND c.column_id = sc.column_id WHERE s.object_id = OBJECT_ID(@TableName) AND s.name = N'_WA_Sys_00000002_47A6D5E5' ORDER BY sc.stats_column_id;

Statistics associated with indexes

When SQL Server creates an index, it also creates a statistics object associated with the index. For example: CREATE INDEX IX_CustomerTransactions_ClientId ON dbo.CustomerTransactions(ClientId) this creates an index statistics object normally named: IX_CustomerTransactions_ClientId when a statistics object corresponds to an index, its stats_id matches the index's index_id.

User-created statistics

Statistics can also be created explicitly: CREATE STATISTICS ST_CustomerTransactions_ClientId_Status ON dbo.CustomerTransactions ( ClientId, StatusId ); This object is identified in sys.stats with: user_created = 1 auto_created = 0 and AUTO_UPDATE_STATISTICS applies to index statistics, automatically created single-column statistics, manually created statistics and filtered statistics.

2. Statistics are not updated together

Assume that a table has these statistics:

_WA_Sys_00000002_xxxxxxxx for ClientId
_WA_Sys_00000003_xxxxxxxx for StatusId
IX_CustomerTransactions_CreatedDate for CreatedDate PK_CustomerTransactions and TransactionId

After a large data load, several or all of them could become eligible for an automatic update. However, SQL Server does not immediately update all eligible statistics. Before compiling a query, the Query Optimizer identifies the statistics that could be relevant to the query predicates and checks whether those statistics are outdated.

Consider this query:

SELECT TransactionId, ClientId, Amount FROM dbo.CustomerTransactions WHERE ClientId = 100;

The optimizer might need a histogram on ClientId. If the corresponding statistics object is outdated and has crossed its update threshold, SQL Server may update that specific statistics object.

It does not need to update unrelated statistics on:

StatusId
CreatedDate

As a result, statistics on the same table can legitimately have different update times:

PK_CustomerTransactions 2026-07-22 10:53
IX_CustomerTransactions_ClientId 2026-07-22 10:50
IX_CustomerTransactions_CreatedDate 2026-07-22 10:13
_WA_Sys_00000003_47A6D5E5 2026-07-19 10:00

This pattern can be evidence of demand-driven automatic updates rather than evidence of a malfunction.

3. What does modification_counter represent?

The modification_counter returned by sys.dm_db_stats_properties represents the number of modifications made to the leading statistics column since that statistics object was last updated. This definition is especially important for multicolumn statistics.

For example: CREATE INDEX IX_CustomerTransactions_ClientId_Status ON dbo.CustomerTransactions ( ClientId, StatusId );

The associated statistics object contains:

Histogram: ClientId
Density information: ClientId ClientId, StatusId

The histogram and modification_counter are based on the leading column, ClientId.

If only StatusId is modified: UPDATE dbo.CustomerTransactions SET StatusId = 2 WHERE TransactionId = 100; this does not have the same statistics impact as changing ClientId, because ClientId is the leading histogram column.

SQL Server statistics contain only one histogram, built on the first key column. Multicolumn statistics additionally contain density information for column prefixes.

4. Why do multiple statistics sometimes have similar modification counters?

This commonly happens after an insert operation.

For example:

INSERT INTO dbo.CustomerTransactions ( TransactionId, ClientId, StatusId, Amount, CreatedDate ) SELECT TransactionId, ClientId, StatusId, Amount, CreatedDate FROM dbo.StagingCustomerTransactions;

Each inserted row introduces a value for every populated column. Consequently, multiple single-column statistics may show similar increases in their modification counters. This is particularly visible after a large ETL operation: Table rows before the load: 487,673 Rows inserted by the ETL: 515,244 Current approximate row count: 1,002,917

Several statistics could then show modification counters close to the number of inserted rows. That does not mean SQL Server must update all those statistics immediately. They become candidates for updating, but an update is normally triggered when query optimization requires them.

5. Does automatic updating apply only to _WA_Sys_ statistics?

AUTO_UPDATE_STATISTICS applies to:

Automatically created _WA_Sys statistics
Index statistics Primary-key index statistics
User-created statistics

Each statistics object is evaluated independently. Therefore, SQL Server might update: PK_CustomerTransactions while leaving this object unchanged: _WA_Sys_00000003_47A6D5E5

The reverse is also possible. The behavior depends on which statistics are considered relevant during compilation or cached-plan validation.

6. What happens when a _WA_Sys_ statistic and an index statistic cover the same column?

This is one of the most interesting scenarios. Assume SQL Server originally created: _WA_Sys_00000002_xxxxxxxx for ClientId.

Later, someone creates this index: CREATE INDEX IX_CustomerTransactions_ClientId ON dbo.CustomerTransactions(ClientId);

The table now has two statistics objects with histograms on ClientId: _WA_Sys_00000002_xxxxxxxx and IX_CustomerTransactions_ClientId

Conceptually: _WA_Sys statistic Histogram on ClientId Index statistic Histogram on ClientId

For a query such as: SELECT * FROM dbo.CustomerTransactions WHERE ClientId = @ClientId; the optimizer can have more than one potentially relevant statistics object. It may rely on the index statistics object rather than the older _WA_Sys_ object.

In that case:

IX_CustomerTransactions_ClientId Updated recently
_WA_Sys_00000002_xxxxxxxx
- Old last_updated value
- High modification_counter

This does not necessarily mean that automatic statistics updating has failed. It can mean that the _WA_Sys_ statistic has become redundant and has not been required by recent compilations.

However, this should be presented carefully: The Query Optimizer is not publicly documented as always preferring an index statistic over an equivalent _WA_Sys_ statistic.

The statistics selected can depend on:

Query.
Predicates.
Available indexes.
Filtered versus unfiltered statistics.
Statistics freshness.
Sampling quality.
Multicolumn density information.
Cardinality Estimator behavior.
Existing cached plans.

The correct conclusion is that the scenario is possible and plausible, but the execution plan should be inspected before claiming that a specific statistics object was used.

As we wrote down in multiple articles in our blog (below), identify redudant statistics is part of DBA work to avoid this situation, also, in other situations, I saw that the maintenance plan is taking too much time because we are updating statistics that we are not using or migth be duplicated. The following script identifies statistics whose leading columns overlap:

DECLARE @TableName sysname = N'dbo.CustomerTransactions'; ;WITH LeadingStatisticsColumns AS ( SELECT s.object_id, s.stats_id, s.name AS statistics_name, s.auto_created, s.user_created, s.no_recompute, s.has_filter, s.filter_definition, sc.column_id, c.name AS leading_column, i.index_id, i.name AS index_name FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id AND sc.stats_column_id = 1 INNER JOIN sys.columns AS c ON c.object_id = sc.object_id AND c.column_id = sc.column_id LEFT JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.stats_id WHERE s.object_id = OBJECT_ID(@TableName) ) SELECT leading_column, statistics_name, CASE WHEN index_id IS NOT NULL THEN N'INDEX STATISTICS' WHEN auto_created = 1 THEN N'AUTO-CREATED _WA_SYS' WHEN user_created = 1 THEN N'USER-CREATED STATISTICS' ELSE N'OTHER' END AS statistics_type, index_name, has_filter, filter_definition, no_recompute, COUNT(*) OVER ( PARTITION BY column_id ) AS statistics_on_same_leading_column FROM LeadingStatisticsColumns ORDER BY leading_column, statistics_type, statistics_name;

This does not automatically mean that one object should be deleted. It only identifies an overlap.

7. Script

The following example demonstrates how an automatically created statistic can coexist with a later index statistic.

DROP TABLE IF EXISTS dbo.CustomerTransactions; GO CREATE TABLE dbo.CustomerTransactions ( TransactionId int NOT NULL, ClientId int NOT NULL, StatusId tinyint NOT NULL, Amount decimal(12,2) NOT NULL, CreatedDate datetime2(0) NOT NULL, CONSTRAINT PK_CustomerTransactions PRIMARY KEY CLUSTERED (TransactionId) ); GO

Insert sample data

;WITH Numbers AS ( SELECT TOP (200000) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) ) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b ) INSERT INTO dbo.CustomerTransactions ( TransactionId, ClientId, StatusId, Amount, CreatedDate ) SELECT n, n % 5000, n % 5, CONVERT(decimal(12,2), n % 10000), DATEADD ( minute, -(n % 100000), SYSUTCDATETIME() ) FROM Numbers; GO

Ensure that automatic statistics creation and updating are enable

ALTER DATABASE CURRENT SET AUTO_CREATE_STATISTICS ON; GO ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS ON; GO

Trigger automatic statistics creation on ClientId

SELECT COUNT_BIG(*) FROM dbo.CustomerTransactions WHERE ClientId = 100 OPTION (RECOMPILE); GO

Check the statistics created for ClientId

SELECT s.stats_id, s.name AS statistics_name, s.auto_created, s.user_created, c.name AS column_name FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id INNER JOIN sys.columns AS c ON c.object_id = sc.object_id AND c.column_id = sc.column_id WHERE s.object_id = OBJECT_ID(N'dbo.CustomerTransactions') AND c.name = N'ClientId' ORDER BY s.stats_id;

Create an index on the same column

CREATE INDEX IX_CustomerTransactions_ClientId ON dbo.CustomerTransactions(ClientId);

The table can now have:

Modify the data significantly

UPDATE dbo.CustomerTransactions SET ClientId = ClientId + 10000 WHERE TransactionId <= 100000;

Review the counters again

SELECT s.name AS statistics_name, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter FROM sys.stats AS s OUTER APPLY sys.dm_db_stats_properties ( s.object_id, s.stats_id ) AS sp WHERE s.object_id = OBJECT_ID(N'dbo.CustomerTransactions') ORDER BY s.stats_id

Force a new compilation using ClientId

SET STATISTICS XML ON; GO SELECT COUNT_BIG(*) FROM dbo.CustomerTransactions WHERE ClientId = 10100 OPTION (RECOMPILE); GO SET STATISTICS XML OFF; GO

After the query, review:

The actual execution plan XML.
The StatisticsInfo elements.
last_updated.
modification_counter.

The exact object updated is an optimizer decision and can vary by SQL Server version, build, compatibility level and query shape. The test should therefore be used to observe the behavior rather than to assume a fixed preference.

Finally, as you could see SQL Server choose _WA_Sys_00000002_151102AD instead of IX_CustomerTransactions_ClientId to update. In some situations, depending on execution plan, SQL Server might choose IX_CustomerTransactions_ClientId to update instead of _WA_Sys_00000002_151102AD and for this reason, doesn't mean that SQL Server is not updating the statistics it is depending that it is choosing one of them that the column is involved.

My lessons learned, a statistic can be outdated without being relevant, and it can be relevant without being the only available source of cardinality information. Before interpreting an old last_updated value as an automatic statistics failure, identify the leading column, look for overlapping statistics, inspect the execution plan and determine whether there is a real estimation or performance problem.

Articles:

Lesson Learned #482: Identifying Potential Duplicate Statistics | Microsoft Community Hub

Lesson Learned #324: Query Recompilation in Azure SQL | Microsoft Community Hub

Lessons Learned #537: Copilot Prompts for Troubleshooting on Azure SQL Database | Microsoft Community Hub

Lesson Learned #498:Understanding the Role of STATMAN in SQL Server and Its Resource Consumption | Microsoft Community Hub

Disclaimer:

The scripts included in this article are provided for demonstration and educational purposes only. They create a sample table, insert a significant number of rows, create indexes and statistics, modify data, and change automatic statistics settings in the current database.

Run the complete demonstration only in a test or non-production environment. Review and adapt the database name, object names, row volume, and statements before execution.

The results may vary depending on the SQL Server version, database compatibility level, existing configuration, data distribution, and workload. Always test the scripts in a representative environment before applying any conclusion or change to a production system.

Lessons Learned #546: Maintaining a Local Azure Resource Inventory

Jose_Manuel_Jurado — Tue, 21 Jul 2026 09:07:08 GMT

I worked on a service request that our customer has an application works repeatedly with the same Azure resources. I guess that it may be useful to maintain our own persistent inventory instead of retrieving and validating every resource individually during each execution.

In this example, a PowerShell script maintains an inventory of Azure SQL logical servers in a local JSON file.

The idea is:

Load the local JSON inventory -> Query Azure Resource Graph -> Compare both inventories -> Validate only detected changes -> Update the JSON file.
Azure Resource Graph provides the current list of Microsoft.Sql/servers resources. The result is compared with the inventory stored by the application.
If a server exists in both inventories, it is marked as:
- Observed: No additional request is required.
- NewValidatedByPointGet:If a new server is detected, it is validated individually with Get-AzSqlServer before being added
- If a previously known server is missing from the current result, the script also validates it individually. The possible results are:
  - - RecoveredByPointGet: the server still exists and remains in the inventory.
    - Deleted: the individual request returns ResourceNotFound, so the server is removed.
    - UnknownRetainedFromCache: the validation is inconclusive, so the previous inventory entry is preserved.

The JSON file therefore represents the application’s active resource inventory and remains available between executions. I think this approach reduces repeated API requests because individual validation is performed only when a resource is new, missing, or has changed.

I would like to share the PowerShell Script.

Set-StrictMode -Version Latest $ErrorActionPreference = "Stop" # ------------------------------------------------------------ # Configuration # ------------------------------------------------------------ $tenantId = "<tenant-id>" $subscriptionId = "<subscription-id>" $resourceGroup = "<resource-group>" $cacheFile = ".\sql-server-inventory.json" # Required modules: # Install-Module Az.ResourceGraph -Scope CurrentUser # Install-Module Az.Sql -Scope CurrentUser # Connect-AzAccount -Tenant $tenantId Set-AzContext ` -Tenant $tenantId ` -Subscription $subscriptionId ` -ErrorAction Stop | Out-Null # ------------------------------------------------------------ # Helper functions # ------------------------------------------------------------ function ConvertTo-NormalizedResourceId { param( [Parameter(Mandatory)] [string]$ResourceId ) return $ResourceId.Trim().TrimEnd("/").ToLowerInvariant() } function Test-IsResourceNotFound { param( [Parameter(Mandatory)] [System.Management.Automation.ErrorRecord]$ErrorRecord ) $errorText = @( $ErrorRecord.Exception.Message $ErrorRecord.ErrorDetails.Message $ErrorRecord.FullyQualifiedErrorId $ErrorRecord.ToString() ) -join " " return ( $errorText -match "(?i)(\b404\b|ResourceNotFound|ServerNotInSubscriptionResourceGroup)" ) } function New-InventoryItem { param( [Parameter(Mandatory)] [string]$ResourceId, [Parameter(Mandatory)] [string]$Name, [string]$Location, [Parameter(Mandatory)] [string]$ValidationStatus ) return [pscustomobject]@{ id = $ResourceId name = $Name location = $Location validationStatus = $ValidationStatus } } # ------------------------------------------------------------ # 1. Load the persistent inventory # ------------------------------------------------------------ if (Test-Path -LiteralPath $cacheFile) { $jsonContent = Get-Content ` -LiteralPath $cacheFile ` -Raw ` -ErrorAction Stop if ([string]::IsNullOrWhiteSpace($jsonContent)) { $cachedServers = @() } else { $cachedServers = @( $jsonContent | ConvertFrom-Json ` -ErrorAction Stop ) } } else { $cachedServers = @() } $cachedServersById = @{} foreach ($cachedServer in $cachedServers) { $resourceId = [string]$cachedServer.id if ([string]::IsNullOrWhiteSpace($resourceId)) { continue } $normalizedId = ConvertTo-NormalizedResourceId ` -ResourceId $resourceId $cachedServersById[$normalizedId] = $cachedServer } Write-Host "Stored inventory: $($cachedServersById.Count) server(s)" # ------------------------------------------------------------ # 2. Discover the current resources # ------------------------------------------------------------ $query = @" Resources | where subscriptionId =~ '$subscriptionId' | where resourceGroup =~ '$resourceGroup' | where type =~ 'microsoft.sql/servers' | project id = tostring(id), name = tostring(name), location = tostring(location) "@ try { $argResponse = Search-AzGraph ` -Query $query ` -Subscription $subscriptionId ` -First 1000 ` -ErrorAction Stop } catch { throw ( "Azure Resource Graph query failed. " + "The existing inventory has not been modified. " + "Error: $($_.Exception.Message)" ) } if ( $null -ne $argResponse -and $argResponse.PSObject.Properties.Name -contains "Data" ) { $currentServers = @($argResponse.Data) } else { $currentServers = @($argResponse) } $currentServersById = @{} foreach ($currentServer in $currentServers) { $resourceId = [string]$currentServer.id $serverName = [string]$currentServer.name if ( [string]::IsNullOrWhiteSpace($resourceId) -or [string]::IsNullOrWhiteSpace($serverName) ) { continue } $normalizedId = ConvertTo-NormalizedResourceId ` -ResourceId $resourceId $currentServersById[$normalizedId] = $currentServer } Write-Host "Current observation: $($currentServersById.Count) server(s)" # ------------------------------------------------------------ # 3. Build the synchronized active inventory # ------------------------------------------------------------ $activeInventoryById = @() $activeInventoryIndex = @{} $deletedServers = @() foreach ($normalizedId in $currentServersById.Keys) { $currentServer = $currentServersById[$normalizedId] $resourceId = [string]$currentServer.id $serverName = [string]$currentServer.name $location = [string]$currentServer.location if ($cachedServersById.ContainsKey($normalizedId)) { # The resource is present in both inventories. $item = New-InventoryItem ` -ResourceId $resourceId ` -Name $serverName ` -Location $location ` -ValidationStatus "Observed" $activeInventoryIndex[$normalizedId] = $item continue } # The resource is new. Validate it individually. Write-Host "Validating new server '$serverName'..." try { $validatedServer = Get-AzSqlServer ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -ErrorAction Stop $item = New-InventoryItem ` -ResourceId $resourceId ` -Name ([string]$validatedServer.ServerName) ` -Location ([string]$validatedServer.Location) ` -ValidationStatus "NewValidatedByPointGet" $activeInventoryIndex[$normalizedId] = $item } catch { Write-Warning ( "New server '$serverName' could not be validated " + "and was not added to the inventory. " + "Error: $($_.Exception.Message)" ) } } # ------------------------------------------------------------ # 4. Validate previously known resources missing from discovery # ------------------------------------------------------------ foreach ($normalizedId in $cachedServersById.Keys) { if ($currentServersById.ContainsKey($normalizedId)) { continue } $cachedServer = $cachedServersById[$normalizedId] $resourceId = [string]$cachedServer.id $serverName = [string]$cachedServer.name $location = [string]$cachedServer.location Write-Host ( "Server '$serverName' is missing from the current " + "observation. Running individual validation..." ) try { $validatedServer = Get-AzSqlServer ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -ErrorAction Stop $item = New-InventoryItem ` -ResourceId $resourceId ` -Name ([string]$validatedServer.ServerName) ` -Location ([string]$validatedServer.Location) ` -ValidationStatus "RecoveredByPointGet" $activeInventoryIndex[$normalizedId] = $item Write-Warning ( "Server '$serverName' was not returned by discovery, " + "but individual validation confirmed that it still exists." ) } catch { if (Test-IsResourceNotFound -ErrorRecord $_) { $deletedServers += [pscustomobject]@{ id = $resourceId name = $serverName location = $location validationStatus = "Deleted" } Write-Warning ( "Deleted server detected: '$serverName'. " + "It will be removed from the active inventory." ) } else { # The result is inconclusive. Preserve the previous entry. $item = New-InventoryItem ` -ResourceId $resourceId ` -Name $serverName ` -Location $location ` -ValidationStatus "UnknownRetainedFromCache" $activeInventoryIndex[$normalizedId] = $item Write-Warning ( "The status of server '$serverName' could not be " + "confirmed. The previous inventory entry was retained. " + "Error: $($_.Exception.Message)" ) } } } # ------------------------------------------------------------ # 5. Save the updated active inventory # ------------------------------------------------------------ $activeInventory = @( $activeInventoryIndex.Values | Sort-Object name ) $jsonOutput = ConvertTo-Json ` -InputObject $activeInventory ` -Depth 10 $temporaryFile = "$cacheFile.tmp" Set-Content ` -LiteralPath $temporaryFile ` -Value $jsonOutput ` -Encoding utf8 ` -Force Move-Item ` -LiteralPath $temporaryFile ` -Destination $cacheFile ` -Force # ------------------------------------------------------------ # 6. Report the synchronization result # ------------------------------------------------------------ Write-Host "" Write-Host "Active inventory: $($activeInventory.Count) server(s)" $activeInventory | Format-Table ` name, location, validationStatus ` -AutoSize if ($deletedServers.Count -gt 0) { Write-Host "" Write-Warning "Confirmed deleted servers:" $deletedServers | Format-Table ` name, location, validationStatus ` -AutoSize }

Disclaimer

This PowerShell script is provided as a simplified proof of concept to illustrate a persistent resource inventory pattern.

It should be reviewed, tested, and adapted before being used in a production environment. Authentication, permissions, error handling, retry policies, concurrency, logging, inventory storage, and operational requirements may differ between environments.

The local JSON file is suitable for demonstration purposes and small automation scenarios.

SQL Server 2016 Reaches End of Support: A Customer Engineer's Perspective on What's Next

Karunakar_Kotha — Fri, 17 Jul 2026 16:44:50 GMT

Why This Day Matters

July 14, 2026 marks the end of Extended Support for SQL Server 2016. After today, organizations running SQL Server 2016 will no longer receive security updates, non-security hotfixes, or product support unless they have a specific support strategy in place.

As a Customer Engineer, I've spent years helping customers navigate technology lifecycle events. This one feels particularly significant because SQL Server 2016 remains heavily deployed across enterprise environments.

For official lifecycle dates and milestones, see the Microsoft Lifecycle Policy for SQL Server 2016:

Reference: https://learn.microsoft.com/lifecycle/products/sql-server-2016

Why SQL Server 2016 Feels Different

SQL Server 2016 introduced capabilities that many organizations still rely on today, including:

Query Store
Always Encrypted
Native JSON Support
Stretch Database
Enhanced security and performance capabilities

For many businesses, SQL Server 2016 became the standard data platform supporting ERP systems, billing applications, healthcare workloads, manufacturing systems, and custom line-of-business applications.

Ten years is a long time in IT. During that period, databases often evolve from "just another server" into business-critical infrastructure.

Learn more about SQL Server 2016 features:

Reference: https://learn.microsoft.com/sql/sql-server/what-s-new-in-sql-server-2016

What "End of Support" Really Means

One of the most common misconceptions I hear is:

"Will my SQL Server stop working tomorrow?"

No.

The server will continue running. Applications will continue connecting.

What changes is your risk profile.

Organizations will no longer receive:

Security updates
Product fixes
Technical support
Regulatory assurance associated with supported software

Microsoft's official guidance on end-of-support considerations can be found here:

Reference: https://learn.microsoft.com/sql/sql-server/end-of-support/sql-server-end-of-support-overview

Option 1: Upgrade to a Supported SQL Server Version

For many organizations, the most straightforward path is upgrading to SQL Server 2022 or SQL Server 2025.

Benefits include:

Continued support
Latest security protections
Performance improvements
New AI and data platform capabilities

Before upgrading, review compatibility considerations and perform application testing.

Reference: https://learn.microsoft.com/sql/database-engine/install-windows/upgrade-sql-server

Reference: https://learn.microsoft.com/sql/sql-server/sql-server-release-notes

Option 2: Move to Azure SQL Managed Instance

Azure SQL Managed Instance is often my recommendation when customers want to reduce operational overhead while maintaining high SQL Server compatibility.

Advantages include:

Automated patching
Built-in high availability
Automated backups
Near full SQL Server compatibility
Reduced infrastructure management

Learn more:

Reference: https://learn.microsoft.com/azure/azure-sql/managed-instance/sql-managed-instance-paas-overview

Option 3: Lift and Shift to SQL Server on Azure Virtual Machines

For organizations seeking minimal application changes, SQL Server on Azure Virtual Machines can provide a familiar environment while eliminating hardware refresh requirements.

This option also enables customers to take advantage of Extended Security Updates through Azure while planning broader modernization initiatives.

Reference: https://learn.microsoft.com/azure/azure-sql/virtual-machines/windows/sql-server-on-azure-vm-iaas-what-is-overview

Option 4: Purchase Extended Security Updates (ESUs)

If immediate migration is not possible, Extended Security Updates provide up to three additional years of Critical security updates.

ESUs can be valuable when:

Vendor certification delays upgrades
Hardware refresh projects are underway
Business priorities prevent immediate migration

However, ESUs should be viewed as a temporary bridge rather than a long-term strategy.

Reference: https://learn.microsoft.com/sql/sql-server/end-of-support/sql-server-extended-security-updates

Don't Skip the Assessment Phase

The most successful migration projects begin with discovery and assessment.

Common issues identified include:

Deprecated features
Compatibility concerns
Linked Server dependencies
SQL Agent jobs
CLR integrations
Legacy SSIS and SSRS workloads

Resources that can help:

https://learn.microsoft.com/azure/migrate/migrate-services-overview

https://learn.microsoft.com/sql/sql-server/azure-arc/overview

https://learn.microsoft.com/sql/dma/dma-overview

Migration Tools Worth Evaluating

Different migration scenarios require different tools.

Microsoft provides several options:

Data Migration Assistant (DMA)
SQLPackage
Azure Database Migration Service
Managed Instance Link
Distributed Availability Groups

Comprehensive migration guidance:

https://techcommunity.microsoft.com/blog/microsoftdatamigration/microsoft-options-to-migrate-sql-server-databases/4407666

https://learn.microsoft.com/azure/dms/dms-overview

https://learn.microsoft.com/sql/tools/sqlpackage/sqlpackage

Lessons Learned from Customer Engagements

After supporting numerous SQL Server modernization projects, several themes consistently emerge:

The database is rarely the hardest part.
Applications and dependencies drive complexity.
Downtime requirements significantly influence migration strategy.
Organizations that start 6–9 months early typically experience smoother transitions.
ESUs buy time, but they do not replace a modernization plan.

For migration planning best practices:

https://learn.microsoft.com/azure/cloud-adoption-framework/migrate/

Final Thoughts

If you're reading this on or shortly after July 14, 2026, and SQL Server 2016 is still running in your environment, you're not alone.

Whether your path forward is:

SQL Server 2025
Azure SQL Managed Instance
SQL Server on Azure Virtual Machines
Extended Security Updates

the most important step is to begin with an assessment and establish a modernization plan.

SQL Server 2016 served organizations exceptionally well for nearly a decade. The conversation now is not about the end of SQL Server 2016. It's about preparing your data platform for the next decade.

Understanding Microsoft Entra ID Group Membership Caching and Azure SQL Authentication Timing

Mohamed_Baioumy_MSFT — Fri, 17 Jul 2026 09:50:36 GMT

Contributor: hudajazmawi

Executive Summary

Organizations frequently use Microsoft Entra ID groups to manage access to Azure SQL databases. This approach simplifies administration, improves security, and supports just-in-time access models.

In some scenarios, users may experience temporary authentication failures shortly after being granted access through a Microsoft Entra ID group. These failures can appear inconsistent, especially when access succeeds to one database while failing against another. Understanding how group membership caching works during authentication can help explain this behavior and reduce unnecessary troubleshooting efforts.

This article explains a real-world scenario involving temporary authentication failures after group assignment, describes the underlying authentication behavior, and provides practical recommendations for validation and mitigation.

Issue Description

A user was granted access to Azure SQL through membership in a Microsoft Entra ID group. Shortly afterward, the user attempted to connect using Microsoft Entra authentication.

The observed behavior was:

Authentication to certain databases succeeded immediately.
Authentication to other databases failed temporarily.
The issue appeared shortly after the group membership was granted.
Access eventually began working without any configuration changes.
The behavior resolved after a period of time without additional intervention.

At first glance, the results appeared inconsistent because some connection attempts were successful while others failed, even though the same user credentials and group assignments were being used.

Technical Background

Azure SQL supports Microsoft Entra authentication, allowing access to be granted through users, groups, and service principals managed within Microsoft Entra ID.

When a user authenticates, Azure SQL must determine the user's effective permissions. For users who belong to many Microsoft Entra groups, membership information may be cached to improve authentication efficiency and reduce repeated directory lookups.

Caching is a common design pattern used throughout distributed systems to improve performance, scalability, and reliability. However, because caches contain information retrieved at a specific point in time, there can be a temporary delay before recently changed security information becomes visible to all authentication requests.

This behavior is particularly important to understand when organizations use:

Just-in-time access workflows
Privileged access management processes
Temporary group assignments
Automated access provisioning
Frequent permission validation testing

Root Cause

The investigation determined that the authentication failures were caused by Microsoft Entra ID group membership caching.

A login attempt occurred before the user was added to the required Microsoft Entra ID group.

During that earlier authentication attempt, the user's group memberships were retrieved and cached.

After the user was added to the required group, subsequent authentication attempts continued using the previously cached membership information until the cache expired. As a result, authentication requests temporarily evaluated permissions using outdated group membership data.

Because the newly assigned group membership had not yet been reflected in the cached information, authentication failed even though access had already been granted.

Once the cached membership information expired and fresh group membership data was retrieved, authentication succeeded without any additional configuration changes.

Detailed Explanation

To understand the behavior, consider the following simplified sequence:

Step 1: Initial Authentication

A user attempts to connect to Azure SQL before being added to the required Microsoft Entra ID group.

During this process:

The user's current group memberships are evaluated.
Membership information is cached.
The required access group is not yet present.

Authentication behavior reflects the permissions available at that moment.

Step 2: Group Membership Change

The user is added to the appropriate Microsoft Entra ID group.

From an administrative perspective, the access assignment has been completed successfully.

However, any previously cached authentication information may still reflect the user's earlier membership state.

Step 3: Immediate Retesting

The user immediately attempts another connection.

Although the directory now contains the new group membership, the authentication process may still reference cached membership information created before the change occurred.

The result can be a temporary authentication failure.

Step 4: Cache Expiration

After the cached data expires or is refreshed, authentication retrieves updated membership information.

The newly assigned group is now visible during authorization evaluation.

At this point, authentication succeeds as expected.

Why Some Databases May Behave Differently

One of the most confusing aspects of these scenarios is that different databases may appear to behave differently even when they use identical group assignment models.

This typically occurs because authentication state and cache usage can differ depending on the sequence and timing of connection attempts.

For example:

Database A may be accessed for the first time after the group assignment occurs.
Database B may have received a connection attempt before the group assignment occurred.

As a result:

Database A may evaluate fresh membership information and allow access.
Database B may continue referencing previously cached membership information until the cache expires.

This can create the appearance of inconsistent behavior even though the system is operating as designed.

Mitigation and Recommendations

The following practices can help reduce the likelihood of encountering similar authentication timing scenarios.

1. Assign Access Before Testing

Whenever possible, add users to the required Microsoft Entra ID groups before any authentication attempts are made against Azure SQL resources.

This helps ensure that fresh membership information is used during the first authentication request.

2. Avoid Immediate Validation After Permission Changes

If a user has recently been granted group-based access, consider allowing time for authentication cache refresh behavior before conducting validation testing.

Immediate testing can sometimes produce results based on older membership information.

3. Plan for Temporary Authentication Delays

Organizations implementing just-in-time access should account for the possibility of short propagation and cache refresh intervals when designing operational procedures.

4. Use DBCC FLUSHAUTHCACHE When Appropriate

For controlled testing and validation scenarios, administrators may use:

DBCC FLUSHAUTHCACHE;

This command can help refresh authentication cache behavior during troubleshooting and validation activities.

As with any administrative operation, testing should be performed according to organizational change-management procedures.

5. Capture Precise Timing Information

When investigating authentication behavior, collecting exact timestamps is extremely valuable.

Recommended data points include:

Time the user was added to the Microsoft Entra ID group
Time of each authentication attempt
Database target of each connection attempt
Time any cache refresh operation was performed
Time authentication eventually succeeded

Accurate timestamps help establish a clear correlation between group membership changes and authentication behavior.

Validation Guidance

If you need to verify whether group membership caching is influencing authentication results, consider the following approach:

Record the exact time a user is added to the required Microsoft Entra ID group.
Record the time of every authentication attempt.
Identify whether any login attempts occurred before the group membership change.
Observe whether successful authentication occurs after a period of time without configuration changes.
Where appropriate, perform controlled tests using authentication cache refresh procedures.
Compare authentication outcomes against the timeline of group membership updates.

This structured approach often helps determine whether the observed behavior is related to authentication caching rather than a permission configuration issue.

Key Takeaways

Temporary authentication failures immediately after group-based access assignment do not necessarily indicate a configuration problem.
Authentication behavior may be influenced by previously cached Microsoft Entra ID group membership information.
Login attempts that occur before a group membership change can affect subsequent authentication behavior until cached data expires.
Different databases may appear to behave differently if they are accessed at different points in the authentication timeline.
Capturing precise timestamps significantly improves troubleshooting accuracy.
Proper testing practices and awareness of cache behavior can reduce confusion and accelerate issue resolution.

Closing Summary

Microsoft Entra ID group-based authorization provides a powerful and scalable way to manage Azure SQL access. However, like many modern cloud authentication systems, caching is used to optimize performance and improve efficiency.

When group memberships change immediately before authentication testing, temporary differences between cached and current membership information may lead to short-lived authentication failures. Understanding this behavior can help administrators accurately interpret results, design effective validation procedures, and avoid unnecessary troubleshooting.

By assigning permissions before authentication attempts, allowing appropriate time for cache refresh behavior, and capturing precise timing information during investigations, organizations can more effectively manage Microsoft Entra-based access and streamline their operational workflows.

As always, when troubleshooting authentication scenarios, focusing on the exact sequence and timing of events often provides the clearest path to identifying the underlying cause and validating a successful resolution.

Lessons Learned #545:Understanding client_ip = 0.0.0.0 in Azure SQL Auditing

Jose_Manuel_Jurado — Thu, 16 Jul 2026 17:30:03 GMT

During my analysis, I reproduced the same behavior in a test environment using a client connection to Azure SQL Database through a Microsoft.Sql Virtual Network Service Endpoint . After enabling Azure SQL Auditing and reviewing the original Azure SQL Database Audit file (.xel), the connection was also recorded with client_ip = 0.0.0.0.

This confirms that 0.0.0.0 can represent a valid client connection using a Service Endpoint and should not automatically be interpreted as internal Azure platform.

When the original client IP is not exposed, one of the best ways to identify the originating application is to configure a meaningful Application Name property in the SQL connection string:Application Name=Customer-Production;

Therefore, when reviewing Azure SQL audit records with client_ip = 0.0.0.0, check the original .xel audit file and use the application_name, host_name, authenticated principal, database name, and timestamp to correlate the activity with the correct application.

Lessons Learned #544: How to Detect INT Identity Exhaustion Before Inserts Fail.

Jose_Manuel_Jurado — Mon, 13 Jul 2026 17:28:24 GMT

Recently, I worked on a support case involving an Azure SQL Database table where the customer had reached the maximum value supported by the INT data type. The table used an INT IDENTITY(1,1) column as its primary key. Over time, the generated identity value approached the maximum value supported by INT. Once the available range was exhausted, the application was no longer able to insert new rows.

At that point, the column needed to be changed from INT to BIGINT. However, performing this type of migration on a very large table can be a complex and time-consuming operation. The situation is that an INT column uses 4 bytes and supports values from: -2,147,483,648 to: 2,147,483,647.

To prevent similar problems in the future, I suggested using the following query to review the current status of all INT IDENTITY columns in the database. The query uses the sys.identity_columns catalog view:

SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, CONVERT(bigint, c.last_value) AS CurrentIdentityValue, CONVERT(bigint, 2147483647) AS MaximumIntValue, CONVERT(bigint, 2147483647) - ISNULL(CONVERT(bigint, c.last_value), 0) AS RemainingValues, CAST( ISNULL(CONVERT(decimal(20,2), c.last_value), 0) / 2147483647 * 100 AS decimal(6,2) ) AS PercentUsed FROM sys.identity_columns AS c INNER JOIN sys.tables AS t ON c.object_id = t.object_id INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id WHERE TYPE_NAME(c.system_type_id) = 'int' ORDER BY PercentUsed DESC;

I prefer using this query instead of relying on: SELECT COUNT(*) FROM dbo.TableName. The number of rows in a table does not necessarily match the current identity value. Rows might have been deleted, transactions might have been rolled back, and identity values might contain gaps.

For this reason, the current identity value is a better indicator of the remaining capacity. Adding this query as a regular preventive check can help identify identity columns that are approaching their limits before they cause application failures.

I would like to share with you an example creates a table with an identity seed close to the maximum value supported by INT:

DROP TABLE IF EXISTS dbo.IdentityCapacityDemo2; CREATE TABLE dbo.IdentityCapacityDemo2 ( Id INT IDENTITY(2147483600,1) NOT NULL, CreatedDate datetime2(0) NOT NULL CONSTRAINT DF_IdentityCapacityDemo2_CreatedDate DEFAULT SYSUTCDATETIME(), CONSTRAINT PK_IdentityCapacityDemo2 PRIMARY KEY CLUSTERED (Id) )

Insert 20 rows using this command:

insert into IdentityCapacityDemo2(CreatedDate) values(SYSUTCDATETIME())

Example of returns:

I think runnning this preventive check can help detect identity exhaustion before it affects the application.

Lessons Learned #543: Evaluating MultiSubnetFailover with Azure SQL Database

Jose_Manuel_Jurado — Mon, 13 Jul 2026 16:34:02 GMT

Last week, I worked on a support case in which the use of the MultiSubnetFailover connection-string feature was being considered for an application connecting to Azure SQL Database.

The expectation was that enabling the following option could improve connection recovery during a database failover changing MultiSubnetFailover to True.

This option is commonly associated with SQL Server high availability, and Azure SQL Database is also designed to remain available by moving databases between replicas when required. However, after reviewing the Azure SQL Database connectivity architecture and comparing the behavior with the property enabled and disabled, I did not observe a clear improvement.

The property could be added to the connection string without generating an error, and the application was able to connect successfully with both configurations.

What MultiSubnetFailover is designed for

MultiSubnetFailover was introduced primarily for SQL Server high-availability configurations such as:

Always On Availability Group listeners.
SQL Server Failover Cluster Instance virtual network names.

In a multi-subnet Availability Group, a listener name may resolve to multiple IP addresses located in different network subnets.

Without MultiSubnetFailover=True, the application may try those addresses sequentially. If the first address is not currently active, the connection can be delayed while the attempt waits for a timeout. When the option is enabled, supported SQL client drivers can attempt connections to the listener addresses in parallel and use the first address that responds successfully. This can reduce connection time after an Availability Group failover because the SQL client is directly involved in selecting the reachable listener address.

Why Azure SQL Database is different

Azure SQL Database uses a different connectivity architecture. The application connects to a logical server endpoint: <server-name>.database.windows.net. The Azure SQL connectivity layer receives the connection and routes it to the infrastructure currently hosting the database.

Depending on the configured connection policy, the Azure SQL gateway either proxies the connection or redirects the application to the appropriate database node. The important difference is that the SQL client does not receive a list containing the IP addresses of the Azure SQL Database primary and secondary replicas. The decision and the associated routing are managed by the Azure SQL Database platform.

Although Azure SQL Database internally uses multiple replicas for high availability, this is not the same connectivity model as a SQL Server Availability Group listener that publishes multiple addresses through DNS.

What about Failover Groups?

Azure SQL Database Failover Groups provide a stable listener endpoint such as: <failover-group-name>.database.windows.net. Following a regional failover, the listener is updated so that it points to the logical server hosting the new primary databases. This process depends partly on DNS. The listener name remains the same, but its DNS target changes after the failover.

This is still different from a SQL Server multi-subnet Availability Group listener. The Failover Group listener does not expose the addresses of the Azure SQL Database replicas to the SQL client. Therefore, MultiSubnetFailover=True cannot directly select the new primary replica.

In this scenario, application recovery continues to depend on the service transition, DNS resolution, and retry behavior.

The importance of retry logic

One of the main lessons from this case was that retry logic is more relevant to Azure SQL Database resiliency than enabling MultiSubnetFailover. An application connecting to Azure SQL Database must expect occasional transient connectivity errors. These can occur during maintenance, scaling, failover, network interruptions, or temporary service conditions.

An appropriate retry strategy should normally include:

A limited number of retry attempts.
A short delay before the first retry.
Increasing delays between subsequent attempts.
A maximum retry interval.
Creation of a fresh SQL connection.

For transactions, retry logic requires additional care. The application must determine whether the transaction was committed, rolled back, or left in an unknown state before repeating the complete operation.

Understanding % Characters in Azure Blob File Names Using SQL OPENROWSET (BULK)

Thamires_Lemes — Thu, 02 Jul 2026 16:22:14 GMT

Why the % character is different

In URL percent-encoding, the % character starts an encoded sequence. For example:

%20 represents a space.
%25 represents a literal % character.

Because of this, file names that contain a literal % can behave differently from file names containing characters such as #, &, +, ;, =, or @, depending on how the path is processed before blob lookup.

Observed symptom

The customer reported an import failure for a file name similar to:

Company Data - Company Data - éêçëЮй_%25$£#.csv

The error message started at the % sequence instead of showing the full file name:

Invalid format specification: '%25$#.45752234198410731.csv.processed' cannot be opened

This was a useful clue because the reported path appeared to begin at the first % sequence, while the preceding portion of the file name was not present in the error text.

Test setup

The examples below use an external data source pointing to Azure Blob Storage. Replace all placeholders with values from the test environment. Sensitive values such as storage account names, container names, credential names, and SAS tokens should be masked before sharing.

CREATE DATABASE SCOPED CREDENTIAL [<credential_name>]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS_TOKEN>';

CREATE EXTERNAL DATA SOURCE [MyBlobStorageSource]
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://<storage_account>.blob.core.windows.net/<container>',
    CREDENTIAL = [<credential_name>]
);

Reproduction results

Scenario	Blob name / character	OPENROWSET path	Result	Observation
Space in file name	`Test File.pdf`	`BULK 'Test File.pdf'`	Succeeded	Spaces alone did not reproduce the failure.
Literal `%` in file name	`Test%File.pdf`	`BULK 'Test%File.pdf'`	Failed	The file could not be opened when `%` was passed literally.
Literal `%` encoded as `%25`	`Test%File.pdf`	`BULK 'Test%25File.pdf'`	Succeeded	Encoding `%` as `%25` allowed the same blob to be accessed.
Literal string `%25` in file name	`Test%25File.pdf`	`BULK 'Test%2525File.pdf'`	Succeeded	The observed result is consistent with one percent-decoding pass during path resolution.
Other special characters	`#`, `&`, `+`, `;`, `=`, `@`	Tested individually	Succeeded	These characters did not reproduce the same behavior in the tests performed.

Commands used for testing

1. Space in the file name

INSERT INTO testfiles(pdfData)
SELECT BulkColumn
FROM OPENROWSET(
    BULK 'Test File.pdf',
    DATA_SOURCE = 'MyBlobStorageSource',
    SINGLE_BLOB
) AS PdfFile;

2. Literal % in the file name

INSERT INTO testfiles(pdfData)
SELECT BulkColumn
FROM OPENROWSET(
    BULK 'Test%File.pdf',
    DATA_SOURCE = 'MyBlobStorageSource',
    SINGLE_BLOB
) AS PdfFile;

Observed error:

Msg 13822, Level 16, State 1
File 'Test%File.pdf' cannot be opened because it does not exist or it is used by another process.

3. URL-encoded % character

INSERT INTO testfiles(pdfData)
SELECT BulkColumn
FROM OPENROWSET(
    BULK 'Test%25File.pdf',
    DATA_SOURCE = 'MyBlobStorageSource',
    SINGLE_BLOB
) AS PdfFile;

4. Literal %25 in the blob name

INSERT INTO testfiles(pdfData)
SELECT BulkColumn
FROM OPENROWSET(
    BULK 'Test%2525File.pdf',
    DATA_SOURCE = 'MyBlobStorageSource',
    SINGLE_BLOB
) AS PdfFile;

Interpretation

The tests suggest that the path provided to OPENROWSET (BULK) is processed in a way that is consistent with URL-style percent-encoding rules.

A literal % character in a blob name may need to be referenced as %25 in the BULK path.
If the actual blob name contains the literal characters %25, the BULK path may need to use %2525 so that the observed path-resolution behavior resolves it back to %25.
Spaces and the tested characters #, &, +, ;, =, and @ did not reproduce the same behavior in these tests.
The customer error beginning at the %25 sequence is consistent with the file name being interpreted or transformed when the % sequence is encountered.

Practical guidance

Recommended handling for `%` characters in blob names
Main recommendation Avoid passing blob names containing a literal % directly to OPENROWSET (BULK).
When the blob name contains `%` URL-encode the character before generating the BULK path. For example, test referencing a literal % as %25.
When the blob name contains literal `%25` Test whether the path needs to use %2525. In the observed tests, this was consistent with one percent-decoding pass during path resolution.
Application or stored procedure checks Confirm the import process is not decoding, encoding, or re-encoding the file name multiple times before invoking OPENROWSET.

Recommended handling for % characters in blob names

Main recommendation

Avoid passing blob names containing a literal % directly to OPENROWSET (BULK).

When the blob name contains %

URL-encode the character before generating the BULK path. For example, test referencing a literal % as %25.

When the blob name contains literal %25

Test whether the path needs to use %2525. In the observed tests, this was consistent with one percent-decoding pass during path resolution.

Application or stored procedure checks

Confirm the import process is not decoding, encoding, or re-encoding the file name multiple times before invoking OPENROWSET.

Optional PowerShell test using Invoke-Sqlcmd

If testing outside SSMS, Invoke-Sqlcmd can be used with an Entra ID access token. The SAS token is not needed in the command if the database scoped credential already exists in SQL.

Connect-AzAccount

$query = @"
INSERT INTO testfiles(pdfData)
SELECT BulkColumn
FROM OPENROWSET(
    BULK 'Test%25File.pdf',
    DATA_SOURCE = 'MyBlobStorageSource',
    SINGLE_BLOB
) AS PdfFile;
"@

Invoke-Sqlcmd `
    -ServerInstance '<managed-instance-fqdn>,3342' `
    -Database '<database_name>' `
    -AccessToken (Get-AzAccessToken -ResourceUrl 'https://database.windows.net/').Token `
    -Query $query `
    -Verbose `
    -ErrorAction Stop

Conclusion

The controlled tests indicate that the import behavior is related to how % is handled in the file path supplied to SQL OPENROWSET (BULK). The observed results are consistent with percent-decoding during path resolution: %25 behaves like a literal %, and %2525 behaves like a literal %25.

Based on these tests, this should not be described as a general limitation with spaces, Unicode characters, or common special characters in blob names. The evidence points specifically to handling of the % character and percent-encoded sequences in the path passed to OPENROWSET (BULK).