rss.livelink.threads-in-node

Recovering Missing Rows (“Gaps”) in Azure SQL Data Sync — Supported Approaches (and What to Avoid)

Mohamed_Baioumy_MSFT — Sat, 14 Mar 2026 02:27:30 GMT

Azure SQL Data Sync is commonly used to keep selected tables synchronized between a hub database and one or more member databases. In some cases, you may discover a data “gap”: a subset of rows that exist in the source but are missing on the destination for a specific time window, even though synchronization continues afterward for new changes. This post explains supported recovery patterns and what not to do, based on a real support scenario where a customer reported missing rows for a single table within a sync group and requested a way to synchronize only the missing records.

The scenario: Data Sync continues, but some rows are missing

In the referenced case, the customer observed that:

A specific table had a gap on the member side (missing rows for a period), while newer data continued to sync normally afterward.
They asked for a Microsoft-supported method to sync only the missing rows, without rebuilding or fully reinitializing the table.

This is a reasonable goal—but the recovery method matters, because Data Sync relies on service-managed tracking artifacts.

Temptation: “Can we push missing data by editing tracking tables or calling internal triggers?”

A frequent idea is to “force” Data Sync to pick up missing rows by manipulating internal artifacts:

Writing directly into Data Sync tracking tables (for example, tables under the DataSync schema such as *_dss_tracking), or altering provisioning markers.
Manually invoking Data Sync–generated triggers or relying on their internal logic. The case discussion specifically referenced internal triggers such as _dss_insert_trigger, _dss_update_trigger, and _dss_delete_trigger.

Why this is not recommended / not supported as a customer-facing solution

In the case, the guidance from Microsoft engineering was clear:

Manually invoking internal Data Sync triggers is not supported and can increase the risk of data corruption because these triggers are service-generated at runtime and are not intended for manual use.
Directly manipulating Data Sync tracking/metadata tables is not recommended. The customer thread also highlights that these tracking tables are part of Data Sync internals, and using them for manual “push” scenarios is not a supported approach.

Also, the customer conversation highlights an important conceptual point: tracking tables are part of how the service tracks changes; they are not meant to be treated as a user-managed replication queue.

Supported recovery option #1 (recommended): Re-drive change detection via the base table

The most supportable approach is to make Data Sync detect the missing rows through its normal change tracking path—by operating on the base/source table, not the service-managed internals.

A practical pattern: “No-op update” to re-fire tracking

In the internal discussion with the product team, the recommended pattern was to update the source/base table (even with a “no-op” assignment) so that Data Sync’s normal tracking logic is triggered, without manually invoking internal triggers.

Example pattern (conceptual):

UPDATE t SET some_column = some_column -- no-op: value unchanged FROM dbo.YourTable AS t WHERE <filter identifying the rows that are missing on the destination>;

This approach is called out explicitly in the thread as a way to “re-drive” change detection safely through supported mechanisms.

Operational guidance (practical):

Apply the update in small batches, especially for large tables, to reduce transaction/log impact and avoid long-running operations.
Validate the impacted row set first (for example, by comparing keys between hub and member).

Supported recovery option #2: Deprovision and re-provision the affected table (safe “reset” path)

If the gap is large, the row-set is hard to isolate, or you want a clean realignment of tracking artifacts, the operational approach discussed in the case was:

Stop sync
Remove the table from the sync group (so the service deprovisions tracking objects)
Fix/clean the destination state as needed
Add the table back and let Data Sync re-provision and sync again

This option is often the safest when the goal is to avoid touching system-managed artifacts directly.

Note: In production environments, customers may not be able to truncate/empty tables due to operational constraints. In that situation, the sync may take longer because the service might need to do more row-by-row evaluation. This “tradeoff” was discussed in the case context.

Diagnostics: Use the Azure SQL Data Sync Health Checker

When you suspect metadata drift, missing objects, or provisioning inconsistencies, the case recommended using the AzureSQLDataSyncHealthChecker script.

This tool:

Validates hub/member metadata and scopes against the sync metadata database
Produces logs that can highlight missing artifacts and other inconsistencies
Is intended to help troubleshoot Data Sync issues faster

A likely contributor to “gaps”: schema changes during Data Sync (snapshot isolation conflict)

In the case discussion, telemetry referenced an error consistent with concurrent DDL/schema changes while the sync process is enumerating changes (snapshot isolation + metadata changes).

A well-known related error is SQL Server error 3961, which occurs when a snapshot isolation transaction fails because metadata was modified by a concurrent DDL statement, since metadata is not versioned. Microsoft documents this behavior and explains why metadata changes conflict with snapshot isolation semantics.

Prevention guidance (practical)

Avoid running schema deployments (DDL) during active sync windows.
Use a controlled workflow for schema changes with Data Sync—pause/coordinate changes to prevent mid-sync metadata shifts. (General best practices exist for ongoing Data Sync operations and maintenance.)

Key takeaways

Do not treat Data Sync tracking tables/triggers as user-managed “replication internals.” Manually invoking internal triggers or editing tracking tables is not a supported customer-facing recovery mechanism.
Do recover gaps via base table operations (insert/update) so the service captures changes through its normal path—“no-op update” is one practical pattern when you already know the missing row set.
For large/complex gaps, consider the safe reset approach: remove the table from the sync group and re-add it to re-provision artifacts.
Use the AzureSQLDataSyncHealthChecker to validate metadata consistency and reduce guesswork.
If you see intermittent failures around deployments, consider the schema-change + snapshot isolation pattern (e.g., error 3961) as a possible contributor and schedule DDL changes accordingly.

February 2026 Recap: Azure Database for PostgreSQL

gauri-kasar — Wed, 11 Mar 2026 17:09:35 GMT

Hello Azure Community,

We’re excited to share the February 2026 recap for Azure Database for PostgreSQL, featuring a set of updates focused on speed, simplicity, and better visibility. From Terraform support for Elastic Clusters and a refreshed VM SKU selection experience in the Azure portal to built‑in Grafana dashboards, these improvements make it easier to build, operate, and scale PostgreSQL on Azure. This recap also includes practical GIN index tuning guidance, enhancements to the PostgreSQL VS Code extension, and improved connectivity for azure_pg_admin users.

Features

Terraform support for Elastic Clusters - Generally Available
Dashboards with Grafana - Generally Available
Easier way to choose VM SKUs on portal – Generally Available
What’s New in the PostgreSQL VS Code Extension
Priority Connectivity to azure_pg_admin users
Guide on 'gin_pending_list_limit' indexes

Terraform support for Elastic Clusters

Terraform now supports provisioning and managing Azure Database for PostgreSQL Elastic Clusters, enabling customers to define and operate elastic clusters using infrastructure‑as‑code workflows. With this support, it is now easier to create, scale, and manage multi‑node PostgreSQL clusters through Terraform, making it easier to automate deployments, replicate environments, and integrate elastic clusters into CI/CD pipelines. This improves operational consistency and simplifies management for horizontally scalable PostgreSQL workloads.

Learn more about building and scaling with Azure Database for PostgreSQL elastic clusters.

Dashboards with Grafana — Now Built-In

Grafana dashboards are now natively integrated into the Azure Portal for Azure Database for PostgreSQL. This removes the need to deploy or manage a separate Grafana instance. With just a few clicks, you can visualize key metrics and logs side by side, correlate events by timestamp, and gain deep insights into performance, availability, and query behavior all in one place.

Whether you're troubleshooting a spike, monitoring trends, or sharing insights with your team, this built-in experience simplifies day-to-day observability with no added cost or complexity.

Try it under Azure Portal > Dashboards with Grafana in your PostgreSQL server view.

For more details, see the blog post: Dashboards with Grafana — Now in Azure Portal for PostgreSQL.

Easier way to choose VM SKUs on portal

We’ve improved the VM SKU selection experience in the Azure portal to make it easier to find and compare the right compute options for your PostgreSQL workload. The updated experience organizes SKUs in a clearer, more scannable view, helping you quickly compare key attributes like vCores and memory without extra clicks. This streamlined approach reduces guesswork and makes selecting the right SKU faster and more intuitive.

What’s New in the PostgreSQL VS Code Extension

The VS Code extension for PostgreSQL helps developers and database administrators work with PostgreSQL directly from VS Code. It provides capabilities for querying, schema exploration, diagnostics, and Azure PostgreSQL management allowing users to stay within their editor while building and troubleshooting. This release focuses on improving developer productivity and diagnostics. It introduces new visualization capabilities, Copilot-powered experiences, enhanced schema navigation, and deeper Azure PostgreSQL management directly from VS Code.

New Features & Enhancements

Query Plan Visualization: Graphical execution plans can now be viewed directly in the editor, making it easier to diagnose slow queries without leaving VS Code.
AGE Graph Rendering: Support is now available for automatically rendering graph visualizations from Cypher queries, improving the experience of working with graph data in PostgreSQL.
Object Explorer Search: A new graphical search experience in Object Explorer allows users to quickly find tables, views, functions, and other objects across large schemas, addressing one of the highest-rated user feedback requests.
Azure PostgreSQL Backup Management: Users can now manage Azure Database for PostgreSQL backups directly from the Server Dashboard, including listing backups and configuring retention policies.
Server Logs Dashboard: A new Server Dashboard view surfaces Azure Database for PostgreSQL server logs and retention settings for faster diagnostics. Logs can be opened directly in VS Code and analyzed using the built-in GitHub Copilot integration.

This release also includes several reliability improvements and bug fixes, including resolving connection pool exhaustion issues, fixing Docker container creation failures when no password is provided, and improving stability around connection profiles and schema-related operations.

Priority Connectivity to azure_pg_admin Users

Members of the azure_pg_admin role can now use connections from the pg_use_reserved_connections pool. This ensures that an admin always has at least one available connection, even if all standard client connections from the server connection pool are in use. By making sure admin users can log in when the client connection pool is full, this change prevents lockout situations and lets admins handle emergencies without competing for available open connection slots.

Guide on 'gin_pending_list_limit' indexes

Struggling with slow GIN index inserts in PostgreSQL? This post dives into the often-overlooked gin_pending_list_limit parameter and how it directly impacts insert performance. Learn how GIN’s pending list works, why the right limit matters, and practical guidance on tuning it to strike the perfect balance between write performance and index maintenance overhead.

For a deeper dive into gin_pending_list_limit and tuning guidance, see the full blog here.

Learning Bytes

Create Azure Database for PostgreSQL elastic clusters with terraform:

Elastic clusters in Azure Database for PostgreSQL let you scale PostgreSQL horizontally using a managed, multi‑node architecture. With Elastic cluster now generally available, you can provision and manage elastic clusters using infrastructure‑as‑code, making it easier to automate deployments, standardize environments, and integrate PostgreSQL into CI/CD workflows.

Elastic clusters are a good fit when you need:

Horizontal scale for large or fast‑growing PostgreSQL workloads
Multi‑tenant applications or sharded data models
Repeatable and automated deployments across environments

The following example shows a basic Terraform configuration to create an Azure Database for PostgreSQL flexible server configured as an elastic cluster.

resource "azurerm_postgresql_flexible_server" "elastic_cluster" { name = "pg-elastic-cluster" resource_group_name = <rg-name> location = <region> administrator_login = var.admin_username administrator_password = var.admin_password version = "17" sku_name = "GP_Standard_D4ds_v5" storage_mb = 131072 cluster { size = 3 } }

Conclusion

That’s a wrap for the February 2026 Azure Database for PostgreSQL recap. We’re continuing to focus on making PostgreSQL on Azure easier to build, operate, and scale whether that’s through better automation with Terraform, improved observability, or a smoother day‑to‑day developer and admin experience. Your feedback is important to us, have suggestions, ideas, or questions? We’d love to hear from you: https://aka.ms/pgfeedback.

February 2026 Recap: Azure Database for MySQL

SaurabhKirtani — Wed, 11 Mar 2026 13:00:00 GMT

We're excited to share a summary of the Azure Database for MySQL updates from the last couple of months.

Extended Support Timeline Update

Based on customer feedback requesting additional time to complete major version upgrades, we have extended the grace period before extended support billing begins for Azure Database for MySQL:

MySQL 5.7: Extended support billing start date moved from April 1, 2026 to August 1, 2026.
MySQL 8.0: Extended support billing start date moved from June 1, 2026 to January 1, 2027.

This update provides customers additional time to plan, validate, and complete upgrades while maintaining service continuity and security. We continue to recommend upgrading to a supported MySQL version as early as possible to avoid extended support charges and benefit from the latest improvements. Learn more about performing a major version upgrade in Azure Database for MySQL.

When upgrading using a read replica, you can optionally use the Rename Server feature to promote the replica and avoid application connection‑string updates after the upgrade completes. Rename Server is currently in Private Preview and is expected to enter Public Preview around the April 2026 timeframe.

Private Preview - Fabric Mirroring for Azure Database for MySQL

This capability enables real‑time replication of MySQL data into Microsoft Fabric with a zero‑ETL experience, allowing data to land directly in OneLake in analytics‑ready formats. Customers can seamlessly analyse mirrored data using Microsoft Fabric experiences, while isolating analytical workloads from their operational MySQL databases.

Stay Connected

We welcome your feedback and invite you to share your experiences or suggestions at AskAzureDBforMySQL@service.microsoft.com

Stay up to date by visiting What's new in Azure Database for MySQL, and follow us on YouTube | LinkedIn | X for ongoing updates.

Thank you for choosing Azure Database for MySQL!

Counting distinct values

KrishKK — Tue, 10 Mar 2026 10:59:41 GMT

I’m trying to create a view but I’m struggling to find a solution to this issue.

I need a method that counts multiple visits to the same location as one if they occur within 14 days of each other. If there are multiple visits to the same location and the gap between them is more than 14 days, then each should be counted separately.

For example, in the attached screenshot:

Brussels had visits on 08/05 and 15/05, which are less than 14 days apart, so this should be counted as one visit.

Dublin had visits more than a month apart, so these should be counted as two separate visits.

Could someone please guide me on how to achieve this?

Thanks.

Using Different Azure Key Vault Keys for Azure SQL Servers in a Failover Group

Harshitha_Reddy_Chappidi — Tue, 10 Mar 2026 09:59:01 GMT

Transparent Data Encryption (TDE) in Azure SQL Database encrypts data at rest using a database encryption key (DEK). The DEK itself is protected by a TDE protector, which can be either:

A service-managed key (default), or
A customer-managed key (CMK) stored in Azure Key Vault.

Many enterprises adopt customer-managed keys to meet compliance, key rotation, and security governance requirements.

When configuring Geo-replication or Failover Groups, Azure SQL supports using different Key Vault keys on the primary and secondary servers. This provides additional flexibility and allows organizations to isolate encryption keys across regions or subscriptions.

Microsoft documentation describes this scenario for new deployments in detail. If you are configuring geo-replication from scratch, refer to the following article: Geo-Replication and Transparent Data Encryption Key Management in Azure SQL Database | Microsoft Community Hub

However, customers often ask:

How can we change the Azure Key Vault and use different customer-managed keys on the primary and secondary servers when the failover group already exists without breaking the replication?

This article walks through the safe process to update Key Vault keys in an existing failover group configuration.

Architecture Overview

In a failover group configuration:

Primary Server

Uses CMK stored in Key Vault A

Secondary Server

Uses CMK stored in Key Vault B

Even though each server uses its own key, both servers must be able to access both keys. This requirement exists because during failover operations the database must still be able to decrypt the existing Database Encryption Key (DEK).

Therefore:

Each logical server must have both Key Vault keys registered and have the required permissions
Only one key is configured as the active TDE protector

Scenario

Existing environment:

Azure SQL Failover Group configured

TDE enabled with Customer Managed Key with same key.

Primary server:

Secondary server:

Customer wants to:

Move to different Azure Key Vaults
Use separate CMKs for primary and secondary servers

Step-by-Step Implementation

Step 1 – Add the Secondary Server Key to the Primary Server

Before changing the TDE protector on the secondary server, the secondary CMK must first be registered on the primary logical server.

This ensures that both servers are aware of both encryption keys.

Add-AzSqlServerKeyVaultKey -KeyId ‘https://testanu2.vault.azure.net/keys/tey/29497babb0cb4af58a773104a5dd61e5' -ServerName 'tdeprimarytest' -ResourceGroupName ‘harshitha_lab’

This command registers the Key Vault key with the logical SQL server but does not change the active TDE protector.

Step 2 – Change the TDE Protector on the Secondary Server

After registering the key, update the TDE protector on the secondary server to use its designated CMK.

This can be done from the Azure Portal:

Navigate to Azure SQL Server (Secondary)
Select Transparent Data Encryption
Choose Customer-managed key
Select the Key Vault key intended for the secondary server
Save the configuration

At this stage:

Secondary server uses Key Vault B
Primary server still uses Key Vault A

Step 3 – Add the Primary Server Key to the Secondary Server

Next, perform the same operation in reverse.

Add-AzSqlServerKeyVaultKey -KeyId ‘https://testprimarysubham.vault.azure.net/keys/subham/e0637ed7e3734f989b928101c79ca565' -ServerName ‘tdesecondarytest’ -ResourceGroupName ‘harshitha_lab’

Now both servers contain references to both keys.

Step 4 – Change the TDE Protector on the Primary Server

Once the key is registered, update the primary server TDE protector to use its intended CMK.

This can also be done through the Azure Portal or PowerShell.

After this step:

Server	Key Vault	Active CMK
Primary	Key Vault A	Primary CMK
Secondary	Key Vault B	Secondary CMK

Step 5 – Verify Keys Registered on Both Servers

Use the following PowerShell command to confirm that both keys are registered on each server.

Primary Server

Get-AzSqlServerKeyVaultKey -ServerName "tdeprimarytest" -ResourceGroupName "harshitha_lab"

Secondary Server

Get-AzSqlServerKeyVaultKey -ServerName "tdesecondarytest" -ResourceGroupName "harshitha_lab"

Both outputs should list both Key Vault keys.

Step 6 – Verify Database Encryption State

Connect to the database using SQL Server Management Studio (SSMS) and run the following query to verify the encryption configuration.

SELECT DB_NAME(db_id()) AS database_name, dek.encryption_state, dek.encryption_state_desc, dek.key_algorithm, dek.key_length, dek.encryptor_type, dek.encryptor_thumbprint FROM sys.dm_database_encryption_keys AS dek WHERE database_id <> 2;

Key columns to check:

Column	Description
encryption_state	Shows if the database is encrypted
encryptor_type	Indicates if the key is from Azure Key Vault
encryptor_thumbprint	Identifies the CMK used for encryption

At this stage, the primary database thumbprint should match the primary server CMK on the secondary database.

Primary:

Secondary:

Step 7 – Validate After Failover

Perform a planned failover of the failover group.

After failover, run the same query again.

You should observe:

The encryptor_thumbprint changes
The database is now encrypted using the secondary server’s CMK which is now primary

Primary server after failover:

Secondary server which is primary before failover

This confirms that the failover group is successfully using different Key Vault keys for each server.

Key Takeaways

Azure SQL Failover Groups support different Customer Managed Keys across regions.
Both logical servers must have access to both keys.
Only one key acts as the active TDE protector per server.
Validation should always include checking the encryptor thumbprint before and after failover.

This approach allows organizations to implement regional key isolation, meet compliance requirements, and maintain high availability with secure key management.

Understanding Hash Join Memory Usage and OOM Risks in PostgreSQL

FranciscoPardillo — Mon, 09 Mar 2026 13:38:54 GMT

Background: Why Memory Usage May Exceed work_mem

work_mem is commonly assumed to be a hard upper bound on per‑query memory usage.

However, for Hash Join operations, memory consumption depends not only on this parameter but also on:

✅ Data cardinality

✅ Hash table internal bucket distribution

✅ Join column characteristics

✅ Number of batches created

✅ Parallel workers involved

Under low‑cardinality conditions, a Hash Join may place an extremely large number of rows into very few buckets—sometimes a single bucket. This causes unexpectedly large memory allocations that exceed the nominal work_mem limit.

Background: What work_mem really means for Hash Joins

work_mem controls the amount of memory available per operation (e.g., a sort or a hash) per node (and per parallel worker) before spilling to disk. Hash operations can additionally use hash_mem_multiplier×work_mem for their hash tables. [postgresql.org], [postgresqlco.nf]
The Hash Join algorithm builds a hash table for the “build/inner” side and probes it with the “outer” side. The table is split into buckets; if it doesn’t fit in memory, PostgreSQL partitions work into batches (spilling to temporary files). Skewed distributions (e.g., very few distinct join keys) pack many rows into the same bucket(s), exploding memory usage even when work_mem is small. [postgrespro.com], [interdb.jp]
In EXPLAIN (ANALYZE) you’ll see Buckets:, Batches:, and Memory Usage: on the Hash node; Batches > 1 indicates spilling/partitioning. [postgresql.org], [thoughtbot.com]
The default for hash_mem_multiplier is version‑dependent (introduced in PG13; 1.0 in early versions, later 2.0). Tune with care; it scales the memory that hash operations may consume relative to work_mem. [pgpedia.info]

A safe, reproducible demo (containerized community PostgreSQL)

The goal is to show that data distribution alone can drive order(s) of magnitude difference in hash table memory, using conservative settings.

In order to simulate the behavior we´ll use pg_hint_plan extension to guide the execution plans and create some data distribution that may not have a good application logic, just to force and show the behavior.

Start PostgreSQL 16 container

docker run --name=postgresql16.8 -p 5414:5432 -e POSTGRES_PASSWORD=<password> -d postgres:16.8 docker exec -it postgresql16.8 /bin/bash -c "apt-get update -y;apt-get install procps -y;apt-get install postgresql-16-pg-hint-plan -y;apt-get install vim -y;apt-get install htop -y" docker exec -it postgresql16.8 /bin/bash vi /var/lib/postgresql/data/postgresql.conf -- Adding pg_hint_plan to shared_preload_libraries psql -h localhost -U postgres create extension pg_hint_plan; docker stop postgresql16.8 docker start postgresql16.8

To connect to our docker container we use:

psql -h localhost -p 5414 -U postgres

Connect and apply conservative session-level settings

We’ll discourage other join methods so the planner prefers Hash Join—without needing any extension.

set hash_mem_multiplier=1; set max_parallel_workers=0; set max_parallel_workers_per_gather=0; set enable_parallel_hash=off; set enable_material=off; set enable_sort=off; set pg_hint_plan.debug_print=verbose; set client_min_messages=notice; set pg_hint_plan.enable_hint_table=on;

Create tables and load data

We´ll create two tables for the join, table_1, with a single row, table_h initially with 10mill rows

drop table table_s; create table table_s (column_a text); insert into table_s values ('30020'); vacuum full table_s; drop table table_h; create table table_h(column_a text,column_b text); INSERT INTO table_h(column_a,column_b) SELECT i::text, i::text FROM generate_series(1, 10000000) AS t(i); vacuum full table_h;

Run Hash Join (high cardinality)

We´ll run the join using column_a in both tables, that was created previously having high cardinality in table_h

explain (analyze,buffers,costs,verbose) SELECT /*+ HashJoin(s h) Leading((s h)) */ COUNT(*) FROM table_s s JOIN table_h h ON s.column_a= h.column_a;

You should see a Hash node with small Memory Usage (a few MB) and Batches: 256 or similar due to our tiny work_mem, but no ballooning. Exact numbers vary by hardware/version/stats. (EXPLAIN fields and interpretation are documented here.) [postgresql.org]

QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=280930.01..280930.02 rows=1 width=8) (actual time=1902.965..1902.968 rows=1 loops=1) Output: count(*) Buffers: shared read=54055, temp read=135 written=34041 -> Hash Join (cost=279054.00..280805.01 rows=50000 width=0) (actual time=1900.539..1902.949 rows=1 loops=1) Hash Cond: (s.column_a = h.column_a) Buffers: shared read=54055, temp read=135 written=34041 -> Seq Scan on public.table_s s (cost=0.00..1.01 rows=1 width=32) (actual time=0.021..0.022 rows=1 loops=1) Output: s.column_a Buffers: shared read=1 -> Hash (cost=154054.00..154054.00 rows=10000000 width=32) (actual time=1896.895..1896.896 rows=10000000 loops=1) Output: h.column_a Buckets: 65536 Batches: 256 Memory Usage: 2031kB Buffers: shared read=54054, temp written=33785 -> Seq Scan on public.table_h h (cost=0.00..154054.00 rows=10000000 width=32) (actual time=2.538..638.830 rows=10000000 loops=1) Output: h.column_a Buffers: shared read=54054 Query Identifier: 334721522907995613 Planning: Buffers: shared hit=10 Planning Time: 0.302 ms JIT: Functions: 11 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 0.441 ms, Inlining 0.000 ms, Optimization 0.236 ms, Emission 2.339 ms, Total 3.017 ms Execution Time: 1903.472 ms (25 rows)

Findings (1) When we have the data totally distributed with high cardinality it takes only 2031kB of memory usage (work_mem), shared hit/read=54055

Force low cardinality / skew and re‑run

We´ll update table_h to have column_a all values to '30020', so, having only 1 distinct value for all the rows in the table

update table_h set column_a='30020', column_b='30020'; vacuum full table_h;

Checking execution plan:

QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=279056.04..279056.05 rows=1 width=8) (actual time=3568.936..3568.938 rows=1 loops=1) Output: count(*) Buffers: shared read=54056, temp read=63480 written=63480 -> Hash Join (cost=279055.00..279056.03 rows=1 width=0) (actual time=2650.696..3228.610 rows=10000000 loops=1) Hash Cond: (s.column_a = h.column_a) Buffers: shared read=54056, temp read=63480 written=63480 -> Seq Scan on public.table_s s (cost=0.00..1.01 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=1) Output: s.column_a Buffers: shared read=1 -> Hash (cost=154055.00..154055.00 rows=10000000 width=7) (actual time=1563.987..1563.989 rows=10000000 loops=1) Output: h.column_a Buckets: 131072 (originally 131072) Batches: 512 (originally 256) Memory Usage: 371094kB Buffers: shared read=54055, temp written=31738 -> Seq Scan on public.table_h h (cost=0.00..154055.00 rows=10000000 width=7) (actual time=2.458..606.422 rows=10000000 loops=1) Output: h.column_a Buffers: shared read=54055 Query Identifier: 334721522907995613 Planning: Buffers: shared hit=6 read=1 dirtied=1 Planning Time: 0.237 ms JIT: Functions: 11 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 0.330 ms, Inlining 0.000 ms, Optimization 0.203 ms, Emission 2.311 ms, Total 2.844 ms Execution Time: 3584.439 ms (25 rows)

Now, the Hash node typically reports hundreds of MB of Memory Usage, with more/larger temp spills (higher Batches, more temp_blks_*). What changed? Only the distribution (cardinality). (Why buckets/batches behave this way is covered in the algorithm references.) [postgrespro.com], [interdb.jp]

Findings (2) When we have the data distributed with LOW cardinality it takes 371094kB of memory usage (work_mem), shared hit/read=54056

So, same amount of data being handled by the query in terms of shared memory, but totally different work_mem usage pattern due to the low cardinality and the join method (Hash Join) that put most of those rows in a single bucket and that is not limited by default, so, it can cause OOM errors at any time.

Scale up rows to observe linear growth

We´ll add same rows in table_h repeat times so we can play with more data (low cardinality)

insert into table_h select * from table_h; vacuum full table_h;

You’ll see Memory Usage and temp I/O scale with rowcount under skew. (Beware: this can become I/O and RAM heavy—do this incrementally.) [thoughtbot.com]

NumRows table_h	Shared read/hit	Written	Temp read/written	Memory Usage (work_mem)
10M	54056		63480+63480	371094kB
20M	108110		126956+126956	742188kB
80M	432434 (1,64GB)	0	253908+253908	2968750kB (2,8GB)

Observability: what you will (and won’t) see

EXPLAIN is your friend

EXPLAIN (ANALYZE, BUFFERS) exposes Memory Usage, Buckets:, Batches: in the Hash node and temp block I/O. Batches > 1 is a near‑certain sign of spilling. [postgresql.org], [thoughtbot.com]

Query Store / pg_stat_statements limitations
- Azure Database for PostgreSQL – Flexible Server Query Store aggregates runtime and (optionally) wait stats over time windows and stores them in azure_sys, with views under query_store.*. It’s fantastic to find which queries chew CPU/I/O or wait, but it doesn’t report per‑query transient memory usage (e.g., “how many MB did that hash table peak at?”) you can estimate reviewing the temporary blocks. [learn.microsoft.com]
- Under the hood, what you do get—whether via Query Store or vanilla PostgreSQL pg_stat_statements—are cumulative counters like shared_blks_read, shared_blks_hit, temp_blks_read, temp_blks_written, timings, etc. Those help confirm buffer/temp activity, yet no direct hash table memory metric exists. Combine them with EXPLAIN and server metrics to triangulate. [postgresql.org]

Tip (Azure Flexible Server)
Enable Query Store in Server parameters via pg_qs.query_capture_mode and (optionally) wait sampling via pgms_wait_sampling.query_capture_mode, then use query_store.qs_view to correlate temp block usage and execution times across intervals. [learn.microsoft.com]

Typical OOM symptom in logs

In extreme skew with concurrent executions, you may encounter:

ERROR: out of memory

DETAIL: Failed on request of size 32800 in memory context "HashBatchContext".

This is a classic signature of hash join memory pressure. [postgresql.org], [thisistheway.wiki]

What to do about it (mitigations & best practices)

Don’t force Hash Join unless required
If you used planner hints (e.g., pg_hint_plan) or GUCs (Grand Unified Configuration) to force Hash Join, remove them and let the planner re‑evaluate. (If you must hint, be aware pg_hint_plan is a third‑party extension and not available in all environments.) [pg-hint-pl...thedocs.io], [pg-hint-pl...thedocs.io]
Fix skew / cardinality at the source
- Re‑model data to avoid low‑NDV (Number of Distinct Values in a column) joins (e.g., pre‑aggregate, filter earlier, or exclude degenerate keys).
- Ensure statistics are current so the planner estimates are realistic. (Skew awareness is limited; poor estimates → risky sizing.) [postgresql.org]
Pick a safer join strategy when appropriate
If distribution is highly skewed, Merge Join (with supporting indexes/sort order) or Nested Loop (for selective probes) might be more memory‑predictable. Let the planner choose, or enable alternatives by undoing GUCs that disabled them. [postgresql.org]
Bound memory consciously
- Keep work_mem modest for mixed/OLTP workloads; remember it’s per operation, per node, per worker.
- Adjust hash_mem_multiplier judiciously (introduced in PG13; default now commonly 2.0) if you understand the spill trade‑offs. [postgresqlco.nf], [pgpedia.info]
Observe spills and tune iteratively
Use EXPLAIN (ANALYZE, BUFFERS) to see Batches (spills) and Memory Usage; use Query Store/pg_stat_statements to find which queries generate the most temp I/O. Raise work_mem for a session only when justified. [postgresql.org], [postgresql.org]
Parallelism awareness
Each worker can perform its own memory‑using operations; parallel hash join has distinct behavior. If you aren’t sure, temporarily disable parallelism to simplify analysis, then re‑enable once you understand the footprint. [postgresql.org]

Validating on Azure Database for PostgreSQL – Flexible Server

The behavior is not Azure‑specific, but you can reproduce the same sequence on Flexible Server (e.g., General Purpose). A few notes:

Confirm/adjust work_mem, hash_mem_multiplier, enable_* planner toggles as session settings. (Azure exposes standard PostgreSQL parameters.) [learn.microsoft.com]
Use Query Store to confirm stable shared/temporary block patterns across executions, then use EXPLAIN (ANALYZE, BUFFERS) per query to spot hash table memory footprints. [learn.microsoft.com], [postgresql.org]
Changing some default parameters

We´ll repeat previous steps in Azure Database for PostgreSQL Flexible server:

Creating and populating tables

Query & Execution plan

explain (analyze,buffers,costs,verbose) SELECT /*+ HashJoin(s h) Leading((s h)) */ COUNT(*) FROM table_s s JOIN table_h h ON s.column_a= h.column_a;

QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=279052.88..279052.89 rows=1 width=8) (actual time=3171.186..3171.191 rows=1 loops=1) Output: count(*) Buffers: shared hit=33 read=54023, temp read=135 written=34042 I/O Timings: shared read=184.869, temp read=0.278 write=333.970 -> Hash Join (cost=279051.84..279052.88 rows=1 width=0) (actual time=3147.288..3171.182 rows=1 loops=1) Hash Cond: (s.column_a = h.column_a) Buffers: shared hit=33 read=54023, temp read=135 written=34042 I/O Timings: shared read=184.869, temp read=0.278 write=333.970 -> Seq Scan on public.table_s s (cost=0.00..1.01 rows=1 width=32) (actual time=0.315..0.316 rows=1 loops=1) Output: s.column_a Buffers: shared read=1 I/O Timings: shared read=0.018 -> Hash (cost=154053.04..154053.04 rows=9999904 width=7) (actual time=3109.278..3109.279 rows=10000000 loops=1) Output: h.column_a Buckets: 131072 Batches: 256 Memory Usage: 2551kB Buffers: shared hit=32 read=54022, temp written=33786 I/O Timings: shared read=184.851, temp write=332.059 -> Seq Scan on public.table_h h (cost=0.00..154053.04 rows=9999904 width=7) (actual time=0.019..1258.472 rows=10000000 loops=1) Output: h.column_a Buffers: shared hit=32 read=54022 I/O Timings: shared read=184.851 Query Identifier: 5636209387670245929 Planning: Buffers: shared hit=37 Planning Time: 0.575 ms Execution Time: 3171.375 ms (26 rows)

Findings (3) In Azure Database for PostgreSQL Flexible server, when we have the data totally distributed with high cardinality it takes only 2551kB of memory usage (work_mem), shared hit/read=54056

Skew it to LOW cardinality

As we did previously, we change column_a to having only one different value in all rows in table_h

update table_h set column_a='30020', column_b='30020'; vacuum full table_h;

In this case we force the join method with pg_hint_plan:

explain (analyze,buffers,costs,verbose) SELECT /*+ HashJoin(s h) Leading((s h)) */ COUNT(*) FROM table_s s JOIN table_h h ON s.column_a= h.column_a; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=279056.04..279056.05 rows=1 width=8) (actual time=4397.556..4397.560 rows=1 loops=1) Output: count(*) Buffers: shared hit=2 read=54055, temp read=63480 written=63480 I/O Timings: shared read=89.396, temp read=90.377 write=300.290 -> Hash Join (cost=279055.00..279056.03 rows=1 width=0) (actual time=3271.145..3987.154 rows=10000000 loops=1) Hash Cond: (s.column_a = h.column_a) Buffers: shared hit=2 read=54055, temp read=63480 written=63480 I/O Timings: shared read=89.396, temp read=90.377 write=300.290 -> Seq Scan on public.table_s s (cost=0.00..1.01 rows=1 width=32) (actual time=0.006..0.008 rows=1 loops=1) Output: s.column_a Buffers: shared hit=1 -> Hash (cost=154055.00..154055.00 rows=10000000 width=7) (actual time=1958.729..1958.731 rows=10000000 loops=1) Output: h.column_a Buckets: 262144 (originally 262144) Batches: 256 (originally 128) Memory Usage: 371094kB Buffers: shared read=54055, temp written=31738 I/O Timings: shared read=89.396, temp write=149.076 -> Seq Scan on public.table_h h (cost=0.00..154055.00 rows=10000000 width=7) (actual time=0.159..789.449 rows=10000000 loops=1) Output: h.column_a Buffers: shared read=54055 I/O Timings: shared read=89.396 Query Identifier: 8893575855188549861 Planning: Buffers: shared hit=5 Planning Time: 0.157 ms Execution Time: 4414.268 ms (25 rows)

NumRows table_h	Shared read/hit	Written	Temp read/written	Memory Usage (work_mem)
10M	54056		63480+63480	371094kB
20M	108110		126956+126956	742188kB
80M	432434	0	253908+253908	2968750kB

We observe the same numbers compared with our docker installation.

See the extension docs for installation/usage and the hint table for cases where you want to force a specific join method. [pg-hint-pl...thedocs.io], [pg-hint-pl...thedocs.io]

FAQ

Q: I set work_mem = 4MB. Why did my Hash Join report ~371MB Memory Usage?
A: Hash joins can use up to hash_mem_multiplier × work_mem per hash table, and skew can cause large per‑bucket chains. Multiple nodes/workers multiply usage. work_mem is not a global hard cap. [postgresqlco.nf], [pgpedia.info]

Q: How do I know if a Hash Join spilled to disk?
A: In EXPLAIN (ANALYZE), Hash node shows Batches: N. N > 1 indicates partitioning and temp I/O; you’ll also see temp_blks_read/written in buffers and Temp I/O timings. [postgresql.org], [thoughtbot.com]

Q: Can Query Store tell me per‑query memory consumption?
A: Not directly. It gives time‑sliced runtime and wait stats (plus temp/shared block counters via underlying stats), but no “peak MB used by this hash table” metric. Use EXPLAIN and server metrics. [learn.microsoft.com], [postgresql.org]

Q: I hit “Failed on request … in HashBatchContext.” What’s that?
A: That’s an OOM raised by the executor while allocating memory. Reduce skew, avoid forced hash joins, or review per‑query memory and concurrency. [postgresql.org]

Ready‑to‑use mitigation checklist (DBA quick wins)

Remove joins hints/GUC overrides that force Hash Join; re‑plan. [pg-hint-pl...thedocs.io]
Refresh stats; confirm realistic rowcount/NDV estimates. [postgresql.org]
Consider alternate join strategies (Merge/Index‑Nested‑Loop) when skew is high. [postgresql.org]
Keep work_mem conservative for OLTP; consider session‑scoped bumps only for specific analytic queries. [postgresql.org]
Tune hash_mem_multiplier carefully only after understanding spill patterns. [postgresqlco.nf]
Use EXPLAIN (ANALYZE, BUFFERS) to verify Batches and Memory Usage. [postgresql.org]
Use Query Store/pg_stat_statements to find heavy temp/shared I/O offenders over time.

Guide to Upgrade Azure Database for MySQL from 8.0 to 8.4

SamZhao — Thu, 05 Mar 2026 14:00:00 GMT

This guide merges Azure official documentation and best practices from real-world upgrades. Always refer to the latest Azure documentation and portal/CLI for any updates or discrepancies.

1. Background

Why Upgrade?

MySQL 5.7 reached end of community support in October 2023, and will enter extended support in August 2026.
MySQL 8.0 will reach end of community support in April 2026 and will enter extended support in January 2027.
MySQL 8.4 is the first official LTS (Long Term Support) version, officially supported by Oracle until April 2032.
Upgrading ensures security, performance, and continued support.

Supported Upgrade Paths:

5.7 → 8.0 (must be completed first)
8.0 → 8.4
Direct 5.7 → 8.4 is NOT supported.
Downgrades are NOT supported.

Key Notes:

Irreversible: Major version upgrades are irreversible and cannot be rolled back directly.
Downtime: The server is offline during the upgrade. Downtime depends on database size and table count.
HA Limitation: High Availability (HA) servers cannot achieve near-zero downtime during major upgrades due to cross-version replication instability.
Performance: Some workloads may not see performance improvements after upgrade.

2. Key Changes

Authentication Plugin Changes

mysql_native_password is disabled by default in the MySQL 8.4 community version. However, it is enabled by default in Azure MySQL 8.4. Older client versions may not be able to connect using mysql_native_password. If you encounter this issue, upgrade your client version first.
The default_authentication_plugin variable should be removed.
All users should be migrated to the caching_sha2_password plugin.

Foreign Key Constraint Enforcement

Before the upgrade, referenced columns must have a unique key; check all foreign key constraints before upgrade.

Removed Features

FLUSH HOSTS command → use TRUNCATE TABLE performance_schema.host_cache
PASSWORD() function → use ALTER USER
tx_isolation option → use transaction_isolation
expire_logs_days → use binlog_expire_logs_seconds
AUTO_INCREMENT is no longer allowed on FLOAT/DOUBLE columns

New Reserved Keywords

MANUAL, PARALLEL, QUALIFY, TABLESAMPLE — do not use these as unquoted identifiers

Terminology Changes

MASTER/SLAVE terminology updated to SOURCE/REPLICA
Update all scripts and applications using old the terms

InnoDB 8.0 → 8.4 Default Parameter Changes

innodb_adaptive_hash_index: ON → OFF
innodb_change_buffering: All → None
innodb_buffer_pool_in_core_file: ON → OFF
innodb_io_capacity: 200 → 10000
innodb_log_buffer_size: 16MB → 64MB

3. Upgrade Prerequisites

Basic Requirements

Item	Requirement
Deployment Type	Azure Database for MySQL Flexible Server instance only
Region	Region must support MySQL 8.4 in Azure Portal
Current Version	Must be 8.0.x (check via SELECT VERSION(); or Portal)
Server Status	Server must be running, with no ongoing scaling/restart/updates

Read Replica Version

If read replicas exist, upgrade them before upgrading the primary server. Cross-version replication is not guaranteed to be stable. Follow official documentation for handling replicas (stop replication and upgrade separately, or delete and recreate after upgrade).

Create On-Demand Backup

Before upgrading production, create an on-demand backup for rollback if needed: - Azure Portal > Your MySQL Server > Backup & Restore > Backup Now - Backups created before upgrade restore to the old version; backups after upgrade restore to the new version.

XA Transactions

Ensure no active or pending XA transactions:

XA RECOVER;

If any results are returned, roll back these transactions:

XA ROLLBACK 'xid';

SQL Mode

Remove deprecated sql_mode values before upgrade.

Deprecated: NO_AUTO_CREATE_USER, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS - Check current SQL mode:

SELECT @@sql_mode;

In Azure Portal: Go to Server Parameters > Search “sql_mode” > Remove deprecated values > Save

4. Pre-Upgrade Checks

Use Upgrade Check Tool (MySQL Shell)

Check Items

The tool performs 40+ automated checks, mainly including:

Check Category	Description
Deprecated Time Types	Checks for old-format time columns
Reserved Keyword Conflicts	Checks for conflicts with reserved keywords in the new version
UTF8MB3 Charset	Checks for objects needing migration to UTF8MB4
Removed System Variables	Checks for configuration items no longer supported
Authentication Plugin Change	Checks for users needing updated authentication methods
Partition Table Restrictions	Checks for partition compatibility
Foreign Key Names	Checks for foreign key name conflicts

Oracle provides util.checkForServerUpgrade() to detect compatibility issues.

Install MySQL Shell 8.4 or higher.
Connect to Azure MySQL server:

mysqlsh --host=<your-server>.mysql.database.azure.com \ --user=<admin-user> \ --password \ --ssl-mode=REQUIRED

Run upgrade check:

util.checkForServerUpgrade() util.checkForServerUpgrade({"targetVersion": "8.4.0"})

Command-line (recommended):

mysqlsh <admin-user>@<your-server>.mysql.database.azure.com:3306 \ --ssl-mode=REQUIRED \ -- util checkForServerUpgrade \ --target-version=8.4.0 \ --output-format=JSON

Required privileges: RELOAD, PROCESS, SELECT.

The tool performs 40+ checks (deprecated types, reserved keywords, charset, removed variables, auth plugins, partitioning, FK constraints, etc.).

Review Results

Errors: Must be fixed before upgrade
Warnings: Strongly recommended to fix
Notices: Informational

Additional Compatibility and Validation Checks

Client Library Compatibility:

Some client libraries may be incompatible with MySQL 8.0+ due to outdated drivers. Collect and review all client driver versions and test compatibility in a staging environment before upgrade.

Authentication Changes

After upgrade, applications may fail to connect if not compatible with new authentication plugins. MySQL 8.0 defaults to caching_sha2_password, and MySQL 8.4 disables mysql_native_password by default.

Solution: Upgrade client drivers to versions supporting the new authentication method. If legacy drivers must be used, temporarily set user authentication to mysql_native_password and plan to upgrade drivers as soon as possible.

Charset and Collation Changes:

MySQL 8.0 defaults to utf8mb4 charset and utf8mb4_0900_ai_ci collation, which may cause changes in string comparison, index length limits, sort order, or JOIN-related errors due to charset mismatches.

Recommendations:

- Check if ORM and drivers support utf8mb4.
- Adjust index lengths (utf8mb4 uses up to 4 bytes per character).
- Specify COLLATE explicitly if you need to preserve original sort behavior.
- Standardize charset and collation settings for all tables and columns.

Object Statistics:

Review table and object statistics to estimate upgrade and rollback time.

Performance Considerations:

Most SQL workloads benefit from upgrades, but some queries may experience degraded performance (e.g., queries with ORDER BY, complex JOINs, subqueries, or many bound parameters). The optimizer evolves between versions (e.g., max_length_for_sort_data was deprecated in 8.0.20, execution plan changes, etc.).

Recommendations:

- Use standard SQL tuning methods: review execution plans, optimize queries, and add indexes as needed.

Test in Staging

💡 Strong Recommendation: First perform the upgrade in a staging environment (such as a restored backup copy of the original server) and validate the following:

Whether the upgrade process completes successfully
Estimated downtime
Application compatibility
Performance baseline comparison

5. Upgrade Methods & Scenarios

Upgrade Method Comparison

Method	Pros	Cons	Recommended For
In-Place Upgrade	Simple, no connection string change, no extra cost	Longer downtime, can be hours or even days, rollback only via backup	Dev/test, small prod, downtime allowed
Replica Based Upgrade	Low risk, fast rollback, minimal downtime	Extra cost, more steps, connection string change	Production, downtime sensitive

Method 1: In-Place Upgrade, Azure Portal (In-Place Upgrade, Recommended)

For General Purpose and Memory Optimized SKUs:

Sign in to Azure Portal
Navigate to your MySQL Flexible Server
Select Overview
Find MySQL Version and click Upgrade
Select target version 8.4
Review pre-upgrade validation
Confirm and start upgrade

For Burstable SKU: May require temporary SKU change to General Purpose before upgrade, then revert after.

Method 2: Azure CLI (In-Place Upgrade)

az cloud set --name AzureCloud az login az mysql flexible-server upgrade \ --name <your-server-name> \ --resource-group <your-resource-group> \ --version 8.4

Method 3: Minimal Downtime (Read Replica)

For production environments sensitive to downtime:

Create read replica (Portal > Replication > Add Replica)
Wait for replica sync (check SHOW REPLICA STATUS\G, ensure Seconds_Behind_Master = 0)
Upgrade replica to 8.4 (Portal or CLI)
Validate replica upgrade (SELECT VERSION();)
Promote replica to primary (Portal > Replication > Stop Replication)
Update application connection strings to new primary
(Optional) Delete old primary

This approach is an architecture migration + failover, not the same as in-place upgrade. Cross-version replication stability is not guaranteed. Fully test in staging and communicate risks to stakeholders.

6. Post-Upgrade Validation

Check MySQL version:

SELECT VERSION();

Check database and table status:

SHOW DATABASES; USE your_database; SHOW TABLE STATUS; CHECK TABLE your_table;

Validate application connectivity and key business functions
Monitor metrics for 24-48 hours: CPU, memory, I/O, query latency, error logs

7. Rollback Strategy

Major version upgrades are irreversible. Rollback requires restoring from backup:

Azure Portal > MySQL Server > Backup & Restore > Select pre-upgrade backup > Restore
Specify new server name
Wait for restore to complete
Update application connection strings to restored server

💡 The restored server is a new, independent server. The original server remains.

Business Rollback Advice:

- Confirm data loss/replay strategy for changes between upgrade and rollback
- Assess impact on dependent systems (reports, ETL, etc.)
- After rollback, point applications to old server, clean up new objects, and re-validate key functions

8. FAQs

Q1: Can I upgrade directly from MySQL 5.7 to 8.4?

No. Upgrade must be sequential: 5.7 → 8.0 → 8.4

Q2: How long does the upgrade take?

Downtime depends on database size, storage, and table count. Test in staging to estimate.

Q3: Can HA servers achieve zero downtime?

No. Major upgrades cannot use HA for near-zero downtime due to cross-version replication instability.

Q4: Will performance improve after upgrade?

Not guaranteed. Some workloads may see no change or slight decrease. Benchmark before and after.

Q5: Can I change other server properties during upgrade?

Not recommended. Avoid changing other properties via REST API or SDK during upgrade.

Q6: How to estimate downtime?

The number of objects contained in the database is related to the upgrade time; the larger the number, the longer it takes. The most accurate way to assess this is as follows. 1. Restore a test server from backup 2. Perform upgrade on test server 3. Record actual upgrade time 4. Add buffer for production

Q7: What happens to backups during/after upgrade?

Backups before upgrade restore to old version; after upgrade, to 8.4. Confirm retention and PITR settings before upgrade.

Q8: Is upgrade downtime included in SLA?

Planned downtime for upgrade is not counted in availability SLA. Schedule during maintenance window and communicate with stakeholders.

Q9: Charset and collation issues after upgrade?

MySQL 8.0+ defaults to utf8mb4 and utf8mb4_0900_ai_ci. Check ORM/driver support, adjust index length, specify COLLATE if needed, and unify charset settings.

Q10: Performance issues after upgrade?

Some queries may slow down due to optimizer changes. Use EXPLAIN/ANALYZE, add/optimize indexes, use query hints, and review execution plans.

Q11: Authentication issues after upgrade?

MySQL 8.0+ defaults to caching_sha2_password. Upgrade client drivers. If needed, temporarily revert user auth to mysql_native_password (not recommended for 8.4).

Q12: Client library compatibility?

Test all client libraries in staging. Upgrade outdated connectors/ORMs.

Q13: What is Azure Extended Support?

Azure provides paid extended support for MySQL versions after community EOL. See Extended Support for MySQL.

Stay Connected

We welcome your feedback and invite you to share your experiences or suggestions at AskAzureDBforMySQL@service.microsoft.com

Stay up to date by visiting What's new in Azure Database for MySQL, and follow us on YouTube | LinkedIn | X for ongoing updates.

Thank you for choosing Azure Database for MySQL!

References:

Troubleshooting Intermittent Query Failures on Azure SQL DB Read Replicas (Error 3947)

Anasuah_Chakraborty — Thu, 05 Mar 2026 07:58:51 GMT

Recently, I worked on an interesting customer case involving intermittent query failures while fetching reporting data from a read replica.

The customer was running their database on the Azure SQL Database Hyperscale service tier, utilizing the read scale-out replica to offload reporting traffic from the primary compute node.

However, while loading reports, the application occasionally took longer than usual and then failed with the following error:

[DataSource.Error] ERROR [HY000] [Microsoft][ODBC Driver 18 for SQL Server] Unspecified error occurred on SQL Server. Connection may have been terminated by the server.

ERROR [HY000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server] The service has encountered an error processing your request. Please try again.
Error code 3947.

In this post, I’ll walk through how we analyzed the issue, identified the root cause, and the mitigation strategies that helped the customer.

Understanding Error 3947

The key indicator in the error message was Error 3947.

3947 – The transaction was aborted because the secondary compute failed to catch up redo. Retry the transaction.

This error typically occurs when the secondary compute node (read replica) cannot keep up with redo operations being replayed from the primary replica.

When this happens, the system may terminate long-running queries on the replica to prevent the replica from falling too far behind the primary.

Step 1 – Connect to the Read Replica

To begin troubleshooting, connect to the read replica using SSMS or your client tool with the following connection parameter:

ApplicationIntent=ReadOnly

This ensures the session connects to the read scale-out replica instead of the primary compute node.

Step 2 – Check for Active Blocking Sessions

Once connected to the read replica, the first step is to check whether queries are blocking each other.

The following query helps identify active requests and blocking chains.

SELECT

r.session_id,

r.status,

r.command,

r.blocking_session_id,

r.wait_type,

r.wait_time,

r.wait_resource,

r.cpu_time,

r.total_elapsed_time,

DB_NAME(r.database_id) AS database_name,

SUBSTRING(t.text,

(r.statement_start_offset/2)+1,

((CASE r.statement_end_offset

WHEN -1 THEN DATALENGTH(t.text)

ELSE r.statement_end_offset END

- r.statement_start_offset)/2) + 1) AS running_statement

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

WHERE r.session_id <> @@SPID

ORDER BY r.total_elapsed_time DESC;

Columns to observe:

blocking_session_id – indicates if a query is being blocked
wait_type – shows what the query is waiting for
total_elapsed_time – identifies long-running queries

Step 3 – Identify Compile Locks and Schema Locks

While investigating the issue, we observed multiple compile locks and schema locks on several table objects.

Even though the database is read-only, queries still need access to metadata, and this can result in schema-related locks.

Use the following query to identify schema locks on objects.

SELECT

tl.request_session_id,

tl.resource_type,

tl.resource_associated_entity_id AS object_id,

OBJECT_NAME(tl.resource_associated_entity_id) AS object_name,

tl.request_mode,

tl.request_status

FROM sys.dm_tran_locks tl

WHERE tl.resource_type = 'OBJECT'

AND tl.request_mode LIKE '%SCH%'

ORDER BY tl.request_session_id;

Typical schema locks include:

SCH-S – Schema Stability
SCH-M – Schema Modification

These locks indicate metadata access or schema changes.

Step 4 – Identify Waiting Tasks

Next, check whether sessions are waiting due to schema lock contention.

SELECT

wt.session_id,

wt.wait_type,

wt.wait_duration_ms,

wt.blocking_session_id,

wt.resource_description

FROM sys.dm_os_waiting_tasks wt

WHERE wt.session_id > 50

ORDER BY wt.wait_duration_ms DESC;

Common wait types that may appear include:

LCK_M_SCH_S
LCK_M_SCH_M

These wait types usually indicate schema lock contention between queries and redo operations.

Why Blocking Happens on Read Replicas

In normal scenarios, read-only databases eliminate most user-generated blocking since users cannot modify data.

However, schema changes performed on the primary replica are still replayed on the read-only replica through the redo process.

Examples include:

ALTER TABLE
Index rebuilds
Statistics updates

During this replay process, the redo thread temporarily acquires schema locks to maintain metadata consistency.

If a read query accesses the same object at the same time, the query may need to wait until the schema operation completes.

When Long Running Queries Block the Redo Process

Queries running on read replicas must still access metadata such as:

Tables
Indexes
Statistics

In rare cases, the following scenario can occur:

A query on the read replica acquires metadata locks.
The primary replica executes schema changes.
The redo process attempts to replay those changes on the secondary.
The redo process becomes blocked by the query.

If the blocking query runs for too long, the system may terminate the query automatically to prevent the read replica from falling behind the primary.

When this occurs, the session may receive errors such as:

Error 1219
Error 3947

Root Cause in This Customer Scenario

In this particular case, the customer had an ETL pipeline running every 5 minutes on the primary replica.

The ETL process frequently modified database objects, which resulted in metadata changes that needed to be replayed on the read replica.

At the same time, reporting queries were running on the read replica. Occasionally, these queries conflicted with schema operations being replayed through the redo process.

This caused the redo process to lag behind, and eventually the system terminated the queries to maintain replication health, resulting in Error 3947.

Mitigation and Best Practices

While this behavior is by design, the following best practices can help minimize the impact.

Avoid Frequent Schema Changes During Peak Workloads

Frequent schema modifications increase the likelihood of blocking on read replicas.

Where possible:

Schedule schema changes during low workload windows
Reduce frequent metadata modifications in ETL jobs

Monitor Long Running Queries

Long-running queries increase the risk of blocking redo operations.

Use the following query to identify such queries:

SELECT

r.session_id,

r.start_time,

r.total_elapsed_time/1000 AS elapsed_seconds,

r.status,

r.wait_type,

r.blocking_session_id,

t.text AS query_text

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

WHERE r.session_id <> @@SPID

ORDER BY r.total_elapsed_time DESC;

Implement Retry Logic in Applications

Because these errors can occur intermittently, applications should implement retry logic when encountering:

Error 3947
Error 1219

Retrying the transaction typically succeeds once the redo process catches up.

Conclusion

Read replicas significantly improve scalability by offloading reporting workloads, but they still depend on the redo process to stay synchronized with the primary replica.

If long-running queries interfere with redo operations, the system may terminate those queries to protect replication health and availability.

By monitoring blocking, schema locks, and long-running queries on read replicas, it becomes easier to identify and mitigate these scenarios.

Managed Identity Support for Azure SQL Database Import & Export services (preview)

HugoQueiroz_MSFT — Tue, 03 Mar 2026 18:05:24 GMT

Today we’re announcing a public preview that lets Azure SQL Database Import & Export services authenticate with user-assigned managed identity. Now Azure SQL Databases can perform import and export operations with no passwords, storage keys or SAS tokens.

With this preview, customers can choose to use either a single user-assigned managed identity (UAMI) for both SQL and Storage permissions or assign separate UAMIs, one for the Azure SQL logical server and another for the Storage account, for full separation of duties.

At a glance:

Run Import/Export using a user-assigned managed identity (UAMI).
Use one identity for both SQL and Storage, or split them if you prefer tighter scoping.
Works in the portal, REST, Azure CLI, and PowerShell.

Why this matters:

Managed identity support makes SQL migrations simpler and safer, no passwords, storage keys, or SAS tokens. By leveraging managed identity when integrating Import/Export into a pipeline, you streamline access management and enhance security: permissions are granted directly to the identity, reducing manual credential handling and the risk of exposing sensitive information. This keeps operations efficient and secure, without secrets embedded in scripts

You’ve got two straightforward options:

One UAMI for everything (simplest setup).
Two UAMIs, one for SQL and one for Storage, recommended if you wish to maintain more strictly defined permissions.

Getting started:

Create a user-assigned managed identity (UAMI)
Decide up front whether you want one identity end-to-end, or two identities (SQL vs Storage) for separation of duties.

Attach the UAMI to the Azure SQL logical server
On the server Identity blade, add the UAMI so the Import/Export job can run as that identity.

Set the server’s Microsoft Entra ID admin to the UAMI
In Microsoft Entra ID > Set admin, select the UAMI. This is what lets the workflow authenticate to SQL without a password.

Grant Storage access
Use Storage Blob Data Reader for import and Storage Blob Data Contributor for export, assigned in Access control (IAM). If you can, scope the assignment to the container that holds the .bacpac.

Pass resource IDs (not names) in your calls

In REST/CLI/PowerShell, you pass the UAMI resource ID as the value of administratorLogin (SQL identity) and storageKey (Storage identity), and set authenticationType / storageKeyType to ManagedIdentity.
- administratorLogin → UAMI resource ID used for SQL auth
- storageKey → UAMI resource ID used for Storage
- authauthenticationType / storageKeyType → ManagedIdentity

Run the import/export job
Kick it off from the portal, REST, Azure CLI, or PowerShell. From there, the service uses the identity you selected to reach both SQL and Storage.

Portal experience

In the Azure portal, you can choose Authentication type = Managed identity and select the user-assigned managed identity to use for the operation.

Figure 1: Azure portal Import/Export experience with Managed identity authentication selected.

Notes

This preview supports user-assigned managed identities (UAMIs).
For least privilege, scope Storage roles to the specific container used for the .bacpac file and use two user-assigned managed identities (UAMIs), one for SQL and one for the storage.

Sample 1: REST API — Export using one UAMI:

$exportBody = "{ `n `"storageKeyType`": `"ManagedIdentity`", `n `"storageKey`": `"${managedIdentityServerResourceId}`", `n `"storageUri`": `"${storageUri}`", `n `"administratorLogin`": `"${managedIdentityServerResourceId}`", `n `"authenticationType`": `"ManagedIdentity`" `n}" $export = Invoke-AzRestMethod -Method POST -Path "/subscriptions/${subscriptionId}/resourceGroups/${resourceGroupName}/providers/Microsoft.Sql/servers/${serverName}/databases/${databaseName}/export?api-version=2024-05-01-preview" -Payload $exportBody # Poll operation status Invoke-AzRestMethod -Method GET $export.Headers.Location.AbsoluteUri

Sample 2: REST API — Import to a new server using one UAMI:

$serverName = "sql-mi-demo-target" $databaseName = "sqldb-mi-demo-target" # Same UAMI for SQL auth + Storage access $importBody = "{ `n `"operationMode`": `"Import`", `n `"administratorLogin`": `"${managedIdentityServerResourceId}`", `n `"authenticationType`": `"ManagedIdentity`", `n `"storageKeyType`": `"ManagedIdentity`", `n `"storageKey`": `"${managedIdentityServerResourceId}`", `n `"storageUri`": `"${storageUri}`", `n `"databaseName`": `"${databaseName}`" `n}" $import = Invoke-AzRestMethod -Method POST -Path "/subscriptions/${subscriptionId}/resourceGroups/${resourceGroupName}/providers/Microsoft.Sql/servers/${serverName}/databases/${databaseName}/import?api-version=2024-05-01-preview" -Payload $importBody # Poll operation status Invoke-AzRestMethod -Method GET $import.Headers.Location.AbsoluteUri

Sample 3: PowerShell — Export using two UAMIs:

# Server UAMI for SQL auth, Storage UAMI for storage access New-AzSqlDatabaseExport -ResourceGroupName $resourceGroupName -DatabaseName $databaseName -ServerName $serverName -StorageKeyType ManagedIdentity -StorageKey $managedIdentityStorageResourceId -StorageUri $storageUri -AuthenticationType ManagedIdentity -AdministratorLogin $managedIdentityServerResourceId

Sample 4: PowerShell — Import to a new server using two UAMIs:

New-AzSqlDatabaseImport -ResourceGroupName $resourceGroupName -DatabaseName $databaseName -ServerName $serverName -DatabaseMaxSizeBytes $databaseSizeInBytes -StorageKeyType "ManagedIdentity" -StorageKey $managedIdentityStorageResourceId -StorageUri $storageUri -Edition $edition -ServiceObjectiveName $serviceObjectiveName -AdministratorLogin $managedIdentityServerResourceId -AuthenticationType ManagedIdentity

Sample 5: Azure CLI — Export using two UAMIs:

az sql db export -s $serverName -n $databaseName -g $resourceGroupName --auth-type ManagedIdentity -u $managedIdentityServerResourceId --storage-key $managedIdentityStorageResourceId --storage-key-type ManagedIdentity --storage-uri $storageUri

Sample 6: Azure CLI — Import to a new server using two UAMIs:

az sql db import -s $serverName -n $databaseName -g $resourceGroupName --auth-type ManagedIdentity -u $managedIdentityServerResourceId --storage-key $managedIdentityStorageResourceId --storage-key-type ManagedIdentity --storage-uri $storageUrib

For more information and samples, please check Tutorial: Use managed identity with Azure SQL import and export (preview)

GA of update policy SQL Server 2025 for Azure SQL Managed Instance

Mladen_Andzic — Tue, 03 Mar 2026 00:22:29 GMT

We’re happy to announce that the update policy SQL Server 2025 for Azure SQL Managed Instance is now generally available (GA). SQL Server 2025 update policy contains all the latest SQL engine innovation while retaining database portability to the recent major release of SQL Server.

Update policy is an instance configuration option that provides flexibility and allows you to choose between instant access to the latest SQL engine features and fixed SQL engine feature set corresponding to 2022 and 2025 major releases of SQL Server. Regardless of the update policy chosen, you continue to benefit from Azure SQL platform innovation. New features and capabilities not related to the SQL engine – everything that makes Azure SQL Managed Instance a true PaaS service – are successively delivered to your Azure SQL Managed Instance resources.

What’s new in SQL Server 2025 update policy

In short, instances with update policy SQL Server 2025 benefit from all the SQL engine features that were gradually added to the Always-up-to-date policy over the past few years and are not available in the SQL Server 2022 update policy. Let’s name few most notable features, with complete list available in the update policy documentation:

Update policy for each modernization strategy

Always-up-to-date is a “perpetual” update policy. It has no end of lifetime and brings new SQL engine features to instances as soon as they are available in Azure. It enables you to always be at the forefront - to quickly adopt new yet production-ready SQL engine features, benefit from them in everyday operations and keep a competitive edge without waiting for the next major release of SQL Server.

In contrast, update policies SQL Server 2025 and SQL Server 2022 contain fixed sets of SQL engine features corresponding to the respective releases of SQL Server. They’re optimized to fulfill regulatory compliance, contractual, or other requirements for database/workload portability from managed instance to SQL Server. Over time, they get security patches, fixes, and incremental functional improvements in form of Cumulative Updates, but not new SQL engine features. They also have limited lifetime, aligned with the period of mainstream support of SQL Server releases. As the end of mainstream support for the update policy approaches, you should upgrade instances to a newer policy. Instances will be automatically upgraded to the next more recent update policy at the end of mainstream support of their existing update policy.

Best practices with the Update policy feature

Plan for the end of lifetime of SQL Server 2022 update policy if you’re using it today, and upgrade to a newer policy on your terms before automatic upgrade kicks in. Choose between Always-up-to-date and SQL Server 2025 update policy.
Make sure to add update policy configuration to your deployment templates and scripts, so that you don’t rely on service defaults that may change in the future.
Be aware that using some of the newly introduced features may require changing the database compatibility level. Consult feature documentation for details.

What’s coming next

SQL Server 2025 will become the default update policy in Azure portal during March 2026.

Future versions of REST API, PowerShell and CLI will also have the default value changed to SQL Server 2025 for the „database format“ parameter which corresponds to the instance’s update policy.

SQL Server 2022 policy will reach end of lifetime on January 11, 2028 when the mainstream support for SQL Server 2022 ends. Plan timely and change the update policy of your instances before that date.

Update policy transitions

Summary

Update policy SQL Server 2025 for Azure SQL Managed Instance is now generally available. It brings the same set of SQL engine features that exist in the new SQL Server 2025. Consider it if you have regulatory compliance, contractual, or other reasons for database/workload portability from Azure SQL Managed Instance to SQL Server 2025. Otherwise, use the Always-up-to-date policy which always provides the latest features and benefits available to Azure SQL Managed Instance.

If your instances are currently configured with SQL Server 2022 update policy, update them to a newer policy before the end of mainstream support.

For more details visit Update policy documentation. To stay up to date with the latest feature additions to Azure SQL Managed Instance, subscribe to the Azure SQL video channel, subscribe to the Azure SQL Blog feed, or bookmark What’s new in Azure SQL Managed Instance article with regular updates.

Why Developers and DBAs love SQL’s Dynamic Data Masking (Series-Part 1)

MadhumitaTripathyMSFT — Mon, 02 Mar 2026 10:17:08 GMT

Dynamic Data Masking (DDM) is one of those SQL features (available in SQL Server, Azure SQL DB, Azure SQL MI, SQL Database in Microsoft Fabric) that both developers and DBAs can rally behind. Why? Because it delivers a simple, built-in way to protect sensitive data—like phone numbers, emails, or IDs—without rewriting application logic or duplicating security rules across layers. With just a single line of T-SQL, you can configure masking directly at the column level, ensuring that non-privileged users see only obfuscated values while privileged users retain full access. This not only streamlines development but also supports compliance with data privacy regulations like GDPR and HIPAA, etc. by minimizing exposure to personally identifiable information (PII).

In this first post of our DDM series, we’ll walk through a real-world scenario using the default masking function to show how easy it is to implement and how much development effort it can save.

Scenario: Hiding customer phone numbers from support queries

Imagine you have a support application where agents can look up customer profiles. They need to know if a phone number exists for the customer but shouldn’t see the actual digits for privacy. In a traditional approach, a developer might implement custom logic in the app (or a SQL view) to replace phone numbers with placeholders like “XXXX” for non-privileged users. This adds complexity and duplicate logic across the app.

With DDM’s default masking, the database can handle this automatically. By applying a mask to the phone number column, any query by a non-privileged user will return a generic masked value (e.g. “XXXX”) instead of the real number. The support agent gets the information they need (that a number is on file) without revealing the actual phone number, and the developer writes zero masking code in the app.

This not only simplifies the application codebase but also ensures consistent data protection across all query access paths. As Microsoft’s documentation puts it, DDM lets you control how much sensitive data to reveal “with minimal effect on the application layer” – exactly what our scenario achieves.

Using the ‘Default’ Mask in T-SQL :

The ‘Default’ masking function is the simplest mask: it fully replaces the actual value with a fixed default based on data type. For text data, that default is XXXX. Let’s apply this to our phone number example. The following T-SQL snippet works in Azure SQL Database, Azure SQL MI and SQL Server:

SQL

-- Step 1: Create the table with a default mask on the Phone column

CREATE TABLE SupportCustomers (

    CustomerID   INT PRIMARY KEY,

    Name         NVARCHAR(100),

    Phone        NVARCHAR(15) MASKED WITH (FUNCTION = 'default()')  -- Apply default masking

);

-- Step 2: Insert sample data

INSERT INTO SupportCustomers (CustomerID, Name, Phone)

VALUES (1, 'Alice Johnson', '222-555-1234');

-- Step 3: Create a non-privileged user (no login for simplicity)

CREATE USER SupportAgent WITHOUT LOGIN;

-- Step 4: Grant SELECT permission on the table to the user

GRANT SELECT ON SupportCustomers TO SupportAgent;

-- Step 5: Execute a SELECT as the non-privileged user

EXECUTE AS USER = 'SupportAgent';

SELECT Name, Phone FROM SupportCustomers WHERE CustomerID = 1

Alternatively, you can use Azure Portal to configure masking as shown in the following screenshot:

Expected result: The query above would return Alice’s name and a masked phone number. Instead of seeing 222-555-1234, the Phone column would show XXXX. Alice’s actual number remains safely stored in the database, but it’s dynamically obscured for the support agent’s query.

Meanwhile, privileged users such as administrator or db_owner which has CONTROL permission on the database or user with proper UNMASK permission would see the real phone number when running the same query.

How this helps Developers :

By pushing the masking logic down to the database, developers and DBAs avoid writing repetitive masking code in every app or report that touches this data. In our scenario, without DDM you might implement a check in the application like:

If user_role == “Support”, then show “XXXX” for phone number, else show full phone.

With DDM, such conditional code isn’t needed – the database takes care of it. This means:

Less application code to write and maintain for masking

Consistent masking everywhere (whether data is accessed via app, report, or ad-hoc query).

Quick changes to masking rules in one place if requirements change, without hunting through application code.

From a security standpoint, DDM reduces the risk of accidental data exposure and helps in compliance scenarios where personal data must be protected in lower environments or by certain roles, while reducing the developer effort drastically.

In the next posts of this series, we’ll explore other masking functions (like Email, Partial, and Random etc) with different scenarios. By the end, you’ll see how each built-in mask can be applied to make data security and compliance more developer-friendly!

Reference Links :

Dynamic Data Masking - SQL Server | Microsoft Learn

Dynamic Data Masking - Azure SQL Database & Azure SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn

Part 2: Safely Cleaning Orphaned Records in Change Tracking Side Tables

Mohamed_Baioumy_MSFT — Sat, 28 Feb 2026 02:11:17 GMT

Applies to: Azure SQL Database (Change Tracking enabled)

Recap (Part 1)

In Part 1, we covered how to detect “orphaned” records in Change Tracking (CT) side tables — rows whose sys_change_xdes_id no longer has a matching transaction entry in the commit table (sys.syscommittab). This situation often leads to unexpected CT growth and “stuck cleanup” symptoms because the mapping data required for normal cleanup is missing.

Part 1 link: Identifying Orphaned Records in Change Tracking Side Tables (Read‑Only Health Check)

Why Part 2 is needed

A common “root pattern” we see in the field is:

Side-table cleanup attempts to delete expired metadata
Some side-table deletions fail (locks/timeouts/errors)
Commit-table cleanup proceeds anyway (or a custom workflow deletes from commit table without validating side-table deletes)
Remaining side-table rows now reference xdes_id values that no longer exist in sys.syscommittab → orphans

Microsoft Learn also emphasizes that syscommittab cleanup depends on side-table cleanup — commit-table cleanup should happen only after side tables are cleaned.

This Part 2 script focuses on removing orphaned rows from side tables (and does not touch sys.syscommittab), so cleanup logic can stabilize again.

Important prerequisites & constraints (read this first)

1) Internal table access on Azure SQL Database

In Azure SQL Database, customers may not be able to access certain internal CT artifacts directly (even when attempting DAC-style workflows). In the related case discussions, internal testing noted that self‑service cleanup against internal tables can be infeasible.

2) CHECKPOINT note (why it’s in the script)

sys.dm_tran_commit_table exposes commit-table data and is backed by sys.syscommittab. Microsoft Learn notes that read-only users may not see live changes until a CHECKPOINT occurs.
That’s why your script includes the optional CHECKPOINT comment before reading commit-table state.

3) Supported guidance vs. custom remediation

Microsoft Learn provides official troubleshooting/mitigation guidance for CT cleanup issues (including checking dbo.MSChange_tracking_history, assessing stale rows, and using sp_flush_commit_table_on_demand for commit-table cleanup).
Your script is a targeted remediation pattern for a specific failure mode (orphaned side-table rows). Use it carefully, test first, and follow organizational approval processes.

What this script does (high-level)

T-SQL script is essentially Part 1 detection + optional targeted delete generation:

Compute the “safe cleanup point” from CT retention (wall-clock → CSN) using sp_changetracking_time_to_csn — the same concept used in Part 1.
Enumerate CT side tables via sys.internal_tables where internal_type = 209 (CT side tables).
For each side table, identify candidate orphaned transaction IDs (sys_change_xdes_id) that:
- are older than a computed boundary (@minXdesId derived from sys.dm_tran_commit_table), and
- have no matching xdes_id in sys.syscommittab at/before the cleanup point
Print orphan counts per side table using RAISERROR … WITH NOWAIT (operator-friendly, streaming output).
Safety cross-check: abort if any “orphan” unexpectedly exists in sys.syscommittab (defensive sanity gate)
Generate a DELETE statement for the current side table (execution is commented out)

The script (Part 2) — “detect + generate delete”

Below is T-SQL script. I kept the delete step disabled by default, so it remains safe to share. (You can enable execution only after approvals/testing.)

-- use <[DBName]> -- switch to the right database -- run checkpoint first to ensure all in-memory commit table data is persisted to disk (syscommittab) -- checkpoint SET NOCOUNT ON -- find the invalid clean version based on configured retention DECLARE time DATETIME, @csn BIGINT = 0, @minCleanupPoint BIGINT = 0 DECLARE @retention_period INT, @retention_period_units NVARCHAR(10) SELECT @retention_period = retention_period, @retention_period_units = retention_period_units FROM sys.change_tracking_databases where database_id = DB_ID() SELECT time = CASE WHEN @retention_period_units = 1 then DATEADD(minute, (-1 * @retention_period), GETUTCDATE()) WHEN @retention_period_units = 2 then DATEADD(hour, (-1 * @retention_period), GETUTCDATE()) ELSE DATEADD(day, (-1 * @retention_period), GETUTCDATE()) END EXEC sp_changetracking_time_to_csn time = time, @csn = @csn OUTPUT SELECT @minCleanupPoint = @csn SELECT @minCleanupPoint as minCsn -- 688118 -- iterate over all the change tracking side tables DECLARE @sideTable SYSNAME; DECLARE ct_cursor CURSOR FAST_FORWARD FOR SELECT name FROM sys.internal_tables WHERE internal_type = 209; -- internal_type = 209 is for change tracking side tables OPEN ct_cursor; FETCH NEXT FROM ct_cursor INTO @sideTable; WHILE @@FETCH_STATUS = 0 BEGIN -- find the minimum expired xdes id declare @minXdesId BIGINT SELECT @minXdesId = min(xdes_id) FROM sys.dm_tran_commit_table where commit_ts <= @minCleanupPoint -- SELECT @minXdesId as minXdes -- create temp table for storing orphaned xdes id DROP TABLE IF EXISTS #OrphanedXdes; CREATE TABLE #OrphanedXdes ( sys_change_xdes_id BIGINT NOT NULL ); DECLARE @sql NVARCHAR(MAX); SET @sql = N' INSERT INTO #OrphanedXdes(sys_change_xdes_id) SELECT ct.sys_change_xdes_id FROM sys.' + QUOTENAME(@sideTable) + N' AS ct WHERE ct.sys_change_xdes_id < @minXdesId AND NOT EXISTS ( SELECT 1 FROM sys.syscommittab AS s WHERE s.xdes_id = ct.sys_change_xdes_id AND s.commit_ts <= @minCleanupPoint );'; EXEC sys.sp_executesql @sql, N'@minXdesId BIGINT, @minCleanupPoint BIGINT', @minXdesId = @minXdesId, @minCleanupPoint = @minCleanupPoint; DECLARE @orphanedIdsCount BIGINT; SET @sql = N' SELECT @cnt = COUNT_BIG(sys_change_xdes_id) FROM #OrphanedXdes; '; EXEC sys.sp_executesql @sql, N'@cnt BIGINT OUTPUT', @cnt = @orphanedIdsCount OUTPUT; -- Raise error if any orphaned xdes exists IF (@orphanedIdsCount > 0) BEGIN DECLARE @msg NVARCHAR(4000) = @sideTable + N' : ' + CONVERT(NVARCHAR(30), @orphanedIdsCount); RAISERROR (@msg, 16, 1) WITH NOWAIT; DECLARE @newLine NVARCHAR(10) = CHAR(13) + CHAR(10) PRINT (@newLine) END -- Cross-check that no xdes should exist in syscommittab -- !!!IMPORTANT!!! RAISE an error and stop the cleanup if it does SET @sql = N' DECLARE @nonMatchingXdesCount BIGINT; SELECT @nonMatchingXdesCount = COUNT_BIG(*) FROM #OrphanedXdes AS ct WHERE EXISTS ( SELECT 1 FROM sys.syscommittab AS s WHERE s.xdes_id = ct.sys_change_xdes_id ); -- SELECT @nonMatchingXdesCount as nonMatchingXdesCount IF (COALESCE(@nonMatchingXdesCount, 0) > 0) BEGIN TRY DECLARE @msg NVARCHAR(1024); SET @msg = N''Cleanup aborted: orphan cross-check failed for side table [' + @sideTable + N'].''; RAISERROR(@msg, 16, 1) WITH NOWAIT; RETURN; END TRY BEGIN CATCH THROW; END CATCH '; EXEC sys.sp_executesql @sql; IF (@orphanedIdsCount > 0) BEGIN -- Prepare the query to delete the orphaned rows from the side table SET @sql = N'DELETE ct FROM sys.' + @sideTable + N' ct WHERE EXISTS (SELECT 1 FROM #OrphanedXdes AS o WHERE o.sys_change_xdes_id = ct.sys_change_xdes_id);'; SELECT @sql -- validate the delete query is correctly generated -- Sample delete statement: DELETE ct FROM sys.change_tracking_1221579390 ct WHERE EXISTS (SELECT 1 FROM #OrphanedXdes AS o WHERE o.sys_change_xdes_id = ct.sys_change_xdes_id); -- NOTE: Uncomment the below query to execute the delete statement and remove the orphaned records -- EXEC sys.sp_executesql @sql; END DROP TABLE IF EXISTS #OrphanedXdes; FETCH NEXT FROM ct_cursor INTO @sideTable; END CLOSE ct_cursor; DEALLOCATE ct_cursor; SET NOCOUNT OFF

Why the “cross-check abort” is a good idea

Notice the safety gate:

You first define orphans as those not existing in sys.syscommittab (for the cleanup horizon).
Then you re-check: “If any of these appear in syscommittab, abort.”

This prevents an accidental delete if:

the cleanup horizon math is wrong,
the environment has unexpected visibility differences, or
the temp-table contents are not what you expect.

That defensive posture aligns well with the general principle documented in Microsoft Learn: commit-table cleanup should only occur after side-table cleanup, and troubleshooting should be data-driven and cautious.

How to interpret the output

If you see no RAISERROR lines, the script did not find orphaned rows under the defined criteria.
If you see:
- change_tracking_<id> : <count>
that indicates <count> orphaned transaction references in that CT side table. This is the same style used in Part 1 for long-running, streaming progress.

Next steps (recommended order)

Confirm CT configuration (retention + AUTO_CLEANUP status) using official guidance.
Run Part 1 / Part 2 detection to quantify scope (which side tables, how many).
If you need to remediate:
- Prefer supported mitigations where possible (for example, disable/enable CT on a table to purge tracking metadata for that table is listed as a “quickest remedy” in Microsoft Learn for certain cleanup lock conflict scenarios).
- If table-level disable/enable isn’t acceptable, use an approval-driven approach for targeted cleanup.

Closing

Orphaned CT side-table records are one of those “silent growth” conditions that can be easy to miss until storage or CHANGETABLE performance becomes painful. Part 1 helps you spot the issue early; Part 2 helps you prepare a safe, targeted cleanup workflow — with explicit safety gates and a delete step that remains disabled by default.

References

Troubleshooting Change Tracking cleanup growth and orphaned rows in Azure SQL Database

Mohamed_Baioumy_MSFT — Sat, 28 Feb 2026 01:49:00 GMT

Applies to: Azure SQL Database
Scenario: Change Tracking (CT) side tables grow unexpectedly, and “orphaned” rows appear after switching between auto-cleanup and custom/scheduled manual cleanup.

The problem (what we observed)

In the case tracked, the discussion focused on Change Tracking cleanup behavior—including unexpected growth in CT side tables and orphaned records. The customer also referenced earlier guidance to move away from auto-cleanup due to locking concerns during upgrades, and the team needed to propose safe next steps quickly.

A parallel concern was that CT auto-cleanup could block DDL during upgrades (schema lock behavior), which triggered work to deploy a fix and validate it in a lab before broader rollout.

Why this is tricky

The engagement highlighted that manual cleanup and auto-cleanup can behave differently in real-world, high-scale environments (large number of CT-enabled tables, heavy activity, and operational constraints like access and auditing). Investigation efforts included:

validating where orphaned rows exist and how many CT side tables are affected,
checking whether auto-cleanup is enabled/disabled, and
using auditing / Extended Events to identify who/what is dropping related history objects.

Additionally, Snapshot Isolation can prevent cleanup from progressing in some cases. it was noted that long-running snapshot transactions can prevent a safe cleanup point from advancing, which can block removal of expired entries from internal commit tracking tables until those transactions complete.

Practical troubleshooting steps (what helped)

1) Confirm CT configuration (retention + auto-cleanup)

Use the Change Tracking configuration options to validate retention and whether auto-cleanup is enabled. Microsoft Learn documents enabling CT at the database level (including CHANGE_RETENTION and AUTO_CLEANUP). Enable and Disable Change Tracking - SQL Server | Microsoft Learn

2) Quick backlog signal: commit table “oldest commit_time”

During the investigation, the team used a lightweight query to sanity-check backlog in the commit table:

SELECT TOP (1) * FROM sys.dm_tran_commit_table ORDER BY commit_time ASC;

if the returned commit_time is close to the retention horizon, auto-cleanup is likely keeping up (this query doesn’t require DAC).

3) Detect orphaned rows in CT side tables (read-only script)

A key artifact from this case is the following T-SQL script, which calculates a cleanup point based on configured retention and then iterates over CT side tables (sys.internal_tables where internal_type = 209) to identify rows whose sys_change_xdes_id no longer has a matching entry in sys.syscommittab at/below the cleanup point.

-- use <[DBName]> -- switch to the right database SET NOCOUNT ON -- find the invalid clean version based on configured retention DECLARE time DATETIME, @csn BIGINT = 0, @minCleanupPoint BIGINT = 0 DECLARE @retention_period INT, @retention_period_units NVARCHAR(10) SELECT @retention_period = retention_period, @retention_period_units = retention_period_units FROM sys.change_tracking_databases where database_id = DB_ID() SELECT time = CASE WHEN @retention_period_units = 1 then DATEADD(minute, (-1 * @retention_period), GETUTCDATE()) WHEN @retention_period_units = 2 then DATEADD(hour, (-1 * @retention_period), GETUTCDATE()) ELSE DATEADD(day, (-1 * @retention_period), GETUTCDATE()) END EXEC sp_changetracking_time_to_csn time = time, @csn = @csn OUTPUT SELECT @minCleanupPoint = @csn SELECT @minCleanupPoint as minCsn -- 688118 -- iterate over all the change tracking side tables DECLARE @sideTable SYSNAME; DECLARE ct_cursor CURSOR FAST_FORWARD FOR SELECT name FROM sys.internal_tables WHERE internal_type = 209; -- internal_type = 209 is for change tracking side tables OPEN ct_cursor; FETCH NEXT FROM ct_cursor INTO @sideTable; WHILE @@FETCH_STATUS = 0 BEGIN -- find the minimum expired xdes id declare @minXdesId BIGINT SELECT @minXdesId = min(xdes_id) FROM sys.dm_tran_commit_table where commit_ts <= @minCleanupPoint -- SELECT @minXdesId as minXdes -- create temp table for storing orphaned xdes id DROP TABLE IF EXISTS #OrphanedXdes; CREATE TABLE #OrphanedXdes ( sys_change_xdes_id BIGINT NOT NULL ); DECLARE @sql NVARCHAR(MAX); SET @sql = N' INSERT INTO #OrphanedXdes(sys_change_xdes_id) SELECT ct.sys_change_xdes_id FROM sys.' + QUOTENAME(@sideTable) + N' AS ct WHERE ct.sys_change_xdes_id < @minXdesId AND NOT EXISTS ( SELECT 1 FROM sys.syscommittab AS s WHERE s.xdes_id = ct.sys_change_xdes_id AND s.commit_ts <= @minCleanupPoint );'; EXEC sys.sp_executesql @sql, N'@minXdesId BIGINT, @minCleanupPoint BIGINT', @minXdesId = @minXdesId, @minCleanupPoint = @minCleanupPoint; DECLARE @orphanedIdsCount BIGINT; SET @sql = N' SELECT @cnt = COUNT_BIG(sys_change_xdes_id) FROM #OrphanedXdes; '; EXEC sys.sp_executesql @sql, N'@cnt BIGINT OUTPUT', @cnt = @orphanedIdsCount OUTPUT; -- Raise error if any orphaned xdes exists IF (@orphanedIdsCount > 0) BEGIN DECLARE @msg NVARCHAR(4000) = @sideTable + N' : ' + CONVERT(NVARCHAR(30), @orphanedIdsCount); RAISERROR (@msg, 16, 1) WITH NOWAIT; DECLARE @newLine NVARCHAR(10) = CHAR(13) + CHAR(10) PRINT (@newLine) END DROP TABLE IF EXISTS #OrphanedXdes; FETCH NEXT FROM ct_cursor INTO @sideTable; END CLOSE ct_cursor; DEALLOCATE ct_cursor; SET NOCOUNT OFF

Here’s the high-level logic (excerpted/annotated from the script):

Read retention settings from sys.change_tracking_databases
Convert “retention window” to a cleanup CSN using sp_changetracking_time_to_csn
For each CT side table (sys.internal_tables internal_type = 209):
- compare side-table sys_change_xdes_id vs. sys.syscommittab and count “orphaned” xdes ids
- emit a message when orphaned counts are present

Tip: This is read-only diagnostic logic. In your environment, validate permissions and impact before running in production.

4) If auto-cleanup is unexpectedly disabled, re-enable and monitor

In the email thread, the team observed auto-cleanup was disabled in at least one environment and recommended re-enabling it, then monitoring the CT history table to confirm cleanup activity resumes.

5) Use auditing / Extended Events to identify unexpected object drops

When investigating why a “history table” disappeared, the team reviewed extended event data and noted evidence of a specific application context associated with the drop (shared in the meeting discussion).

This is a key lesson: without auditing, it can be difficult to determine who/what disabled auto-cleanup or dropped relevant objects; the email thread explicitly called this out.

Mitigation options discussed

(often safest): disable & re-enable Change Tracking on affected tables

As an alternative to running manual deletion scripts, the troubleshooting recommended disabling and re-enabling Change Tracking on the set of tables containing orphaned rows—described as a well-established and safer cleanup method that avoids needing elevated access to run cleanup scripts directly.

Trade-off: disabling CT on a table removes existing change data from the corresponding side tables for that table.

About auto-cleanup performance improvements (why “stay on auto” may be preferred)

The troubleshooting included discussion that auto-cleanup is the area that continues to receive improvements and that performance enhancements exist (for example, improved adaptive behavior in newer SQL Server versions). Microsoft Learn describes that SQL Server 2025 introduces an “adaptive shallow cleanup approach” for large side tables, enabled by default, and explains how cleanup behavior changes compared to prior versions.

While Azure SQL Database implementation details differ from boxed SQL Server, the key operational takeaway from the discussion was: if possible, prefer auto-cleanup and avoid manual cleanup unless you have a strong reason, because manual cleanup may lack telemetry and can be harder to reason about at scale.

Key takeaways

Start with read-only validation: use a backlog signal query and an orphan-detection script to quantify scope before making changes.
Auditability matters: without auditing/trace evidence, identifying who disabled auto-cleanup or dropped related objects is difficult.
Snapshot Isolation can block cleanup progress: long-running snapshot transactions may prevent safe cleanup from advancing. Keep snapshot transactions short where possible.
A safe mitigation exists disable/re-enable Change Tracking on affected tables (with awareness of change data loss) can be safer than running deletion scripts.

References

Microsoft Learn: Change Tracking management overview (permissions, internal tables, behavior considerations). Manage Change Tracking - SQL Server | Microsoft Learn
Microsoft Learn: Enable and Disable Change Tracking (retention + auto-cleanup configuration). Enable and Disable Change Tracking - SQL Server | Microsoft Learn

Cannot enable Change Data Capture (CDC) on Azure SQL Database: Msg 22830 + Error 40529 (SUSER_SNAME)

Mohamed_Baioumy_MSFT — Sat, 28 Feb 2026 01:25:09 GMT

Issue

While enabling CDC at the database level on Azure SQL Database using:

EXEC sys.sp_cdc_enable_db; GO

the operation fails.

Error

The customer observed the following failure when running sys.sp_cdc_enable_db:

Msg 22830: Could not update the metadata that indicates the database is enabled for Change Data Capture.
Failure occurred when executing drop user cdc
Error 40529: "Built-in function 'SUSER_SNAME' in impersonation context is not supported in this version of SQL Server."

What we checked (quick validation)

Before applying any changes, we confirmed CDC wasn’t partially enabled and no CDC artifacts were created:

-- Is CDC enabled for this database? SELECT name, is_cdc_enabled FROM sys.databases WHERE name = DB_NAME();

-- Does CDC schema exist? SELECT name FROM sys.schemas WHERE name = 'cdc';

-- Does CDC user exist? SELECT name FROM sys.database_principals WHERE name = 'cdc';

These checks were used during troubleshooting in the SR.
(Also note: Microsoft Learn documents that enabling CDC creates the cdc schema/user and requires exclusive use of that schema/user.)

Cause

In this case, the failure aligned with a known Azure SQL Database CDC scenario: enabling CDC can fail if there is an active database-level trigger that calls SUSER_SNAME().

To identify active database-level triggers, we used:

SELECT name, object_id FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND is_disabled = 0;

Resolution / Workaround

The customer resolved the issue by:

Identifying the active database-level trigger.
Disabling the trigger temporarily.
Enabling CDC at the database level and then enabling CDC on the required tables.
Re-enabling the trigger after CDC was successfully enabled.

Post-resolution verification

After enabling CDC, you can validate the state using:

-- Confirm CDC enabled at DB level SELECT name, is_cdc_enabled FROM sys.databases WHERE name = DB_NAME();

And for table-level tracking, Microsoft Learn recommends checking the is_tracked_by_cdc column in sys.tables.

Notes / Requirements

To enable CDC for Azure SQL Database, db_owner is required.
Azure SQL Database uses a CDC scheduler (instead of SQL Server Agent jobs) for capture/cleanup.

DORA exit planning for Azure SQL Database: a practical, “general guidance” blueprint

Mohamed_Baioumy_MSFT — Sat, 28 Feb 2026 01:12:22 GMT

Why this matters: Under the EU Digital Operational Resilience Act (DORA), many financial entities are strengthening requirements around ICT risk management, third‑party risk oversight, and—critically—exit planning / substitutability. Microsoft provides resources to help customers navigate DORA, including a DORA compliance hub in the Microsoft Trust Center.

This post distills general guidance based on a real-world support thread where a customer requested a formal advisory describing an exit strategy for an Azure SQL Database workload (including a large database scenario).

(Note: The content here is intentionally generalized and not legal advice—always align with your compliance team and regulators.)

The customer asked Microsoft Support for a formal response to support DORA regulatory expectations, focusing on data portability, exit planning, and substitution capabilities for workloads running on Azure SQL Database.

The support response framed the need as:

a regulatory submission use case under DORA,
where Microsoft can provide official references and describe the technical capabilities enabling portability and exit.
while customers remain responsible for defining, documenting, testing, and periodically validating their exit procedures.

Microsoft’s DORA resources: where to pull “regulatory artifacts” from

A key part of the Support Request response was pointing to Microsoft’s formal compliance resources:

Microsoft publishes DORA-related guidance and operational resilience materials via the Microsoft Trust Center and makes compliance documentation available via the Service Trust Portal for supervisory/audit processes.
Microsoft also maintains a DORA compliance hub in the Trust Center aimed at helping financial institutions meet DORA requirements.
Microsoft Learn provides an overview of DORA, scope, and key areas for customer consideration. Navigating DORA compliance | Microsoft Trust Center

Practical takeaway: For DORA evidence packs, align your narrative to the regulator’s questions, and use Trust Center / Service Trust Portal materials as the “Microsoft-published” backbone, then attach your customer-owned exit runbooks and test evidence.

Data ownership and portability: the foundation of an exit plan

In the ticket’s advisory, Microsoft Support emphasized:

Azure SQL Database is built on the SQL Server engine, and customers retain ownership of their data.
The service supports portability through SQL Server–compatible schemas, T‑SQL, and documented export/restore mechanisms, reducing dependency on proprietary formats.

How to use this in a DORA exit narrative:
Frame “reversibility” as standards-based data and schema portability (SQL/T‑SQL + documented export/import). That’s exactly the type of substitutability narrative many regulators want to see.

Supported exit strategy building blocks (Azure SQL Database → on‑prem SQL Server)

The Support Request response described the exit approach at a high level, using supported, documented capabilities:

Exporting database schema and data using SQL Server–compatible formats
Restoring or importing into an on‑prem SQL Server environment with functional equivalence
Maintaining security controls (auth, encryption in transit/at rest, integrity protections) during transition
Validating restored data and application functionality as part of exit testing

One concrete, Microsoft-documented portability method for Azure SQL Database is exporting to a BACPAC (schema + data), which can later be imported into SQL Server.

BACPAC: what Microsoft documentation explicitly calls out (and why it matters for “exit planning”)

Microsoft Learn documents:

A BACPAC contains metadata and data and can be stored in Azure Blob storage or local storage and later imported into Azure SQL Database, Azure SQL Managed Instance, or SQL Server. Export a BACPAC File - SQL Server | Microsoft Learn
For transactional consistency, ensure no write activity during export or export from a transactionally consistent copy.
Blob-storage exports have a maximum BACPAC size of 200 GB; larger exports should go to local storage using SqlPackage.
BACPAC is not intended as a backup/restore mechanism; Azure SQL has built-in automated backups.

DORA relevance: BACPAC is a strong “portability evidence” artifact because it is explicitly positioned for “archiving” or “moving to another platform,” including SQL Server. sql-docs/azure-sql/database/database-export.md at live · MicrosoftDocs/sql-docs

Large databases: why “one-button export” may not be your plan

The Support Request thread highlighted a “large database” scenario and referenced that Microsoft documentation describes high-level migration patterns such as offline export/import and staged validation for large databases.

In practice, if your database is far beyond BACPAC’s typical constraints (for example, BACPAC export to blob capped at 200 GB), your exit plan should explicitly describe:

a staged approach (e.g., dry-run validation environment, phased cutover planning),
capacity planning (network bandwidth, validation windows),
and a testing cadence that produces regulator-friendly evidence.

The ticket response also emphasized that customers should plan for sufficient time and capacity for transfer and validation (especially for large databases).

Customer responsibilities under DORA (the part regulators care about most)

A key statement from the Support Request advisory is worth repeating as general guidance:

Microsoft provides the technical capabilities enabling data portability and exit, but customers remain responsible for defining, documenting, testing, and periodically validating exit procedures—including planning timelines, allocating sufficient capacity, executing test exits, and maintaining evidence for regulatory review.

This aligns well with DORA’s intent and Microsoft’s broader DORA guidance narrative: DORA requires operational resilience outcomes, and organizations must integrate cloud capabilities into their governance and controls.

A simple DORA-ready “exit plan checklist” you can adapt

Below is a general checklist you can use to structure your exit plan documentation and evidence pack—aligned with what was emphasized in the Support Request:

Scope & dependencies
Identify the Azure SQL Database workloads, dependent applications, and data flows to be included in the exit plan. (Customer-owned documentation and evidence)
Portability mechanism(s)
Reference documented portability options such as schema+data export mechanisms (e.g., BACPAC) where applicable.
Security controls during transition
Document how auth and encryption controls are maintained during transfer and restoration validation.
Validation plan
Define how you will validate data integrity and application functionality in the target environment.
Scale planning (large DBs)
Document transfer capacity planning, timelines, and staged validation where needed.
Evidence & audit trail
Store test outputs, run logs, and references to Microsoft Trust Center / Service Trust Portal materials used in submissions.

From the SR’s formal advisory perspective, the message is consistent:

Azure SQL Database supports data portability and exits planning via SQL Server–compatible design and documented export/import mechanisms,
Microsoft provides DORA-oriented compliance materials via the Trust Center and Service Trust Portal.
and customers should own the exit runbook, testing, and evidence required for regulatory review.

Offline vs. Immutable Backups for Azure SQL Database (General Guidance)

Mohamed_Baioumy_MSFT — Sat, 28 Feb 2026 00:48:26 GMT

Security and compliance teams often ask for “offline backups” and “immutable backups” as part of ransomware resilience, audit readiness, and recovery strategy. In Azure SQL Database, these terms map to different capabilities and operating models.

Immutable backups (WORM) are supported natively for Long-Term Retention (LTR) backups in Azure SQL Database, using time-based and legal hold immutability modes.
Offline backups (customer-controlled copies stored under your own governance) typically require a customer-managed export/copy process, because platform-managed backups (including LTR) are retained within Azure’s managed backup system.

This post explains the difference, what’s supported today, and practical design considerations to help you choose the right approach.

1) Start by clarifying the requirement: “offline” vs. “immutable”

What “immutable backup” means in practice

An immutable backup is stored in a Write Once, Read Many (WORM) state—non-modifiable and non-erasable for a user-defined retention period, providing protection against accidental or malicious deletion or modification (including by privileged administrators).

Azure SQL Database supports backup immutability for LTR backups, written to Azure immutable storage.

What “offline backup” usually implies

“Offline” is commonly used to mean customer-controlled copies stored separately (often under separate access controls and retention governance). Azure SQL Database’s built-in backups are platform-managed; if your policy explicitly requires “offline/customer-controlled” artifacts, you typically implement an export/copy process in addition to platform-managed backups.

2) What Azure SQL Database supports natively: LTR + immutability (WORM)

Long-Term Retention (LTR) basics

LTR exists to retain backups beyond the short-term retention window. It relies on the full database backups automatically created by the Azure SQL service and stores specified full backups in redundant Azure Blob storage with a retention policy of up to 10 years.

If an LTR policy is configured, automated backups are copied to different blobs for long-term storage. The copy process is a background job that has no performance impact on the database workload.

Immutability modes for LTR backups

Azure SQL Database LTR backups support both:

Time-based immutability (policy-driven) — enabled at the LTR policy level; once enabled and locked, new LTR backups taken from that point forward inherit the settings and remain immutable until the retention period ends.
Legal hold immutability — enabled/disabled on a specific existing backup, independent of time-based settings; backups remain immutable until legal hold is explicitly removed.

3) Key operational constraint to plan for (important!)

Microsoft documentation notes that when you configure immutability for an Azure SQL Database with an LTR policy, the associated logical server can be blocked from deletion until all immutable backups are removed (time-based and/or legal hold).

This is a big lifecycle-management consideration (especially for non-prod environments or automation that deletes/recreates servers).

4) Cost model: what changes when you enable immutability?

According to the documentation, there’s no additional cost for enabling immutability on LTR backups; however, backup storage charges continue to accrue as long as the immutable backup file exists, even if it’s past the configured LTR expiration date (while it remains immutable).

For broader spend governance, Microsoft recommends using Cost Management features to set budgets, monitor costs, and review forecasted costs and trends.

Practical interpretation (design guidance): immutability changes the delete/modify behavior and therefore can affect how long backups remain stored, which can influence total backup storage cost over time. This aligns with the “charges continue to accrue as long as the immutable backup file exists” note.

5) How to enable LTR and immutability (high-level, supported steps)

Configure LTR retention (Azure portal)

Microsoft’s guidance for LTR policy management includes navigate to your server → Backups → Retention policies, select the database(s), and set weekly/monthly/yearly retention periods (or 0 for none).

Enable time-based immutability (Azure portal)

The time-based immutability article describes enabling it through the server’s Backups → Retention Policies → Configure Policies, then checking Enable time-based immutability policy and lock the time-based immutable policy (backups aren’t immutable until locked).

Note: The documentation also highlights that only backups taken after enabling and locking time-based immutability become immutable; for existing backups, use legal hold immutability instead.

6) If you truly need “offline” (customer-controlled) backups

Azure SQL Database platform-managed backups (including LTR) are retained within Azure’s managed backup system. If your requirement explicitly mandates customer-controlled “offline” copies, you typically implement a customer-managed export/backup approach and store exported artifacts under your own governance and retention controls.

This is commonly positioned as complementary to platform-managed backups—use platform features for operational recovery plus LTR immutability for tamper-proof retention and add customer-managed exports only if “offline custody” is a strict requirement.

7) Compliance notes you can cite internally

The immutability documentation states Azure SQL Database backup immutability helps meet stringent regulatory requirements (examples listed include SEC Rule 17a-4(f), FINRA Rule 4511(c), and CFTC Rule 1.31(c)-(d)). It also notes that the Cohasset report is available in the Microsoft Service Trust Center, and you can request a letter of attestation via Azure Support.

8) Decision checklist (quick guidance)

Use this to decide what to implement:

Choose native LTR immutability if you need:

Tamper-proof backups (WORM) for compliance/audit/ransomware resilience.
A solution integrated into Azure SQL Database backup retention (LTR).

Add customer-managed exports if you need:

Customer-controlled “offline” custody of backup artifacts under your own storage governance/retention controls.

Plan carefully for lifecycle automation if you enable immutability:

Logical server deletion can be blocked while immutable backups exist.

References (Microsoft documentation)

Backup immutability for LTR backups: Backup Immutability for Long-Term Retention Backups - Azure SQL Database | Microsoft Learn
Time-based immutability configuration: Configure Time Based Backup Immutability for Long-Term Retention Backups - Azure SQL Database | Microsoft Learn
Legal hold immutability configuration: Managing Retention period of Azure SQL Database Backup
LTR overview (how it works, background copy/no perf impact): Long-Term Retention Backups - Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn
Manage LTR retention policies: Azure SQL Database: Manage long-term backup retention - Azure SQL Database | Microsoft Learn
Plan and manage Azure SQL Database costs: Plan and Manage Costs - Azure SQL Database | Microsoft Learn

Dashboards with Grafana - Now in Azure Portal for PostgreSQL

varun-dhawan — Thu, 26 Feb 2026 19:19:45 GMT

Monitoring Azure Database for PostgreSQL just got significantly simpler. With the new Azure Monitor Dashboards with Grafana, you can visualize key metrics and logs directly inside Azure Portal - no Grafana servers to deploy, no configuration to manage, and no additional cost

In this post, we’ll walk through how these built-in Grafana dashboards help you troubleshoot faster, understand database behavior at a glance, and decide when you might still want Azure Managed Grafana for advanced scenarios.

Native Grafana Dashboards — No Setup, No Hosting, No Cost

We are thrilled to announce that Azure Database for PostgreSQL users can now access prebuilt Grafana dashboards directly within the Azure portal - with no additional cost or configuration required. This integration eliminates the complexity of deploying and administering self-hosted or managed Grafana instances. Grafana’s powerful visualization capabilities are now embedded directly in the Azure experience

From the moment you open the Azure Portal you have immediate access to dashboards for PostgreSQL. Simply navigate to Azure Database for PostgreSQL server in the Azure Portal and select “Dashboards with Grafana” and choose Featured dashboards. Within seconds, you have a rich, real-time view of your database server’s health - no custom queries or manual wiring required.

Figure 1: Azure Portal showing the “Dashboards with Grafana” blade , featuring the prebuilt monitoring dashboard tile.

Comprehensive PostgreSQL Metrics at a Glance

Figure 2: Azure PostgreSQL Grafana dashboard showing resource utilization, performance metrics, and server configuration.

As shown above, the new Grafana dashboard provides at-a-glance visibility into all the key metrics that matter for Azure Database for PostgreSQL. These dashboards are purpose-built to surface the health and performance of your database server, so you can immediately spot trends or issues.

Quick Configuration Snapshot

Figure 3: PostgreSQL server details, showing version, region, compute size, availability, and resource usage gauges.

Every monitoring session starts with instant answers to critical questions:

Is the server up?
Is High Availability configured?
How much storage is available?

The summary panel provides:

Instance status (Up/Down)
High Availability and replica configuration
Azure region, PostgreSQL version, and SKU
Live resource usage (CPU, memory, storage)

No extra clicks. No custom queries. Just clarity.

Metrics Coverage

The prebuilt dashboards visualize telemetry emitted by Azure Database for PostgreSQL, including:

Server availability and status
Active connections and connection failures
CPU and memory utilization
Storage usage and WAL consumption
Disk I/O (IOPS and throughput)
Network ingress and egress
Transaction rates, commits, and rollbacks

These metrics are collected via Azure Monitor platform metrics and refreshed at near-real-time intervals (depending on metric type). For a complete list, see the Azure Database for PostgreSQL monitoring documentation.

Metrics and Logs—Together

Ever struggled to trace a spike in CPU to the actual query behind it? With PostgreSQL logs and metrics visualized side-by-side, you can now correlate the exact timestamp of a CPU surge with detailed query logs in seconds.

Figure 4: CPU usage metrics co-relation with PostgreSQL log entries in Azure Monitor, highlighting slow query detection and log integration.

💡Note: To view logs in Grafana, make sure diagnostic settings are enabled to send PostgreSQL logs to Azure Monitor Logs. You can configure this in the Azure Portal under your PostgreSQL resource > Monitoring > Diagnostic settings. Learn how.

In the example above, high CPU usage (73.2%) aligns precisely with poor-running queries against a large salesorderdetail_big table. This helps engineers instantly validate and pinpoint slow queries without jumping between tools.

The unified Metric + Logs view, you can:

Plot query errors over time
Correlate failed logins with resource spikes
Investigate locking or memory pressure using timestamps

Grafana Explore mode is also available for deep-dive troubleshooting without altering dashboards.

First-Class Azure Integration

This is not just embedded Grafana - it is first-class Azure-native:

Dashboards are Azure resources, scoped to subscriptions and resource groups
Access is controlled using Azure RBAC
Dashboards can be exported and deployed via ARM templates
Easy sharing and migration across environments

You get the flexibility of open-source Grafana with Azure’s enterprise-grade governance.

Getting Started

To use the pre-built dashboard

Open the Azure portal
Navigate to Azure Database for PostgreSQL
Select Dashboards with Grafana
Open the PostgreSQL featured dashboard

To customize a dashboard:

Open the prebuilt PostgreSQL dashboard
Select Save As to create a copy
Modify panels or add new visualizations
Connect additional data sources (metrics or logs)
Save and share with your team

For advanced customization, refer to the Azure Monitor + Grafana Learn documentation.

When to Use Azure Managed Grafana?

Dashboards with Grafana in the Azure portal cover most common PostgreSQL monitoring scenarios. Azure Managed Grafana is still the right choice when you need:

Extended plugin support (community and OSS plugins)
Advanced authentication and provisioning APIs
Fine-grained, multi-tenant access control
Multi-cloud or hybrid data source connectivity

See the detailed comparison to choose the right option.

Learn More

Start visualizing your Azure PostgreSQL data instantly—right where you already work.

Renaming an Azure SQL DB encrypted with DB level-CMK can render it Inaccessible

Tancy — Wed, 25 Feb 2026 21:06:03 GMT

Issue

An issue was brought to our attention recently by a customer where we noticed that the cx DB was Inaccessible. On further investigation, it was concluded that the database was encrypted with DB level CMK & the DB was renamed, which had made it inaccessible. Here’s the error message you may get:

Azure Portal shows the Error:

“Access to Azure Key Vault has been lost for this database. Existing data will be inaccessible until this issue is resolved.”

Attempting Key revalidation in the portal produces:

"AADSTS1000901: The provided certificate cannot be used for requesting tokens. The value of token_not_after on the certificate should be greater than the current time. "

Mitigation

Please start by validating the following:

Key Vault key is active, enabled, RSA 2048, no expiration.

Managed identity exists and has correct RBAC role.

Firewall and private endpoints are fine with no changes.

As mentioned in our Public documentation, after renaming an Azure SQL database, the identities on the DB must be reassigned. To reassign the identity, set the managed identity to None after your resource name (Azure SQL DB Name) changes and then apply the same user assigned managed identity to it.

Additional Questions:

After the primary DB is Online, do I have to fix the Geo-DR or Any Replica DBs?

As mentioned in the Public Documentation- "Once the database is back online, previously configured server-level settings, including failover group configurations, tags, and database-level settings such as elastic pool configurations, read scale, auto pause, point-in-time restore history, long-term retention policy, and others are lost. Hence, it's recommended that customers implement a notification system to detect the loss of encryption key access within 30 minutes. After the 30-minute window has expired, we advise validating all server and database level settings on the recovered database." To reestablish primary-secondary link (After 30 Min duration), customers have to delete failover group, create geo-replica, create secondary db, recreate failover group and add the db to the failover group.

Deleting the Failover Group:

Creating the Geo-Replica:

Now Secondary DB is created successfully:

Now Recreate the Failover Group:

Add the DB to the Failover Group:

DB added to the Failover Group

2. Does the requirement to reassign the managed identity applies exclusively to Azure SQL Database and Azure SQL Managed Instance, or if this behavior also affects any other managed database services offered in Azure or any other service ? (i.e: postgres flexible server, MySQL etc.

This issue is specific to “Database Level CMK in Azure SQL” which is our new and recent offering. Since the identities are assigned to a database resource, if you change the resource the customer owning the identity needs to make sure that the new resource has the new identity. (Azure SQL Managed Instance does not support database level CMK)

This is NOT a problem/requirement for Server level CMK in Azure SQL. Other Azure managed database services (for example, Azure Database for PostgreSQL Flexible Server, MySQL, or MariaDB) use different encryption and keymanagement implementations and do not exhibit this same managedidentity reassignment behavior after rename operations.

3. Can an end-customer share notification of Loss of DB level CMK?

Not exactly, but please check the public documentation to monitor key status. You may also subscribe to various key vault alerts, which will notify them about key expiry or any changes in key permissions.

References:

Transparent data encryption (TDE) with database level customer-managed keys - Azure SQL Database | Microsoft Learn

Managed Identity in Microsoft Entra for Azure SQL - Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn

Configure Azure Key Vault alerts | Microsoft Learn

Web activity failure due to Invoking endpoint failed with HttpStatusCode - 403 -- help?

AzureNewbie1 — Wed, 25 Feb 2026 17:21:42 GMT

Hi,
I have an Azure Data Factory (ADF) instance that I am using to create a Pipeline to ingest external (cloud based) 3rd party data into my Azure SQL Server database. I am a novice with ADF and have only used it to ingest some external SQL data into my SQL database - it did work.
The external source I'm attempting to extract from uses an OAuth 2.0 API and an API is something I've not used before.

Using Postman (never used this software before this attempt), I have passed the external source's base_url, client_id, and client_secret, and in return successfully received an access token. This tells me that the base_url, client_id, and client_secret values I passed are correct and accepted by the target source/application.

Feeling encourage to implement the same values into ADF, I first created a Linked Service which with a successful test connection returned - see below. This Linked Service uses the same values as the Postman entry which granted an access token.

I then created a Pipeline with a Web activity object within it. The General and User Properties don't have any configuration, only the Settings tab does which shown below. Again, the URL, Client ID and Client Secret configured here are the same as those used in Postman (and the Linked Service).

I execute the Web object and it returns with a failure - see below.

The error states the endpoint refused the request (for an access token). Is this accurate as I was able to receive an access token via Postman when using the same credentials? I don't understand why via Postman I can received an access token but via ADF it errors. I'm wondering if I've completed the ADF parts incorrectly, or if there is more needed just to received an access token, or if it's something else?
Are you able to advise what's taking place here?
Thanks.

Nasdaq builds thoughtfully designed AI for board governance with PostgreSQL on Azure

charlesfeddersenMS — Wed, 25 Feb 2026 05:34:02 GMT

Authored by: Charles Federssen, Partner Director of Product Management for PostgreSQL at Microsoft and Mohsin Shafqat, Senior Manager, Software Engineering at Nasdaq

When people think of Nasdaq, they usually think of markets, trading floors, and financial data moving at extraordinary speed. But behind the scenes, Nasdaq also plays an equally critical role in how boards of directors govern, deliberate, and make decisions.

Nasdaq Boardvantage® is the company’s governance platform, used by more than 4,400 organizations worldwide—including nearly half of the Fortune 100. It’s where directors review board books, collaborate in an environment designed with robust security, and prepare for meetings that often involve some of the most sensitive information a company has.

In recent years, Nasdaq set out to modernize Nasdaq Boardvantage with AI, without compromising security and reliability. That journey was featured in a Microsoft Ignite session, “Nasdaq Boardvantage: AI-Driven Governance on PostgreSQL and Foundry.” It offers a practical look at how Azure Database for PostgreSQL can support AI-driven applications where precision, isolation, and data control are non-negotiable.

Introducing AI where trust is everything

Board governance isn’t a typical productivity workload. Board packets can run 400 to 600 pages, meeting minutes are legal records, and any AI-generated insight must be confined to a customer’s own data.

“Our customers trust us with some of their most strategic, sensitive data,” said Mohsin Shafqat, Senior Manager of Software Development at Nasdaq. That trust meant tackling several core challenges upfront, including:

How do you minimize AI hallucinations in a governance context?
How do you guarantee tenant isolation at scale?
How do you keep data regional across a global customer base?

A cloud foundation built for governance

Before adding intelligence, Nasdaq decided to re-architect Nasdaq Boardvantage on Microsoft Azure, using Azure Kubernetes Service (AKS) to run containerized, multi-tenant workloads with strong isolation boundaries. Microsoft Foundry provides the managed foundation for deploying, governing, and operating AI models across this architecture, adding consistency, security, and control as intelligence is introduced.

At the data layer, Azure Database for PostgreSQL and Azure Database for MySQL became the backbone for governance data. PostgreSQL, in particular, plays a central role in managing structured governance information alongside vector embeddings that support AI-driven features. Together, these services give Nasdaq the performance, security, and operational control required for a highly regulated, multi-tenant environment, while still moving quickly.

Key architectural choices included:

Tenant isolation by design, with separate databases and storage
Regional deployments to align with data residency requirements
High availability and managed operations, so teams could focus on product innovation instead of infrastructure maintenance

PostgreSQL and pgvector: Powering context-aware AI

With that foundation in place, Nasdaq was ready to carefully introduce AI. One of the first AI capabilities was intelligent document summarization. Board materials that once took hours to review could now be condensed into concise, contextually accurate summaries.

Under the hood, this required more than just calling an LLM. Nasdaq uses pgvector, natively supported in Azure Database for PostgreSQL, to store and query embeddings generated from board documents. This allows the platform to perform hybrid searches that combine traditional SQL queries with vector similarity to retrieve the most relevant context before sending anything to a language model.

Instead of treating AI as a black box, the team built a pipeline where:

Documents are processed with Azure Document Intelligence to preserve structure and meaning
Content is chunked and embedded
Embeddings are stored in PostgreSQL with pgvector
Vector similarity searches retrieve precise context for each AI task

Because this runs inside PostgreSQL, the same database benefits from Azure’s built-in high availability, security controls, and operational tooling – delivering tangible results, including a 25% reduction in overall board preparation time and internal testing shows 91–97% accuracy for AI-generated summaries and meeting minutes.

From summaries to an AI Board Assistant

With summarization working in production, Nasdaq expanded further. The team is now building an AI-powered Board Assistant that will help directors prepare for upcoming meetings by surfacing trends, risks, and insights from prior discussions.

This introduces a new level of scale. Years of board data across thousands of customers translate into millions of embeddings. PostgreSQL continues to anchor this architecture, storing vectors for semantic retrieval while MySQL supports complementary non-vector workloads. Across Nasdaq Boardvantage, users are advised to always review AI outputs, and no customer data is shared or used to train external models. “We designed AI for governance, not the other way around,” Shafqat said.

More importantly, customers trust the system because security, isolation, and data control were engineered in from day one.

Looking ahead

Nasdaq’s work shows how Azure Database for PostgreSQL can support AI workloads that demand both intelligence and integrity. With PostgreSQL at the core, Nasdaq has built a governance platform that scales globally, respects regulatory boundaries, and introduces AI in a way that feels dependable and not experimental.

What started as a modernization of Nasdaq Boardvantage is now influencing how Nasdaq approaches AI across the enterprise.

To dive deeper into the architecture and hear directly from the engineers behind it, watch the Ignite session and check out these resources:

Watch the Ignite breakout session for a technical walkthrough of how Nasdaq Boardvantage is built, including PostgreSQL on Azure, pgvector, and Microsoft Foundry in production.
Read the case study to see how Nasdaq introduced AI into board governance and what changed for directors, administrators, and decision-making.
Watch the Ignite broadcast for a candid discussion on Azure Database for PostgreSQL, Azure HorizonDB, and what it takes to scale AI-driven governance.

rss.livelink.threads-in-node

Recovering Missing Rows (“Gaps”) in Azure SQL Data Sync — Supported Approaches (and What to Avoid)

The scenario: Data Sync continues, but some rows are missing

Temptation: “Can we push missing data by editing tracking tables or calling internal triggers?”

Why this is not recommended / not supported as a customer-facing solution

Supported recovery option #1 (recommended): Re-drive change detection via the base table

A practical pattern: “No-op update” to re-fire tracking

Supported recovery option #2: Deprovision and re-provision the affected table (safe “reset” path)

Diagnostics: Use the Azure SQL Data Sync Health Checker

A likely contributor to “gaps”: schema changes during Data Sync (snapshot isolation conflict)

Prevention guidance (practical)

Key takeaways

February 2026 Recap: Azure Database for PostgreSQL

Features

Terraform support for Elastic Clusters

Dashboards with Grafana — Now Built-In

Easier way to choose VM SKUs on portal

What’s New in the PostgreSQL VS Code Extension

New Features & Enhancements

Priority Connectivity to azure_pg_admin Users

Guide on 'gin_pending_list_limit' indexes

Learning Bytes

Conclusion

February 2026 Recap: Azure Database for MySQL

Extended Support Timeline Update

Private Preview - Fabric Mirroring for Azure Database for MySQL

Stay Connected

Counting distinct values

Using Different Azure Key Vault Keys for Azure SQL Servers in a Failover Group

Key Takeaways

Understanding Hash Join Memory Usage and OOM Risks in PostgreSQL

Background: Why Memory Usage May Exceed work_mem

Background: What work_mem really means for Hash Joins

A safe, reproducible demo (containerized community PostgreSQL)

Start PostgreSQL 16 container

Connect and apply conservative session-level settings

Create tables and load data

Run Hash Join (high cardinality)

Force low cardinality / skew and re‑run

Scale up rows to observe linear growth

Observability: what you will (and won’t) see

EXPLAIN is your friend

Query Store / pg_stat_statements limitations

Typical OOM symptom in logs

What to do about it (mitigations & best practices)

Validating on Azure Database for PostgreSQL – Flexible Server

Changing some default parameters

Creating and populating tables

Query & Execution plan

Skew it to LOW cardinality

FAQ

Further reading

Ready‑to‑use mitigation checklist (DBA quick wins)

Guide to Upgrade Azure Database for MySQL from 8.0 to 8.4

1. Background

2. Key Changes

Authentication Plugin Changes

Foreign Key Constraint Enforcement

Removed Features

New Reserved Keywords

Terminology Changes

InnoDB 8.0 → 8.4 Default Parameter Changes

3. Upgrade Prerequisites

Basic Requirements

Read Replica Version

Create On-Demand Backup

XA Transactions

SQL Mode

4. Pre-Upgrade Checks

Use Upgrade Check Tool (MySQL Shell)

Check Items

Review Results

Additional Compatibility and Validation Checks

Test in Staging

5. Upgrade Methods & Scenarios

Upgrade Method Comparison

Method 1: In-Place Upgrade, Azure Portal (In-Place Upgrade, Recommended)

Method 2: Azure CLI (In-Place Upgrade)

Method 3: Minimal Downtime (Read Replica)

6. Post-Upgrade Validation

Why Developers and DBAs love SQL’s Dynamic Data Masking (Series-Part 1)