rss.livelink.threads-in-node

ADF - Support for Federated Identity Credentials

rcaidben — Wed, 15 Jul 2026 18:01:00 GMT

Our team is working on migrating existing pipelines to Azure Data Factory (ADF). As part of this effort, we are evaluating ADF for executing Kusto commands and would like to use an existing Microsoft Entra application registration authenticated through Federated Identity Credentials (OIDC workload identity federation).

Today, ADF appears to primarily support Managed Identity-based authentication patterns. Is there any planned support for using a customer-owned App Registration with Federated Identity Credentials (FIC)? If so, is there any public roadmap or timeline that can be shared?

We are actively moving away from secret- and certificate-based authentication. Supporting FIC would allow us to continue using our existing application identities, which already have the required permissions on source and destination datasets, and would avoid the need to introduce additional User Assigned Managed Identities and request new permission grants from dataset owners as part of the migration.

Generally Available: SQL Migration to SQL Server on Azure Virtual Machines in Azure Architecture

danimir — Tue, 14 Jul 2026 15:02:54 GMT

One Migration Experience, More Flexibility

Modernizing SQL Server estates is rarely a single-step journey. Organizations often operate across on-premises, hybrid, and cloud environments while balancing application dependencies, operational requirements, and modernization goals. SQL Server migration enabled by Azure Arc simplifies this process by bringing migration activities into a single experience in the Azure portal.

With the July 2026 release, we are announcing General Availability of SQL Server on Azure Virtual Machines as a migration target in Azure Arc, allowing customers to have a greater flexibility to choose Azure destination that best aligns with to your needs without introducing additional tools or migration processes.

Whether migrating to the fully managed Azure SQL Managed Instance service or to SQL Server running on Azure VMs, the experience remains consistent and familiar.

Unified Migration Workflow

A key benefit of SQL Server migration enabled by Azure Arc is that the entire migration lifecycle is managed from a single tool in Azure portal.

After a SQL Server instance is enabled by Azure Arc, customers can:

Assess migration readiness
Select a migration target
Configure migration settings
Monitor migration progress
Validate results
Perform final cutover

All of these activities are performed directly from the Azure portal using a guided workflow designed to simplify migration planning and execution.

The prerequisite remains unchanged: source SQL Server instances must be enabled by Azure Arc before migration can begin. The result is a flexible, scalable, and consistent migration experience that supports hybrid realities, reduces operational overhead, and helps customers modernize SQL Server estates at their own pace.

Consistent Experience Across Azure SQL Targets

Migration to SQL Server on Azure Virtual Machines follows the same operational model already available for Azure SQL Managed Instance migration scenarios.

Customers use the same migration dashboard, monitoring experience, and guided workflow regardless of the destination. This consistency reduces learning curves, simplifies operational processes, and enables teams to select the most appropriate Azure SQL platform without changing migration methodology.

Online Migration Using Backup and Log Shipping

Migration to SQL Server on Azure Virtual Machines uses backup and restore with log shipping to support online migration scenarios while minimizing downtime.

The process begins with a full database backup that is restored to the target SQL Server instance running on an Azure Virtual Machine. Transaction log backups that are uploaded continuously by your workflows to Azure Blob Storage are continuously applied to the target database, keeping it closely synchronized with the source environment.

Azure Blob Storage serves as the intermediary staging location between source and target systems. To support efficient data movement and restore operations, both the Azure Blob Storage account and the target SQL Server on Azure Virtual Machines must reside in the same Azure region.

Within the Azure Arc migration experience, customers select the Azure Blob Storage container that stores the backup files. Azure Arc automatically restores the full backup and continuously applies transaction log backups as they become available. Customers are responsible for configuring and maintaining the continuous upload of transaction log backups to Azure Blob Storage.

Customer-Controlled Cutover

When the customer is ready to complete migration, upon customer initiated cutover, Azure Arc performs the final synchronization by applying the last uploaded backup and bringing the target database online.

This approach gives organizations full control over the migration timeline and cutover window, allowing downtime to be planned according to business requirements while reducing operational complexity.

Learn More

To learn more about SQL Server migration enabled by Azure Arc, see: Migration Overview

For information about migrating to SQL Server in Azure VM, see: Migrate to SQL Server in Azure VM

Generally Available: SQL Migration to SQL Server on Azure Virtual Machines in Azure Architecture

danimir — Tue, 14 Jul 2026 15:01:52 GMT

One Migration Experience, More Flexibility

Whether migrating to the fully managed Azure SQL Managed Instance service or to SQL Server running on Azure VMs, the experience remains consistent and familiar.

Unified Migration Workflow

A key benefit of SQL Server migration enabled by Azure Arc is that the entire migration lifecycle is managed from a single tool in Azure portal.

After a SQL Server instance is enabled by Azure Arc, customers can:

Assess migration readiness
Select a migration target
Configure migration settings
Monitor migration progress
Validate results
Perform final cutover

All of these activities are performed directly from the Azure portal using a guided workflow designed to simplify migration planning and execution.

Consistent Experience Across Azure SQL Targets

Migration to SQL Server on Azure Virtual Machines follows the same operational model already available for Azure SQL Managed Instance migration scenarios.

Online Migration Using Backup and Log Shipping

Migration to SQL Server on Azure Virtual Machines uses backup and restore with log shipping to support online migration scenarios while minimizing downtime.

Customer-Controlled Cutover

Learn More

To learn more about SQL Server migration enabled by Azure Arc, see: Migration Overview

For information about migrating to SQL Server in Azure VM, see: Migrate to SQL Server in Azure VM

PostgreSQL on Azure: Two services, one future-proofed ecosystem

charlesfeddersenMS — Tue, 14 Jul 2026 15:00:00 GMT

At Microsoft Build 2026, the Azure Databases team announced the public preview of Azure HorizonDB, a new powerhouse for PostgreSQL in the cloud. It’s a fully managed, PostgreSQL-compatible cloud database service that delivers sub-millisecond latency, rapid read scale-out, and seamless integration with Microsoft Foundry to empower teams to build secure, compliant and high-performing applications with confidence. At the same event, we also announced several enhancements to the existing managed PostgreSQL offering, Azure Database for PostgreSQL flexible server, boosting performance, analytics and security, and expanding tooling for migration scenarios.

Where there was one, now there’s two

Now our customers have two strong options to choose from. Azure Database for PostgreSQL remains a reliable, cost-effective, fully open-source compatible workhorse for most users’ everyday needs. Azure HorizonDB is the new PostgreSQL-compatible service with an elastic scale-out architecture built on a highly optimized shared storage system that unlocks 3x faster OLTP performance and other cloud-native advantages for the most demanding workloads.

With this new service, you might be wondering which service is the best fit. Let’s take a closer look at these options and explore where they align and differ and what you might want to consider when making your choice.

Azure Database for PostgreSQL: Enterprise ready, managed open source

Microsoft is one of the largest contributors to the open-source Postgres project and has also invested heavily in PostgreSQL managed services on Azure. Microsoft engineers have authored or co-authored hundreds of code commits and provided extensive reviews, and, to date, have made more than 345 commits and changed more than 64K lines of code for PostgreSQL 19. In the cloud, Azure Database for PostgreSQL is built on the open-source ecosystem, sharing the same extensions and experience that developers and DBAs know and love. Patching, backups, scaling, and monitoring are all simple, one-click operations. If you have an app that already uses a PostgreSQL database, in another cloud or on-premises, migrating to Azure for the added benefits is an easy lift and shift.

Inside Azure Database for PostgreSQL

Azure Database for PostgreSQL is a mature, feature-rich service already battle-tested by thousands of applications and being used by Fortune 500s across sectors.

Enterprise-grade performance: Compute tiers can scale up to 192 vCores with features like read replicas and elastic clusters, which is based on the open-source Citus extension and unique to this class of service, make it easy to right-size workloads and optimize performance by offloading read-heavy traffic or sharding data across nodes.

Reliability and security: Backed by Azure’s robust infrastructure, Azure Database for PostgreSQL comes equipped with high availability and zone-redundant options, point-in time restore and security features, including data encryption, network isolation, and Entra ID for enterprise identity.

Frictionless migrations: Migrating existing PostgreSQL workloads to Azure Database for PostgreSQL is very straightforward thanks to built-in migration tooling. We’ve even launched AI-assisted tooling for Oracle to PostgreSQL migrations in VS Code, which leverages GitHub Copilot AI to handle app and schema conversions and pre-migration validations.

From incorporating cutting-edge hardware, horizontal scaling and Microsoft Fabric and Microsoft Foundry integrations, to supporting 90+ open-source extensions and counting, we continue to optimize the service to meet the needs of our customers building on open-source Postgres.

Azure HorizonDB: Next-gen engine to build what’s next

Azure HorizonDB was designed and purpose-built to meet the needs of modern AI-native applications and large-scale enterprise migrations. Shireesh Thota, Azure Databases CVP, describes it as the database of choice for workloads that need “a lot of storage, want really fast latencies and significantly higher IOPS.” The service offers faster throughput than open-source PostgreSQL and rapid compute scale-out to support the performance and availability needs of your most demanding applications.

Inside Azure HorizonDB

Azure HorizonDB is where performance meets possibility, empowering teams to build intelligent apps to scale, modernize, and innovate without compromise.

Cloud-tuned performance: The cloud-native architecture of Azure HorizonDB fully decouples compute and storage, enabling users to scale database resources independently. The service can support deployments up to 3,072 vCores and 128 TB of shared storage for a single workload, and provides a single endpoint for read replicas with transparent load balancing to deliver massive read throughput seamlessly to the application.

Reliability and security: Azure HorizonDB comes standard with features to support production-level, mission-critical enterprise workloads. Built-in multi-availability zone (AZ) replication reduces failover time to less than 5 seconds, and native integration with Microsoft Entra ID and private endpoint networking ensures Azure HorizonDB meets the Azure-standard enterprise-grade security from day one.

Tailored for AI and next-gen apps: Azure HorizonDB provides an extensive set of AI features for building modern applications. In comparison to similar PostgreSQL services in the cloud, IDC described Azure HorizonDB as having "fewer moving parts and a straighter path to AI features.” Azure HorizonDB ships with Microsoft’s latest version of DiskANN vector indexing, which includes advanced filtering that delivers up to 3x faster vector search than traditional pgvector indexes. It also comes with built-in AI Model Management for native integration to Microsoft Foundry models, AI Functions to invoke models from SQL, and AI Pipelines to provide durable orchestration of data modification.

Developer productivity: Along with building the best Postgres service, Microsoft is committed to delivering the best Postgres developer tools to the entire community. The Microsoft PostgreSQL extension for Visual Studio (VS) Code makes the coding environment Postgres-aware to help optimize queries, schemas and query performance using AI.

Azure HorizonDB is the next-generation of PostgreSQL on Azure for mission-critical, high-throughput, and data-intensive workloads. For everything else, Azure Database for PostgreSQL remains a strong choice.

Making your selection

Adopting technology should always be driven by a real need. Having two choices is great, but it raises the logical question: “which one is right for me and when?”

Choose Azure Database for PostgreSQL when:

You’re migrating existing Postgres databases as-is. You can migrate seamlessly to Azure Database for PostgreSQL with minimal tweaks or reconfigurations.

You require full open-source compatibility, including rapid adoption of new community versions. Azure Database for PostgreSQL now ships major versions on the same day as the community release.

You want to start now and decide later. Azure Database for PostgreSQL is generally available in 60+ regions today. Later, if your project requires greater scale, you can upgrade to Azure HorizonDB, and the migration process will be quick and easy.

Choose Azure HorizonDB when:

You’re migrating tier-1 workloads to the cloud that already have critical scale, performance, and availability requirements. Up to 128 TB of storage and 3,072 vCores for a single workload makes Azure HorizonDB the ideal destination for these workloads.

You anticipate requirements that go beyond Azure Database for PostgreSQL’s capabilities. Azure HorizonDB expands on the capabilities of Azure Database for PostgreSQL, so you’ll be future proofed for scale and reliability.

You are focused on building next-gen intelligent apps. Azure HorizonDB is optimized for building new AI applications, enabling developers to ship faster with fewer moving parts.

Both services are built on the core Postgres engine, and upgrading to Azure HorizonDB is easy. If your scenario changes, your toolkit can change too. Azure offers the managed service to support you either way.

Choose the cloud with the deepest Postgres expertise

The PostgreSQL ecosystem on Azure is richer than ever. With both Azure Database for PostgreSQL and Azure HorizonDB, Azure covers the spectrum from steady, everyday workloads to cutting-edge, innovative ones. Whether you’re in a two-person startup or a Fortune 500 enterprise, PostgreSQL on Azure can meet your business’ needs.

Now is the perfect time to make a move to Postgres on Azure:

Learn more about Azure Database for PostgreSQL

Learn more about Azure HorizonDB

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

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

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

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

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

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

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

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

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

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

Insert 20 rows using this command:

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

Example of returns:

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

Lessons Learned #543: Evaluating MultiSubnetFailover with Azure SQL Database

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

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

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

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

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

What MultiSubnetFailover is designed for

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

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

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

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

Why Azure SQL Database is different

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

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

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

What about Failover Groups?

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

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

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

The importance of retry logic

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

An appropriate retry strategy should normally include:

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

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

Number of concurrent transactions supported for an Azure SQL 4 vCores

SudeepSahdeva — Tue, 07 Jul 2026 02:51:17 GMT

I have an Azure SQL:

Parameters	Specifications
Deployment Model	General Purpose – Serverless
SKU / Performance Tier	Standard Series
Database Size	128 GB
Max vCores	4 vCores (scalable within Serverless range)

How many concurrent transactions are supported for an Azure SQL db with the above specifications.

Thanks

From RAG to agents: Build AI pipelines inside Azure HorizonDB

abeomor-msft — Thu, 02 Jul 2026 16:52:43 GMT

By Abe Omorogbe, Navya Teja Gajula, Binnur Gorer, B Harsha Kashyap, Krishnakumar Ravi (KK) from Microsoft PostgreSQL AI team

If you’ve ever shipped a RAG app, this will feel familiar. Your data lives in Postgres. But the pipeline that turns that data into vectors lives somewhere else, spread across external services, queues, and retry logic. And when the embedding API hiccups mid-batch? That’s a 2 a.m. production incident. You didn’t set out to build your own embedding service. You just wanted to search your documents.
And RAG is only the beginning. The moment AI works on your data: extraction, summarization, reranking, keeping embeddings fresh, or powering agent, you’re back to stitching together more services, queues, and glue code, all outside the database.

AI pipelines in Azure HorizonDB (Preview) removes that entire stack. Define your workflows steps like chunking, embeding, extracting, and generating in SQL, and HorizonDB runs them as AI pipelines next to your data. No orchestrator. No glue code. Just Postgres.

In this post we'll cover:

The external-orchestrator issue that every AI on Postgres team eventually hits
What AI pipelines are, and the four-part anatomy that makes them click
Use cases worth trying: semantic search, knowledge extraction, content generation, smarter reranking, and always-fresh embeddings
How to watch your pipelines run as live graphs in VS Code
How to spin up HorizonDB and run your first pipeline today

🚀 Try it on Azure HorizonDB. AI pipelines are built into Microsoft's new PostgreSQL cloud service, no extra infrastructure to stand up. Write ai.create_pipeline(...), call ai.run(...), and it runs. Get started in HorizonDB →

AI preprocessing runs outside the database, far from your data

The standard way to get data into a vector store looks reasonable on a whiteboard: a service reads source rows, calls an embedding API, and writes chunks back to Postgres. However, some interesting issues often occur in production.

The embedding API fails mid-batch, and there's no shared checkpoint showing which rows were completed. You rerun the job, and the extra API calls increases cost.
A worker crashes after writing chunks but before flipping the parent row's processed flag. Now your embeddings are quietly inconsistent, and nobody knows.

Every one of these is the same missing primitive: durable, checkpointed execution that lives where your data lives. External orchestrators can do it, but now you're operating a second service just to feed the first one.

AI pipelines move that logic into HorizonDB itself. The source, the steps, the sink, and the full run history are all SQL protected by the same transactions, backups, and point-in-time restore your data already has. The database is already where your data commits. It's a natural place for the pipeline to live too.

Anatomy of an AI pipeline in HorizonDB

AI Pipeline in HorizonDB, the steps are optional and can be adjusted as needed.

A pipeline has four parts:

Source: where rows come from. A table_source(...) over a HorizonDB table, optionally with an incremental_column so the pipeline skips rows it already processed.
Steps: the AI operations that transform each row, in order. Each step appends columns to the in-flight batch.
Sink: where results land, ready for use by your AI apps or agent.
Trigger: 'on_change' (run automatically when source rows change) or 'manual' (run only when you call ai.run()).

Those four parts give the pipeline its shape. The steps are where you define the AI work itself, using composable building blocks:

Step	What it does
ai.chunk()	Split long text into overlapping chunks
ai.embed()	Generate vector embeddings
ai.extract()	Pull structured fields out of text with an LLM
ai.generate()	Generate text from a prompt (i.e content generation, classify, summarize and more)
ai.rank()	Score documents against a query

How the pieces fit together. The ai.* API gives you the AI pipeline shape: sources define where data comes from, steps define the AI work to perform, sinks define where results land, and triggers define when the pipeline runs. Under the covers, HorizonDB turns that definition into a durable execution graph, where each step can be checkpointed, retried, and resumed if something fails.

Built on open source. That durability isn't magic, every AI pipeline compiles down to a graph that runs on pg_durable, Microsoft's open-source durable-execution engine for PostgreSQL (built on the duroxide Rust runtime). The ai.* API is the AI-shaped surface (sources, steps, sinks, triggers) and pg_durable is the general-purpose engine underneath that handles checkpointing, retries, and crash recovery. So, your pipelines stand on a transparent, inspectable foundation you can read, and run on any Postgres 17 & 18. No black box, no lock-in.

Use case 1: Semantic search over your data

This is one of the most popular use cases. Turn a table of documents into searchable vectors, durably, and keep them fresh as the data changes. That last part matters: in production, documents are edited, added, and deleted constantly, and every change needs the right chunks and embeddings updated without reprocessing the entire corpus or leaving stale vectors behind. With AI pipelines, HorizonDB can track those incremental updates for you. Chunk the body, embed each chunk, and land the result in a DiskANN-indexed table.

-- Define the pipeline: source -> chunk -> embed -> sink. SELECT ai.create_pipeline( name => 'rag_pipeline', source => ai.table_source(table_name => 'documents'), steps => ARRAY[ ai.chunk(input => 'content', chunk_size => 512, overlap => 64), ai.embed(model => 'default-embedding', input => 'chunk_text', dimensions => 1536) ], trigger => 'on_change', -- re-embed automatically as rows change sink => ai.table_sink('rag_pipeline_output') ); -- Run it SELECT ai.run('rag_pipeline'); -- Search your data SELECT chunk_text, embedding <=> azure_openai.create_embeddings('text-embedding-3-small', 'how does vector search work?')::vector AS distance FROM rag_pipeline_output ORDER BY distance LIMIT 3;

That's the entire ingestion layer; chunking, embedding, checkpointing, retries, and sink writes in one definition. Because trigger => 'on_change', the pipeline updates embeddings whenever source rows change, processing only what is new or modified instead of redoing the whole corpus. Your vectors stay in sync with your data, and your ingestion work stays efficient as the dataset grows. Point a query at the DiskANN index and you've got production semantic search without a single line of application glue.
That's the whole loop: define, run, inspect. The embedding service you were about to build the queue, the workers, the retry logic, the checkpoint table, the 2 a.m. production incident doesn't happens.

Why it's better than an external service: a failure in ai.embed() never re-runs ai.chunk(), each step is a durable node. If the database restarts mid-run, it resumes from the last checkpointed batch, not row zero.

Use case 2: Turn unstructured text into structured metadata

Support tickets, contracts, product reviews, research papers are full of structure that's locked inside unstructured documents. ai.extract() pulls named fields out of text and merges them into the metadata JSONB column, so you can filter and aggregate on things an LLM read for you.

SELECT ai.create_pipeline( name => 'extraction_pipeline', source => ai.table_source(table_name => 'documents'), steps => ARRAY[ ai.chunk(input => 'content'), ai.extract( input => 'chunk_text', data => ARRAY['topics: string - the main topics discussed', 'entities: string - named people, products, or places'] model => 'my-gpt' -- optional, the default model when AI model management is activate ) ], sink => ai.table_sink('extraction_pipeline_output') ); SELECT ai.run('extraction_pipeline'); -- Now query the structured fields the LLM extracted: SELECT doc_id, metadata->'topics' AS topics, metadata->'entities' AS entities FROM extraction_pipeline_output;

You describe each field as a label: description string in the ai.extract step, and HorizonDB does the rest durably, in bulk, with the same retry-and-resume guarantees.

Each field is a label, either a bare name like product, or the detailed form name: type - description (for example `sentiment: number - sentiment score from 1 to 5`). HorizonDB does the rest, durably, in bulk, with the same retry-and-resume guarantees.

Use case 3: Summarize and rewrite content at scale

ai.generate() runs an LLM prompt against every row, perfect for bulk summarization, classification, tone normalization, or generating titles. Because it's a pipeline, "summarize 4 million documents" becomes a job that survives restarts instead of a script you have to monitor overnight.

SELECT ai.create_pipeline( name => 'summary_pipeline', source => ai.table_source(table_name => 'documents'), steps => ARRAY[ ai.chunk(input => 'content'), ai.generate( input => 'chunk_text', system_prompt => 'Create a concise summary in 50 words or fewer.' model => 'my-gpt' -- optional, the default model when AI model management is activate ) ], sink => ai.table_sink('generation_pipeline_output') ); SELECT ai.run('summary_pipeline'); -- Now query the generated text: SELECT doc_id, left(generated_text, 100) AS summary_preview FROM generation_pipeline_output WHERE generated_text IS NOT NULL LIMIT 5;

Swap the system_prompt and the same shape becomes a classifier ("Label this ticket as billing, bug, or feature request"), a translator, or a headline generator. The instruction goes in system_prompt; the result lands in generated_text.

Use case 4: Keep embeddings fresh, and re-embed cleanly when the model changes

This is where AI pipelines become especially useful. In a real AI app, two things change constantly: your data and your model. AI pipelines are designed to handle both changes directly.

Your data changes. Set incremental_column and an on_change trigger, and the pipeline only embeds new or changed rows, automatically, forever, until you pause or drop it.

SELECT ai.create_pipeline( name => 'rag_pipeline', source => ai.table_source( table_name => 'documents', incremental_column => 'updated_at' -- only process what changed ), steps => ARRAY[ ai.chunk(input => 'content'), ai.embed(model => 'default-embedding', input => 'chunk_text', dimensions => 1536) ], trigger => 'on_change', sink => ai.table_sink('rag_pipeline_output') );

Your model changes. Bump the model or the dimensions, then run a single, resumable backfill, no migration script, no babysitting:

TRUNCATE rag_pipeline_output; SELECT ai.backfill('rag_pipeline');

The backfill runs as one durable instance. If the database restarts mid-backfill, it picks up from the last checkpointed batch instead of starting over. The painful "re-embed everything" migration becomes a one-liner you can actually trust.

Watch your pipelines run as live graphs in VS Code

A pipeline you can see is a pipeline you can trust. Install the PostgreSQL extension for VS Code, connect to HorizonDB, then right-click your database and open Pipelines & Workflows → AI Pipelines.

AI Pipelines in HorizonDB

Select any run and the center pane renders the execution as a color-coded graph:

Blue 🔵 : source and sink (where data enters and exits)
Green 🟢 : processing steps (chunk, embed, extract, generate, rank)
Pink 🟣 : external model and service calls

For each run you can read the status (completed, running, failed), the run ID for traceability, start time and duration for performance, and a link back to the pipeline definition. When a run fails, open the graph and jump straight to the step where execution stopped, no log spelunking.

Get Started: Try It Now

We have a few demoes of AI pipelines in action:

Resource	Link
Microsoft Build AI Pipeline Demo	Simplify app dev with cloud-native PostgreSQL in Azure HorizonDB \| DEM364
Microsoft Build AI Pipeline GitHub	AI Pipelines Demo GitHub Repo \| DEM364
Microsoft Mechanic Demo	AI Pipeline Demo on Microsoft Mechanic
Documentation	AI pipelines on HorizonDB

Enabling AI pipelines takes minutes: enable to azure_ai, pg_durable, vector and pg_diskann extensions and you can get started.

-- On Azure HorizonDB — the extensions are built in. CREATE EXTENSION IF NOT EXISTS pg_durable; CREATE EXTENSION IF NOT EXISTS azure_ai; CREATE EXTENSION IF NOT EXISTS vector; CREATE EXTENSION IF NOT EXISTS pg_diskann;

That's it, your PostgreSQL database can now run AI pipelines

Learn more

MS Learn AI pipelines on HorizonDB:

Azure HorizonDB Preview

pg_durable on GitHub (open source)

MS Learn Durable Functions on HorizonDB

Scalable vector search with DiskANN

PostgreSQL extension for VS Code

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

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

Why the % character is different

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

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

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

Observed symptom

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

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

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

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

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

Test setup

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

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

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

Reproduction results

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

Commands used for testing

1. Space in the file name

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

2. Literal % in the file name

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

Observed error:

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

3. URL-encoded % character

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

4. Literal %25 in the blob name

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

Interpretation

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

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

Practical guidance

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

Recommended handling for % characters in blob names

Main recommendation

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

When the blob name contains %

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

When the blob name contains literal %25

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

Application or stored procedure checks

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

Optional PowerShell test using Invoke-Sqlcmd

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

Connect-AzAccount

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

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

Conclusion

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

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

3 Reasons Enterprise SQL Server Migrations Slow Down - and How to Avoid Them

dhanMMS — Tue, 30 Jun 2026 23:45:00 GMT

Summary

Many of Enterprises around the globe have relied on SQL Server for over 3 decades to run their mission critical business applications. Their SQL Server estates face pressure from downtime risk, cost volatility, end of support timelines and modernization demands. As these customers get ready to modernize their data to use the latest capabilities of A.I and cloud native application trends, they want to migrate and modernize their SQL Servers to use Azure SQL with a modernization strategy built on confidence of customer success. Enterprise migrations rarely fail because of migration tools.

They slow down because organizations struggle to answer three questions:

How much downtime can we tolerate?
What will it cost after migration?
Are we choosing the right target platform?

The organizations that answer these questions early move faster and with less risk. For the DB Administrators, Data architects, application architect and cloud-cost decision makers there are important technical considerations before, during and after data modernization to avoid long term costs and operational concerns. The Microsoft SQL Team has helped many customers modernize their SQL. We discuss important guidelines that can help resolve the 3 major concerns that block or slow SQL Server migration and modernization in Enterprises. This is covered in the episode of DataExposed for which this companion blog goes into the details.

What are important triggers that cause customers and partners to consider SQL modernization?

There are many business triggers that force Enterprises to migrate their data to public cloud. As SQL Server 2012 to SQL Server 2016 are already in the end of support stage of their lifecycle, customers need to upgrade SQL Server in place or migrate to AzureSQL. Due to cyber security threats, customers are feeling more vulnerable to attackers. Moving their data into a secure environment is essential for protecting not just their data but their business. Customers are reporting the need to free up IT dollars to invest into other parts of the business that may need it more. These may be anything from datacenter contract expirations, need for Hardware refreshes to software license renewals. As the business grows or becomes cyclical, there is surge in demand. Capacity constraints become a barrier for such expansions. These are triggers that cause them to rethink their data modernization strategy. Data modernization and moving the data to a elastic, scalable, secure and resilient data platform such as Azure SQL, becomes essential.

The Three Migration Blockers

However, data modernization and migration is not without any risk. Based on our customers experience, here are three key reasons that we have commonly encountered that halt or slow down SQL modernization.

1. Downtime Risk

Business stakeholders often require strict service level commitments before authorizing production cutovers. Even when migrations are technically feasible, organizations may delay projects if they believe downtime windows could impact revenue, customer experience, or regulatory obligations. Most customers are still offered offline migration paths which can take hours to days, even though zero-downtime migrations are possible which take seconds to minutes.

2. Cost uncertainty

Many modernization projects are approved based on expected cost savings. However, if infrastructure sizing, licensing assumptions, storage consumption, or disaster recovery requirements are not evaluated properly, the actual operational cost can exceed initial expectations.

Cost uncertainty often slows executive approval processes and extends migration timelines.

3. Compatibility and Feature Fit

When migrating SQL Server, Azure SQL has several deployment offerings from IaaS to PaaS. These include SQL Server on Azure VM, Azure SQL Managed Instance, Azure SQL DB Hyperscale and Azure SQL in Microsoft Fabric. Many customers maybe using SQL Server features like Cross-database queries, CLR, SSIS, SQL Agent, and linked servers. They make a safe decision to lift and shift migrate to SQL Server on Azure VMs IaaS instead of modernizing to a PaaS service like Azure SQL Managed Instance. However, in the process, they lose the opportunity to use the PaaS capabilities, manageability and AI/Fabric capabilities in Azure by making this choice. Enterprise Architects, Application Architects, Database developers and DB Administrators have to make the right choice taking both development as well as operational costs and compatibility when they make their SQL modernization decisions.

Here are best practices some of the biggest and successful SQL migrations have used to make the migration and modernization journey with confidence. While we cannot disclose specific customer names, these guidelines are based on helping many large to small Enterprise customers.

Azure SQL Managed Instance as the Resiliency Anchor

Azure SQL Managed Instance is often the platform that helps organizations overcome all three concerns simultaneously because it combines near-full SQL Server compatibility with platform-as-a-service benefits.

Azure SQL Managed Instance (Azure SQL MI) Next-gen General Purpose is now generally available, bringing a built-in performance and scale upgrade for General Purpose workloads, including up to 500 databases per instance, up to 32 TB storage, lower latency, and higher IOPS. The release also adds more flexible cost-performance tuning with independent vCore, IOPS, and memory scaling, plus faster management operations to adapt to changing workload demand. For enterprise SQL Server modernization, this positions Azure SQL MI as a stronger path for high-compatibility migrations that need better price-performance without moving to a full replatform.

Let us dive deeper into how this helps address the downtime risk concerns by enables three levels of resiliency and high availability features.

Local Redundancy

Azure SQL Managed Instance provides first layer of Local Redundancy — built into every Azure SQL MI instance at no extra cost. Azure SQL Managed Instance uses local redundancy by default to keep workloads available during node, VM, rack, maintenance, and other local failures within a single datacenter, with Service Fabric orchestrating failover. In General Purpose (including Next-gen GP), this is implemented as stateless compute plus remote stateful storage; during failover, the engine process moves to another compute node and reattaches data, which can cause temporary performance impact due to cold cache. In Business Critical, local redundancy uses multiple synchronized replicas with local SSD storage (Always On-like architecture), enabling fast failover and read scale-out on secondaries.Next-gen General Purpose is an architectural upgrade to the existing General Purpose service tier that uses an upgraded remote storage layer that stores instance data and log files on Elastic SAN instead of page blobs and maintains it locally. Local redundancy protects against local infrastructure issues. This gives you a 99.99% SLA but not full datacenter/zone disasters, so zone redundancy (where supported) or disaster recovery (DR) options like failover groups/geo-restore are needed for broader resilience.

Zone Redundancy

The second layer is Zone Redundancy, which is accomplished placing data replicas across availability zones. Your Azure SQL MI resources are distributed across multiple availability zones within a region. This protects against the failure of an entire datacenter because each Azure availability zone is a separate physical location with independent power, cooling and networking. It relies on synchronous replication using zone-redundant storage for General Purpose. For Business critical, it uses Always On Availability group replicas across zones for Business Critical. Always On availability group technology replicates data changes from the primary instance to standby replicas in other availability zones. In the event of an outage, there's an automatic failover that seamlessly transitions one of the standby replicas to be prima. These replicas are always in sync — which means zero data loss. Failover typically happens in under 30 seconds, and your SLA jumps to 99.995%.

Failover Groups

The third layer is Failover Groups. This is your cross-region disaster recovery solution. It asynchronously replicates all user databases to a secondary Azure SQL MI instance in a different Azure region. Because it is asynchronous replication, there is potential for momentary data loss in the case of a datacenter outage. But it still protects the data against the worst case failure — a full regional outage. If the replica is a standby replica, there is no license required and it is used only for disaster recovery.

Using these options, business stakeholders can get their assurance that they have Enterprise grade availability and resiliency platform of AzureSQL for running their mission critical workloads. You can read more about these HA and Resiliency options in Microsoft Learn.

Cost Governance for Enterprise Buyers

The total cost of data modernization and migration is not a one-time estimate but an ongoing one. In this case, Azure SQL MI provides Enterprise DB Administrators many levers through pricing model choice, right-sizing, elasticity, serverless options and dev/test free tiers. Let us explore how these can be combined for smart cost estimations.

Lets also look at the best offering for the cost-conscious Enterprises - Azure SQL DB Hyperscale. With Azure SQL DB Hyperscale, you get the SQL Server engine, T-SQL compatibility, High Availability, Disaster recovery, security, backups, and management all bundled into the service price. No separate cost for SQL Server license. Hyperscale separates compute and storage that can scale independently and does not force you to overprovision. You have to only pay what you use which is ideal for seasonal workloads, Dev/Test, SaaS applications, predictable daytime trends, and up to 60% savings when you use Elastic pools.

Azure Hybrid benefit (AHB)- Azure Hybrid Benefit lets you bring your existing SQL Server investments to Azure and reduce compute costs, accelerating your ROI from cloud migration while preserving all the benefits of Azure SQL

Azure SQL DB Free offer – is the strongest product offering. Enterprises can use all features of Azure SQL at no cost for up to 10 Azure SQL DB free-tier. 100,000 vCore-seconds of serverless compute per month, 32GB data storage, 32 GB backup storage, serverless auto-scaling and auto-pause if you hit the limit per month. Run your POCs at no cost and evaluate before you move to Azure SQLDB, especially SMB& some enterprise

Azure SQL Managed Instance also offers 1 free Azure SQL MI instance per Azure subscription giving you 720vCore hours per month, 64GB storage, up to 500 databases, automated backups and 12 months free.

And if data migration is not possible due to data compliance or data proximity purposes, Azure Arc Pay-As-You-Go (PAYG) gives you cloud-style SQL licensing for servers running anywhere—on-premises, at the edge, or in other clouds. Instead of making large up-front licensing investments, you only pay for SQL Server while it's running, while still gaining access to Azure Arc management, security, monitoring, and modernization capabilities.

For seasonal, variable, or growth-oriented workloads, PAYG can improve cash flow and reduce licensing complexity. Reserved instances allow Enterprise customers to commit to using Azure SQL resource for a period of one or three years to receive a significant discount. This option combined with AHB can save you even more up to 80%. We have a comprehensive licensing guide for on-premises SQL Server for your reference.

Azure SQL enables a variety of cloud cost-models for a wide range of enterprise workload needs to help Enterprise cloud cost decision makers and DB Administrators make the right choice for their workloads.

Target selection guidance

While Azure SQL has multiple deployment options to migrate your on-premises work loads, it is critical to make the right choice long term. Customers can install SQL Server on-premises, they can use Azure SQL deployment options, and also run SQL Server in other clouds like Amazon Web Services and Google Cloud.

If there is an Enterprise workload that is not ready to modernize, you have the ability to lift and shift into SQL Server in Azure VM. It is a low cost migration option, because the application does not need any modification and it gives DB Administrators full control over the SQL server and underlying Windows or Linux OS. This can be a first step to modernization for some customers who are risk-averse.

For those Enterprise customers who are willing to modernize their workloads and SQL Server instances, Azure SQL DB Hyperscale is the best option. Azure SQL Database Hyperscale helps organizations modernize their most demanding database workloads with virtually unlimited growth, high performance, and cloud-scale economics. Customers can scale storage and compute independently, support large multi-terabyte databases, accelerate application performance with read-scale replicas, and eliminate the operational complexity of managing infrastructure, backups, patching, and high availability. They can build cloud-native applications or cloud-enable existing applications.

However, if Enterprise customers want good compatibility with their on-premises SQL Server but continue down the modernization path - their best option is Azure SQL Managed Instance. They can modernize the instance and not impact the application as there is no application change required. Applications will continue to work and the DB Administrators do not need to worry about managing infrastructure and all the overhead that comes with managing, self-managing your SQL Server virtual machines.

For SQL Server customers, PostgreSQL may look like an attractive low cost option. However, it requires re-platforming that could add significant hidden cost due to retraining all their DBAs and their developers to do performance optimization, performance best practices and operational maintenance. Lastly, our same SQL engine is also available to customers as a SaaS-ified version, Fabric SQL database as well.

All these options use the exact same SQL engine which makes it easier for Database developers and DB Administrators continue to use the same expertise, tools and process. Making the right choice of Azure SQL deployment is not just on the fastest way to modernize but the right long term approach.

Conclusion and Next steps

Enterprise SQL Server migrations rarely stall because of migration technology. More often, they are delayed by concerns around downtime, cost predictability, and platform selection. Organizations that address these questions early can accelerate modernization while reducing operational risk.

Azure SQL provides multiple modernization paths—from SQL Server on Azure Virtual Machines to Azure SQL Managed Instance and Azure SQL Database—allowing organizations to balance compatibility, operational simplicity, resiliency, and cost efficiency based on their business requirements.

As modernization initiatives accelerate, the most successful projects are those that treat migration not as a one-time infrastructure event, but as a long-term platform strategy.

Whether its the newest and the fastest way for us to migrate customers, we have all the comprehensive Copilot enabled AI-assisted migration tooling, technical training and support you need. Look for more blogs, whitepapers, guides and training based on best practices used real-world data modernization projects.

Log Insights in Minutes: A Simpler pgBadger Workflow

varun-dhawan — Wed, 01 Jul 2026 00:09:22 GMT

Sometimes the fastest way to understand a PostgreSQL workload is not another dashboard. It is a good log report.

pgBadger is a PostgreSQL log analysis tool that turns raw PostgreSQL logs into an interactive HTML report. It helps summarize query activity, connection patterns, errors, temporary files, lock waits, autovacuum activity, and more.

Earlier guidance for generating pgBadger reports from Azure Database for PostgreSQL Flexible Server focused on exporting logs through Diagnostic Settings, storing them in a storage account, and then using tools such as BlobFuse and jq to extract PostgreSQL log lines from JSON files.

That workflow is still useful when customers centralize logs across multiple servers. However, if you are already using the Server logs feature in Azure Database for PostgreSQL Flexible Server, there is a much simpler path.

In this post: You’ll learn how to generate a pgBadger HTML report from Azure Database for PostgreSQL Flexible Server by downloading native PostgreSQL .log files directly from the Azure portal. No storage account, BlobFuse mount, or JSON extraction required.

Fast path

Configure log_line_prefix.
Enable Server logs for download.
Download the PostgreSQL .log files.
Run pgBadger with the matching prefix.
Open pgbadger-report.html.

Why use this workflow?

With Server logs, you can download native PostgreSQL .log files directly from the Azure portal and run pgBadger locally.

Older path	Simpler path in this blog
Diagnostic Settings → Storage account → BlobFuse → JSON extraction → pgBadger	Server logs → Download `.log` files → pgBadger

Area	Older Diagnostic Settings workflow	Server logs workflow
Export path	Diagnostic Settings to storage account	Download `.log` files directly from the portal
Format	JSON payloads need extraction	Native PostgreSQL `.log` files
Extra tooling	BlobFuse and `jq` JSON parsing	None
Best suited for	Centralized or multi-server logging	Quick per-server analysis
Outcome	Flexible, but more setup	Faster path to pgBadger

Recommended: Use the Server logs workflow when you want a fast, low-friction way to generate a pgBadger report from one Azure Database for PostgreSQL Flexible Server.

When should you use this workflow?

Use this workflow when...	Use Diagnostic Settings when...
You need a quick report for one Flexible Server.	You centralize logs from many servers.
You want to run pgBadger locally.	You need long-term retention or workspace-level querying.
You want to avoid JSON extraction.	You already have automated log export pipelines.

Before you start

A machine where you can install or run pgBadger.
A working Perl runtime.
Git Bash on Windows, so the multi-line shell commands work as shown.
Portal access to your Azure Database for PostgreSQL Flexible Server.
Permission to update server parameters and enable Server logs.

Important: pgBadger can only analyze what PostgreSQL logs capture. To populate query timing and slow-query sections in the report, enable log_min_duration_statement before collecting logs. Logs collected before that change will not include duration data.

Workflow overview

Task	Type	Rough effort
Install or prepare pgBadger	One-time setup per analysis machine	5–10 minutes
Configure `log_line_prefix`	One-time setup per server	2–3 minutes
Enable Server logs	One-time setup per server	2–3 minutes
Download logs and run pgBadger	Repeatable	2–5 minutes

Install or prepare pgBadger on the machine where you will analyze logs.
Configure log_line_prefix so pgBadger can parse each log line.
Enable Server logs, so PostgreSQL logs are available for download.
Download the logs and run pgBadger locally.

💡Pro tip: Start with a narrow log window first. Use one or two hourly log files, confirm the report looks right, and then expand the analysis window if needed.

Step 1: Install pgBadger

Before generating a report, you need pgBadger available on the machine where you plan to analyze the downloaded PostgreSQL log files. Run this on a Linux VM, WSL, or another Linux-based environment where you can install packages.

Note: Azure Cloud Shell may work for quick testing, but package installation and build-tool availability can vary by session. For repeatable analysis, use a Linux VM, WSL, or another environment you control.

Copy and run

sudo apt-get update && sudo apt-get install -y git perl make gcc && \
git clone https://github.com/darold/pgbadger.git && \
cd pgbadger && \
perl Makefile.PL && \
make && \
sudo make install && \
pgbadger -V

What good looks like: The install command completes successfully and pgbadger -V returns the installed pgBadger version.

Step 2: Configure `log_line_prefix`

This is a one-time server configuration step.

The log_line_prefix parameter controls the beginning of each PostgreSQL log line. pgBadger uses this prefix to extract useful fields such as timestamp, user, database, and process ID.

In the Azure portal, open your Flexible Server and go to Server parameters. Search for:

Parameter

log_line_prefix

Set this value

%m user=%u db=%d pid=%p:

Then select Save.

In Server parameters, confirm that the custom value is saved for log_line_prefix.

Figure 1: Set log_line_prefix so pgBadger can correctly parse timestamp, user, database, and process ID from each log line.

Prefix tokens

Token	Meaning
`%m`	Timestamp with milliseconds
`%u`	Username
`%d`	Database name
`%p`	Process ID

After this change, log lines should look like this:

Example log line

2026-06-22 19:00:00.070 UTC user=pgadmin db=highcpu pid=3805603: LOG: statement: SELECT 1 FROM pg_extension WHERE extname='pg_stat_statements'

The matching pgBadger prefix for this log format is:

Matching pgBadger prefix

%m user=%u db=%d pid=%p:

You will use this same value later in the pgBadger command.

What good looks like: The server parameter is saved, and new PostgreSQL log lines begin with timestamp, user, database, and process ID fields that match the pgBadger prefix.

Step 3: Enable Server logs for download

This is also a one-time setup step.

In the Azure portal, open your Flexible Server and go to Server logs. Enable:

Portal setting

Capture logs for download

Set the retention period based on how long you want logs to remain available for download. For example, a 7-day retention period keeps logs available for download for 7 days.

In Server logs, enable Capture logs for download and choose the retention window.

Figure 2: Enable Capture logs for download and set a retention period long enough to cover the analysis window you want to inspect.

What good looks like: After Server logs are enabled, hourly PostgreSQL log files appear in the Server logs blade and can be downloaded from the Azure portal.

Once enabled, hourly log files appear in the Server logs blade. The files are named by date and hour, for example:

Example log files

postgresql_2026_06_22_19_00_00.log
postgresql_2026_06_22_20_00_00.log

Step 4: Download and organize the logs locally

From the Server logs page, select the .log files for the time window you want to analyze and download them.

For example, to analyze activity between 19:00 and 21:00 UTC, download:

Example files to download

postgresql_2026_06_22_19_00_00.log
postgresql_2026_06_22_20_00_00.log

On your local machine, create a folder for that analysis window. A simple convention is to use the Mon-DD format.

Folder name

Jun-22

Place the downloaded .log files inside that folder. Your local folder structure should look like this:

Folder structure

pgbadger-13.1/
  pgbadger
  Jun-22/
    postgresql_2026_06_22_19_00_00.log
    postgresql_2026_06_22_20_00_00.log

Step 5: Generate the pgBadger report

Open Git Bash from the folder where pgBadger is located. For example, if pgBadger is inside the pgbadger-13.1 folder, open Git Bash from that folder.

#	Action	Command
1	Set the folder	`FOLDER=Jun-22`
2	Confirm files	`ls -lh ./$FOLDER`
3	Run pgBadger	Use the full command below.

Copy and run

FOLDER=Jun-22

ls -lh ./$FOLDER

perl -X ./pgbadger -f stderr \
  --prefix '%m user=%u db=%d pid=%p:' \
  ./$FOLDER/*.log \
  -o ./$FOLDER/pgbadger-report.html

Command breakdown

Part of command	Purpose
`perl -X ./pgbadger`	Runs pgBadger and suppresses non-critical Perl warnings.
`-f stderr`	Parses PostgreSQL stderr log files.
`--prefix '%m user=%u db=%d pid=%p:'`	Matches the `log_line_prefix` set on the server.
`./$FOLDER/*.log`	Analyzes every `.log` file in the selected folder.
`-o ./$FOLDER/pgbadger-report.html`	Writes the HTML report into the same folder.

When the command completes successfully, you should see output like this:

Expected output

Parsed 12134249 bytes of 12134249 (100.00%), queries: 26684, events: 83
LOG: Ok, generating html report...

What good looks like: pgBadger finishes parsing the logs and creates pgbadger-report.html in the selected folder.

Step 6: Open the report

Open the generated report:

Copy and run

start ./$FOLDER/pgbadger-report.html

The report opens in your default browser. The final report is created here:

Generated report path

Jun-22/pgbadger-report.html

What the report can show

The pgBadger report gives you a quick view into the workload shape for the selected log window. For example, in a sample run across two hourly log files, pgBadger summarized:

Total number of queries.
Number of unique normalized queries.
Query traffic over time.
Events such as errors and fatal messages.
Session and connection patterns.

Once the report opens, start with Global Stats to confirm the time range, total queries, normalized queries, and query peak.

Figure 3: Start with Global Stats to validate the selected time range, total query count, normalized query count, and query peak.

Query volume and normalized queries

Many raw queries can often reduce to a smaller number of normalized query patterns. This helps identify whether the workload is spread across many different query shapes or dominated by a smaller set of repeated statements.

Example: In this sample run, 26,684 queries reduced to 59 normalized query shapes. That suggests the workload is mostly a small set of repeated statements, which can help focus tuning effort.

Traffic patterns

The SQL Traffic section helps identify spikes, quiet periods, and workload changes over time.

Figure 4: Use SQL Traffic to identify query spikes, quiet periods, and workload changes during the selected log window.

Figure 5: Review the query breakdown to compare read vs. write volume and query-type distribution for the selected Server logs window.

For example, if the report shows a steady baseline followed by a sharp spike, that spike can be correlated with application activity, batch jobs, synthetic tests, or operational events during the same time window.

Query duration

If query duration shows 0 ms or the slow query sections are empty, it usually means duration logging was not enabled when the logs were collected.

In that case, pgBadger can still show query counts and events, but it cannot calculate the slowest queries, total execution time, average duration, or maximum duration.

To unlock those timing sections, enable log_min_duration_statement, collect fresh logs, and rerun pgBadger.

What pgBadger cannot infer from missing logs

pgBadger reports are only as complete as the log data you provide. If PostgreSQL did not log duration, lock waits, temporary files, or autovacuum activity during the selected time window, pgBadger cannot reconstruct those details later.

To analyze...	Enable before collecting logs
Slow queries	`log_min_duration_statement`
Lock waits	`log_lock_waits`
Temporary files	`log_temp_files`
Autovacuum activity	`log_autovacuum_min_duration`

Repeatable copy/paste block

Reusable command block

Change only FOLDER for each new analysis window.

Copy and run

FOLDER=Jun-22

ls -lh ./$FOLDER

perl -X ./pgbadger -f stderr \
  --prefix '%m user=%u db=%d pid=%p:' \
  ./$FOLDER/*.log \
  -o ./$FOLDER/pgbadger-report.html

start ./$FOLDER/pgbadger-report.html

For another date, change only this line:

Update this value

FOLDER=Jun-22

Examples:

Example folder values

FOLDER=Jun-23
FOLDER=Jul-01
FOLDER=Aug-15

Optional: Improve report quality

pgBadger can only analyze the information captured in PostgreSQL logs.

The default logs may be enough for query frequency, connection activity, and errors. For deeper performance troubleshooting, consider enabling additional logging parameters based on your scenario.

Scenario	Parameter	Suggested value	Notes
Slow query analysis	`log_min_duration_statement`	`1000`	Logs statements slower than 1 second.
Short controlled test	`log_min_duration_statement`	`0`	Logs every statement. Use carefully.
Lock troubleshooting	`log_lock_waits`	`on`	Helps identify lock waits.
Temporary file analysis	`log_temp_files`	`0`	Logs all temporary files.
Autovacuum visibility	`log_autovacuum_min_duration`	`0`	Useful during focused analysis.

Useful parameters include:

Recommended logging parameters

log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0

To capture query durations, configure:

Duration logging

log_min_duration_statement = 1000

This logs statements that run longer than 1000 milliseconds.

For short test runs, you can temporarily use:

Short test run only

log_min_duration_statement = 0

Caution: Use log_min_duration_statement = 0 carefully on busy production servers. It logs every statement and can generate a large volume of logs.

Duration matters: If duration logging is not enabled, pgBadger can still show query counts and events, but slowest-query, total duration, average duration, and maximum duration sections will be limited or empty.

Common mistakes and quick fixes

Symptom	Likely cause	Fix
Report is empty	Prefix mismatch	Match `--prefix` with `log_line_prefix`.
No duration data	Duration logging was not enabled	Set `log_min_duration_statement` before collecting logs.
No files visible	Server logs disabled or retention expired	Enable capture and check retention.
pgBadger command fails	pgBadger is not in the current folder or path	Run `pgbadger -V` to confirm installation.

Common troubleshooting FAQs

1. Report is created but empty

This usually means the pgBadger prefix did not match the actual log format.

Check the first few lines:

Copy and run

head -5 ./$FOLDER/*.log

Make sure the pgBadger --prefix matches the server’s log_line_prefix.

2. Report shows queries but no duration

PostgreSQL logged statements but did not log durations.

Enable one of the following, collect fresh logs, and rerun pgBadger:

Parameter options

log_min_duration_statement = 1000

# or temporarily for testing
log_min_duration_statement = 0

3. No `.log` files are visible

Confirm that Server logs are enabled:

Portal setting

Capture logs for download

Also check the retention period. If the retention period has expired, older logs may no longer be available for download.

4. pgBadger command fails

Confirm that pgBadger is available in the current folder or installed in your path.

Copy and run

pgbadger -V

If you are running pgBadger from the local folder, use:

Copy and run

perl -X ./pgbadger

Summary

For customers already using Azure Database for PostgreSQL Flexible Server logs, the pgBadger workflow is straightforward:

Install pgBadger.
Configure log_line_prefix.
Enable Server logs for download.
Download the .log files.
Place them in a local date-based folder.
Run pgBadger with the matching prefix.
Open pgbadger-report.html.

Bottom line: Server logs give you the shortest path from Azure Database for PostgreSQL Flexible Server logs to a pgBadger report. Download the native .log files, run pgBadger with the matching prefix, and open the generated HTML report.

References

Lessons Learned #542: Reviewing Historical Azure SQL Database Storage Growth

Jose_Manuel_Jurado — Mon, 29 Jun 2026 10:46:52 GMT

This week I worked on a service request where our customer needed to understand how an Azure SQL Database had grown over time. This information can be useful for capacity planning, cost analysis, and performance reviews.

There are several possible approaches, depending on whether we need to review recent historical data that is still available in Azure Monitor, or whether we need to start collecting long-term historical data from now on.

In this lesson learned, I would like to summarize some of the options available.

1. Reviewing recent historical data using Azure Monitor metrics

The first point to clarify is how Azure Monitor metrics retention works.

Most Azure platform metrics are retained for up to 93 days. However, a single Azure Monitor Metrics chart can query no more than 30 days of data at a time. This means that, if the data is still within the Azure Monitor retention window, we might need to review the metric in 30-day intervals.

For Azure SQL Database storage usage, the metric commonly used is Data space used

2. Exporting metrics to Log Analytics for long-term analysis

If the requirement is to perform long-term analysis, I would like to recommended option is to enable Diagnostic Settings on the Azure SQL Database and send the metrics to a Log Analytics workspace.

Azure SQL Database diagnostic telemetry can be exported to different destinations, including:

Log Analytics workspace
Storage Account
Event Hubs

Using Log Analytics provides a very flexible way to query, aggregate, and visualize the data by using KQL.

Once the metrics are available in Log Analytics, we can calculate the monthly database growth. For example:

AzureMetrics | where ResourceProvider =~ "MICROSOFT.SQL" | where ResourceId == "/SUBSCRIPTIONS/your subscription/RESOURCEGROUPS/yourresourcegroup/PROVIDERS/MICROSOFT.SQL/SERVERS/yourserver/DATABASES/yourdatabase" | where MetricName == "storage" | summarize arg_max(TimeGenerated, Average) by Month = startofmonth(TimeGenerated) | project Month, DataSpaceUsedGB = round(Average / 1024 / 1024 / 1024, 2) | order by Month asc

This query takes the last available value for each month and converts the metric from bytes to GB. Depending on the analysis requirements, the query can be customized.

3. Creating a custom database space usage history process

If we need more control, or if we want to collect more granular database-level information, another option is to create a custom process that periodically captures the current database space usage into a table. This approach can be useful when we want to keep the information inside the database itself and avoid depending on external telemetry storage for this specific requirement.

For example, the following table can be used to store daily or weekly snapshots:

CREATE TABLE dbo.DatabaseSpaceUsageHistory ( SnapshotTimeUtc datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(), DatabaseName sysname NOT NULL, DataAllocatedMB decimal(19,2) NULL, DataUsedMB decimal(19,2) NULL, DataUnusedMB decimal(19,2) NULL, LogAllocatedMB decimal(19,2) NULL ); --Example collection query: INSERT INTO dbo.DatabaseSpaceUsageHistory ( DatabaseName, DataAllocatedMB, DataUsedMB, DataUnusedMB, LogAllocatedMB ) SELECT DB_NAME() AS DatabaseName, SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8.0 / 1024 AS DataAllocatedMB, SUM(CASE WHEN type_desc = 'ROWS' THEN FILEPROPERTY(name, 'SpaceUsed') END) * 8.0 / 1024 AS DataUsedMB, ( SUM(CASE WHEN type_desc = 'ROWS' THEN size END) - SUM(CASE WHEN type_desc = 'ROWS' THEN FILEPROPERTY(name, 'SpaceUsed') END) ) * 8.0 / 1024 AS DataUnusedMB, SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8.0 / 1024 AS LogAllocatedMB FROM sys.database_files;

This process can be executed daily, weekly, or monthly using the automation method that best fits the environment. This approach provides more control over the data collected, the retention period, and the frequency of collection.

Take control of your PostgreSQL maintenance

jasomaning — Fri, 26 Jun 2026 22:21:20 GMT

Maintenance is an important part of keeping Azure Database for PostgreSQL flexible server secure, reliable, and up to date. It helps deliver platform updates, security patches, operating system updates, and PostgreSQL engine updates that keep servers running smoothly.

But we also know that timing matters.

For teams running production workloads, a maintenance event during peak traffic, a migration, financial close, a major release, or a seasonal business event can create unnecessary stress. Even a brief restart or connection interruption can affect applications, customers, and business operations.

That’s why we’re excited to share that new self-service maintenance controls for Azure Database for PostgreSQL flexible server are now generally available in the Azure portal.

With these new capabilities, customers can see upcoming maintenance, reschedule eligible planned maintenance to a preferred date and time, apply maintenance when they’re ready, and review maintenance history after events complete.

Why we built this

Azure Database for PostgreSQL flexible server already offers maintenance scheduling options through Custom Maintenance Windows (CMW) and System-managed Maintenance Windows (SMW). These options give you a starting point for controlling when maintenance updates are applied.

But customers told us they needed more flexibility after a specific maintenance event was scheduled.

Sometimes the original maintenance time still lands during a critical business period. In the past, customers who needed to defer maintenance often had to open a support request. That added time, extra coordination, and operational overhead for something many teams wanted to handle directly.

With this release, you now have more control from the Azure portal.

What’s new

The new maintenance experience introduces four key capabilities.

1. View upcoming maintenance

You can now view upcoming maintenance before it begins. This gives teams a clearer view of when maintenance is scheduled, what type of maintenance is planned, and whether the event can be rescheduled.

To view upcoming maintenance:

In the Azure portal, go to your Azure Database for PostgreSQL flexible server.
On the server Overview page, review the Next Maintenance field.

Figure 1: View upcoming maintenance

If upcoming maintenance is available, the Next Maintenance field displays the scheduled maintenance time.

Select the Next Maintenance value to open the Maintenance page.
On the Maintenance page, review the Maintenance status section.

The Maintenance status section shows upcoming maintenance events that apply to your server, including the scheduled time, status, maintenance type, and available actions.

Instead of reacting at the last minute, database administrators and application teams can plan ahead with more confidence.

2. Reschedule maintenance to a preferred date and time

For eligible maintenance events, you can reschedule planned maintenance to a preferred date and time up to two weeks later.

To reschedule maintenance:

In the Azure portal, go to your Azure Database for PostgreSQL flexible server.
In the left menu, under Settings, select Maintenance.
In the Maintenance status section, review the upcoming maintenance event.
If the event is eligible, select Reschedule.
Choose an eligible future date and time.

Figure 2: Reschedule maintenance to a preferred date/time

Only dates and times that meet the service rules and your maintenance policy are available for selection. Note: Reschedule maintenance is not supported for servers on SMW schedule.

Select Reschedule to confirm the new maintenance time.
After confirmation, review the Maintenance status section to verify that the new start time is displayed.

The portal shows a confirmation after the maintenance event is successfully rescheduled.

This is useful when the original maintenance time overlaps with a period where even a short interruption could be disruptive, such as peak application traffic, end-of-quarter processing, planned migrations, major releases, holiday events, or internal change freeze periods.

3. Apply maintenance on demand

You can also apply eligible maintenance immediately when the timing works better for you.

To apply maintenance on demand:

In the Azure portal, go to your Azure Database for PostgreSQL flexible server.
In the left menu, under Settings, select Maintenance.
In the Maintenance status section, review the upcoming maintenance event.
If the event is eligible, select Reschedule, followed by Apply now.
Select Yes - Apply Maintenance Now to start maintenance.
Monitor the Maintenance status section.

Figure 3: Apply maintenance on demand

The maintenance event status updates as the workflow progresses. When maintenance completes, the status changes to Complete and the event moves to the Maintenance history section.

For example, a team might decide to apply maintenance during a quieter period, after validating application readiness, or before a planned release. This gives customers a way to complete maintenance on their own timeline instead of waiting for the scheduled window.

4. View maintenance history

After maintenance completes, you can review maintenance history for your server. This helps teams confirm when maintenance occurred and supports operational reviews, troubleshooting, and audit-related workflows.

To view maintenance history:

In the Azure portal, go to your Azure Database for PostgreSQL flexible server.
In the left menu, under Settings, select Maintenance.
On the Maintenance page, review the Maintenance history section.
Select a maintenance event Tracking ID to view more details, such as the maintenance type, start time, and final status.
Select Export to CSV to download maintenance history.

Figure 4: View maintenance history

If no past maintenance events are available for the server, the maintenance history section might be empty.

Built for real production schedules

Production environments rarely operate on a simple schedule. Maintenance often needs to be coordinated with application releases, internal change windows, customer commitments, and business calendars.

These new controls are designed to make that coordination easier. By making maintenance more visible and actionable, Azure Database for PostgreSQL flexible server helps you plan with less uncertainty and more control.

Frequently asked questions

Which maintenance events can I reschedule?

Only eligible planned maintenance events on Custom Maintenance Schedule (CMW) can be rescheduled. Some maintenance, such as time-sensitive security or compliance updates, might not be eligible for rescheduling.

How far can I reschedule maintenance?

Eligible maintenance can be moved to a preferred date and time up to two weeks from the originally scheduled maintenance date.

Can I reschedule maintenance more than once?

Yes. You can update the selected maintenance date and time again, as long as the maintenance event remains eligible and the new time is within the allowed two-week rescheduling window.

Why can’t I reschedule maintenance right before it starts?

Rescheduling is unavailable shortly before the originally scheduled maintenance time. This lock-in period helps ensure the maintenance workflow can begin reliably.

Can I apply maintenance before its scheduled time?

Yes. When an upcoming maintenance event is eligible, you can select Apply now in the Azure portal to begin maintenance immediately.

Will applying maintenance on demand restart my server?

It might. Some maintenance operations require a server restart, which can result in a brief interruption or connection churn. Apply maintenance when your application can tolerate that impact.

Where can I use these maintenance controls?

The new controls are available in the Azure portal. Open your Azure Database for PostgreSQL flexible server and go to Settings > Maintenance.

Can I use Azure CLI or REST API?

Azure CLI and REST API support are coming soon. At general availability, customers can use the Azure portal to view upcoming maintenance, reschedule eligible events, apply maintenance on demand, and view maintenance history.

Can I view maintenance that has already completed?

Yes. The Maintenance history section in the Azure portal shows completed maintenance events. You can select an event to review its details and export the history to CSV when needed.

What should I do if I need to defer maintenance for more than two weeks?

Create an Azure support request. The self-service experience supports rescheduling eligible maintenance for up to two weeks, but support can help assess requests that require a longer deferral.

Getting started

To get started, open your Azure Database for PostgreSQL flexible server in the Azure portal and navigate to Settings > Maintenance. These new self-service maintenance controls are now generally available in the Azure portal for Azure Database for PostgreSQL flexible server. CLI and API support are coming soon.

Learn More

To learn more about rescheduling maintenance and related Azure PostgreSQL capabilities, see:

Generic Best Practices for HikariCP with Azure Database for PostgreSQL

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

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

Introduction

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

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

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

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

Understanding Key HikariCP Parameters

1. Maximum Lifetime (maxLifetime)

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

Why It Matters

Connections can become stale over time due to:

Network interruptions
Infrastructure updates
Connection state changes
TCP idle behavior

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

Recommended Practice

Avoid configuring the value too low.

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

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

A reasonable starting point is:

spring.datasource.hikari.maxLifetime=1800000

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

Avoid

maxLifetime=300000

(5 minutes)

This often causes unnecessary connection churn without providing additional benefits.

2. Minimum Idle Connections (minimumIdle)

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

Why It Matters

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

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

Recommended Practice

For most workloads:

minimumIdle = maximumPoolSize

minimumIdle slightly lower than maximumPoolSize

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

Example

maximumPoolSize=20 minimumIdle=15

Avoid

maximumPoolSize=20 minimumIdle=20

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

3. Idle Timeout (idleTimeout)

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

Why It Matters

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

The application server
Azure Database for PostgreSQL

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

Recommended Practice

Keep the default value unless there is a specific requirement.

spring.datasource.hikari.idleTimeout=600000

which equals:

10 minutes (600,000 ms)

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

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

Avoid

idleTimeout=10000

(10 seconds)

Such aggressive settings often result in unnecessary connection creation cycles.

4. Maximum Pool Size (maximumPoolSize)

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

Why It Matters

This is often the most important HikariCP setting.

If the Pool Is Too Small

Applications may experience:

Connection is not available, request timed out

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

If the Pool Is Too Large

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

Connection contention
Increased context switching
Higher memory consumption
Reduced overall performance

Recommended Practice

Pool size should be based on:

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

There is no universal value that fits every workload.

Start conservatively:

maximumPoolSize=10

maximumPoolSize=20

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

Fixed-Size Pool Recommendation

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

Configure:

maximumPoolSize=20 minimumIdle=20

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

Benefits

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

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

minimumIdle
idleTimeout

Instead, simply recycle connections using:

maxLifetime

Additional Recommendations

Enable TCP Keepalive

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

For PostgreSQL applications, consider enabling TCP keepalive:

tcpKeepAlive=true

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

Monitor Connection Usage

Track:

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

These metrics help identify whether pool sizing is appropriate.

Investigate Long-Running Queries

Connection pool problems are often symptoms rather than root causes.

A frequent scenario is:

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

When analyzing HikariCP issues, always review:

Query performance
Blocking situations
Database resource utilization
Application connection handling logic

Sample Production Configuration

spring.datasource.hikari.maximumPoolSize=20

spring.datasource.hikari.minimumIdle=15

spring.datasource.hikari.maxLifetime=1800000

spring.datasource.hikari.idleTimeout=600000

spring.datasource.hikari.connectionTimeout=30000

spring.datasource.hikari.keepaliveTime=60000

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

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

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

Conclusion

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

As a general rule:

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

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

References

Connection pooling best practices - Azure Database for PostgreSQL

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

HikariCP Documentation and Pool Sizing Guidance

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

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

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

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

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

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

If we review the execution plan for the following query:

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

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

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

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

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

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

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

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

For example:

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

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

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

Data sync fails with deadlock error

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

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

Error:

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

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

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

A Sample deadlock graph looks below:

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

https://github.com/microsoft/AzureSQLDataSyncHealthChecker

Explanation:

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

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

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

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

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

For more details, refer to the official documentation:

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

Azure SQL DB Fabric Mirroring with Private Endpoint

shaurya-singh — Wed, 08 Jul 2026 10:52:35 GMT

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

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

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

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

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

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

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

High Level Configuration Steps

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

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

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

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

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

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

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

Introduction

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

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

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

Symptoms

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

Administrators may encounter errors similar to:

Cannot enumerate changes at the RelationalSyncProvider.

SqlError Number: 40197

The service has encountered an error processing your request.

Please try again.

Error code 40549

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

Additional Warning Signs

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

Understanding How Azure SQL Data Sync Uses Change Tracking

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

The synchronization architecture generally consists of:

Hub Database

The central synchronization endpoint responsible for orchestrating synchronization.

Member Databases

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

Change Tracking

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

Synchronization Metadata

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

Change Enumeration

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

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

Investigation Process

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

Step 1 – Identify the Failing Object

Review synchronization logs to determine which table repeatedly generates failures.

Step 2 – Determine Where the Failure Occurs

Determine whether the issue originates from:

Hub Database
Member Database
Synchronization Infrastructure

Step 3 – Evaluate Synchronization Backlog

Assess the volume of pending changes and synchronization metadata growth.

Step 4 – Assess Resource Governance Impact

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

Useful T-SQL Scripts for Azure SQL Data Sync Troubleshooting

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

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

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

1. Verify Whether Change Tracking Is Enabled

Azure SQL Data Sync requires Change Tracking to function correctly.

Check Database-Level Change Tracking

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

Check Table-Level Change Tracking

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

Why This Matters

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

2. Estimate Synchronization Backlog Size

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

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

Why This Matters

A very large backlog may indicate:

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

3. Review Change Tracking Metadata

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

Why This Matters

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

4. Validate Database Service Tier

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

Why This Matters

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

5. Check for Resource Governance Events

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

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

Why This Matters

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

Examples include:

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

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

6. Identify Large Tables

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

Why This Matters

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

7. Review Change Tracking Across All Tables

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

Why This Matters

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

Troubleshooting Checklist

When troubleshooting Azure SQL Data Sync failures:

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

Root Cause Analysis

The investigation ultimately revealed several contributing factors:

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

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

Resolution

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

Recovery Approach

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

Following this approach, synchronization resumed successfully without further failures.

Why the Resolution Works

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

Benefits include:

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

Technical Recommendations

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

Common Error Messages

Error 40197

The service has encountered an error processing your request.

Please try again.

Potential Causes

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

Error 40549

Error code 40549

Potential Causes

Excessive resource consumption
Long-running transactions
Synchronization enumeration pressure

Cannot Enumerate Changes at the RelationalSyncProvider

Cannot enumerate changes at the RelationalSyncProvider.

Potential Causes

Change Tracking backlog growth
Excessive synchronization metadata
Resource-governance intervention

Lessons Learned

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

Key Takeaways

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

Conclusion

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

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

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

Generally Available: Microsoft Entra Server Principals and Server Roles for Azure SQL Database

PDasgupta — Thu, 18 Jun 2026 20:27:39 GMT

The problem we're solving

Previously, Microsoft Entra identities in Azure SQL Database could only be created as contained database users - principals scoped to a single database with no server-level presence. That meant:

No granular server-level delegation. You couldn't assign a server role such as ##MS_ServerStateReader## (to query DMVs across databases) or ##MS_LoginManager## (to manage logins) to an Entra principal. Only the Entra admin or a SQL login could perform these server-scoped tasks.
Per-database provisioning overhead. Each Entra principal had to be created separately as a contained database user in every database that required access, with no way to inherit server-scoped permissions.
No centralized “disable” switch. Offboarding meant tracking down a contained database user in every database - there was no server-level login to disable.

These gaps forced many teams to keep SQL authentication for administrative tasks, even when they wanted to go password-less with Entra.

What changes with GA

Microsoft Entra logins become first-class server principals in the logical master database, just like SQL logins. This capability has been in public preview on Azure SQL Database (and is already generally available on Azure SQL Managed Instance and SQL Server 2022+); with this release it reaches general availability on Azure SQL Database, unlocking three things for production use:

1. Server role assignment for Entra identities

Azure SQL Database's seven fixed server-level roles can be assigned to Entra server principals(logins). These roles cover database connectivity, database management, definition and security-definition reads, login management, and server-state read/manage.

This means you can give your monitoring service principal read-only DMV access across all databases (##MS_ServerStateReader##), delegate login management to a security team member (##MS_LoginManager##), or let a DevOps app create databases (##MS_DatabaseManager##). All without SQL auth, all with Entra identities.

2. Server-wide login model

Instead of provisioning contained users independently in every database, you can create database users mapped to a server login (CREATE USER ... FROM LOGIN). These users inherit server-scoped permissions automatically. One login, many databases — managed from a single place.

For the T-SQL syntax, see Create and utilize Microsoft Entra server logins.

3. Centralized logins enable/disable

ALTER LOGIN [user@contoso.com] DISABLE - one command blocks that identity from connecting to every database on the server. No more hunting down per-database users during an offboarding or incident response. When you re-enable the login, access is restored everywhere.

Note: ALTER LOGIN ... DISABLE applies only to login-based users, not contained database users. It blocks new connections only; existing sessions remain active until terminated with KILL if needed. For immediate effect, see cache propagation. Microsoft Entra group logins are not supported; see the server principals documentation for alternatives.

What does this unlock for your organization

Ability to go password-less. With server principals and roles now generally available, organizations can adopt Entra-only authentication without a remaining server-level functionality gap. Entra logins bring parity with SQL logins closer, making it practical to disable SQL authentication entirely and using Entra as the sole authentication path.

Least-privilege administration. Server-level roles simplify permission management by enabling customers to delegate common management and monitoring responsibilities without requiring admin privileges, enabling adherence to least privilege and separation of duties at scale, while making administration across databases on the same logical server much easier. Server roles let you scope access precisely, previously, the only server-wide option for an Entra identity was the all-powerful Entra admin. Give your security auditors ##MS_SecurityDefinitionReader## role instead of 'db_owner'. Give your monitoring tools ##MS_ServerStateReader## instead of an over-privileged administrator role.

Zero-touch DevOps. A service principal with ##MS_DatabaseManager## and ##MS_LoginManager## can automate database and user provisioning end-to-end. After the initial Entra admin bootstrap, no human needs to be in the loop for routine operations.

Faster incident response. When a principal is compromised, disable the login at the server level. New connections are blocked across all databases immediately - without needing to know which databases the user had access to. To cut off active sessions immediately, flush the authentication caches and KILL existing sessions.

Geo-replica support. Entra logins created on the primary server are automatically available on geo-replicas, with read-only access to replicated databases.

Key things to know

Bootstrap requirements. The Microsoft Entra admin must create the first Entra login. After that, any Entra principal with ALTER ANY LOGIN or ##MS_LoginManager## membership can create additional logins.
Entra admin takes precedence. If a principal is both the Entra admin and has a login, the admin permissions win. The login permissions have no additional effect.
Cache propagation. Role membership and permission changes take effect on the next connection. For immediate effect, clear the auth cache with DBCC FLUSHAUTHCACHE and DBCC FREESYSTEMCACHE('TokenAndPermUserStore').
EXECUTE AS LOGIN is not supported for Entra logins on Azure SQL Database (it is supported on Managed Instance).

Get started

Configure a Microsoft Entra admin on your logical server
Create your first Entra login and assign server roles (step-by-step tutorial)
Understand the server roles and their permissions
Consider enabling Entra-only authentication to eliminate SQL auth entirely

Ready to migrate from SQL Authentication?

If you're looking to move your existing SQL logins to Entra, check out Securing Azure SQL Database with Microsoft Entra password-less authentication - migration guide. It walks through the end-to-end journey from SQL auth to Entra, including how to identify SQL login dependencies, convert them to Entra principals, and enable Entra-only mode.

Learn more

Microsoft Entra server principals (logins) - full reference: syntax, permissions, limitations.
Azure SQL Database server roles - role descriptions, permission matrix, examples.
Microsoft Entra authentication overview - how Entra auth works with Azure SQL.
Manage logins and users - login lifecycle management.

Scaling Write Throughput in Azure Database for MySQL Using Application-Level Sharding

ramkumarchan — Thu, 18 Jun 2026 16:16:06 GMT

This blog post walks through scaling write throughput in Azure Database for MySQL using application level sharding. It starts with the why behind sharding and then builds a complete C# implementation that spreads writes across three Azure Database for MySQL Flexible Servers.

Why Shard in the First Place?

This post focuses specifically on scaling write throughput. A well-tuned single primary node can take you remarkably far, and techniques such as indexing strategies, write batching, redo log optimization, and vertical compute scaling each deliver real, lasting value. For many workloads, these optimizations are all you will ever need. That said, as write volume continues to grow, a single primary eventually approaches its practical capacity, and at that point the most durable way to keep scaling is to distribute the write workload across multiple primary instances. This architecture is what we call sharding.

When you reach this inflection point, there are two primary patterns for managing multiple write nodes:

Proxy or Middleware Layer Sharding: A sharding aware proxy sits between the application and a pool of Azure Database for MySQL instances, routing queries based on a shard key. While this abstracts the underlying topology from the application layer, it introduces an additional, complex component to operate, secure, scale, and patch.
Application Layer Sharding: The application itself resolves the destination shard key and determines which of the N Azure Database for MySQL instances should receive a write before ever opening a database connection. Each backend target remains a completely standard, independent Azure Database for MySQL instance.

This post explores the second approach. The core appeal of application layer sharding is architectural simplicity: it introduces zero infrastructure overhead and eliminates an extra network hop. Every shard behaves exactly like a standalone instance, meaning your existing backup, restore, monitoring pipelines, and the Azure portal function seamlessly without modification. The explicit tradeoff is that you forgo cross shard joins and distributed transactions in exchange for absolute predictability and control over data access patterns.

The Plan

We will build a small order management service that distributes its data across three Azure Database for MySQL instances that already exist. The application, written in C# on .NET 8, owns the partitioning logic.

The premise: the three servers are already provisioned, the firewalls are configured, the network paths are established, and each server has its own administrative credentials. We are not provisioning infrastructure in this post. we are writing the application code that consumes it.

mysql-shard-0.mysql.database.azure.com user: shard0_admin pwd: <secret-0> mysql-shard-1.mysql.database.azure.com user: shard1_admin pwd: <secret-1> mysql-shard-2.mysql.database.azure.com user: shard2_admin pwd: <secret-2>

Each server hosts an identical appdb database with the same schema:

CREATE TABLE users ( user_id BIGINT NOT NULL PRIMARY KEY, email VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_email (email) ); CREATE TABLE orders ( order_id BIGINT NOT NULL PRIMARY KEY, user_id BIGINT NOT NULL, amount_cents INT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY ix_user (user_id) );

Two design decisions in this schema warrant explanation:

No AUTO_INCREMENT for user_id or order_id. Two shards would otherwise generate the same value 42 independently. Instead, we assign identifiers in the application, using a scheme such as Snowflake, ULID, or UUIDv7.
orders carries user_id, and we route by it. This is the single most important rule of sharding: choose a shard key that keeps related data colocated, so that the common queries remain on a single shard.

A note on UNIQUE KEY uq_email. A unique index enforces uniqueness only within a single physical shard. Because we route by user_id, two users with different IDs and the same email may land on different shards, and both inserts will succeed. If you require globally unique emails, two options exist: (a) maintain a separate email → user_id lookup table on a single "directory" server and write to it first within an idempotent flow, or (b) shard the users table by a hash of email instead. We retain user_id routing throughout this post because it is the correct choice for orders, and we treat per shard email uniqueness as a best effort guard rather than a hard global invariant.

How the Partitioning Works

The naive approach to sharding is shard = hash(key) % N. This works until you need to add a fourth server, at which point roughly 75% of your data must move. In any system of meaningful size, that is prohibitively expensive.

The established solution is virtual buckets. You hash the key into a large, fixed bucket space (here, 1024), then map buckets to physical shards. When you add capacity, you relocate only buckets; you never rehash the entire dataset.

In production, the bucket_to_shard_map typically resides in a system such as Azure App Configuration or etcd, so that you can rebalance without redeploying. For this post, we keep it as an in-memory array seeded at startup, which is straightforward to replace later.

The Project

ShardingDemo/ ├── ShardingDemo.csproj ├── appsettings.json ├── Models.cs ├── ShardRouter.cs ├── UserRepository.cs └── Program.cs

ShardingDemo.csproj

<Project Sdk="Microsoft.NET.Sdk"> <PropertyGroup> <OutputType>Exe</OutputType> <TargetFramework>net8.0</TargetFramework> <Nullable>enable</Nullable> <ImplicitUsings>enable</ImplicitUsings> </PropertyGroup> <ItemGroup> <PackageReference Include="MySqlConnector" Version="2.6.0" /> <PackageReference Include="Microsoft.Extensions.Hosting" Version="8.0.0" /> <PackageReference Include="Microsoft.Extensions.Configuration.Binder" Version="8.0.0" /> </ItemGroup> <ItemGroup> <Content Include="appsettings.json" CopyToOutputDirectory="PreserveNewest" /> </ItemGroup> </Project>

appsettings.json

Shards is an ordered list, and a shard's position in the array is its logical ID.

{ "Shards": [ { "Host": "mysql-shard-0.mysql.database.azure.com", "Database": "appdb", "User": "shard0_admin", "Password": "REPLACE_ME_0" }, { "Host": "mysql-shard-1.mysql.database.azure.com", "Database": "appdb", "User": "shard1_admin", "Password": "REPLACE_ME_1" }, { "Host": "mysql-shard-2.mysql.database.azure.com", "Database": "appdb", "User": "shard2_admin", "Password": "REPLACE_ME_2" } ] }

Models.cs

namespace ShardingDemo; public sealed record User(long UserId, string Email, DateTime CreatedAt); public sealed record Order(long OrderId, long UserId, int AmountCents, DateTime CreatedAt); public sealed class ShardConfig { public required string Host { get; init; } public required string Database { get; init; } public required string User { get; init; } public required string Password { get; init; } }

ShardRouter.cs

using System.Security.Cryptography; using System.Text; using MySqlConnector; namespace ShardingDemo; public sealed class Shard : IAsyncDisposable { public int Id { get; } public MySqlDataSource DataSource { get; } public Shard(int id, ShardConfig cfg) { Id = id; var csb = new MySqlConnectionStringBuilder { Server = cfg.Host, Port = 3306, Database = cfg.Database, UserID = cfg.User, Password = cfg.Password, SslMode = MySqlSslMode.Required, Pooling = true, MinimumPoolSize = 2, MaximumPoolSize = 100, ConnectionTimeout = 10, DefaultCommandTimeout = 30, }; DataSource = new MySqlDataSourceBuilder(csb.ConnectionString).Build(); } public ValueTask DisposeAsync() => DataSource.DisposeAsync(); } public sealed class ShardRouter : IAsyncDisposable { private const int VirtualBuckets = 1024; private readonly IReadOnlyList<Shard> _shards; private readonly int[] _bucketToShardId; public ShardRouter(IEnumerable<ShardConfig> configs) { _shards = configs.Select((c, i) => new Shard(i, c)).ToList(); // Even distribution. Replace with a map loaded from your control plane for live rebalancing. _bucketToShardId = new int[VirtualBuckets]; for (int i = 0; i < VirtualBuckets; i++) _bucketToShardId[i] = i % _shards.Count; } public IReadOnlyList<Shard> AllShards => _shards; private static int BucketFor(long shardKey) { byte[] hash = MD5.HashData(Encoding.ASCII.GetBytes(shardKey.ToString())); // Use the first byte pair as an unsigned value, then map it into the bucket space. int value = (hash[0] << 8) | hash[1]; return value % VirtualBuckets; } public Shard ShardForKey(long shardKey) { int bucket = BucketFor(shardKey); return _shards[_bucketToShardId[bucket]]; } public async ValueTask DisposeAsync() { foreach (var s in _shards) await s.DisposeAsync(); } }

UserRepository.cs

Observe that every per user method calls ShardForKey(userId), even when inserting an order. This is the colocation rule at work. An order and its owning user always reside on the same shard, so queries for a single user only ever reach one shard. Only the cross-shard aggregate (TotalRevenueCentsAsync) must fan out.

using MySqlConnector; namespace ShardingDemo; public sealed class UserRepository { private readonly ShardRouter _router; public UserRepository(ShardRouter router) { _router = router; } public async Task CreateUserAsync(long userId, string email, CancellationToken ct = default) { var shard = _router.ShardForKey(userId); await using var conn = await shard.DataSource.OpenConnectionAsync(ct); await using var cmd = conn.CreateCommand(); cmd.CommandText = "INSERT INTO users (user_id, email) VALUES (@id, Email)"; cmd.Parameters.AddWithValue("@id", userId); cmd.Parameters.AddWithValue("@email", email); await cmd.ExecuteNonQueryAsync(ct); } public async Task<User?> GetUserAsync(long userId, CancellationToken ct = default) { var shard = _router.ShardForKey(userId); await using var conn = await shard.DataSource.OpenConnectionAsync(ct); await using var cmd = conn.CreateCommand(); cmd.CommandText = "SELECT user_id, email, created_at FROM users WHERE user_id = ID"; cmd.Parameters.AddWithValue("@id", userId); await using var reader = await cmd.ExecuteReaderAsync(ct); if (!await reader.ReadAsync(ct)) return null; return new User(reader.GetInt64(0), reader.GetString(1), reader.GetDateTime(2)); } public async Task AddOrderAsync(long orderId, long userId, int amountCents, CancellationToken ct = default) { // Routed by user_id, so orders colocate with their owning user. var shard = _router.ShardForKey(userId); await using var conn = await shard.DataSource.OpenConnectionAsync(ct); await using var cmd = conn.CreateCommand(); cmd.CommandText = """ INSERT INTO orders (order_id, user_id, amount_cents) VALUES (@oid, @uid, amt) """; cmd.Parameters.AddWithValue("@oid", orderId); cmd.Parameters.AddWithValue("@uid", userId); cmd.Parameters.AddWithValue("@amt", amountCents); await cmd.ExecuteNonQueryAsync(ct); } public async Task<IReadOnlyList<Order>> GetOrdersForUserAsync(long userId, CancellationToken ct = default) { var shard = _router.ShardForKey(userId); await using var conn = await shard.DataSource.OpenConnectionAsync(ct); await using var cmd = conn.CreateCommand(); cmd.CommandText = """ SELECT order_id, user_id, amount_cents, created_at FROM orders WHERE user_id = @uid """; cmd.Parameters.AddWithValue("@uid", userId); var list = new List<Order>(); await using var reader = await cmd.ExecuteReaderAsync(ct); while (await reader.ReadAsync(ct)) { list.Add(new Order( reader.GetInt64(0), reader.GetInt64(1), reader.GetInt32(2), reader.GetDateTime(3))); } return list; } /// <summary>Cross shard fanout.</summary> public async Task<long> TotalRevenueCentsAsync(CancellationToken ct = default) { var tasks = _router.AllShards.Select(async shard => { await using var conn = await shard.DataSource.OpenConnectionAsync(ct); await using var cmd = conn.CreateCommand(); cmd.CommandText = "SELECT COALESCE(SUM(amount_cents), 0) FROM orders"; var result = await cmd.ExecuteScalarAsync(ct); return Convert.ToInt64(result); }); var perShard = await Task.WhenAll(tasks); return perShard.Sum(); } }

Program.cs

using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; using Microsoft.Extensions.Hosting; using ShardingDemo; var builder = Host.CreateApplicationBuilder(args); // Bind Shards:[] from appsettings.json (override with user-secrets / env vars / Key Vault) var shardConfigs = builder.Configuration .GetSection("Shards") .Get<List<ShardConfig>>() ?? throw new InvalidOperationException("No 'Shards' section configured."); if (shardConfigs.Count == 0) throw new InvalidOperationException("At least one shard must be configured."); builder.Services.AddSingleton(_ => new ShardRouter(shardConfigs)); builder.Services.AddSingleton<UserRepository>(); using var host = builder.Build(); var repo = host.Services.GetRequiredService<UserRepository>(); var router = host.Services.GetRequiredService<ShardRouter>(); (long Id, string Email)[] users = { (1001, "ada@example.com"), (2002, "linus@example.com"), (3003, "grace@example.com"), (4004, "alan@example.com"), }; foreach (var (id, email) in users) { await repo.CreateUserAsync(id, email); Console.WriteLine($"user {id} -> shard {router.ShardForKey(id).Id}"); } await repo.AddOrderAsync(orderId: 9001, userId: 1001, amountCents: 4999); await repo.AddOrderAsync(orderId: 9002, userId: 1001, amountCents: 1299); await repo.AddOrderAsync(orderId: 9003, userId: 2002, amountCents: 8800); Console.WriteLine($"\nAda: {await repo.GetUserAsync(1001)}"); Console.WriteLine($"Ada's orders: {(await repo.GetOrdersForUserAsync(1001)).Count}"); Console.WriteLine($"\nTotal revenue across 3 shards: " + $"${await repo.TotalRevenueCentsAsync() / 100m:F2}"); await router.DisposeAsync();

Tracing One Request End to End

Consider GetOrdersForUserAsync(1001):

ShardForKey(1001) → MD5("1001") → first two bytes as a number → % 1024 → a bucket in the range 0..1023.
bucket % 3 → a physical shard → for example mysql-shard-2.mysql.database.azure.com.
The MySqlDataSource provides a pooled, TLS encrypted connection authenticated as shard2_admin.
The query runs against shard 2's local ix_user index, with no fan out and at single server speed.

Every call with userId = 1001, whether GetUser, AddOrder, or GetOrdersForUser, lands on the same shard. That is why orders JOIN users ON orders.user_id = users.user_id WHERE user_id = 1001 executes within a single shard, with no cross-shard traffic.

Conclusion

The essential point is this. Once a single primary can no longer absorb your write load, sharding becomes a durable answer, and implementing it at the application layer keeps every part of the system explicit and comprehensible.

When write volume or dataset size outgrows a single primary, application layer sharding provides several benefits.

N independent Azure Database for MySQL instances, each absorbing 1/N of the write traffic.
Queries by user that remain on a single shard and behave like an ordinary, modestly sized database.
A bucket map approach that allows you to add a fourth, fifth, or Nth shard later by relocating slices of data rather than rehashing the entire dataset.
A failure of one shard that affects 1/N of your users rather than all of them.

These benefits come at a genuine cost. You must generate identifiers in the application, global uniqueness requires a secondary lookup table, and aggregate queries fan out across shards. A cross shard write, one that must atomically update data on two different shards, can no longer rely on a single database transaction. Instead it needs an orchestrated sequence of local transactions, where each step carries a compensating action that undoes its effect if a later step fails. None of these are insurmountable. They are simply responsibilities you now assume.

Sharding is a deliberate step to take only once a single primary has genuinely exhausted its write headroom. When you reach that point, the implementation in this post is a representative blueprint.

Stay Connected

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

Thank you for choosing Azure Database for MySQL!

rss.livelink.threads-in-node

ADF - Support for Federated Identity Credentials

Generally Available: SQL Migration to SQL Server on Azure Virtual Machines in Azure Architecture

One Migration Experience, More Flexibility

Unified Migration Workflow

Consistent Experience Across Azure SQL Targets

Online Migration Using Backup and Log Shipping

Customer-Controlled Cutover

Learn More

Generally Available: SQL Migration to SQL Server on Azure Virtual Machines in Azure Architecture

One Migration Experience, More Flexibility

Unified Migration Workflow

Consistent Experience Across Azure SQL Targets

Online Migration Using Backup and Log Shipping

Customer-Controlled Cutover

Learn More

PostgreSQL on Azure: Two services, one future-proofed ecosystem

Where there was one, now there’s two

Azure Database for PostgreSQL: Enterprise ready, managed open source

Inside Azure Database for PostgreSQL

Azure HorizonDB: Next-gen engine to build what’s next

Inside Azure HorizonDB

Making your selection

Choose Azure Database for PostgreSQL when:

Choose Azure HorizonDB when:

Choose the cloud with the deepest Postgres expertise

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

Lessons Learned #543: Evaluating MultiSubnetFailover with Azure SQL Database

What MultiSubnetFailover is designed for

Why Azure SQL Database is different

What about Failover Groups?

The importance of retry logic

Number of concurrent transactions supported for an Azure SQL 4 vCores

From RAG to agents: Build AI pipelines inside Azure HorizonDB

AI preprocessing runs outside the database, far from your data

Anatomy of an AI pipeline in HorizonDB

Use case 1: Semantic search over your data

Use case 2: Turn unstructured text into structured metadata

Use case 3: Summarize and rewrite content at scale

Use case 4: Keep embeddings fresh, and re-embed cleanly when the model changes

Watch your pipelines run as live graphs in VS Code

Get Started: Try It Now

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

Why the % character is different

Observed symptom

Test setup

Reproduction results

Commands used for testing

1. Space in the file name

2. Literal % in the file name

3. URL-encoded % character

4. Literal %25 in the blob name

Interpretation

Practical guidance

Optional PowerShell test using Invoke-Sqlcmd

Conclusion

3 Reasons Enterprise SQL Server Migrations Slow Down - and How to Avoid Them

Summary

What are important triggers that cause customers and partners to consider SQL modernization?

The Three Migration Blockers

1. Downtime Risk

2. Cost uncertainty

3. Compatibility and Feature Fit

Azure SQL Managed Instance as the Resiliency Anchor

Local Redundancy

Zone Redundancy

Failover Groups

Cost Governance for Enterprise Buyers

Target selection guidance

Conclusion and Next steps

Log Insights in Minutes: A Simpler pgBadger Workflow

Why use this workflow?

When should you use this workflow?

Before you start

Workflow overview

Step 1: Install pgBadger

Step 2: Configure log_line_prefix

Prefix tokens

Step 3: Enable Server logs for download

Step 4: Download and organize the logs locally

Step 2: Configure `log_line_prefix`

3. No `.log` files are visible