Azure Database Support Blog articles

Generic Best Practices for HikariCP with Azure Database for PostgreSQL

Mohamed_Baioumy_MSFT — Fri, 26 Jun 2026 22:05:48 GMT

Author: Mohamed Baioumy
Technology: Azure Database for PostgreSQL (Flexible Server & Single Server)
Category: Connectivity | Performance | Application Design

Introduction

Connection pooling is a critical component of application performance when connecting to Azure Database for PostgreSQL. Creating a new PostgreSQL connection is an expensive operation that consumes CPU, memory, and networking resources. Reusing existing connections through a connection pool significantly reduces connection latency, improves throughput, and helps applications scale more efficiently.

Many Java applications use HikariCP, one of the most popular high-performance JDBC connection pools. While HikariCP provides excellent performance out of the box, improperly configured connection pool settings can lead to issues such as:

Connection pool exhaustion
Stale or invalid connections
Increased connection acquisition latency
Excessive connection creation and destruction
Database resource contention
Application timeouts

This article summarizes generic guidance and best practices for configuring HikariCP when working with Azure Database for PostgreSQL Flexible Server and Azure Database for PostgreSQL Single Server.

Understanding Key HikariCP Parameters

1. Maximum Lifetime (maxLifetime)

The maxLifetime property controls how long a connection can remain in the pool before HikariCP retires it and creates a new one.

Why It Matters

Connections can become stale over time due to:

Network interruptions
Infrastructure updates
Connection state changes
TCP idle behavior

Recycling connections periodically helps prevent applications from using long-lived connections that may no longer be healthy.

Recommended Practice

Avoid configuring the value too low.

When maxLifetime is set aggressively, HikariCP continuously destroys and recreates connections, resulting in:

Additional authentication overhead
Increased connection establishment latency
Higher CPU utilization
Reduced application throughput

A reasonable starting point is:

spring.datasource.hikari.maxLifetime=1800000

30 minutes (1,800,000 ms) is commonly used and aligns well with many production workloads. Depending on workload characteristics, values between 30 minutes and 1 hour are generally suitable

Avoid

maxLifetime=300000

(5 minutes)

This often causes unnecessary connection churn without providing additional benefits.

2. Minimum Idle Connections (minimumIdle)

The minimumIdle setting defines how many idle connections HikariCP should keep ready for immediate use.

Why It Matters

A pool with available idle connections can serve application requests immediately without waiting for new connections to be established.

However, maintaining too many idle connections consumes unnecessary database resources.

Recommended Practice

For most workloads:

minimumIdle = maximumPoolSize

minimumIdle slightly lower than maximumPoolSize

This ensures sufficient connections are already available during traffic spikes while avoiding excessive connection creation delays.

Example

maximumPoolSize=20 minimumIdle=15

Avoid

maximumPoolSize=20 minimumIdle=20

only when the application experiences long periods of inactivity and conserving resources is more important than immediate responsiveness.

3. Idle Timeout (idleTimeout)

The idleTimeout property determines how long an unused connection remains in the pool before being removed.

Why It Matters

Connections that sit idle for extended periods consume resources on both:

The application server
Azure Database for PostgreSQL

However, removing idle connections too quickly causes the application to repeatedly establish new connections.

Recommended Practice

Keep the default value unless there is a specific requirement.

spring.datasource.hikari.idleTimeout=600000

which equals:

10 minutes (600,000 ms)

This setting provides a good balance between resource utilization and responsiveness. [Re: EXT: R...0040002947 | Outlook]

The timeout should also be comfortably longer than any expected short application idle periods.

Avoid

idleTimeout=10000

(10 seconds)

Such aggressive settings often result in unnecessary connection creation cycles.

4. Maximum Pool Size (maximumPoolSize)

This parameter determines the maximum number of concurrent database connections the application can maintain.

Why It Matters

This is often the most important HikariCP setting.

If the Pool Is Too Small

Applications may experience:

Connection is not available, request timed out

because all available connections are already in use. Similar scenarios have been observed during customer investigations involving Hikari pool exhaustion.

If the Pool Is Too Large

Applications can overwhelm the database server with excessive concurrent sessions, resulting in:

Connection contention
Increased context switching
Higher memory consumption
Reduced overall performance

Recommended Practice

Pool size should be based on:

Database compute configuration
CPU core count
Query execution duration
Application concurrency requirements
Workload characteristics

There is no universal value that fits every workload.

Start conservatively:

maximumPoolSize=10

maximumPoolSize=20

and increase only after load testing demonstrates a need for additional concurrency.

Fixed-Size Pool Recommendation

For many production workloads, a fixed-size pool provides the simplest and most predictable behavior.

Configure:

maximumPoolSize=20 minimumIdle=20

or omit minimumIdle entirely so it defaults to maximumPoolSize. HikariCP commonly recommends maintaining a fixed-size pool for responsiveness during demand spikes.

Benefits

Faster connection acquisition
Predictable performance
Reduced connection creation latency
Better handling of traffic spikes

When using a small fixed-size pool, there is often little need to aggressively tune:

minimumIdle
idleTimeout

Instead, simply recycle connections using:

maxLifetime

Additional Recommendations

Enable TCP Keepalive

One common cause of stale connections is network devices silently dropping inactive TCP sessions.

For PostgreSQL applications, consider enabling TCP keepalive:

tcpKeepAlive=true

The HikariCP project specifically recommends enabling TCP keepalive to prevent rare situations where pools can lose valid connections.

Monitor Connection Usage

Track:

Active connections
Idle connections
Connection acquisition time
Pool exhaustion events
Database connection counts

These metrics help identify whether pool sizing is appropriate.

Investigate Long-Running Queries

Connection pool problems are often symptoms rather than root causes.

A frequent scenario is:

A query becomes slow.
Connections remain occupied longer.
The pool becomes exhausted.
Applications start timing out.

When analyzing HikariCP issues, always review:

Query performance
Blocking situations
Database resource utilization
Application connection handling logic

Sample Production Configuration

spring.datasource.hikari.maximumPoolSize=20

spring.datasource.hikari.minimumIdle=15

spring.datasource.hikari.maxLifetime=1800000

spring.datasource.hikari.idleTimeout=600000

spring.datasource.hikari.connectionTimeout=30000

spring.datasource.hikari.keepaliveTime=60000

spring.datasource.hikari.maximumPoolSize=20 spring.datasource.hikari.minimumIdle=15 spring.datasource.hikari.maxLifetime=1800000 spring.datasource.hikari.idleTimeout=600000 spring.datasource.hikari.connectionTimeout=30000 spring.datasource.hikari.keepaliveTime=60000

This configuration provides a solid starting point for many Azure Database for PostgreSQL workloads and can be adjusted based on application-specific requirements.

a { text-decoration: none; color: #464feb; } tr th, tr td { border: 1px solid #e6e6e6; } tr th { background-color: #f5f5f5; }

Conclusion

HikariCP is extremely efficient when configured appropriately. The goal is not to maximize the number of connections, but rather to maintain a healthy balance between application responsiveness and database resource consumption.

As a general rule:

Use a reasonable maxLifetime (30–60 minutes)
Keep enough idle connections available for traffic spikes
Avoid aggressive idleTimeout values
Size the pool based on workload characteristics, not guesses
Consider fixed-size pools for predictable performance
Monitor connection usage and query performance regularly

By following these practices, applications connecting to Azure Database for PostgreSQL can achieve improved scalability, lower latency, and more reliable connectivity.

References

Connection pooling best practices - Azure Database for PostgreSQL

Performance best practices for using Azure Database for PostgreSQL – Connection Pooling

HikariCP Documentation and Pool Sizing Guidance

Lessons Learned #541:Automatic Plan Correction vs External Tables: A Practical Lesson from the Field

Jose_Manuel_Jurado — Fri, 26 Jun 2026 20:49:11 GMT

Automatic Plan Correction is one of the most useful capabilities in Azure SQL Database when dealing with plan regressions. It uses Query Store to identify when a query starts using a worse execution plan and, when appropriate, forces the last known good plan.

However, during a recent troubleshooting scenario, I found that not all queries have the same execution characteristics. In particular, queries that reference external tables may behave differently from fully local queries because part of their execution depends on remote data access.

When Query Store is configured to capture all queries, we can use it to identify queries that reference external tables and review whether those query IDs should participate in FORCE_LAST_GOOD_PLAN.

From a practical perspective, external-table queries may not always be the best candidates for Automatic Plan Correction, especially when the expected benefit of automatic plan forcing is not clear. For that reason, the goal of this article is simple: identify queries that reference external tables and, when appropriate, exclude selected query IDs from Automatic Plan Correction.

If we review the execution plan for the following query:

DECLARE @Region nvarchar(50) = N'EMEA' SELECT CustomerId, CustomerName, Region FROM dbo.ExternalCustomers WHERE Region = @Region;

We can see that the plan includes a Remote Query operator. This means that the query is not only accessing local data; part of the execution depends on remote data access through the external table.

For this type of query, Automatic Plan Correction may not provide the same clear benefit as it does for fully local queries. The performance may depend not only on the local execution plan, but also on the remote database, the external data source, network latency, and the amount of data returned from the remote side.

For that reason, queries referencing external tables are good candidates for review before allowing them to participate in FORCE_LAST_GOOD_PLAN.

In this scenario, the first step was to identify the Query Store query_id associated with the query referencing the external table. Since the query text was available in Query Store, we searched for the external table name in sys.query_store_query_text.

SELECT q.query_id, p.plan_id, p.is_forced_plan, p.plan_forcing_type_desc, p.force_failure_count, p.last_force_failure_reason_desc, p.last_execution_time, qt.query_sql_text FROM sys.query_store_query_text AS qt INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id INNER JOIN sys.query_store_plan AS p ON q.query_id = p.query_id WHERE qt.query_sql_text LIKE N'%ExternalCustomers%' ORDER BY p.last_execution_time DESC;

Once the query_id was identified, the next step was to exclude that specific query from Automatic Plan Correction by setting FORCE_LAST_GOOD_PLAN to OFF for that query_id.

EXECUTE sys.sp_configure_automatic_tuning @option = 'FORCE_LAST_GOOD_PLAN', @type = 'QUERY', @type_value = N'<query_id>', @option_value = 'OFF';

For example:

EXECUTE sys.sp_configure_automatic_tuning @option = 'FORCE_LAST_GOOD_PLAN', @type = 'QUERY', @type_value = N'1574', @option_value = 'OFF';

This does not disable Automatic Plan Correction for the entire database. It only tells Automatic Plan Correction to ignore this specific Query Store query ID for FORCE_LAST_GOOD_PLAN.

With this approach, Automatic Plan Correction can remain enabled for the rest of the database workload, while selected queries that depend on external or remote data access can be reviewed and excluded individually when automatic plan forcing is not expected to provide a clear benefit.

Data sync fails with deadlock error

SmritiGupta — Mon, 22 Jun 2026 09:06:18 GMT

Recently I have worked on cases where it is observed that data sync fails with deadlock error.

Error:

Database re-provisioning failed with the exception 'Transaction (Process ID ##) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

To investigate deadlocks, you can enable Extended Events by following the guidance provided here:

https://learn.microsoft.com/en-us/azure/azure-sql/database/analyze-prevent-deadlocks?view=azuresql&tabs=event-file#collect-deadlock-graphs-in-azure-sql-database-with-extended-events

A Sample deadlock graph looks below:

Running the Data Sync Health Checker report also results in the same error being observed.

https://github.com/microsoft/AzureSQLDataSyncHealthChecker

Explanation:

When a deadlock occurs between a Data Sync operation and a customer transaction, the Data Sync operation is always selected as the victim.

To mitigate this issue without removing and recreating the Data Sync configuration, please follow the below steps:

Remove the table referenced in the deadlock error from the Data Sync configuration and then restart the synchronization.
If automatic synchronization is enabled, disable the schedule and run the sync process manually.
Increase the frequency of the synchronization process to minimize overlapping operations and reduce the likelihood of deadlocks.

Additionally, running UPDATE STATISTICS and rebuilding indexes on the system tables involved in Data Sync may help improve performance. This can assist the SQL optimizer in selecting a more efficient execution plan, thereby allowing the synchronization process to complete faster and reducing the likelihood of deadlocks.

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

For more details, refer to the official documentation:

https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database?view=azuresql

Azure SQL DB Fabric Mirroring with Private Endpoint

shaurya-singh — Mon, 22 Jun 2026 10:49:15 GMT

Introduction
Overview steps for configuration of Mirroring between Azure SQL Database to Fabric Mirrored Database over Private Endpoint and Public Connectivity Disabled on source.

Prerequisites
#1 - The minimum requirement for the source Azure SQL Database tier is - it is Standard Tier with DTUs equal or greater than 100.
Free, Basic Tier, or <100 DTUs are NOT supported.
All vCore model tiers supported.

#2 - System Assigned Managed Identity (SAMI) must be enabled on the Azure SQL logical server.

#3 - Microsoft.PowerPlatform should be registered as a source provider at the subscription level.

If this step is not completed, you'll face error in the next steps, while creating the 'Virtual Network Data Gateway', example below.

#4 - The Virtual Network Subnet of the configured Private Endpoint should have the following selected.
Select Microsoft.PowerPlatform/netaccesslinks for the Subnet Delegation tab.

This is a required step, otherwise the subnet is grayed out to select while configuration of the Virtual Network Data Gateway at Fabric level.

High Level Configuration Steps

#1 - Go to Fabric Portal > Your Workspace
Click on Settings button on top right > Click on Manage Connections and Gateways
Go to 'Virtual Network Data Gateway' tab > Click New

In the new page, Select your Capacity, Subscription, Resource Group, VNET and Subnet of the source Azure SQL DB and create it.

#2 - Go back to your workspace, and click new item > Search 'Mirrored Azure SQL Database'

#3 - Here, in Data Gateway section, chose your new created gateway which we created in previous step, and fill the required source Azure SQL Database details and click connect.

#4 - Select the tables to be mirrored in the next steps and you will be able to successfully mirror from Azure SQL Database to Mirrored Azure SQL Database without Public Connectivity and using Private Endpoint.

Troubleshooting Azure SQL Data Sync Failures Caused by Large Change Tracking Backlogs

Mohamed_Baioumy_MSFT — Thu, 18 Jun 2026 21:01:11 GMT

Introduction

Azure SQL Data Sync is a popular solution for synchronizing data across multiple Azure SQL Database instances. It uses Change Tracking to identify and propagate data modifications between participating databases. While Data Sync can operate reliably for extended periods, environments with highly active tables may occasionally encounter synchronization failures that become increasingly difficult to recover from.

In this article, we examine a real-world troubleshooting scenario in which Azure SQL Data Sync repeatedly failed while attempting to synchronize changes for a specific table. The investigation revealed that excessive synchronization metadata growth and a large change backlog were causing change enumeration operations to exceed Azure SQL Database resource governance thresholds, resulting in repeated synchronization failures.

This post explains the symptoms, investigation process, troubleshooting scripts, root cause, mitigation strategy, and preventive measures that administrators can apply in their own Azure SQL Data Sync environments.

Symptoms

The issue manifested as repeated Azure SQL Data Sync failures for a single synchronized table while the sync group remained unhealthy.

Administrators may encounter errors similar to:

Cannot enumerate changes at the RelationalSyncProvider.

SqlError Number: 40197

The service has encountered an error processing your request.

Please try again.

Error code 40549

These errors can occur when Data Sync attempts to enumerate pending changes through Change Tracking and synchronization metadata, but the operation becomes excessively resource intensive.

Additional Warning Signs

Synchronization runs taking significantly longer than usual
Repeated synchronization retries
Increasing synchronization latency
Large Data Sync metadata growth
Sync groups reporting warning or failed states

Understanding How Azure SQL Data Sync Uses Change Tracking

Azure SQL Data Sync relies on SQL Change Tracking to identify modifications occurring within synchronized tables.

The synchronization architecture generally consists of:

Hub Database

The central synchronization endpoint responsible for orchestrating synchronization.

Member Databases

Databases that participate in synchronization and exchange data with the hub.

Change Tracking

Tracks data modifications and provides an efficient mechanism to identify rows that have changed since the last synchronization cycle.

Synchronization Metadata

Data Sync maintains internal metadata used to track synchronization state and determine which changes must be applied.

Change Enumeration

During synchronization, Azure SQL Data Sync enumerates tracked changes and applies them across participating databases.

As synchronization backlog grows, the complexity and duration of enumeration operations increase accordingly.

Investigation Process

The troubleshooting effort focused on identifying where synchronization was failing and determining whether the underlying issue was related to Change Tracking, synchronization metadata, or Azure SQL resource limitations.

Step 1 – Identify the Failing Object

Review synchronization logs to determine which table repeatedly generates failures.

Step 2 – Determine Where the Failure Occurs

Determine whether the issue originates from:

Hub Database
Member Database
Synchronization Infrastructure

Step 3 – Evaluate Synchronization Backlog

Assess the volume of pending changes and synchronization metadata growth.

Step 4 – Assess Resource Governance Impact

Evaluate whether Azure SQL Database resource governance may be terminating synchronization-related operations.

Useful T-SQL Scripts for Azure SQL Data Sync Troubleshooting

During the investigation, several T-SQL queries were used to validate Change Tracking configuration, evaluate synchronization backlog size, identify governance-related interruptions, and assess the overall health of the Azure SQL environment.

All scripts below have been sanitized and generalized for public use.

Note: Replace dbo.SyncTable with the affected synchronized table in your environment.

1. Verify Whether Change Tracking Is Enabled

Azure SQL Data Sync requires Change Tracking to function correctly.

Check Database-Level Change Tracking

-- Run to Master DB SELECT DB_NAME(database_id) AS DatabaseName, is_auto_cleanup_on, retention_period, retention_period_units_desc FROM sys.change_tracking_databases WHERE database_id = DB_ID();

Check Table-Level Change Tracking

-- Run to UserDB SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName, OBJECT_NAME(object_id) AS TableName, begin_version, cleanup_version, min_valid_version FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID('dbo.SyncTable');

Why This Matters

If Change Tracking is disabled, Azure SQL Data Sync cannot enumerate changes successfully.

2. Estimate Synchronization Backlog Size

One of the most useful troubleshooting indicators is the volume of pending changes.

--Run to UserDb SELECT COUNT(*) AS PendingChanges FROM CHANGETABLE(CHANGES dbo.SyncTable, 0) AS CT;

Why This Matters

A very large backlog may indicate:

Synchronization delays
Metadata accumulation
Enumeration pressure
Increased risk of governance-related failures

3. Review Change Tracking Metadata

--Run to UserDB SELECT OBJECT_NAME(object_id) AS TableName, begin_version, min_valid_version, cleanup_version FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID('dbo.SyncTable');

Why This Matters

This information helps determine whether Change Tracking metadata is growing faster than cleanup processes can manage.

4. Validate Database Service Tier

-- Run to UserDB SELECT database_id, edition, service_objective, elastic_pool_name FROM sys.database_service_objectives;

Why This Matters

Resource limitations associated with a database service tier may contribute to synchronization instability under heavy workloads.

5. Check for Resource Governance Events

Run the following query from the master database of the Azure SQL logical server.

SELECT TOP 20 end_time, event_type, event_subtype_desc, description FROM sys.event_log WHERE event_type = 'connection' AND event_subtype_desc = 'killed_by_governance' AND end_time > DATEADD(hour, -24, GETUTCDATE()) ORDER BY end_time DESC;

Why This Matters

This query can reveal whether Azure SQL Database terminated operations because they exceeded governance thresholds.

Examples include:

Long-running synchronization queries
Excessive CPU consumption
Excessive IO workload
Large Change Tracking enumeration operations

Note: sys.event_log is available only from the master database.

6. Identify Large Tables

SELECT t.name AS TableName, SUM(p.rows) AS RowCounts FROM sys.tables t INNER JOIN sys.partitions p ON t.object_id = p.object_id WHERE p.index_id IN (0,1) GROUP BY t.name ORDER BY RowCounts DESC;

Why This Matters

Large high-churn tables often generate substantial amounts of synchronization metadata and are frequently associated with Data Sync performance issues.

7. Review Change Tracking Across All Tables

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName, OBJECT_NAME(object_id) AS TableName, begin_version, cleanup_version, min_valid_version FROM sys.change_tracking_tables ORDER BY TableName;

Why This Matters

This query helps identify whether metadata growth is isolated to a single synchronized table or occurring across multiple tables.

Troubleshooting Checklist

When troubleshooting Azure SQL Data Sync failures:

Confirm Change Tracking is enabled.
Identify the failing synchronized table.
Measure synchronization backlog size.
Review Change Tracking metadata.
Check database service-tier configuration.
Check Azure SQL governance events.
Review table size and update patterns.
Monitor resource utilization.
Validate synchronization health following remediation.

Root Cause Analysis

The investigation ultimately revealed several contributing factors:

A large synchronization backlog accumulated over time.
Change Tracking metadata continued to grow.
Synchronization enumeration operations became increasingly resource intensive.
Azure SQL Database resource governance began terminating long-running synchronization operations.
Data Sync repeatedly retried synchronization and encountered the same failures.

As a result, synchronization was unable to progress beyond the accumulated backlog and remained stuck in a failure cycle.

Resolution

The mitigation focused on reducing synchronization pressure and rebuilding synchronization state.

Recovery Approach

Remove the affected table from the Sync Group.
Save synchronization configuration changes.
Preserve business data through backups or archival.
Reset or recreate the synchronized table when appropriate.
Allow synchronization metadata cleanup.
Re-add the table to the Sync Group.
Trigger synchronization.
Validate successful synchronization completion.

Following this approach, synchronization resumed successfully without further failures.

Why the Resolution Works

This process addresses the underlying metadata problem rather than repeatedly retrying synchronization.

Benefits include:

Cleanup of excessive synchronization metadata
Elimination of accumulated backlog
Reset of synchronization state
Fresh synchronization initialization
Reduced enumeration workload

Technical Recommendations

Monitor synchronization health regularly.
Track synchronization latency.
Observe Data Sync metadata growth.
Investigate synchronization failures early.
Monitor DTU or vCore utilization.
Review high-volume synchronized tables.
Validate Change Tracking health periodically.
Monitor retries and failed sync operations.
Establish proactive alerting.
Review synchronization design for large-scale workloads.

Common Error Messages

Error 40197

The service has encountered an error processing your request.

Please try again.

Potential Causes

Transient platform interruption
Resource-governance intervention
Long-running synchronization operations

Error 40549

Error code 40549

Potential Causes

Excessive resource consumption
Long-running transactions
Synchronization enumeration pressure

Cannot Enumerate Changes at the RelationalSyncProvider

Cannot enumerate changes at the RelationalSyncProvider.

Potential Causes

Change Tracking backlog growth
Excessive synchronization metadata
Resource-governance intervention

Lessons Learned

Monitor Data Sync metadata growth proactively.
Investigate synchronization delays before backlog accumulates.
High-volume transactional tables require closer monitoring.
Resource governance can significantly impact synchronization workloads.
Reinitializing synchronization state may be necessary when metadata growth becomes excessive.

Key Takeaways

Azure SQL Data Sync depends heavily on Change Tracking metadata.
Large synchronization backlogs can cause expensive enumeration operations.
Errors 40197 and 40549 may indicate resource-governance interruptions.
Large metadata accumulation can trigger synchronization failures.
Monitoring synchronization health is essential.
High-volume tables require ongoing review.
Resetting synchronization state can be an effective recovery mechanism.

Conclusion

Azure SQL Data Sync remains a powerful solution for synchronizing data across Azure SQL Database environments. However, synchronization metadata and Change Tracking backlog growth can gradually evolve into serious operational challenges when left unchecked.

In this troubleshooting scenario, synchronization failures were ultimately traced to a combination of excessive Change Tracking backlog growth and Azure SQL Database resource-governance limits. By identifying the affected table, measuring backlog pressure using targeted T-SQL queries, evaluating governance events, and reinitializing synchronization state, synchronization was successfully restored and stabilized.

The key lesson is simple: proactive monitoring of Change Tracking metadata, synchronization backlog size, and Azure SQL workload health can prevent many Data Sync outages before they become business-impacting incidents.

Lessons Learned #540:Bulk Insert Throughput in Azure SQL Hyperscale with Partitioned Heap Tables

Jose_Manuel_Jurado — Wed, 17 Jun 2026 12:12:44 GMT

In this lesson learned, I would like to share an interesting scenario working on a service request where our customer was running a high-volume data load process in Azure SQL Database Hyperscale. The workload was based on a common pattern:

Recreate a staging table.
Load a large number of rows using bulk insert.

The bulk insert showed unstable execution times and became the main area to investigate.

The process was loading a very large number of rows into an Azure SQL Database Hyperscale database. The process used a staging table that was initially loaded as a heap. The main concern was the inconsistent execution time during the load process.

Why Manually Adding Data Files Was Not the Right Direction

In Azure SQL Database Hyperscale, the storage architecture is different from a traditional SQL Server deployment. The data layout and storage management are handled internally by the service.

Because of this architecture, manually creating or pre-allocating multiple data files is not the same tuning option that we may consider in SQL Server on-premises or SQL Server running on Azure Virtual Machines.

For this reason, the troubleshooting focus moved from manual file layout configuration to the actual workload pattern, waits, concurrency, batch size, and staging table design.

What We Observed

During the bulk insert phase, waits such as PAGELATCH_EX were observed. Since the staging table was loaded as a heap and the clustered primary key was created only after the bulk insert completed, OPTIMIZE_FOR_SEQUENTIAL_KEY was not directly applicable to the bulk insert phase.

This changed the direction of the investigation. Instead of focusing on last-page insert contention on an existing clustered index, the analysis moved toward heap insert behavior, allocation contention, concurrency, batch size, and whether a different staging table design could help.

First Recommendation: Start with Low-Impact Changes

Before changing the table design, the first recommendation was to test the least intrusive changes:

Reduce the number of concurrent bulk insert sessions.
Increase the batch size, for example from 10,000 rows to 50,000 or 100,000 rows.
Test TABLOCK on the dedicated heap staging table.

The goal was to avoid assuming that more concurrency would always reduce the total execution time. In some high-volume load scenarios, excessive concurrency may increase contention and make the process less stable.

The Interesting Design Option: Partitioned Heap Staging Table

One of the most interesting design options was to evaluate a partitioned heap staging table.

The idea is simple: instead of loading all rows into a non-partitioned heap staging table, the staging table can be created on the same partition scheme used by the target table, using the same partitioning column.

This does not mean that a partitioned heap will always be faster. However, it can be a useful design option when:

The bulk load phase is affected by allocation or latch contention.
Concurrent load processes can naturally distribute rows across different partition ranges.
The staging table is used only as an intermediate structure.Lessons Learned

The main lessons from this scenario were:

In Azure SQL Database Hyperscale, manually managing multiple data files is not the right tuning direction.
PAGELATCH_EX during heap loading may point to concurrency or allocation-related contention.
Reducing concurrency can sometimes improve total throughput.
Larger batch sizes may provide better results than many small batches.
TABLOCK on a dedicated heap staging table is a low-impact test worth evaluating.
A partitioned heap staging table can be a valid second-phase design option when the load can be distributed across partition ranges.
The best approach is to test small, measurable changes before introducing architectural redesigns.

Final Thoughts

A partitioned heap staging table can be a powerful option, but only when it is tested carefully and when the workload pattern can benefit from partition distribution.

Why do I see many VDI_CLIENT_WORKER sessions in Azure SQL Database — and do they impact performance?

Ashriti_Jamwal — Fri, 29 May 2026 06:52:21 GMT

Sometimes you’ll notice many sessions showing the command VDI_CLIENT_WORKER in Azure SQL Database—often around scaling, replica/copy workflows, or internal seeding operations. These sessions can look alarming, especially during a performance investigation, but they are typically internal background workers. This post explains how to recognize them, what’s safe to do (and what isn’t), and how to focus on the real bottlenecks like blocking/deadlocks or log rate throttling when you’re troubleshooting slowness.

Why you might see VDI_CLIENT_WORKER sessions in Azure SQL Database

The symptom

You run a session query (for example, using sys.dm_exec_requests or a monitoring tool) and observe:

Many sessions with command text VDI_CLIENT_WORKER
They may appear to be “stuck,” persist longer than expected, and can’t be killed
Teams may worry these sessions are “the cause” of slowness

Why it shows up in Azure SQL

In Azure SQL, VDI_CLIENT_* wait types and VDI_CLIENT_WORKER sessions are commonly associated with platform operations that involve copying/seeding—for example:

Scaling operations (service objective changes)
Geo-replication / copy workflows
Replica seeding-like behaviors

Important: The presence of these sessions does not automatically mean they are the bottleneck.

How to validate whether VDI_CLIENT_WORKER is benign?

1) Correlate to recent platform operations.

Ask: did you recently perform (or did the platform perform) one of these?

Scale up/down.
Creation of replicas / geo-secondary operations.
Any database copy-like workflow.

If yes, it’s a strong indicator you’re seeing background workers tied to that lifecycle event.

2) Check whether they consume resources.

A practical approach:

Look for CPU/IO/log pressure at the database level.
Compare the timing of slowness reports with spikes in waits/locks/log write percentage.

If these sessions show minimal resource consumption and are just “present,” treat them as background noise while you investigate real contention.

3) Don’t try to kill them!

These sessions are typically system/internal. Attempts to kill them may fail or be ineffective—and generally aren’t recommended.

4) If you need them to disappear.

In many cases, these internal workers naturally age out. If they remain visible and you need a cleanup path, operational actions like failover/restart may clear stale workers (use change control / maintenance windows as appropriate for your environment).
(This is a practical operational observation; always weigh downtime/impact.)

When performance is actually slow: focus on what usually hurts.

In many real-world incidents, the main causes of slowness are:

Blocking chains / deadlocks.
Transaction log rate throttling (LOG_RATE_GOVERNOR) during heavy DML.
Hot queries running concurrently and contending on the same objects.

Key takeaways

Seeing many VDI_CLIENT_WORKER sessions is often expected around platform copy/seeding workflows and doesn’t automatically indicate a bottleneck.
Don’t attempt to kill system/internal workers; instead, validate resource impact and focus on actual bottlenecks.
For real slowness, prioritize diagnosing blocking/deadlocks and LOG_RATE_GOVERNOR-driven DML throttling.

Unexpected PITR Charges from restorableDroppedDatabases After BC → Hyperscale Migration

Mohamed_Baioumy_MSFT — Thu, 14 May 2026 14:01:54 GMT

Why This Behavior Is by Design

When migrating an Azure SQL Database from Business Critical (BC) to Hyperscale using a manual cutover, some customers notice unexpected Point-in-Time Restore (PITR) backup storage charges appearing under the following resource:

/Microsoft.Sql/servers/<server>/restorableDroppedDatabases/<database>

At first glance, this can be confusing—especially when:

No customer-initiated drop or delete was performed
The database is online and healthy post-migration
Test migrations may not have shown similar charges

This post explains why this happens, why it is expected by design, and how these charges naturally expire.

The Observed Scenario

After a BC → Hyperscale manual cutover, customers may see PITR charges tied to:

restorableDroppedDatabases/<database-name>

Despite the database being active and available in Hyperscale, these charges start appearing immediately after the migration cutover and gradually decrease over time.

Why Does the Database Appear as “Dropped”?

During a manual cutover migration, Azure SQL performs an internal platform-driven workflow to complete the transition between architectures.

From a control-plane perspective:

The source Business Critical logical database is internally dropped
This drop is not initiated by the customer
It is a required system step to complete the Hyperscale migration

Telemetry confirms that the migration workflow transitions through states such as:

Internal drop of the source physical and logical database
Cleanup of metadata and completion of the migration

This entire sequence completes within seconds and is fully platform managed.

Why Are Backup Charges Generated?

Although the source BC database is internally dropped, its pre-migration PITR backups are still retained according to the configured backup retention period.

Here’s the key point:

Backups taken before upgrading to Hyperscale are retained and billed using the dropped-database backup billing model.

Because the source database is now considered dropped (from the BC perspective):

The 1× database-size discount no longer applies
The full data file size is added to the billable backup size
Charges appear under restorableDroppedDatabases

This behavior is explicitly documented as expected in internal Azure SQL billing guidance.

Why Do Charges Decrease Over Time?

These charges are not permanent.

They:

Decrease daily
Continue only while the pre-migration PITR backups are retained
Automatically stop once the retention window expires

In practical terms:

Charges stop when: days_since_migration > configured_backup_retention_days

No cleanup action is required from the customer—the platform handles this automatically.

Why Didn’t Test Migrations Show Similar Charges?

In many reported cases, test or smaller databases migrated using the same method did not generate noticeable charges.

This can be explained by two documented optimizations:

Backup size threshold – very small backup footprints are not charged
Low activity optimization – inactive or low-change databases generate fewer snapshots

As a result, smaller or lightly used test databases may fall below the billing threshold, while larger production databases do not.

Is This a Billing Error or Credit Scenario?

No.

Although the operation is platform-driven:

The behavior is by design
The charges are for temporary retention of valid PITR backups
They naturally expire based on retention

Therefore, this scenario is not considered a billing defect and does not typically warrant credits.

How Can Customers Reduce Charges Faster?

If needed, customers can:

Reduce the PITR backup retention period (minimum is 1 day)
Wait up to 24 hours for billing to reflect the change

This shortens how long the pre-migration backups are retained and billed.

FAQ – restorableDroppedDatabases Charges After BC → Hyperscale Migration

Q1: Why am I seeing PITR charges for restorableDroppedDatabases when my database is still online?

A: During a Business Critical → Hyperscale manual cutover, Azure SQL internally drops the source BC database as part of the migration workflow. While the Hyperscale database is active and healthy, the pre‑migration BC backups are retained and billed under restorableDroppedDatabases.

Q2: Did the customer initiate a drop or delete operation?

A: No. This drop is platform‑driven and required to complete the migration. It is not initiated by the customer.

Q3: What exactly is being billed?

A: The charges are for Point‑in‑Time Restore (PITR) backups taken before the migration. These backups are retained according to the configured backup retention period and are billed using the dropped database billing model.

Q4: Why does the cost appear higher than expected?

A: Once a database is considered “dropped” (from the BC perspective), the 1× database-size discount no longer applies, and the full data file size is included in the billable backup size.

Q5: Will these charges continue indefinitely?

A: No. The charges decrease daily and automatically stop once the pre‑migration backups expire based on the configured PITR retention period.

Q6: Why didn’t this happen with smaller or test databases?

A: Smaller or low‑activity databases may fall below the backup billing threshold, or benefit from low‑activity snapshot optimizations, resulting in no visible charges.

Q7: Is this a billing bug or credit-worthy scenario?

A: No. This behavior is by design and expected. The charges reflect valid backup retention and do not typically qualify for credits.

Q8: Can the customer reduce these charges sooner?

A: Yes. The customer can reduce the PITR backup retention period (minimum 1 day). Billing changes usually reflect within up to 24 hours.

Key Takeaways

The behavior is expected and by design
Charges come from pre-migration BC backups, not the active Hyperscale database
The database was internally dropped as part of migration, not by the customer
Charges decrease daily and stop automatically
No action is required unless the customer wants to reduce retention early

Final Note

As of the time of writing, this behavior is not clearly described in public customer-facing documentation, which explains why it often appears unexpected. Awareness of this mechanism can help set correct expectations when planning BC → Hyperscale manual cutover migrations.

Azure SQL BACPAC Export Failure with CDC & db_cdcreader (SQL71501)

Mohamed_Baioumy_MSFT — Wed, 20 May 2026 13:03:36 GMT

Overview

Exporting an Azure SQL Database to a BACPAC using SqlPackage / SSMS may fail when Change Data Capture (CDC) is enabled and database users (or Entra groups) are assigned to CDC-related roles such as db_cdcreader.

A common error observed:

Error SQL71501: Error validating element:

Role Membership: has an unresolved reference to Role [db_cdcreader].

This issue can be confusing because:

The database is healthy
CDC is functioning correctly
The error occurs only during export

Scenario

From a real customer case:

Azure SQL Database with CDC enabled
An Entra (AAD) group added to db_cdcreader
Export attempted via SqlPackage (v170+)
Export fails during schema validation phase

Root Cause Explained

1. SqlPackage performs strict schema modeling

During export, SqlPackage (via DacFx) builds a logical schema model of the database.

Every object must be fully resolvable
Roles and role memberships are validated
Any missing/unsupported object → export fails

This is why the error appears as:

SQL71501 – unresolved reference

2. CDC introduces system-managed objects

When CDC is enabled, SQL automatically creates:

cdc schema
System tables
Special roles:
- db_cdcreader
- cdc_admin

These objects are not treated as regular user-defined objects:

They are system-managed
Some are implicitly created
Some are not fully modeled/exported by DacFx

3. Role membership is the breaking point

The failure does not happen because the role exists
It happens because:

The role membership exists (e.g., Entra group → db_cdcreader)
But the role itself is not included or resolved in the export model

Result:

Membership → cannot resolve target role → validation failure (SQL71501)

This behavior aligns with documented patterns where CDC roles are excluded or not recognized during BACPAC export.

Reproducing the Issue

You are likely impacted if:

CDC is enabled
Users or Entra groups are assigned to:
- db_cdcreader
- cdc_admin
Export is attempted via:
- SqlPackage
- SSMS “Export Data-tier Application”

Workarounds

Option 1: Temporarily remove role membership

Remove the CDC role membership before export:

ALTER ROLE db_cdcreader DROP MEMBER [your_user_or_group];

Run export, then reassign:

ALTER ROLE db_cdcreader ADD MEMBER [your_user_or_group];

This is the simplest and most reliable workaround
Confirmed in Microsoft Q&A guidance for CDC roles

Option 2: Export from a cleaned database copy

If you cannot modify production (e.g., tooling restrictions):

Create a database copy
Remove CDC-related role memberships
Export from the copy

Recommended when:

Using automation tools (e.g., Commvault)
Production changes are restricted

Option 3: Cleanup unsupported references

General best practice:

Remove unsupported / system-bound references before export
Especially:
- CDC role memberships
- Legacy system objects

Option 4: Use SqlPackage with ExtractAllTableData=True

Another practical workaround is to leverage the SqlPackage option ExtractAllTableData=True, which allows you to extract all data from all user tables.

When set to True:
- Data is extracted from all user tables
- You cannot specify individual tables
When set to False (default):
- You can selectively extract data from specific tables only
- This reduces exposure to unsupported or problematic objects during validation

Example

SqlPackage /Action:Extract /SourceServerName:<server> /SourceDatabaseName:<database> /TargetFile:<output.bacpac> /p:ExtractAllTableData=True

When to use this option

When the export fails due to CDC roles or related schema validation issues (SQL71501)
As a targeted workaround when full export is blocked

Important Considerations

This is not a runtime database issue
It is a schema validation limitation in DacFx / SqlPackage
CDC itself is supported, but:
- Certain security objects are not fully exportable

Key Takeaways

SQL71501 during export is often a model validation issue, not a data issue
CDC roles (db_cdcreader, cdc_admin) can break export due to partial modeling
The failure is triggered by role membership, not CDC itself
Workarounds involve:
- Removing memberships
- Exporting from a cleaned copy

Azure SQL Database BACPAC Export Stuck with Private Link and Resource Locks

hudajazmawi — Tue, 28 Apr 2026 06:07:55 GMT

Symptoms

In real customer environments, this issue commonly appears in the following forms:

An export job is stuck at Running (100%) and never completes, blocking scheduled or automated export jobs.
A BACPAC export operation is canceled (via Azure Portal or CLI), but remains in CancelInProgress for an extended period (for example, more than n hours) without progress.
Multiple databases are impacted, where previous export operations remain stuck, preventing new export requests from starting.
New export attempts fail with the following error: "There is an import or export operation in progress on the database."

These scenarios can significantly impact automation pipelines, backup strategies, and overall operational workflows.

Example from Azure Portal

The following example shows an export operation stuck during cancellation, along with the corresponding error message:

Figure 1: Export operation stuck in CancelInProgress and error indicating another operation is in progress

Root Cause

When you enable the Use private link option during import or export, the service automatically creates temporary, service-managed private endpoints to securely connect the Azure SQL Database to the target Storage Account. You must manually approve the private endpoint for both the Azure SQL logical server and the Azure Blob storage account in separate steps. This tutorial includes the details.

These private endpoints are automatically deleted during the cleanup phase, which occurs when the export operation completes or is canceled.

However, if a Delete lock exists on any of the following resources:

Azure SQL Logical Server / Database
Azure Storage Account
Resource Group (might be an inherited lock)

As a result:

The service cannot delete the service-managed private endpoints
Backend cleanup operations fail to complete
The export/cancel operation remains stuck in an intermediate state

This leads to scenarios where:

Export appearing completed (100%) but not finalized
Cancellation stuck in CancelInProgress
Further Import/Export operations are blocked

This behavior is consistent with cases where resource locks prevent required cleanup operations: Import or Export a Database Using Private Link - Azure SQL Database | Microsoft Learn

Figure 2: Limitation of using Private Link for Import/Export with resource locks applied

Resolution

To unblock the operation:

Remove the Delete lock from the affected resources:
- Azure SQL Server / Database
- Azure Storage Account
- (Check Resource Group level for inherited locks)
Wait for the backend system to:
- Complete Private Endpoint cleanup
- Finalize the export or cancellation operation
Validate that:
- Operation transitions out of Running / CancelInProgress
- No active Import/Export operation remains

Key Takeaway

Import/Export operations using Private Link depend on temporary service-managed infrastructure (private endpoints).

Any restriction (such as a Delete lock) that prevents cleanup of these resources can cause the operation to remain stuck at completion or cancellation phases.

References

Azure SQL Import/Export using Private Link (Microsoft Learn) Import or Export a Database Using Private Link - Azure SQL Database | Microsoft Learn

How to recover Azure SQL Managed Instance access when the admin SQL login is disabled

Abdullah_Qtaishat — Thu, 23 Apr 2026 12:05:50 GMT

Overview

Some of you have probably ran into the issue of losing administrative access to Azure SQL Managed Instance because the SQL login(s) are disabled, or because the login is no longer a member of the sysadmin fixed server role. Since we have received some cases on the matter, I'll be explaining one possible way to recover access in this scenario by using a Microsoft Entra administrator. Azure SQL Managed Instance supports both SQL logins and Microsoft Entra authentication, and the Microsoft Entra administrator for the managed instance can be set from the Azure portal, PowerShell, Azure CLI, or REST API.

Symptom

Customers might see an error similar to the following when trying to connect with the SQL login:

Login failed for user 'sqlmiadmin'. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470)

Connection ID: 'XYZ'

Scenario Details

In the scenario discussed here, the SQL login(s) on the managed instance were either disabled, or their membership in the sysadmin fixed server role was removed. As a result, there was no remaining SQL principal available with sufficient permissions to reverse the change directly. One possible recovery method is to configure, or reconfigure, a Microsoft Entra administrator for the managed instance and use that path to regain administrative access. Setting the Microsoft Entra administrator enables Microsoft Entra authentication for Azure SQL Managed Instance.

Root Cause

The issue occurs because the required T-SQL statements can only be executed by principals that already have sufficient server-level permissions:

ALTER LOGIN ... ENABLE requires ALTER ANY LOGIN. If the target login is a member of sysadmin, enabling or disabling that login also requires CONTROL SERVER.
ALTER SERVER ROLE [sysadmin] ADD MEMBER ... can only be executed by a principal that is already a member of sysadmin or of that same fixed server role. CONTROL SERVER and ALTER ANY SERVER ROLE are not sufficient for adding members to a fixed server role.

Because of this, when no existing SQL principal can perform these actions or all logins gets disabled, an alternate administrative way is needed.

Resolution

One possible way to resolve the issue is to configure a Microsoft Entra administrator for the managed instance by following the steps in Configure Microsoft Entra Authentication - Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn. In the Azure portal, go to the SQL managed instance resource, open Microsoft Entra ID under Settings, choose Set admin, select the required user or group, and then select Save. The same article also documents that you can remove the current Microsoft Entra admin and set it again if needed.

After the Microsoft Entra administrator is configured, connect to the managed instance by using a supported Microsoft Entra authentication method, such as Microsoft Entra Password or Microsoft Entra MFA.

If the issue is that the SQL login is disabled, run the following T-SQL after connecting with a principal that has the required permissions:

ALTER LOGIN [sqlmiadmin] ENABLE; GO

If the issue is that the login is no longer a member of sysadmin, run the following T-SQL:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [sqlmiadmin]; GO

Note

If the Microsoft Entra administrator was already configured and the Microsoft Entra login was also disabled. In such cases, you can remove the Microsoft Entra administrator from the Azure portal and set it again. This refreshes the Microsoft Entra administrator configuration for the managed instance and restores the Microsoft Entra authentication path.

Additional Resources

Configure Microsoft Entra Authentication - Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn

Connect with Microsoft Entra Authentication - Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn

ALTER LOGIN (Transact-SQL) - SQL Server | Microsoft Learn

ALTER SERVER ROLE (Transact-SQL) - SQL Server | Microsoft Learn

Disclaimer

Please note that products, features, and configuration options discussed in this article are subject to change. This article reflects the state of Azure SQL Managed Instance as of April 2026.

I hope you found this article helpful. Please feel free to share your feedback in the comments section.

Azure SQL (LTR): You Don’t Need to Copy LTR Backups Across Regions to Restore Them

Mohamed_Baioumy_MSFT — Wed, 22 Apr 2026 18:31:59 GMT

Summary

Customers sometimes attempt to copy Azure SQL Long-Term Retention (LTR) backups across regions using Copy-AzSqlDatabaseLongTermRetentionBackup, only to hit the error:

LongTermRetentionMigrationRequestNotSupported
LTR backup migration copy feature is not supported on subscription

This blog clarifies why this happens, when LTR backup copy is actually supported, and most importantly the correct and supported way to restore an LTR backup into a different region without copying it.

The Common Scenario

A customer has:

An LTR backup stored in Region A
A need to restore the database into Region B
The assumption that the LTR backup must first be copied cross-region

They attempt:

Copy-AzSqlDatabaseLongTermRetentionBackup

and immediately receive a platform validation error stating the feature isn’t supported on their subscription.

Why This Error Happens

The key misunderstanding is what the LTR backup copy API is actually for.

Copy-AzSqlDatabaseLongTermRetentionBackup is NOT a general-purpose feature

This API is:

Backend-gated
Allowlist-only
Intended only for region decommissioning scenarios

In other words:

It is not supported for normal customer-driven migrations
There is no portal toggle or feature registration
Subscriptions are only allowlisted when Microsoft is retiring a region, and LTR backups must be preserved elsewhere.

Because of this, most subscriptions - will receive:

LongTermRetentionMigrationRequestNotSupported

The Correct & Supported Solution

Good news:

You do NOT need to copy the LTR backup to another region to restore it there.

Azure SQL allows you to:

Restore an LTR backup directly to any Azure SQL logical server, in any region.

Supported Approach: Restore LTR Backup Directly

Use Restore-AzSqlDatabase with the -FromLongTermRetentionBackup switch.

Example (PowerShell)

Restore-AzSqlDatabase ` -FromLongTermRetentionBackup ` -ResourceId $ltrBackup.ResourceId ` -ServerName $serverName ` -ResourceGroupName $resourceGroup ` -TargetDatabaseName "Test" ` -ServiceObjectiveName P1

This works across regions
No backend enablement required
Fully supported and documented

How This Works (Important Concept)

LTR backups are stored in geo-redundant storage
The restore operation does not depend on the original region
The platform automatically handles data access and restores placement

So, while the backup physically originated in Region A, you are free to restore it to Region B, C, or any supported Azure region without copying it first.

When Is LTR Backup Copy Actually Used?

Only in this scenario:

Microsoft-initiated region decommissioning

In that case:

LTR backups must be relocated to remain available
Subscriptions are temporarily allowlisted
Copy-AzSqlDatabaseLongTermRetentionBackup is enabled at the backend

Outside of this scenario, the API is intentionally restricted.

Key Takeaways

You can restore an LTR backup to any region directly
You do not need (and usually cannot use) LTR backup copy
Backup copy is gated and reserved for region retirement scenarios
Use Restore-AzSqlDatabase -FromLongTermRetentionBackup instead

Final Recommendation for Customers

If customers encounter this error:

Reassure them this is not a misconfiguration or permission issue
Explain that LTR restore is the correct solution
Avoid escalation for feature enablement unless a region retirement is involved

Azure Data Sync: Fixing “Cannot find the user ‘DataSync_executor’” When Creating a New Sync Group

Mohamed_Baioumy_MSFT — Wed, 22 Apr 2026 17:17:34 GMT

Summary

When creating a new Azure SQL Data Sync group, customers may encounter the following error during setup—even when no active sync groups exist:

“Failed to perform data sync operation: Cannot find the user 'DataSync_executor', because it does not exist or you do not have permission.”

This failure typically occurs during certificate and symmetric key creation as Azure attempts to grant permissions to the DataSync_executor role. In this post, we’ll walk through:

The common scenario where this issue appears
Why cleanup scripts alone may not fix it
A supported, reliable resolution approach to restore Data Sync successfully

The Problem Scenario

A customer attempts to create a brand-new Azure SQL Data Sync group (hub + members), but the operation fails with an error similar to:

Cannot find the user 'DataSync_executor', because it does not exist or you do not have permission. Creating certificate Creating symmetric key Granting permission to [DataSync_executor] on certificate

Key observations from affected cases:

No active sync group exists
Cleanup scripts (including Data Sync complete cleanup.sql) were already executed
The failure persists even after retrying the setup

Why This Happens

Azure SQL Data Sync depends on system-managed database roles that must be created and configured only by the Azure Data Sync service itself.

If these roles (or related permissions) are:

Missing
Partially deleted
Left in an inconsistent state

then Data Sync may fail while attempting to create certificates or grant required permissions.

Important:
Manually creating or partially restoring these roles is not supported and often leads to repeated failures.

How to Detect the Issue

Before troubleshooting further, confirm whether the required Data Sync roles are missing.

1. Run the Data Sync Health Checker

Ask the customer to run Data Sync Health Checker, then review SyncDB_Log.

Common warnings include:

DataSync_reader IS MISSING
DataSync_executor IS MISSING
Missing EXECUTE/SELECT permissions on dss and TaskHosting schemas

This confirms the root cause is role and permission inconsistency.

Supported and Effective Resolution

Step 1: Verify Roles Are Missing

Run the following query on each affected database (hub and members):

SELECT name FROM sys.database_principals WHERE name IN ('DataSync_executor', 'DataSync_reader');

If no rows are returned, the roles are missing and must be recovered by Azure Data Sync itself - not manually.

Step 2: Fully Clean Up Leftover Data Sync Objects

Do this only if the database is not actively syncing

-- Remove roles if partially present DROP ROLE IF EXISTS DataSync_executor; DROP ROLE IF EXISTS DataSync_reader; -- Drop DataSync schema IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'DataSync') BEGIN DROP SCHEMA DataSync; END

This ensures there are no partial or orphaned Data Sync objects left behind that could interfere with setup.

Step 3: Recreate the Sync Group (Critical Step)

Do not manually recreate roles or permissions
Instead:

Delete the existing (failed) Sync Group from the Azure Portal
Recreate the Sync Group from scratch
Re-add the hub and member databases

During this process, Azure will automatically:

Recreate DataSync_executor and DataSync_reader
Assign all required permissions
Deploy the correct schemas, certificates, and procedures

Key Takeaways

DataSync_executor and DataSync_reader are service-managed roles
Cleanup scripts alone may not fully reset a broken state
Manual role creation is not supported
Deleting and recreating the Sync Group is the only reliable recovery method once roles are missing

Final Recommendation

If you encounter Data Sync setup failures referencing DataSync_executor, always:

Validate role existence
Fully clean up broken artifacts
Let Azure Data Sync recreate everything by rebuilding the Sync Group

This approach consistently resolves the issue and restores a healthy Data Sync deployment.

Data Migration - From SQL MI to SQL DB migration

akiohose — Wed, 22 Apr 2026 05:25:10 GMT

Overview

In this post, I’ll show you how to perform a data migration from Azure SQL Managed Instance (SQL MI) to Azure SQL Database (SQL DB) using a combination of schema + security migration steps and Azure Data Factory (ADF) for moving the data. In an earlier post (Data Migration - Azure SQL MI and Azure SQL DB | Microsoft Community Hub), I briefly covered several approaches for moving data from SQL MI to SQL DB. This article focuses on an approach that works well when you want a repeatable data-movement pipeline (ADF) but still need to migrate database objects and users as part of a complete cutover. Using ADF is a good option when you need a repeatable pipeline or when the source data volume is large. This article walks one of the methods you can use, particularly if source data size is large. This post will walk you through the key configuration steps to copy data from SQL MI to SQL DB using ADF.

Note: If you want to migrate in the opposite direction (Azure SQL Database → SQL Managed Instance), you can follow the same general flow in this article—just swap which platform is the source and which is the destination and adjust any feature-compatibility work accordingly.

The steps below assume that you are migrating data over a private endpoint so that all traffic remains on a secure, private network path. In this configuration, you deploy a Windows VM to host a Self‑Hosted Integration Runtime (SHIR). If your requirements permit data movement over the public network, you can instead use the Azure Integration Runtime (Azure IR), which removes the need for a Windows VM and SHIR. In that case, Azure IR handles the data movement, and traffic flows over the public network rather than through a private endpoint.

High-level steps

Assess compatibility and feature differences (SQL MI vs SQL DB)
Migrate schema and database objects (tables, views, procs, functions, etc.)
Migrate users and permissions (and map logins to users where applicable)
Deploy Azure Data Factory (ADF)
(If using private endpoints) Provision a Windows VM to host SHIR and register it in ADF
Create linked services (connections) for SQL MI and SQL DB
Run the data copy using the Copy Data tool (or a pipeline), validate, and cut over

Pre-migration assessment (SQL MI → SQL DB)

There is no automated assessment tool available to evaluate readiness for migrating between Azure SQL Managed Instance and Azure SQL Database. As a result, pre‑migration assessment is a manual exercise. Before migrating, you should validate high‑level compatibility, including supported features, removal of instance‑level or cross‑database dependencies, security model alignment, target service tier sizing, and application connectivity behavior. Differences in platform capabilities and architecture should be reviewed early to avoid migration or post‑migration issues.

Here is check list to consider:

☐ Feature compatibility reviewed (no unsupported MI features in use)
- Compare SQL Database Engine Features - Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn
- SSMS: Connect and query data - Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn
☐ No instance‑level or cross‑database dependencies required
☐ Security model validated (database‑scoped users and authentication)
☐ Target SQL Database service tier identified and sized
☐ Application connectivity updated (connection strings, retries, HA behavior)
☐ Proof‑of‑concept testing completed with a representative database

Migrate schema and database objects

ADF is primarily a data movement service, so migrate your (*1) schema and programmable objects first. Common options include deploying a DACPAC (SqlPackage) to Azure SQL Database, using SQL Server Data Tools (SSDT) to publish a database project, or generating scripts from SSMS (tables, views, stored procedures, functions, synonyms, sequences, user-defined types, etc.). Ensure the target schema matches the source (data types, collations, constraints) before you start the bulk data copy.

(*1) In the Copy Data tool step (described later), ADF can auto-create the table schemas (and views) based on the source tables. So, you can skip migrating the schema upfront; however, programmable objects (stored procedures, function, etc.) and other non-schema objects have to be migrated.

Create the target Azure SQL Database (and choose the correct server, tier, and size).
Deploy the schema and objects to SQL DB (DACPAC/SSDT/scripts).
Create supporting objects needed for the load (schemas, filegroups aren’t applicable in SQL DB, but schemas and tables are).
Decide when to create indexes and foreign keys: for very large loads, creating them after the data copy can significantly speed up ingestion.

Deploy ADF

Follow the steps in Create an Azure Data Factory - Azure Data Factory | Microsoft Learn to deploy ADF. After deployment, open Azure Data Factory Studio—we’ll use it to configure the integration runtime, linked services, and the copy activity. Following ADF deployment, launch the Azure Data Factory Studio. We will come back to what needs to be configured in ADF Studio

Provision Windows VM – used to host SHIR

This Azure VM hosts the Self-hosted Integration Runtime (SHIR), which ADF uses to connect to your SQL MI and SQL DB over private networking and to run the copy operation.

Configure SHIR in Azure Data Factory and install SHIR on Azure VM

On the page for your data factory, select Launch Studio to open Azure Data Factory Studio. The steps below assume you are working from the Azure VM that will host SHIR. If the VM can’t download the SHIR installer (for example, it has no outbound internet access), download the installer from a machine that has internet access and then copy it to the VM.

Select (1) Manage, and then (2) Integration runtimes to display the integration runtimes.

Select New which will display the Integration runtime setup page at right.

Select Azure, Self-Hosted, and then select Continue.
Select Self-Hosted, and then select Continue.
Enter a name for the integration runtime, and then select Create.
For this example, use Manual setup (Option 2) rather than express setup. Download the integration runtime installer from the provided link.

After downloading the SHIR installer, close the page for now. Back on the Integration runtimes page (in ADF studio), you’ll see the new runtime listed with a status of 'Unavailable'. It will change to 'Running' after you install and register SHIR on the VM.

Run the SHIR installer (the .msi file) and complete the installation wizard. At the end of the wizard, you’ll be prompted to register the integration runtime.

To get the authentication key, return to ADF Studio, select your integration runtime on the Integration runtimes page to open Edit integration runtime, and then copy the key. Paste the key into the SHIR registration dialog on the VM. Select Register to complete registration.

After registration completes, select Finish to close the wizard. You can leave Enable remote access from intranet unchecked unless you plan to add additional SHIR nodes for high availability and scalability. You can change this setting later if needed.

If the registration succeeds, you will see the integration runtimes is at running status.

Configure Linked Service (connections to source and target)

In this step, you configure connections to the source (SQL MI) and the target (Azure SQL DB).

Important: ADF copy activities migrate data. You must create the target database ahead of time.

In ADF Studio, go to Manage > Linked services. This will display the New linked service pane.

Note: If you already have linked services created for both SQL MI and SQL DB, you can reuse them—when configuring the Copy Data tool or a pipeline, simply select the existing linked service for the source and the existing linked service for the destination.

Search for 'sql' to display related connectors. in Data stores. Select the connector for SQL MI, and then select Continue.

Enter required properties to connect to SQL MI.

For Connect via integration runtime, select the self-hosted integration runtime you created earlier.

Set the remaining properties as appropriate for your environment (server name, database name, authentication, etc.). For other properties, set/enter appropriate values to connect to your server.

Example settings used in this demo (key properties only):

Endpoint type: Private endpoint
Authentication type: SQL authentication (demo only—use your preferred/approved auth method)
Trust server certificate: Selected

Next, create a linked service for the target server (Azure SQL Database). Follow the same general steps you used for SQL MI, selecting the appropriate connector and the same SHIR (if you are using private endpoints). You can follow the same steps as done with SQL MI

When finished, you should see two linked services configured—one for SQL MI and one for SQL DB.

Configure Ingestion and run data migration

Now we are ready to perform the data migration. Select [Home] at the ADF Studio and select [Ingest]. This will display the Copy Data tool page.

Important: Before you start, make sure the target database exists in Azure SQL Database and that you have permissions to create/insert data into the target tables.

For larger migrations, consider copying in batches (for example, by date/key range) and using parallelism where appropriate to improve throughput while staying within SQL DB resource limits. Plan time for validation (row counts/checksums or targeted queries) and decide how you will handle ongoing changes during cutover (for example, a final delta load or an application downtime window).

The Copy Data tool page

Select Built-in copy task > Run once now, and Continue

Select Source type and connection. Select table(s) to migrate.

Select Next at the [Apply filter] page

Select the destination

Select Next to get to Column mapping page. Unless you want to specify explicit mapping to customize column/field mapping from source to destination, select Next to accept default settings.

At the Copy Data tool page, leave all with default settings and continue Next.

On the Summary page, select Next to start data migration.

After selecting Next, the migration starts. You can select Finish to close this page and continue monitor the migration progress from the Monitor blade.

Checking migration status

You can select Finish to close the page and go to Monitor to monitor the migration progress.

Validation and cutover

After schema, security, and data are migrated, validate the target before switching applications over to SQL DB. Validation typically includes row counts, checksum sampling, and running key application queries and reports against the new database. For cutover, plan how you will handle changes occurring on SQL MI while the bulk copy is running (for example, schedule downtime, or run a final delta load if your design supports it).

Validate schema: object counts, constraints, and (if used) post-deployment scripts.
Validate security: users/groups exist, role memberships are correct, and the app can connect with least privilege.
Validate data: row counts per table, spot-check aggregates, and targeted checksum comparisons where feasible.
Cut over: update connection strings/DNS, monitor workload in SQL DB, and keep a rollback plan.

Notes and Observations

Migrating database objects

Although this article recommends migrating schemas and database objects before copying data, this demo relies on Azure Data Factory’s automatic schema generation for simplicity. This is useful for testing or learning ADF workflows, but it is not suitable for production migrations.

The Wide World Importers sample database, that was used for the source database, includes a memory‑optimized table, but ADF generated a standard table instead. This illustrates a key limitation: ADF creates tables using basic metadata (columns, data types, and keys) and does not generate full, feature‑equivalent schemas.

Best practice: Use tools such as SSMS, SSDT, DACPAC, or BACPAC to prepare complete schemas and objects in the target database and use ADF strictly for data movement.

Online migration

Online migration keeps the source database available while data is copied to the target using a repeatable pipeline such as Azure Data Factory. Schema and security are prepared in advance, and the bulk data copy runs without requiring an extended outage.

Changes made to the source during the copy aren’t automatically synchronized, so a planned cutover—such as a short downtime window or final validation step—is still required to ensure data consistency before switching applications to the target.

Summary

Migrating from SQL MI to Azure SQL Database is more than a single copy operation. A successful full migration typically includes (1) assessing feature compatibility, (2) deploying schema and objects to the target, (3) recreating users/roles/permissions, and (4) using ADF to move the data in a repeatable, monitorable way—optionally through private networking with SHIR. After the copy completes, validate schema, security, and data, then execute a planned cutover to move applications to SQL DB.

Assess and remediate MI→SQL DB compatibility gaps.
Migrate schema + programmable objects (DACPAC/SSDT/scripts) before loading data.
Migrate security: users, roles, role memberships, and permissions (prefer Entra ID where possible).
Configure ADF (and SHIR if using private endpoints), then run Copy Data/pipelines for the data load.
Validate results and cut over with a rollback plan.

Fix failover group creation errors with TDE CMK on Azure SQL Managed Instance

Abdullah_Qtaishat — Tue, 21 Apr 2026 11:06:46 GMT

Overview

We have received several support cases where customers encounter the error shown below when attempting to create a failover group for Azure SQL Managed Instance. In this article, we explore one of the possible causes of this error.

Creating instance failover group failed.

An unexpected error occured while processing the request. Tracking ID: 'XYZ'

Prerequisites Review

This article focuses on an additional configuration requirement beyond what is documented in
Configure a failover group - Azure SQL Managed Instance | Microsoft Learn:

The secondary managed instance must be empty, without any user databases.
The configuration of your primary and secondary instance should be the same to ensure the secondary instance can sustainably process changes replicated from the primary instance, including during periods of peak activity. This includes the compute size, storage size, and service tier.
The IP address range for the virtual network of the primary instance must not overlap with the address range of the virtual network for the secondary managed instance, or any other virtual network peered with either the primary or secondary virtual network.
Both instances must be in the same DNS zone. When you create your secondary managed instance, you must specify the primary instance's DNS zone ID. If you don't, the zone ID is generated as a random string when the first instance is created in each virtual network and the same ID is assigned to all other instances in the same subnet. Once assigned, the DNS zone can't be modified.
Network Security Groups (NSG) rules for the subnets of both instances must have open inbound and outbound TCP connections for port 5022 and port range 11000-11999 to facilitate communication between the two instances.
Managed instances should be deployed to paired regions for performance reasons. Managed instances that reside in geo-paired regions benefit from a significantly higher geo-replication speed compared to unpaired regions.
Both instances must use the same update policy.

In the scenario discussed here, all of the above requirements were fully satisfied.

Scenario Details

The primary SQL Managed Instance was configured to use customer‑managed keys (CMK) for Transparent Data Encryption (TDE), with automatic key rotation enabled.

The secondary SQL Managed Instance was configured to use service‑managed keys (SMK) or it can be configured with a different CMK than the one used by the primary instance for database encryption.

Root Cause

The issue occurs because all servers participating in geo‑replication must share the same key material as the encryption protector of the primary server. This requirement is documented in
Customer-managed transparent data encryption (TDE) - Azure SQL Database & Azure SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn.

In this scenario, the mismatch between CMK on the primary instance and SMK on the secondary instance caused the failover group creation to fail.

Resolution

If there is a requirement not to encrypt the secondary managed instance databases using the same key as the primary for example, to continue using SMK you can still satisfy the failover group requirement without changing the active encryption protector on the secondary instance.

To achieve this:

Add the primary instance’s TDE key to the secondary managed instance.
Ensure that the option “Make this key the default TDE protector” is disabled.

This allows the key to be used solely for failover group operations while keeping SMK or different CMK as the active TDE protector on the secondary instance after failover group creation.

After fixing the issue, failover group was successfully created.

Additional Resources

Configure a failover group - Azure SQL Managed Instance | Microsoft Learn

Customer-managed transparent data encryption (TDE) - Azure SQL Database & Azure SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn

Disclaimer

Please note that products, features, and configuration options discussed in this article are subject to change. This article reflects the state of Azure SQL Managed Instance as of April 2026.

We hope you found this article helpful. Please feel free to share your feedback in the comments section.

How to take copy only backups with SQL Managed Instance.

ahmaddaoud — Sat, 25 Apr 2026 21:57:06 GMT

In Azure SQL Managed Instance, copy-only backups cannot be created for databases encrypted with service-managed Transparent Data Encryption (The default for all newly provisioned SQL MI). Service-managed TDE relies on an internal encryption key that cannot be exported, which means the backup cannot be restored outside that environment. You will need to change your SQL MI TDE service key for this process (I will explain how to do this in Step 4 below).

In this article, I will explain how to prepare your Azure SQL Managed Instance (SQL MI) to take copy-only backups to Azure Blob Storage by using Managed Identity together with customer-managed encryption keys.

These steps ensure that SQL MI can encrypt the backup by using your own key and securely write it to your storage account without relying on shared keys or SAS tokens.

Step 1: Create or Identify a Backup Encryption Key in Azure Key Vault

First, ensure that you have a Key Vault containing a key that will be used to encrypt the backup. It is essential to have elevated privileges over this key vault, and it's recommended to use a dedicated key for the backup encryption.

Note the Key Identifier:

https://contoso.vault.azure.net/keys/contosokey/01234567890123456789012345678901

as it will be required later when adding the key to SQL MI. This key will be used by SQL MI to encrypt the backup before it is written to Azure Blob Storage.

Step 2: Grant Key Vault Permissions to the SQL MI Managed Identity

SQL Managed Instance uses a system-assigned managed identity. This allows SQL MI to use the key for encryption and decryption operations during backup and restore. This identity must be granted escalated privileges to use the encryption key stored in Key Vault.

In the Key Vault:

1) Navigate to Access control (IAM) or Access policies (depending on whether RBAC or Access Policies are enabled).

2) Choose the SQL MI managed identity.

3) Assign it this role: "Key Vault Crypto Service Encryption User".

Note: If you're using Access Policies mode, then the required key permissions are Get, Wrap Key, Unwrap Key.

Step 3: Grant Storage Permissions to the SQL MI Managed Identity

Next, SQL MI must be authorized to write backup files to the target storage account. This role assignment allows SQL MI to create, write, and manage blobs within the specified container. No storage account keys or SAS tokens are required when using Managed Identity.

In the storage account that hosts your backup container:

1) Go to Access control (IAM).

2) Choose the SQL MI managed identity.

3) Assign it this role: "Storage Blob Data Contributor"

Note: Verify that connectivity from the storage account to SQL MI is established successfully. The storage account must allow the SQL MI subnet to connect to it. Use this script to test connectivity to the storage account from SQL MI: how-to-test-tcp-connection-from-mi

Step 4: Add the Encryption Key to SQL Managed Instance

After Key Vault access has been configured, the encryption key must be registered within SQL MI. You can add the key in two ways:

A) From Azure portal:

Navigate to the relevant SQL Managed Instance, then select the Security option from the left-hand menu. Under Security, open Transparent Data Encryption. From there, you will be able to select the appropriate key from the available drop-down list. Click save once your choice is complete.

B) From Azure Cloud Shell/PowerShell:

First, add the key first with this command:

Add-AzSqlInstanceKeyVaultKey -ResourceGroupName 'ContosoResourceGroup' -InstanceName 'ContosoManagedInstanceName' -KeyId 'https://contoso.vault.azure.net/keys/contosokey/01234567890123456789012345678901'

Then set it as the TDE protector:

Set-AzSqlInstanceTransparentDataEncryptionProtector -Type AzureKeyVault -InstanceName "ContosoManagedInstanceName" -ResourceGroupName "ContosoResourceGroup" -KeyId "https://contoso.vault.azure.net/keys/contosokey/01234567890123456789012345678901" -AutoRotationEnabled $true

Step 5: Create a Credential for the Target Blob Container

Finally, create a SQL credential that maps the Azure Blob Storage container to SQL MI’s managed identity.

Run the following statement on the SQL Managed Instance after you verify that your storage blob container has been created already:

CREATE CREDENTIAL [https://contoso.blob.core.windows.net/myfirstcontainer] WITH IDENTITY = 'Managed Identity';

Important notes: The credential name must exactly match the container URL. This credential is used by SQL Server during BACKUP DATABASE … TO URL.

You have now successfully configured your SQL MI to be able to take copy only backups! This is an example of how your backup command should like:

BACKUP DATABASE ... TO URL = '...' WITH COPY_ONLY, COMPRESSION;

The below sections will explain the common errors that you may run into while taking copy only backups.

Common errors that you may encounter when attempting copy-only backups:

1) Error#1:

Msg 3271, Level 16, State 1, Line 1
A nonrecoverable I/O error occurred on file '<URL to bak file>' Backup to URL received an exception from the remote endpoint. Exception Message: Unable to connect to the remote server.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

This error is caused by:

A) An IP or a port 443 block from NSGs/firewalls.

B) SQL MI is not able to reach the storage blob container due to some network misconfiguration from either side.

Please review your network settings in the storage account and your NSGs and firewall.

2) Error#2:

Msg 3201, Level 16, State 1, Line 1

Cannot open backup device '<URL to the bak file>'. Operating system error 86(The specified network password is not correct.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

This error is caused by misconfigured credentials or missing permissions. Please verify that your SQL MI managed identity has the "Storage Blob Data Contributor" role and the above CREATE CREDENTIAL query was executed with the correct URL name.

3) Error#3:

Msg 3202, Level 16, State 1, Line 1
Write on "<URL to bak file>" failed: 1117(The request could not be performed because of an I/O device error.)
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

This error occurs because your database has passed the blob storage block limit. Depending on the transfer size, a single backup file is capped at roughly 50,000 × MAXTRANSFERSIZE. So, when the individual backup file is more than that, you get this error.

You need to stripe the backup into multiple files to avoid this failure of backup and make sure that MAXTRANSFERSIZE is equals to 4 MB in your TSQL command.

Example:

BACKUP DATABASE […] TO URL = '<storage URL>/backup/DB_part01.bak', […] URL = '<storage URL>/backup/DB_part20.bak', WITH COPY_ONLY, COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536;

Disclaimer
Please note that products and options presented in this article are subject to change. This article reflects for Azure SQL Managed Instance in April 2026.

I hope this article was helpful for you, please feel free to share your feedback in the comments section.

Understanding action_id discrepancies in Azure SQL Database Audit Logs (BCM vs AL / CR / DR)

Sunaina_Agarwal — Thu, 16 Apr 2026 11:41:59 GMT

Overview

While working with Azure SQL Database auditing in enterprise environments, you may encounter an inconsistency in how the action_id field is captured across different PaaS SQL servers.

In one such scenario, a customer observed:

PaaS Server	action_id observed for similar DDL statements
Server A	AL, CR, DR
Server B	BCM only

This inconsistency impacted downstream compliance pipelines, as the audit data was expected to be captured and interpreted uniformly across all servers.

This article explains:

How Azure SQL Auditing works by default
What causes BCM to appear instead of AL/CR/DR
How to standardize audit logs across PaaS servers

How Azure SQL Database Auditing Works?

Azure SQL Database auditing uses a managed and fixed audit policy at the service level.

When auditing is enabled at the server level, the default auditing policy includes the following action groups:

BATCH_COMPLETED_GROUP
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
FAILED_DATABASE_AUTHENTICATION_GROUP

These groups audit:

All query execution activity
Successful authentication attempts
Failed authentication attempts

As a result, SQL batches — including DDL statements like CREATE, ALTER, or DROP on database objects — are captured under the BATCH_COMPLETED_GROUP and appear with action_id = BCM

Why AL, CR, and DR are not captured by default?

Audit action IDs such as AL, CR and DR are considered Security / DDL-level audit events.

These events are not included in the default Azure SQL auditing policy.

Instead, they are generated only when the corresponding Security-related AuditActionGroups are explicitly enabled.

For example:

AuditActionGroup	Captures
DATABASE_OBJECT_CHANGE_GROUP	CREATE / ALTER / DROP on database objects
DATABASE_PRINCIPAL_CHANGE_GROUP	User / role changes
DATABASE_ROLE_MEMBER_CHANGE_GROUP	Role membership updates

DDL operations such as CREATE / ALTER / DROP on database objects are captured under action groups like DATABASE_OBJECT_CHANGE_GROUP.

Observed Behavior in a Newly Created Test Server

Running the following PowerShell command on a newly provisioned logical server showed only the default audit action groups enabled.

(Get-AzSqlServerAudit -ResourceGroupName "RGName" -ServerName "ServerName").AuditActionGroup

Therefore, DDL statements were audited but recorded as action_id = BCM

Enabling AL / CR / DR Action IDs

To capture DDL operations under their respective audit action IDs, configure the required security audit action groups at the SQL Server level.

For example: In this customer scenario, we executed the following command:

Set-AzSqlServerAudit -ResourceGroupName "RGName" -ServerName "ServerName" -AuditActionGroup "DATABASE_PRINCIPAL_CHANGE_GROUP", "DATABASE_ROLE_MEMBER_CHANGE_GROUP", "DATABASE_OBJECT_CHANGE_GROUP"

After applying this configuration:

DDL operations were captured in the audit logs as action_id = CR, AL and DR instead of BCM.

Ensuring Consistent Compliance Across PaaS Servers

To standardize audit logging behavior across environments:

Step 1: Compare AuditActionGroups

Run the following command on all servers:

(Get-AzSqlServerAudit -ResourceGroupName "<RG>" -ServerName "<ServerName>").AuditActionGroup

Step 2: Align AuditActionGroups

Configure all server with same AuditActionGroup values. In this case, value used was below:

Set-AzSqlServerAudit -ResourceGroupName "<RG>" -ServerName "<ServerName>" -AuditActionGroup ` "DATABASE_PRINCIPAL_CHANGE_GROUP", "DATABASE_ROLE_MEMBER_CHANGE_GROUP", "DATABASE_OBJECT_CHANGE_GROUP"

Step 3: Validate

Once aligned, similar SQL statements across all PaaS servers should now generate consistent action_id values in audit logs.

Accepted values for AuditActionGroups. Ensure appropriate groups are enabled based on your organization’s compliance needs.

Accepted values:

BATCH_STARTED_GROUP, BATCH_COMPLETED_GROUP, APPLICATION_ROLE_CHANGE_PASSWORD_GROUP, BACKUP_RESTORE_GROUP, DATABASE_LOGOUT_GROUP, DATABASE_OBJECT_CHANGE_GROUP, DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP, DATABASE_OBJECT_PERMISSION_CHANGE_GROUP, DATABASE_OPERATION_GROUP, DATABASE_PERMISSION_CHANGE_GROUP, DATABASE_PRINCIPAL_CHANGE_GROUP, DATABASE_PRINCIPAL_IMPERSONATION_GROUP, DATABASE_ROLE_MEMBER_CHANGE_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP, SCHEMA_OBJECT_ACCESS_GROUP, SCHEMA_OBJECT_CHANGE_GROUP, SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP, SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP, SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, USER_CHANGE_PASSWORD_GROUP, LEDGER_OPERATION_GROUP, DBCC_GROUP, DATABASE_OWNERSHIP_CHANGE_GROUP, DATABASE_CHANGE_GROUP

Links:

Get-AzSqlServerAudit (Az.Sql) | Microsoft Learn

Set-AzSqlServerAudit (Az.Sql) | Microsoft Learn

Monitoring Azure SQL Data Sync Errors Using PowerShell

Mohamed_Baioumy_MSFT — Tue, 14 Apr 2026 13:27:59 GMT

Azure SQL Data Sync is a powerful service that enables data synchronization between multiple databases across Azure SQL Database and on‑premises SQL Server environments. It supports hybrid architectures and distributed applications by allowing selected data to synchronize bi‑directionally between hub and member databases using a hub‑and‑spoke topology.

However, one of the most common operational challenges faced by support engineers and customers using Azure SQL Data Sync is:

❗ Lack of proactive monitoring for sync failures or errors

By default, Azure SQL Data Sync does not provide native alerting mechanisms that notify administrators when synchronization operations fail or encounter issues. This can result in silent data drift or synchronization delays that may go unnoticed in production environments.

In this blog, we’ll walk through how to monitor Azure SQL Data Sync activity and detect synchronization errors using Azure PowerShell commands.

Why Monitoring Azure SQL Data Sync Matters

Azure SQL Data Sync works by synchronizing data between:

Hub Database (must be Azure SQL Database)
Member Databases (Azure SQL Database or SQL Server)
Sync Metadata Database (stores sync configuration and logs)

All synchronization activity—including errors, failures, and successes—is logged internally within the Sync Metadata Database and exposed through Azure SQL Sync Group logs.

Monitoring these logs enables:

Detection of sync failures
Identification of schema mismatches
Validation of sync completion
Troubleshooting of sync group issues
Verification of last successful sync activity

Prerequisites

Before monitoring Azure SQL Data Sync activity, ensure the following:

Azure PowerShell module (Az.Sql) is installed
You have access to the Azure SQL Data Sync resources
Proper authentication and subscription context are configured

Install and import the required module if not already available:

# Install Azure PowerShell module if not already installed Install-Module -Name Az -Repository PSGallery -Force # Import the SQL module Import-Module Az.Sql

Authenticate to Azure:

# Login to Azure Connect-AzAccount -TenantId "<tenant-id>" # Set subscription context Set-AzContext -SubscriptionId "<subscription-id>"

These commands enable access to Azure SQL Sync Group monitoring operations.

Monitoring Sync Group Status

To retrieve Sync Group details, define the required variables:

# Define variables $resourceGroup = "rg-datasync-demo" $serverName = "<hub-server-name>" $databaseName = "HubDatabase" $syncGroupName = "SampleSyncGroup" # Get sync group details Get-AzSqlSyncGroup -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $databaseName ` -SyncGroupName $syncGroupName | Format-List

Note:
The LastSyncTime property returned by Get-AzSqlSyncGroup may sometimes display a value such as 1/1/0001, even when synchronization operations are completing successfully.
To obtain accurate synchronization timestamps, it is recommended to use Sync Group Logs instead.

Monitoring Sync Activity Using Logs (Recommended)

To monitor synchronization activity and retrieve detailed sync status, use:

# Get sync logs for the last 24 hours $startTime = (Get-Date).AddHours(-24).ToString("yyyy-MM-ddTHH:mm:ssZ") $endTime = (Get-Date).ToString("yyyy-MM-ddTHH:mm:ssZ") Get-AzSqlSyncGroupLog -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $databaseName ` -SyncGroupName $syncGroupName ` -StartTime $startTime ` -EndTime $endTime

This command retrieves:

Sync operation timestamps
Sync status
Error messages
Activity details

Sync Group Logs provide more reliable monitoring information than the Sync Group status output alone.

Retrieving the Last Successful Sync Time

To determine the most recent successful synchronization operation:

# Get the most recent successful sync timestamp $startTime = (Get-Date).AddDays(-7).ToString("yyyy-MM-ddTHH:mm:ssZ") $endTime = (Get-Date).ToString("yyyy-MM-ddTHH:mm:ssZ") Get-AzSqlSyncGroupLog -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $databaseName ` -SyncGroupName $syncGroupName ` -StartTime $startTime ` -EndTime $endTime | Where-Object { $_.Details -like "*completed*" -or $_.Type -eq "Success" } | Select-Object -First 1 Timestamp, Type, Details

This helps administrators validate whether synchronization is occurring as expected across the sync topology.

Filtering for Synchronization Errors

To identify failed or problematic sync operations:

# Get only error logs Get-AzSqlSyncGroupLog -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $databaseName ` -SyncGroupName $syncGroupName ` -StartTime $startTime ` -EndTime $endTime | Where-Object { $_.LogLevel -eq "Error" }

Filtering logs by error type allows for:

Rapid identification of failed sync attempts
Analysis of failure causes
Early detection of data consistency risks

Key Takeaways

Azure SQL Data Sync does not provide native alerting for sync failures
Sync Group Logs offer detailed monitoring of sync operations
Get-AzSqlSyncGroupLog provides accurate timestamps and status
Monitoring logs enables detection of silent sync failures
PowerShell can be used to proactively monitor synchronization health

References

Fixing “There is not enough space on the disk” during Azure Data Sync initial sync (On‑prem ➜ Azure)

Mohamed_Baioumy_MSFT — Mon, 13 Apr 2026 14:31:07 GMT

When you run an initial (first-time) sync from an on‑premises SQL Server database to Azure SQL Database using SQL Data Sync, the local agent may fail with a disk-space error—even when the disk “looks” like it has free space. The reason is that the initial sync can generate large temporary files in the Windows TEMP location used by the Data Sync Agent.

This post explains the symptom, what’s happening under the hood, and the most practical mitigation: move the Data Sync Agent’s TEMP/TMP to a drive with sufficient space and restart the service.

Symptom

During an initial sync (commonly on-premises ➜ Azure), the sync fails while applying a batch file.

Error

You may see an error similar to:

Sync failed with the exception:
“An unexpected error occurred when applying batch file … .batch. See the inner exception for more details. Inner exception: There is not enough space on the disk …”

Microsoft Learn also calls out “disk insufficient space” scenarios for SQL Data Sync and points to the %TEMP% directory as the key location to check.

What’s actually happening (Root Cause)

1) Initial sync uses temp files on the agent machine

During initialization, the local agent can load data and store it as temp files in the system temp folder. This is explicitly called out in the Azure SQL Data Sync scalability guidance.

2) The agent can generate more than “just the batch files”

In practice, you’ll often see:

Batch files (e.g., sync_*.batch)
Extra temp files under folders like MAT_ / MATS_ that are used for internal processing (commonly described as “sorting”/intermediate work).

Internal field experience shared in the Data Sync support channel highlights that the MAT/MATS files can be much larger than the batch files—sometimes 8–10× larger than the data being synced for that table (especially during initialization).

3) Why “I still have free disk space” can be misleading

If your Data Sync Agent’s TEMP points to a system drive (often C:), it can fill quickly with temp batches + MAT/MATS files during the first sync—particularly for large tables or many tables being initialized. The Azure SQL Data Sync “large scale” guidance recommends ensuring the temp folder has enough space before starting initialization and notes you can move TEMP/TMP to another drive.

Mitigation (Recommended)

Option A — Move TEMP/TMP to a larger drive (recommended)

The Microsoft Azure Blog guidance for large-scale initialization is clear: move the temp folder by setting TEMP and TMP environment variables and restart the sync service.

Key point: change the variables for the same account running the Data Sync Agent service

Environment variables exist at user scope and machine scope, and the effective TEMP location depends on which account the agent service runs under.

A simple PowerShell approach (run elevated) is to read and set the variables at the appropriate scope. (Example shown below uses the standard .NET environment APIs.)

# Run in Administrator mode # Get current values [Environment]::GetEnvironmentVariable("TEMP","User") [Environment]::GetEnvironmentVariable("TEMP","Machine") # Set new values (examples) [Environment]::SetEnvironmentVariable("TEMP","D:\TempUser","User") [Environment]::SetEnvironmentVariable("TMP" ,"D:\TempUser","User") # or machine scope: [Environment]::SetEnvironmentVariable("TEMP","D:\TempMachine","Machine") [Environment]::SetEnvironmentVariable("TMP" ,"D:\TempMachine","Machine")

Important: After updating TEMP/TMP, restart the SQL Data Sync agent service so it picks up the new environment settings.

Option B — If you can’t log in as the service account: update TEMP/TMP in the registry for that account

If you need to change TEMP/TMP for a specific account without interactive logon, you can update the user environment variables stored in the registry.

General Windows guidance indicates:

User environment variables live under HKEY_CURRENT_USER\Environment (and for other users, under that user’s SID hive loaded under HKEY_USERS).

A common approach is:

Identify the service account SID (example commands such as WMIC are often used in practice).
Open Registry Editor
Navigate to:
HKEY_USERS\<SID>\Environment
Update TEMP and TMP to a path on a drive with sufficient space.
Restart the Data Sync service.

Option C — Clean up leftover sync temp files (when sync is NOT running)

In some cases, the “disk out of space” condition is caused by leftover sync files that were not removed (for example, if something had files open during deletion). Microsoft Learn suggests manually deleting sync files from %temp% and cleaning subdirectories only when sync is not in progress.

Validation checklist (after the change)

After moving TEMP/TMP and restarting the service, confirm:

New temp path is being used
Initiate sync and check that new sync_*.batch / temp artifacts appear under the new folder.
Sufficient free space exists for initialization
Especially for large tables, ensure the chosen drive can accommodate temp growth during the first sync.
Rerun initial sync
Retry the initial sync after making the change.

Classification

Symptom type: Agent side / initialization failure
Primary root cause: Insufficient disk space on the TEMP location used by the Data Sync Agent during initial sync temp-file generation
Fix type: Configuration / operational (move TEMP/TMP to a larger drive + restart agent service)

a { text-decoration: none; color: #464feb; } tr th, tr td { border: 1px solid #e6e6e6; } tr th { background-color: #f5f5f5; }

Helpful references

Troubleshooting Azure SQL Data Sync Failure: SQL Error 8106 During Bulk Insert

Mohamed_Baioumy_MSFT — Wed, 08 Apr 2026 15:55:41 GMT

Azure SQL Data Sync is widely used to maintain consistency across distributed databases in hub–member topologies. However, synchronization may occasionally fail due to schema mismatches between participating databases — even when everything appears correctly configured at first glance.

In this post, we’ll walk through a real-world troubleshooting scenario involving a Data Sync failure caused by a schema inconsistency related to an IDENTITY column, and how it was mitigated.

Sample Error:

sync_7726d6cb22124c0f901192c434f49106bd618f8ab16343b2adc03250f8367ff4\3953fb7d-1dba-4656-8150-83153d5d019b.batch. See the inner exception for more details. Inner exception: Failed to execute the command 'BulkInsertCommand' for table 'schema.table_name'; the transaction was rolled back. Ensure that the command syntax is correct. Inner exception: SqlException ID: e19b3677-d67e-4c8e-bc49-13d3df61ad0e, Error Code: -2146232060 - SqlError Number:8106, Message: SQL error with code 8106 For more information, provide tracing ID ‘92e76130-f80a-4372-9a48-ec0ede8b0288’ to customer support."

Scenario Overview

A synchronization operation began failing for a specific table within an Azure SQL Data Sync group. The failure was observed during the sync process when applying changes using a batch file.

The error surfaced as part of a failed BulkInsertCommand execution on a synced table, causing the transaction to roll back.

Further investigation revealed the following SQL exception:

SqlError Number: 8106
Table does not have the identity property. Cannot perform SET operation.

Initial Troubleshooting Steps

Before identifying the root cause, the following actions were taken:

The affected table was removed from the sync group.
A sync operation was triggered.
The table was re-added to the sync group.
Sync was triggered again.

Despite performing these steps, the issue persisted with the same error.

This indicated that the failure was not related to sync metadata or temporary configuration inconsistencies.

Root Cause Analysis

After reviewing the table definitions across the sync topology, it was discovered that:

The synchronized table had an IDENTITY column defined on one side of the topology (Hub or Member) but not on the other.

This schema mismatch led to the sync service attempting to apply SET IDENTITY_INSERT operations during the bulk insert phase — which failed on the database where the column lacked the identity property.

Azure SQL Data Sync relies on consistent schema definitions across all participating databases. Any deviation — particularly involving identity columns — can interrupt data movement operations.

Mitigation Approach

To resolve the issue, the following corrective steps were applied:

Remove the affected table from the sync group and save the configuration.
Refresh the sync schema.
Recreate the table to include the appropriate IDENTITY property.
Add the corrected table back to the sync group.
Trigger a new sync operation.

These steps ensured that the table definitions were aligned across all sync participants, allowing the synchronization process to proceed successfully.

Best Practices to Avoid Similar Issues

To prevent identity-related sync failures in Azure SQL Data Sync:

✅ Ensure table schemas are identical across all participating databases before onboarding them into a sync group.
✅ Pay special attention to:
- IDENTITY properties
- Primary keys
- Data types
- Nullable constraints
✅ Always validate schema consistency when:
- Adding new tables to a sync group
- Modifying existing table definitions

Final Thoughts

Schema mismatches — especially those involving identity columns — are a common but often overlooked cause of Data Sync failures. By ensuring consistent table definitions across your hub and member databases, you can significantly reduce the risk of synchronization errors and maintain reliable data movement across regions.