rss.livelink.threads-in-node

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

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

Overview

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

In one such scenario, a customer observed:

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

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

This article explains:

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

How Azure SQL Database Auditing Works?

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

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

BATCH_COMPLETED_GROUP
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
FAILED_DATABASE_AUTHENTICATION_GROUP

These groups audit:

All query execution activity
Successful authentication attempts
Failed authentication attempts

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

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

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

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

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

For example:

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

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

Observed Behavior in a Newly Created Test Server

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

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

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

Enabling AL / CR / DR Action IDs

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

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

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

After applying this configuration:

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

Ensuring Consistent Compliance Across PaaS Servers

To standardize audit logging behavior across environments:

Step 1: Compare AuditActionGroups

Run the following command on all servers:

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

Step 2: Align AuditActionGroups

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

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

Step 3: Validate

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

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

Accepted values:

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

Links:

Get-AzSqlServerAudit (Az.Sql) | Microsoft Learn

Set-AzSqlServerAudit (Az.Sql) | Microsoft Learn

March 2026 Recap: Azure Database for PostgreSQL

gauri-kasar — Wed, 15 Apr 2026 18:58:45 GMT

Hello Azure community,

March was packed with major feature announcements for Azure Database for PostgreSQL. From the general availability of SSDv2, cascading read replicas, to online migration and new monitoring capabilities for logical replication slots to help ensure slots are preserved, this update brings a range of improvements to performance, scale, and reliability.

Features

SSDv2 - Generally Available
Cascading Read replica - Generally Available
Online migration using PgOutput plugin - Generally Available
Google AlloyDB as a migration source - Generally Available
EDB Extended Server as a migration source - Generally Available
Logical replication slot synchronization metrics - Preview
Defender Security Assessments - Preview
New enhancements in the PostgreSQL VS Code Extension
Latest PostgreSQL minor versions: 18.3, 17.9, 16.13, 15.17, 14.22
New extension support for PostgreSQL 18 on Azure Database for PostgreSQL
Guide on PostgreSQL Buffer Cache Analysis, query rewriting and elastic clusters

SSDv2 - Generally Available

Premium SSD v2 is now generally available for Azure Database for PostgreSQL Flexible Server, delivering significant performance and cost-efficiency improvements for I/O‑intensive workloads. It offers up to 4× higher IOPS, lower latency, and improved price‑performance.

With independent scaling of storage and performance, you only pay for what you need. Premium SSD v2 supports storage scaling up to 64 TiB, with performance reaching 80,000 IOPS and 1,200 MiB/s throughput, without tying performance to disk size. IOPS and throughput can be adjusted instantly, with no downtime.

Additionally, built‑in baseline performance at no additional cost ensures consistent performance even for smaller deployments, making Premium SSD v2 a strong choice for modern, high‑demand PostgreSQL applications.

For details about the Premium SSD v2 release, see the GA Announcement Blog and documentation

Cascading read replica - Generally available

Cascading read replicas are now generally available, giving customers greater flexibility to create read replicas from existing read replicas. This capability supports up to two levels of replication and up to 30 read replicas in total, with each read replica able to host up to five cascading replicas.

With cascading read replicas, you can more effectively distribute read traffic across multiple replicas, deploy regional or hierarchical read replicas closer to end users, reduce read latency, and improve overall query performance for read‑heavy workloads. In addition, we’ve rolled out switchover support for both intermediate and cascading read replicas, making it easier to manage replica topologies. Learn more about cascading read replicas through our documentation and a detailed blog walkthrough.

Online migration using PgOutput plugin - Generally Available

The new addition of the PgOutput plugin helps make your Online migration to Azure more robust and seamless. The native "Out-of-the-Box" support that PgOutout offers is more suited for Online Production migrations compared to other logical decoding plugins. PgOutput offers higher throughput and superior performance compared to other logical decoding plugins ensuring your Online migration has very limited downtime. PgOutput also offers fine-grained filtering using Publications where you can migrate specific tables and filter by specific operations.

For more details about this update, see the documentation.

Google AlloyDB as a migration source - Generally Available

Google AlloyDB is now supported as a source in Azure Database for PostgreSQL Migration Service. You can use this capability to migrate your AlloyDB workloads directly to Azure Database for PostgreSQL, using either offline or online migration options. This support helps you move your PostgreSQL databases to Azure with confidence, while taking advantage of Azure’s flexibility and scalability.

To know more about this feature, visit our documentation.

EDB Extended Server as a migration source - Generally Available

Azure Database for PostgreSQL Migration Service now supports EDB Extended Server as a migration source. This enables you to migrate EDB Extended Server workloads to Azure Database for PostgreSQL using both offline and online migration methods. With this addition, you can transition PostgreSQL databases to Azure smoothly and benefit from the scale and flexibility of the Azure platform.

For more details about this update, see the documentation.

Logical replication slot sync status metric - Preview

You can now monitor whether your logical replication slots are failover‑ready using the new logical_replication_slot_sync_status metric, now in preview. This metric provides a simple binary signal indicating whether logical replication slots are synchronized across High availability (HA) primary and standby nodes. It helps you quickly assess failover readiness without digging into replication internals especially valuable for CDC pipelines such as Debezium and Kafka, where data continuity during failover is critical.

Learn more about logical replication metrics in the documentation.

Defender Security Assessments - Preview

In March, we introduced two new Microsoft Defender for Cloud CSPM security recommendations for Azure Database for PostgreSQL Flexible Server, now available in public preview:

Geo-redundant backups should be enabled for PostgreSQL Servers
require_secure_transport should be set to "on" for PostgreSQL Servers

These integrated assessments continuously evaluate database configuration settings against security best practices, helping customers proactively identify and manage security posture risks for their Azure PostgreSQL servers while maintaining alignment with internal and industry standards.

Additional security posture assessments for Azure PostgreSQL will be introduced as they become available.

To learn more, refer to the reference table for all data security recommendations in Microsoft Defender for Cloud.

New enhancements in the PostgreSQL VS Code Extension

The March release (v1.20) of the PostgreSQL VS Code extension delivers new server management capabilities, enhanced query plan analysis, visual improvements, and a batch of bug fixes.

Clone Server: You can now clone an Azure PostgreSQL Flexible Server directly from within the extension. The clone operation is available from the server management UI, allowing you to duplicate a server configuration including region, SKU, and settings without leaving VS Code.
Entra ID Authentication for AI-Powered Schema Conversion: The Oracle-to-PostgreSQL migration experience now supports Microsoft Entra ID authentication for Azure OpenAI connectivity, replacing API key–based authentication. This enables enterprise-grade identity management and access control for AI-powered schema conversion workflows.
Query Plan Visualization Improvements: The Copilot-powered “Analyze with Copilot” feature for query plans has been improved with more relevant optimization recommendations and smoother SQL attachment handling during plan analysis.
Apache AGE Graph Visualizer Enhancements: The graph visualizer received a visual refresh with modernized edge rendering, a color-coded legend, and a new properties pane for exploring element details.
Object Explorer Deep Refresh: The Object Explorer now supports refreshing expanded nodes in place, so newly created tables and objects appear immediately without needing to disconnect and reconnect.
Settings Management: The extension now supports both global user settings and local .vscode/settings.json, providing more robust connection settings management across configuration sources.
Bug Fixes: This release includes numerous bug fixes across script generation (DDL for triggers, materialized views, and functions), IntelliSense (foreign table support), JSON data export, query execution, and server connectivity.

Latest PostgreSQL minor versions: 18.3, 17.9, 16.13, 15.17, 14.22

Azure PostgreSQL now supports the latest PostgreSQL minor versions: 18.3, 17.9, 16.13, 15.17, and 14.22. These updates are applied automatically during planned maintenance windows, ensuring your databases stay up to date with critical fixes and reliability improvements, with no manual action required. This is an out-of-cycle release that addresses regressions identified in the previous update. The release includes fixes across replication, JSON functions, query correctness, indexing, and extensions like pg_trgm, improving overall stability and correctness of database operations.

For details about the minor release, see the PostgreSQL announcement.

New extension support for PostgreSQL 18 on Azure Database for PostgreSQL

Azure Database for PostgreSQL running PostgreSQL 18 now supports extensions that enable graph querying, in‑database AI integration, external storage access, and scalable vector similarity search, expanding the types of workloads that can be handled directly within PostgreSQL.

Newly supported extensions include:

AGE (Apache AGE v1.7.0): Adds native graph data modeling and querying capabilities to PostgreSQL using openCypher, enabling hybrid relational–graph workloads within the same database.
azure_ai: Enables direct invocation of Microsoft Foundry models from PostgreSQL using SQL, allowing AI inference and embedding generation to be integrated into database workflows.
azure_storage: Provides native integration with Azure Blob Storage, enabling PostgreSQL to read from and write to external storage for data ingestion, export, and hybrid data architectures.
pg_diskann: Introduces disk‑based approximate nearest neighbor (ANN) indexing for high-performance vector similarity search at scale, optimized for large vector datasets with constrained memory.

Together, these extensions allow PostgreSQL on Azure to support multi-model, AI‑assisted, and data‑intensive workloads while preserving compatibility with the open‑source PostgreSQL ecosystem.

Guide on PostgreSQL buffer cache analysis, query rewriting

We have rolled out two new blogs on PostgreSQL buffer cache analysis and PostgreSQL query rewriting and subqueries. These blogs help you better understand how PostgreSQL behaves under the hood and how to apply practical performance optimizations whether you’re diagnosing memory usage, reducing unnecessary disk I/O, or reshaping queries to get more efficient execution plans as your workloads scale.

PostgreSQL Buffer Cache Analysis

This blog focuses on understanding PostgreSQL memory behavior through shared_buffers, the database’s primary buffer cache. Using native statistics and the pg_buffercache extension, it provides a data‑driven approach to evaluate cache efficiency, identify when critical tables and indexes are served from memory, and detect cases where disk I/O may be limiting performance. The guide offers a repeatable methodology to support informed tuning decisions as workloads scale.

PostgreSQL Query Rewriting and Subqueries

This blog explores how query structure directly impacts PostgreSQL execution plans and performance. It walks through common anti‑patterns and practical rewrites such as replacing correlated subqueries with set‑based joins, using semi‑joins, and pre‑aggregating large tables to reduce unnecessary work and enable more efficient execution paths. Each scenario includes clear explanations, example rewrites, and self‑contained test scripts you can run.

Azure Postgres Learning Bytes 🎓

How to create and store vector embeddings in Azure Database for PostgreSQL

Vector embeddings sit at the core of many modern AI applications from semantic search and recommendations to RAG‑based experiences. But once you generate embeddings, an important question follows: how do you generate and store them in your existing database server?

With Azure Database for PostgreSQL, you can generate and store vector embeddings directly alongside your application data. By using the `azure_ai` extension, PostgreSQL can seamlessly integrate with Azure OpenAI to create embeddings and store them in your database. This learning byte walks you through a step‑by‑step guide to generating and storing vector embeddings in Azure Database for PostgreSQL.

Step 1: Enable the Azure AI extension

Azure Database for PostgreSQL supports the azure_ai extension, which allows you to call Azure OpenAI service.

Connect to your database and run:

CREATE EXTENSION IF NOT EXISTS azure_ai;

Step 2: Create (or use existing) Azure OpenAI resource

You need an Azure OpenAI resource in your subscription with an embedding model deployed.

In the Azure portal, create an Azure OpenAI resource.
Deploy an embedding model (for example, text-embedding-3-small).

Azure OpenAI provides the endpoint URL and API key

Step 3: Get endpoint and API key

Go to your Azure OpenAI resource in the Azure portal.
Select Keys and Endpoint.
Copy:
- Endpoint
- API Key (Key 1 or Key 2)

Step 4: Configure Azure AI extension with OpenAI details

Store the endpoint and key securely inside PostgreSQL

SELECT azure_ai.set_setting( 'azure_openai.endpoint', 'https://<your-endpoint>.openai.azure.com' ); SELECT azure_ai.set_setting( 'azure_openai.subscription_key', '<your-api-key>' );

Step 5: Generate an embedding

SELECT LEFT( azure_openai.create_embeddings( 'text-embedding-3-small', 'Sample text for PostgreSQL Lab' ):: text, 100 ) AS vector_preview;

Step 6: Add a vector column

Add a vector column to store embeddings (example uses 1536‑dimensional vectors):

ALTER TABLE < table - name > ADD COLUMN embedding VECTOR(1536);

Step 7: Store the embedding

Update your table with the generated embedding:

UPDATE < table - name > SET embedding = azure_openai.create_embeddings( 'text-embedding-3-small', content );

Conclusion

That’s a wrap for our March 2026 recap. This month brought a set of meaningful updates focused on making Azure Database for PostgreSQL more performant, reliable, and scalable whether you’re modernizing workloads, scaling globally, or strengthening your security posture.

We’ll be back soon with more exciting announcements and key feature enhancements for Azure Database for PostgreSQL, so stay tuned! Your feedback is important to us, have suggestions, ideas, or questions? We’d love to hear from you: https://aka.ms/pgfeedback.

Tutorial: Building AI Agents That Talk to Your Azure Database for MySQL

FarahAbdou — Wed, 15 Apr 2026 13:00:00 GMT

What if you could ask your database a question in plain English and get the answer instantly, without writing a single line of SQL?

In this tutorial, you'll build a Python-based AI agent that connects to Azure Database for MySQL server and uses OpenAI's function calling to translate natural language questions into SQL queries, execute them, and return human-readable answers. The agent can explore your schema, answer business questions, and even self-correct when it writes invalid SQL.

What you'll build:

An Azure Database for MySQL server with sample data
A Python AI agent with three tools: list_tables, describe_table, and run_sql_query
- In the context of AI agents, tools are functions the agent can call to interact with external systems like querying a database, fetching a file, or calling an API. Here, our agent has three tools that let it explore and query your MySQL database.
An interactive chat interface where you ask questions and the agent auto-generates and runs SQL

Prerequisites

Before you begin, make sure you have:

An Azure account — Sign up for free (includes 12 months of free MySQL hosting)
An OpenAI API key — Get one here (you'll need a few dollars of credit)
Python 3.10+ — Download here (check "Add to PATH" during install)
A code editor — VS Code recommended
Optional: You can download the complete project from this GitHub repository, or follow the step‑by‑step instructions below to build it from scratch.

Step 1 — Create the Azure Database for MySQL server

Go to the Azure Portal
Search for "Azure Database for MySQL server" and click + Create
Configure the following settings:

Setting	Value
Resource group	rg-mysql-ai-agent (create new)
Server name	mysql-ai-agent (or any unique name)
Region	Your nearest region
MySQL version	8.4
Workload type	Dev/Test (Burstable B1ms — free for 12 months)
Admin username	mysqladmin
Password	A strong password — save it!

4. ✅ Check "Add firewall rule for current IP address"

⚠️ Important: If you skip the firewall settings, you won't be able to connect from Cloud Shell or your local machine.

5. Click Review + create → Create and wait 3–5 minutes

Once deployment finishes, navigate to your server and note the hostname from the Connection details:

mysql-ai-agent.mysql.database.azure.com

Step 2 — Load Sample Data

Open Azure Cloud Shell by clicking the >_ icon in the portal's top toolbar. Select Bash if prompted.

Connect to your MySQL server. You can copy the exact connection command from the "Connect from browser or locally" section on your server's overview page in the Azure portal:

mysql -h mysql-ai-agent.mysql.database.azure.com -u mysqladmin -p

Enter your password when prompted (the cursor won't move — just type and press Enter).

Now paste the following SQL to create a sample sales database:

CREATE DATABASE demo_sales; USE demo_sales;

CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), city VARCHAR(50), signup_date DATE ); CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, product VARCHAR(100), amount DECIMAL(10,2), order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(id) ); INSERT INTO customers (name, email, city, signup_date) VALUES ('Sara Ahmed', 'sara@example.com', 'Cairo', '2024-06-15'), ('John Smith', 'john@example.com', 'London', '2024-08-22'), ('Priya Patel', 'priya@example.com', 'Mumbai', '2025-01-10'); INSERT INTO orders (customer_id, product, amount, order_date) VALUES (1, 'Azure Certification Voucher', 150.00, '2025-03-01'), (2, 'MySQL Workbench Pro License', 99.00, '2025-03-10'), (1, 'Power BI Dashboard Template', 45.00, '2025-04-05'), (3, 'Data Analysis Course', 200.00, '2025-05-20');

Verify the data:

SELECT * FROM customers; SELECT * FROM orders;

Type exit to leave MySQL.

Step 3 — Set Up the Python Project

Open a terminal on your local machine and create the project:

mkdir mysql-ai-agent cd mysql-ai-agent python -m venv venv

Activate the virtual environment:

Windows (PowerShell):

venv\Scripts\Activate.ps1

macOS/Linux:

source venv/bin/activate

Install the required packages:

pip install openai mysql-connector-python python-dotenv

Step 4 — Configure Environment Variables

Create a file named .env in your project folder:

OPENAI_API_KEY=sk-proj-xxxxxxxxxxxxxxxxxxxxxxxx MYSQL_HOST=mysql-ai-agent.mysql.database.azure.com MYSQL_USER=mysqladmin MYSQL_PASSWORD=YourPasswordHere MYSQL_DATABASE=demo_sales

🔒 Security: Never commit this file to Git. Add .env to your .gitignore

Step 5 — Build the Agent

Open VS Code, create a new file called mysql_agent.py in your mysql-ai-agent folder, and paste the following code. Let's walk through each section.

5.1 — Imports and Database Connection

import os import json import mysql.connector from openai import OpenAI from dotenv import load_dotenv load_dotenv() def get_db_connection(): return mysql.connector.connect( host=os.getenv("MYSQL_HOST"), user=os.getenv("MYSQL_USER"), password=os.getenv("MYSQL_PASSWORD"), database=os.getenv("MYSQL_DATABASE"), ssl_disabled=False )

This loads your secrets from .env and creates a reusable MySQL connection function with SSL encryption.

5.2 — Define the Three Tools

These are the functions the AI agent can call:

def list_tables(): conn = get_db_connection() cursor = conn.cursor() cursor.execute("SHOW TABLES") tables = [row[0] for row in cursor.fetchall()] cursor.close() conn.close() return json.dumps({"tables": tables}) def describe_table(table_name): conn = get_db_connection() cursor = conn.cursor() cursor.execute(f"DESCRIBE `{table_name}`") columns = [] for row in cursor.fetchall(): columns.append({ "field": row[0], "type": row[1], "null": row[2], "key": row[3] }) cursor.close() conn.close() return json.dumps({"table": table_name, "columns": columns}) def run_sql_query(query): if not query.strip().upper().startswith("SELECT"): return json.dumps({"error": "Only SELECT queries are allowed."}) conn = get_db_connection() cursor = conn.cursor() try: cursor.execute(query) columns = [desc[0] for desc in cursor.description] rows = cursor.fetchall() results = [] for row in rows: results.append(dict(zip(columns, row))) return json.dumps({"columns": columns, "rows": results}, default=str) except mysql.connector.Error as e: return json.dumps({"error": str(e)}) finally: cursor.close() conn.close()

A few things to note:

run_sql_query only allows SELECT statements — this is a safety guardrail that prevents the AI from modifying data
The try/except block is critical — if the AI generates invalid SQL (e.g., a bad GROUP BY), the error message is returned to OpenAI, and the model automatically corrects its query and retries. Without this, the script would crash.

5.3 — Register Tools with OpenAI

This tells OpenAI what tools the agent has access to:

tools = [ { "type": "function", "function": { "name": "list_tables", "description": "List all tables in the connected MySQL database.", "parameters": {"type": "object", "properties": {}, "required": []} } }, { "type": "function", "function": { "name": "describe_table", "description": "Get the schema (columns and types) of a specific table.", "parameters": { "type": "object", "properties": { "table_name": {"type": "string", "description": "Name of the table to describe"} }, "required": ["table_name"] } } }, { "type": "function", "function": { "name": "run_sql_query", "description": "Execute a read-only SQL SELECT query and return results.", "parameters": { "type": "object", "properties": { "query": {"type": "string", "description": "The SQL SELECT query to execute"} }, "required": ["query"] } } } ] def call_tool(name, args): if name == "list_tables": return list_tables() elif name == "describe_table": return describe_table(args["table_name"]) elif name == "run_sql_query": return run_sql_query(args["query"]) else: return json.dumps({"error": f"Unknown tool: {name}"})

5.4 — The Agent Loop

This is the core logic. It sends the user's message to OpenAI, processes any tool calls, and loops until the model produces a final text response:

def chat(user_message, conversation_history): client = OpenAI() conversation_history.append({"role": "user", "content": user_message}) print(f"\n{'='*60}") print(f"🧑 You: {user_message}") print(f"{'='*60}") while True: response = client.chat.completions.create( model="gpt-4o-mini", messages=conversation_history, tools=tools, tool_choice="auto" ) assistant_message = response.choices[0].message if assistant_message.tool_calls: conversation_history.append(assistant_message) for tool_call in assistant_message.tool_calls: fn_name = tool_call.function.name fn_args = json.loads(tool_call.function.arguments) print(f" 🔧 Calling tool: {fn_name}({json.dumps(fn_args)})") result = call_tool(fn_name, fn_args) print(f" ✅ Tool returned: {result[:200]}...") conversation_history.append({ "role": "tool", "tool_call_id": tool_call.id, "content": result }) else: final_answer = assistant_message.content conversation_history.append({"role": "assistant", "content": final_answer}) print(f"\n🤖 Agent:\n{final_answer}") return conversation_history

The while True loop is what makes self-correction possible. When a tool returns an error, the model sees it in the conversation and generates a corrected tool call in the next iteration.

5.5 — Main Entry Point

if __name__ == "__main__": print("\n" + "=" * 60) print(" 🤖 MySQL AI Agent") print(" Powered by OpenAI + Azure Database for MySQL") print(" Type 'quit' to exit") print("=" * 60) system_message = { "role": "system", "content": ( "You are a helpful data analyst agent connected to an Azure Database for MySQL. " "You have 3 tools: list_tables, describe_table, and run_sql_query. " "ALWAYS start by listing tables and describing their schema before writing queries. " "Only generate SELECT statements. Never write INSERT, UPDATE, DELETE, or DROP. " "Present query results in clean, readable tables. " "If the user asks a question, figure out the right SQL to answer it." ) } conversation_history = [system_message] while True: user_input = input("\n🧑 You: ").strip() if user_input.lower() in ("quit", "exit", "q"): print("\n👋 Goodbye!") break if not user_input: continue conversation_history = chat(user_input, conversation_history)

Your final project folder should look like this:

Step 6 — Run and Test the Agent

python mysql_agent.py

Test:

Prompt: Which product generated the most revenue and who bought it?

How Self-Correction Works

One of the most powerful aspects of this agent is its ability to recover from SQL errors automatically. Azure Database for MySQL has sql_mode=only_full_group_by enabled by default, which rejects queries where non-aggregated columns aren't in the GROUP BY clause.

When the AI generates an invalid query, here's what happens:

The run_sql_query function catches the MySQL error
It returns the error message as the tool result
OpenAI sees the error in the conversation context
The model generates a corrected query automatically
The agent retries — and succeeds

Without the try/except error handling, the entire script would crash. This is a key design pattern for production AI agents.

Security Best Practices

When building AI agents that interact with databases, security is critical:

Read-only enforcement — The run_sql_query function rejects anything that isn't a SELECT statement
SSL encryption — All connections use ssl_disabled=False, ensuring data in transit is encrypted
Environment variables — Credentials are stored in .env, never hardcoded
Principle of least privilege — For production, create a dedicated MySQL user with SELECT-only permissions:

CREATE USER 'ai_agent'@'%' IDENTIFIED BY 'AgentPass123!'; GRANT SELECT ON demo_sales.* TO 'ai_agent'@'%'; FLUSH PRIVILEGES;

Network isolation — For production workloads, consider using Azure Private Link instead of public access.

Conclusion

In this tutorial, you built a Python AI agent that connects to Azure Database for MySQL and answers natural language questions by auto-generating SQL - complete with self-correction and security guardrails. Clone the GitHub repo, spin up your own server, and start experimenting!

If you'd like to connect to Azure Database for MySQL using the Model Context Protocol (MCP), see Unlocking AI-Driven Data Access: Azure Database for MySQL Support via the Azure MCP Server.

If you have any feedback or questions about the information provided above, please leave a comment below. Thank you!

Azure SQL Migration Starts in SSMS—All in One Flow

neelball — Wed, 15 Apr 2026 12:26:12 GMT

Migrate SQL Server Using SQL Server Management Studio (SSMS)

Migrating SQL Server workloads to Azure doesn’t have to be complex or fragmented. SQL Server Management Studio (SSMS) provides a guided, end-to-end migration experience that works seamlessly for both Azure Arc–enabled and standalone SQL Server instances.

In this blog, we’ll walk through how SSMS helps you assess, choose, and migrate your SQL Server workloads using the right Azure target and migration method.

One Tool, Multiple Migration Paths

SSMS acts as the central starting point for SQL Server migration:

Run readiness and compatibility assessments
Discover recommended Azure targets
Launch guided migrations using the right method for your scenario
Seamlessly connect to the Azure portal when needed

The experience adapts based on whether your SQL Server is Azure Arc–enabled or not.

Migrating Non‑Arc SQL Server (Local or On‑Premises)

If your SQL Server is not Arc-enabled, SSMS still gives you everything you need—starting locally and moving to Azure when appropriate.

Step 1: Run SQL Server Assessment in SSMS

SSMS performs a local assessment to:

- Evaluate database readiness
- Identify potential blocking issues
- Recommend supported Azure targets

Step 2: Provision the Target via Azure SQL Hub

From SSMS, you’re guided to Azure SQL Hub in the Azure portal to:

- Configure your migration target
- Select the right Azure SQL deployment option

Step 3: Choose the Right Migration Method

SSMS then walks you through guided migration experiences, tailored to your target:

🟦 Azure SQL Managed Instance

- MI Link – Near real-time migration with minimal downtime
- Backup & Restore – Simple, deterministic migrations

🟦 Azure SQL Database / Azure SQL VM / Other Targets

- Azure Database Migration Service (DMS)
  A guided, end-to-end migration experience supporting multiple Azure targets

SSMS helps ensure you’re choosing the right migration method for the right workload, without needing to switch tools manually.

Migrating Azure Arc–Enabled SQL Server

If your SQL Server is connected to Azure via Azure Arc, SSMS provides a streamlined experience that bridges on-premises management and Azure services.

What happens in SSMS?

When you start migration from SSMS for an Arc-enabled SQL Server:

- SSMS detects the Arc connection
- You’re redirected to the Azure portal for the migration flow
- Azure provides a centralized, cloud-based migration experience with deeper integration

This model is ideal if your organization is already using Azure Arc for governance, inventory, and centralized management.

Upgrade Readiness and Compatibility Checks

Planning to upgrade SQL Server or ensure compatibility with newer versions and Azure targets?

SSMS includes Upgrade Assessment, which helps you:

- Identify deprecated or breaking changes
- Understand feature compatibility
- Reduce risks before upgrading or migrating

This is especially useful when modernizing older SQL Server versions or preparing for cloud migration.

Putting It All Together

With SSMS, SQL Server migration becomes:

- Guided – No guesswork on tools or methods

- Flexible – Supports Arc and non‑Arc environments
- Integrated – Local assessment + Azure portal workflows
- Modern – Designed for hybrid and cloud-first strategies

Whether you’re migrating a single database or planning a large-scale modernization, SSMS provides a unified starting point to get you there with confidence.

What’s Next?

Start with Assessment in SSMS
Choose your Azure target
Migrate using MI Link, Backup/Restore, or DMS
Validate compatibility using Upgrade Assessment

SSMS isn’t just a management tool anymore—it’s your launchpad for SQL Server modernization.

Learn More - Migrate SQL Server to Azure SQL | Microsoft Learn

Combining pgvector and Apache AGE - knowledge graph & semantic intelligence in a single engine

Raunak — Wed, 15 Apr 2026 11:59:07 GMT

Inspired by GraphRAG and PostgreSQL Integration in Docker with Cypher Query and AI Agents, which demonstrated how Apache AGE brings Cypher based graph querying into PostgreSQL for GraphRAG pipelines. This post takes that idea further combining AGE's graph traversal with pgvector's semantic search to build a unified analytical engine where vectors and graphs reinforce each other in a single PostgreSQL instance.

This post targets workloads where entity types, relationship semantics, and schema cardinality are known before ingestion. Embeddings are generated from structured attribute fields; graph edges are typed and written by deterministic ETL. No LLM is involved at any stage. You should use this approach when you have structured data and need operational query performance, and deterministic, auditable, sub-millisecond retrieval.

The problem nobody talks about the multi database/ multi hop tax

If you run technology for a large enterprise, you already know the data problem. It is not that you do not have enough data. It is that your data lives in too many places, connected by too many fragile pipelines, serving too many conflicting views of the same reality.

Here is a pattern that repeats across industries. One team needs to find entities "similar to" a reference item — not by exact attribute match, but by semantic meaning derived from unstructured text like descriptions, reviews, or specifications. That is a vector similarity problem.

Another team needs to traverse relationships trace dependency chains, map exposure paths, or answer questions like "if this node is removed, what downstream nodes are affected?" That is a graph traversal problem.

Meanwhile, the authoritative master data of IDs, attributes, pricing, transactional history already lives in Postgres.

Now you are operating three databases. Three bills. Three sets of credentials. Three backup strategies. A fragile ETL layer stitching entity IDs across systems, breaking silently whenever someone adds a new attribute to the master table. And worst of all, nobody can ask a question that spans all three systems without custom application code.

Azure PostgreSQL database can already do all three jobs. Two extensions pgvector for vector similarity search and Apache AGE extension for graph traversal bringing these capabilities natively into the database. No new infrastructure. No sync pipelines. No multi database tax!

This post walks through exactly how to combine them, why each piece matters at scale, and what kinds of queries become possible when you stop treating vectors and graphs as separate concerns.

The architecture: Two extensions, One engine

pgvector adds a native vector data type and distance operators (<=>, <->, <#>) with HNSW and IVFFlat index support.

pg_diskann adds a third index type that keeps the index on disk instead of in memory, enabling large scale vector search without proportional RAM.

example 1 - to run a product similarity query such as the one below which corelates products sold across multiple markets which are related (cosine similarity).

- The limit clause in sub query limits the similarity search to closest 1 product recommendation

- High similarity score of > 0.75 (aka 75% similarity in embeddings)

-- Table DDL - for illuatration purposes only CREATE TABLE IF NOT EXISTS products ( id SERIAL PRIMARY KEY, sku TEXT UNIQUE NOT NULL, name TEXT NOT NULL, brand TEXT NOT NULL, category TEXT NOT NULL, subcategory TEXT, market TEXT NOT NULL, region TEXT, description TEXT, ingredients TEXT, avg_rating FLOAT DEFAULT 0.0, review_count INT DEFAULT 0, price_usd FLOAT, launch_year INT, status TEXT DEFAULT 'active', embedding vector(384) );

SELECT us.name AS us_product, us.brand AS us_brand, in_p.name AS india_match, in_p.brand AS india_brand, Round((1 - (us.embedding <=> in_p.embedding))::NUMERIC, 4) AS similarity FROM products us cross join lateral ( SELECT name, brand, embedding FROM products WHERE market = 'India' AND category = us.category ORDER BY embedding <=> us.embedding limit 1 ) in_p WHERE us.market = 'US' AND us.category = 'Skincare' AND us.avg_rating >= 4.0 AND round((1 - (us.embedding <=> in_p.embedding))::NUMERIC, 4)> 0.75 ORDER BY similarity DESC limit 20;

AGE adds a cypher() function that executes cypher queries against a labeled property graph stored in the database managed and maintained under the ag_catalog schema. Vertices and edges become first class PostgreSQL rows with agtype properties.

The age extension supports MATCH, CREATE, MERGE, WITH, and aggregations.

example 2 - to run a product similarity query such as the one below which returns common products sold via multiple retail channels.

SET search_path = ag_catalog, "$user", public; SELECT * FROM cypher('cpg_graph', $$ MATCH (p:Product)-[:SOLD_AT]->(walmart:RetailChannel {name: 'Walmart'}) MATCH (p)-[:SOLD_AT]->(target:RetailChannel {name: 'Target'}) MATCH (b:Brand)-[:MANUFACTURES]->(p) RETURN b.name AS brand, p.name AS product, p.category AS category, p.market AS market, p.price_usd AS price ORDER BY p.category, b.name $$) AS (brand agtype, product agtype, category agtype, market agtype, price agtype);

The critical point and takeaway here is that both extensions participate in the same query planner and executor. A CTE that calls pgvector's <=> operator can feed results into a cypher() call in the next CTE all within a single transaction, sharing all available processes and control the database has to offer.

Finally, you are looking at code that looks like -

CREATE EXTENSION IF NOT EXISTS vector; CREATE EXTENSION IF NOT EXISTS age; SET search_path = ag_catalog, "$user", public; SELECT create_graph('knowledge_graph');

The bridge: pgvector → Apache AGE

This is the architectural centrepiece where the mechanism that turns vector similarity scores into traversable graph edges. Without this “bridge” pgvector and AGE are two isolated extensions.

Why bridge at all?

pgvector answers: "What is similar to X?" AGE answers: "What is connected to Y, and how?"

These are fundamentally different questions operating on fundamentally different data structures. pgvector works on a flat vector space and every query is a distance calculation against an ANN index.

AGE works on a labelled property graph where every query is a pattern match across typed nodes and edges.

What if now the question is – What is like X and connected to Y and how?

This is where the bridge gets activated comes into life.

This takes cosine similarity distance scores from pgvector and writes them as SIMILAR_TO edges in the AGE property graph turning a distance computation into a traversable relationship.

Once similarity is an edge, cypher queries can then combine it with structural edges in a single declarative pattern.

for ind_prod_id, us_prod_id, similarity in pairs: execute_cypher(cur, f""" MATCH (a:Product {{product_id: { ind_prod_id }}}), (b:Product {{product_id: { us_prod_id }}}) CREATE (a)-[:SIMILAR_TO {{score: {score:.4f}, method: 'pgvector_cosine'}}]->(b) CREATE (b)-[:SIMILAR_TO {{score: {score:.4f}, method: 'pgvector_cosine'}}]->(a) """)

The cypher() function translates Cypher into DML against ag_catalog tables under the hood, these are plain PostgreSQL heap inserts just like another row.

The score property is the edge weight on the SIMILAR_TO relationship. Its value is the similarity score computed from pgvector using cosine similarity, so a higher score means the two products are more semantically similar.

The method property is metadata on that same edge. It records how the score was produced. In this case, pgvector_cosine is just a string label indicating that the relationship was derived using pgvector based cosine similarity.

Cosine similarity is symmetric, but property graph traversal is directional i.e. MATCH (a)-[:SIMILAR_TO]->(b) won't find the reverse path unless both directional edges exist.

Why this combination matters

One backup strategy. One monitoring stack. One connection pool. One failover target. One set of credentials. One database restore considerations - for teams already running Az PostgreSQL databases in production this adds capabilities without adding any net new infrastructure.

Unified cost model

The planner assigns cost estimates to index scan for both execution engines using the same cost framework it uses for B-tree lookups and sequential scans. It can decide whether to use the HNSW index or fall back to a sequential scan based on table statistics and server parameters.

As you have learnt so far, there is no separate storage or database engine to learn.

Bringing all this knowledge together

Examples 1 and 2 were all about native vector search and native graph search example in a classic product catalog scenario, respectively. Now, let’s bring this to life - What if now the question is – What is like X and connected to Y and how?

In this use case - pgvector finds the cross market matches (as shown in example 1), then Cypher checks which of those matches are sold at both Walmart and Target:

SET search_path = ag_catalog, "$user", public; -- Cross-market matching (pgvector) → Retail channel overlap (graph) WITH cross_market AS ( SELECT us.id AS us_id, us.name AS us_product, us.brand AS us_brand, in_p.id AS india_id, in_p.name AS india_match, in_p.brand AS india_brand, ROUND((1 - (us.embedding <=> in_p.embedding))::numeric, 4) AS similarity FROM products us CROSS JOIN LATERAL ( SELECT id, name, brand, embedding FROM products WHERE market = 'India' AND category = us.category ORDER BY embedding <=> us.embedding LIMIT 1 ) in_p WHERE us.market = 'US' AND us.category = 'Skincare' AND us.avg_rating >= 4.0 AND ROUND((1 - (us.embedding <=> in_p.embedding))::numeric, 4) > 0.75 ), dual_channel AS ( SELECT (pid::text)::int AS product_id, brand::text AS brand FROM cypher('cpg_graph', $$ MATCH (p:Product)-[:SOLD_AT]->(w:RetailChannel {name: 'Walmart'}) MATCH (p)-[:SOLD_AT]->(t:RetailChannel {name: 'Target'}) MATCH (b:Brand)-[:MANUFACTURES]->(p) RETURN p.product_id AS pid, b.name AS brand $$) AS (pid agtype, brand agtype) ) SELECT cm.us_product, cm.us_brand, cm.india_match, cm.india_brand, cm.similarity, CASE WHEN dc.product_id IS NOT NULL THEN 'Yes' ELSE 'No' END AS india_match_at_walmart_and_target FROM cross_market cm LEFT JOIN dual_channel dc ON dc.product_id = cm.india_id ORDER BY cm.similarity DESC LIMIT 20;

Conclusion

The Azure PostgreSQL database ecosystem has quietly assembled the components for a unified semantic + structural analytics engine in form of extensions.

pgvector with pg_diskann delivers production grade approximate nearest-neighbour search with ANN indexes.

Apache AGE delivers cypher based property graph traversal. Together with a “bridge,” they enable query patterns that are impossible in either system alone and they do it within the ACID guarantees, operational tooling, and SQL vocabulary knowledge you already have.

Stop paying for three databases when one will do!

Monitoring Azure SQL Data Sync Errors Using PowerShell

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

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

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

❗ Lack of proactive monitoring for sync failures or errors

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

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

Why Monitoring Azure SQL Data Sync Matters

Azure SQL Data Sync works by synchronizing data between:

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

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

Monitoring these logs enables:

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

Prerequisites

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

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

Install and import the required module if not already available:

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

Authenticate to Azure:

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

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

Monitoring Sync Group Status

To retrieve Sync Group details, define the required variables:

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

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

Monitoring Sync Activity Using Logs (Recommended)

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

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

This command retrieves:

Sync operation timestamps
Sync status
Error messages
Activity details

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

Retrieving the Last Successful Sync Time

To determine the most recent successful synchronization operation:

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

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

Filtering for Synchronization Errors

To identify failed or problematic sync operations:

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

Filtering logs by error type allows for:

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

Key Takeaways

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

References

Cascading Read Replicas Now Generally Available!

gauri-kasar — Mon, 13 Apr 2026 17:23:08 GMT

We’re excited to announce the General Availability of cascading read replicas in Azure Database for PostgreSQL. This capability allows you to create read replicas for your Azure Database for PostgreSQL instance not only from a primary server, but also from existing read replicas, enabling multi‑level replication chains.

Coordinating read‑heavy database workloads across multiple regions can be challenging, especially when you’re trying to deliver low‑latency read response experiences to users spread across different geographic locations. One effective way to address this is by placing read replicas closer to where your users are, allowing applications to serve read requests with significantly reduced latency and improved performance.

What are cascading read replicas?

With cascading read replicas, you can scale read‑intensive workloads more effectively, distribute read traffic efficiently, and support advanced deployment topologies such as globally distributed applications. Each read replica can act as a source for additional replicas, forming a tree‑like replication structure. For example, if your primary server is deployed in one region, you can create direct replicas in nearby regions and then cascade additional replicas to more distant locations. This approach helps spread read traffic evenly while minimizing latency for users around the world. We support up to 2 levels of replication with this feature. Level 1 will be all the read replicas and level 2 will be cascading read replicas.

Why use cascading read replicas?

Improved scalability
Cascading read replicas support multi‑level replication, making it easier to handle high volumes of read traffic without overloading a single instance by scaling up to 30 read replicas.
Geographic distribution
By placing replicas closer to your global user base, you can significantly reduce read latency and deliver faster, more responsive application experiences.
Efficient read traffic distribution
Distributing read workloads across multiple replicas helps balance load, improving overall performance and reliability.

Additionally, cascading read replicas offer operational flexibility. If you observe replication lag, you can perform a switchover operation between a cascading read replica with its source or intermediate replica, helping you maintain optimal performance and availability for your replicas.

How does replication work with cascading read replicas?

The primary server acts as a source for the read replica. Data is asynchronously replicated to these replicas. When we add cascading replicas, the previous replicas act as a data source for replication.

In the diagram above, “primary-production-server” is the primary server with three read replicas. One of these replicas, “readreplica01”, serves as the source for another read replica, “readreplica11” which is a cascading read replica.

With cascading read replicas, you can add up to five read replicas per source and replicate data across two levels, as shown in the diagram. This allows you to create up to 30 read replicas in total five read replicas directly from the primary server, and up to 25 additional replicas at the second level (each second-level replica can have up to five read replicas).

If you notice replication lag between an intermediate read replica and a cascading read replica, you can use a switchover operation to swap “readreplica01” and “readreplica11”, helping reduce the impact of lag.

To learn more about cascading read replicas, please refer to our documentation: Cascading read replicas

Deploying cascading read replicas on Azure portal

Navigate to the “Replication” tab and then click on “Create replica” highlighted in red as shown below:
After creating a read replica as the below screenshot shows that you have 1 read replica that is attached to the primary instance.
Click on the created replica and navigate to the replication tab, source server is “read-replica-01” and we will be creating a cascading read replica under this.
Once cascading read replica is created you can see the role of “read-replica-01” has now changed to Source, Replica. You can perform site swap operation by clicking on the promote button for cascading read replica.

Deploy cascading read replica with terraform:

Before you start, make sure you have:

An existing primary PostgreSQL Flexible Server
At least one read replica already created from the primary
AzureRM provider with latest version
Proper permissions on the Azure subscription and resource group

Configure the AzureRM Provider: Start by configuring the AzureRM provider in your Terraform project.terraform { required_providers { azurerm = { source = "hashicorp/azurerm" version = "~> 3.80" } } } provider "azurerm" { features {} }
Reference the existing read replica server using the data block to reference the replica server.
data "azurerm_postgresql_flexible_server" "source_replica" { name = "my-read-replica-1" resource_group_name = "my-resource-group" }
Now create a new PostgreSQL Flexible Server and point it to the replica using create_source_server_id.
resource "azurerm_postgresql_flexible_server" "cascading_replica" { name = "my-cascading-replica" resource_group_name = "my-resource-group" location = data.azurerm_postgresql_flexible_server.source_replica.location version = data.azurerm_postgresql_flexible_server.source_replica.version delegated_subnet_id = data.azurerm_postgresql_flexible_server.source_replica.delegated_subnet_id private_dns_zone_id = data.azurerm_postgresql_flexible_server.source_replica.private_dns_zone_id create_mode = "Replica" create_source_server_id = data.azurerm_postgresql_flexible_server.source_replica.id storage_mb = 32768 sku_name = "Standard_D4s_v3" depends_on = [ data.azurerm_postgresql_flexible_server.source_replica ] }
Apply the Terraform Configuration
terraform init terraform plan terraform apply

Key Considerations

Cascading read replicas allow for up to 5 read replicas and two levels of replication.
Creating cascading read replicas is supported in PostgreSQL version 14 and above.
Promote operation is not supported for intermediate read replicas with cascading read replicas.

Conclusion

Cascading read replicas in Azure Database for PostgreSQL offer a scalable way to distribute your read traffic across the same and different regions, reducing the read workload on primary database. For globally distributed applications, this can improve read latency as well as resilience and performance. This design supports horizontal scaling as your application demand grows, ensuring you can handle a high volume of read requests without compromising speed. Get started with this feature today and scale your read workloads.

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

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

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

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

Symptom

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

Error

You may see an error similar to:

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

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

What’s actually happening (Root Cause)

1) Initial sync uses temp files on the agent machine

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

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

In practice, you’ll often see:

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

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

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

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

Mitigation (Recommended)

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

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

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

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

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

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

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

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

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

General Windows guidance indicates:

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

A common approach is:

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

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

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

Validation checklist (after the change)

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

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

Classification

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

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

Helpful references

Unlock real-time intelligence with Azure Managed Redis

Matthew_Burrows — Wed, 08 Apr 2026 16:00:00 GMT

Modern applications are no longer just transactional—they’re intelligent, conversational, and agent-driven. As organizations build copilots and autonomous agents, infrastructure requirements evolve. Applications must reason over context, retrieve knowledge instantly, and operate in real time.

Azure Managed Redis is a fully-managed, enterprise-grade, in-memory data platform powered by Redis Enterprise and operated by Microsoft. It combines sub-millisecond performance, high availability, and advanced capabilities such as vector similarity search and multimodal data support.

In this post, we’ll explore four key industry use cases for Azure Managed Redis—starting with building AI agents on modern agent frameworks.

1) Building AI agents on agent frameworks

Agentic applications require fast memory, contextual retrieval, and low-latency orchestration. Azure Managed Redis provides the real-time data foundation for AI agents built on Microsoft Agent Framework, as well as LangChain and Azure AI services like Azure AI Foundry.

Why Azure Managed Redis for AI agents?

AI agents rely on:

Vector similarity search for retrieval-augmented generation (RAG)
Semantic caching to avoid repeated LLM calls
Short-term memory for conversation state
Long-term memory for preferences
Real-time context updates from tools and APIs

Azure Managed Redis enables all of these within a single in-memory data engine.

Common Agent Scenarios

Customer support agents retrieving knowledge base articles instantly
Marketing copilots generating campaign insights using cached data
Developer assistants accessing documentation with vector search
Operational agents responding to real-time telemetry

With vector indexing and JSON support, Redis can store embeddings alongside structured application data. Agents can retrieve relevant context in milliseconds, dramatically reducing latency and cost while improving response quality.

For organizations adopting Microsoft’s AI ecosystem, Azure Managed Redis integrates seamlessly with Azure networking, identity, and compute services—making it a natural fit for production-grade agent architectures.

2) High-performance caching for modern applications

Caching remains one of the most powerful patterns for improving performance and scalability.

Applications backed by systems such as Azure SQL Database or Azure Database for PostgreSQL benefit from a high-speed caching layer to reduce repeated reads and minimize backend load.

Azure Managed Redis enables:

Microsecond data retrieval
Reduced database pressure
Improved application throughput
Lower infrastructure costs

Industry examples

Retail & e-commerce: Cache product catalogs and pricing during peak events
Financial services: Cache market data and pricing models
Gaming: Store leaderboards and player states

By inserting Redis between your application and your system of record, you gain immediate responsiveness without sacrificing durability.

3) Real-time analytics and streaming workloads

For organizations that depend on real-time insight, in-memory processing is critical.

Azure Managed Redis supports data structures such as Streams, Sorted Sets, JSON, and TimeSeries to enable near real-time analytics.

Key Scenarios

Fraud detection in financial services
IoT telemetry ingestion and analysis
Live dashboards for logistics and operations
Dynamic pricing engines in retail

Because processing happens in memory, latency remains predictable—even at scale.

Streaming data can be ingested into Redis, enriched, scored, and surfaced to applications or dashboards immediately—without waiting for batch systems.

4) Session management at scale

As applications scale horizontally, managing session state becomes increasingly complex.

Azure Managed Redis provides a centralized, highly available session store that enables:

Shared state across distributed app instances
Automatic expiration with TTL
Seamless failover and high availability

Common Scenarios

E-commerce platforms preserving shopping carts
Media platforms tracking user preferences
Enterprise SaaS applications managing authentication tokens

By externalizing session state to Redis, applications become stateless and cloud-native—ready for autoscaling and global distribution.

Enterprise-grade foundation for intelligent applications

Azure Managed Redis delivers:

99.999% availability SLA
Zone redundancy by default
Automatic patching and updates
Advanced security integration such as Entra ID authentication and private endpoints
Native compatibility with modern Azure architectures

Whether you're building AI agents, modernizing transactional applications, or enabling real-time analytics, Azure Managed Redis provides the in-memory data foundation required for intelligent, responsive systems.

Getting started

Ready to start building real-time and agent-driven applications with Azure Managed Redis? Explore the resources below to learn more, deploy your first instance, and experiment with AI-powered scenarios.

Learn more:

Try it yourself:

Explore samples and architectures:

Connect with the community:

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

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

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

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

Sample Error:

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

Scenario Overview

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

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

Further investigation revealed the following SQL exception:

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

Initial Troubleshooting Steps

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

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

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

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

Root Cause Analysis

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

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

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

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

Mitigation Approach

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

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

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

Best Practices to Avoid Similar Issues

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

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

Final Thoughts

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

Premium SSD v2 Is Now Generally Available for Azure Database for PostgreSQL

kabharati — Tue, 07 Apr 2026 17:29:16 GMT

We are excited to announce the General Availability (GA) of Premium SSD v2 for Azure Database for PostgreSQL flexible server. With Premium SSD v2, you can achieve up to 4× higher IOPS, significantly lower latency, and better price-performance for I/O-intensive PostgreSQL workloads. With independent scaling of storage and performance, you can now eliminate overprovisioning and unlock predictable, high-performance PostgreSQL at scale.

This release is especially impactful for OLTP, SaaS, and high‑concurrency applications that require consistent performance and reliable scaling under load.

In this post, we will cover:

Why Premium SSD v2: Core capabilities such as flexible disk sizing, higher performance, and independent scaling of capacity and I/O.
Premium SSD v2 vs. Premium SSD: A side‑by‑side overview of what’s new and what’s improved.
Pricing: Pricing estimates.
Performance: Benchmarking results across two workload scenarios.
Migration options: How to move from Premium SSD to Premium SSD v2 using restore and read‑replica approaches.
Availability and support: Regional availability, supported features, current limitations, and how to get started.

Why Premium SSD v2?

Flexible Disk Size - Storage can be provisioned from 32 GiB to 64 TiB in 1 GiB increments, allowing you to pay only for required capacity without scaling disk size for performance.
High Performance -Achieve up to 80,000 IOPS and 1,200 MiB/s throughput on a single disk, enabling high-throughput OLTP and mixed workloads.
Adapt instantly to workload changes: With Premium SSD v2, performance is no longer tied to disk size. Independently tune IOPS and throughput without downtime, ensuring your database keeps up with real-time demand.
Free baseline performance: Premium SSD v2 includes built-in baseline performance at no additional cost. Disks up to 399 GiB automatically include 3,000 IOPS and 125 MiB/s, while disks sized 400 GiB and larger include up to 12,000 IOPS and 500 MiB/s.

Premium SSD v2 vs. Premium SSD: What’s new?

Pricing

Pricing for Premium SSD v2 is similar to Premium SSD, but will vary depending on the storage, IOPS, and bandwidth configuration set for a Premium SSD v2 disk. Pricing information is available on the pricing page or pricing calculator.

Performance

Premium SSD v2 is designed for IO‑intensive workloads that require sub‑millisecond disk latencies, high IOPS, and high throughput at a lower cost. To demonstrate the performance impact, we ran pgbench on Azure Database for PostgreSQL using the test profile below.

Test Setup

To minimize external variability and ensure a fair comparison:

Client virtual machines and the database server were deployed in the same availability zone in the East US region.
Compute, region, and availability zones were kept identical.
The only variable changed was the storage tier.
TPC-B benchmark using pgbench with a database size of 350 GiB.

Test Scenario 1: Breaking the IOPS Ceiling with Premium SSD v2

Premium SSD v2 eliminates the traditional storage bottleneck by scaling linearly up to 80,000 IOPS, while Premium SSD plateaus early due to fixed performance limits. To demonstrate this, we configured each storage tier with its maximum supported IOPS and throughput while keeping all other variables constant. Premium SSD v2 achieves up to 4x higher IOPS at nearly half the cost, without requiring large disk sizes.

Note: Premium SSD requires a 32 TiB disk to reach 20K IOPS, while SSD v2 achieves 80K IOPS even on a 160 GiB disk though we used 1 TiB disk in this test for a bigger scaling factor for pgbench test.

We ran pgbench across five workload profiles, ranging from 32 to 256 concurrent clients, with each test running for 20 minutes. The results go beyond incremental improvements and highlight a material shift in how applications scale with Premium SSD v2.

Throughput Scaling

As concurrency increases, Premium SSD quickly reaches its IOPS limits while Premium SSD v2 continues to scale.

At 32 clients: Premium SSD v2 achieved 10,562 TPS vs 4,123 TPS on Premium SSD representing a 156% performance improvement.
At 256 clients: At higher load, Premium SSD v2 achieved over 43,000 TPS representing a 279% improvement compared to the 11,465 TPS observed on Premium SSD.

Latency Stability

Throughput is an indication of how much work is done while latency reflects how quickly users experience it. Premium SSD v2 maintains consistently low latency even as workload increases.

Reduced Wait Times: 61–74% lower latency across all test phases.
Consistency under Load: Premium SSD latency increased to 22.3 ms, while Premium SSD v2 maintained a latency of 5.8 ms, remaining stable even under peak load.

IOPS Behavior

The table below illustrates the IOPS behavior observed during benchmarking for both storage tiers.

Dimension	Premium SSD	Premium SSD v2
IOPS	Lower baseline performance, Hits limits early	~2× higher IOPS at low concurrency,
IOPS	Lower baseline performance, Hits limits early	Up to 4× higher IOPS at peak load
IOPS Plateau	Throughput stalls at ~20k IOPS for 64 clients -256 clients	Scales from ~29k IOPS (32 clients) to ~80k IOPS (256 clients)
Additional Clients	Adding clients does not increase throughput	Additional clients continue to drive higher throughput
Primary Bottleneck	Storage becomes the bottleneck early	No single bottleneck observed
Scaling Behavior	Stops scaling early	True linear scaling with workload demand
Resource Utilization	Disk saturation leaves CPU and memory underutilized	Balanced utilization across IOPS, CPU, and memory
Key Takeaway	Storage limits performance before compute is fully used	Unlocks higher throughput and lower latency by fully utilizing compute resources

Test Scenario 2: Better Performance at same price

At the same price point, Premium SSD v2 delivers higher throughput and lower latency than Premium SSD without requiring any application changes. To demonstrate this, we ran multiple pgbench tests using two workload configurations 8 clients / 8 threads and 32 clients / 32 threads with each run lasting 20 minutes. Results were consistent across all runs, with Premium SSD v2 consistently outperforming Premium SSD. Both configurations cost $578/month, the only difference is storage performance.

Results:

Moderate concurrency (8 clients)
Premium SSD v2 delivered approximately 154% higher throughput (Transactions Per Second) than Premium SSD (1,813 TPS vs. 715 TPS), while average latency decreased by about 60% (from ~11.1 ms to ~4.4 ms).

High concurrency (32 clients)
The performance gap increases as concurrency grows, Premium SSD v2 delivered about 169% higher throughput than Premium SSD (3,643 TPS vs. ~1,352 TPS) and reduced average latency by around 67% (from ~26.3 ms to ~8.7 ms).

IOPS Behavior

In the 8‑client, 8‑thread test, Premium SSD reached its IOPS ceiling early, operating at 100% utilization, while Premium SSD v2 retained approximately 30% headroom under the same workload delivering 8,037 IOPS vs 3,761 IOPS with Premium SSD.
When the workload increased to 32 clients and 32 threads, both tiers approached their IOPS limits however, Premium SSD v2 sustained a significantly higher performance ceiling, delivering approximately 2.75x higher IOPS (13,620 vs. 4,968) under load.

Key Takeaway: With Premium SSD v2, you do not need to choose between cost and performance you get both. At the same price, applications run faster, scale further, and maintain lower latency without any code changes.

Migrate from Premium SSD to Premium SSD v2

Migrating is simple and fast. You can migrate from Premium SSD to Premium SSD v2 using the two strategies below with minimal downtime. These methods are generally quicker than logical migration strategies, such as exporting and restoring data using pg_dump and pg_restore.

When migrating from Premium SSD to Premium SSD v2, using a virtual endpoint helps keep downtime to a minimum and allows applications to continue operating without requiring configuration changes after the migration.

After the migration completes, you can stop the original server until your backup requirements are met. Once the required backup retention period has elapsed and all new backups are available on the new server, the original server can be safely deleted.

Region Availability & Features Supported

Premium SSD v2 is available in 48 regions worldwide for Azure Database for PostgreSQL – Flexible Server. For the most up‑to‑date information on regional availability, supported features, and current limitations, refer to the official Premium SSD v2 documentation.

Getting Started:

To learn more, review the official documentation for storage configuration available with Azure Database for PostgreSQL. Your feedback is important to us, have suggestions, ideas, or questions? We would love to hear from you: https://aka.ms/pgfeedback.

Azure SQL is Deprecating the “No Minimum TLS” (MinTLS None) Configuration

talawren — Mon, 06 Apr 2026 20:27:03 GMT

As part of the retirement of lower TLS versions 1.0 and 1.1 and the enforcement of 1.2 as the new default minimum TLS version, we will be removing the No Minimum TLS (MinTLS = “None” or "0") option and updating these configurations to TLS 1.2.

No Minimum TLS allowed Azure SQL Database and Azure SQL Managed Instance resources to accept client connections using any TLS protocol version and unencrypted connections.

Over the past year, Azure has retired TLS 1.0 and 1.1 for all Azure databases, due to known security vulnerabilities in these older protocols. As of August 31, 2025, creating servers configured with versions 1.0 and 1.1 was disallowed and migration to 1.2 began. With legacy TLS versions being deprecated, TLS 1.2 will become the secure default minimum TLS version for new Azure SQL DB and MI configurations and for all client-server connections, rendering the MinTLS = None setting obsolete. As a result, the MinTLS = None configuration option will be deprecated for new servers, and existing servers configured with No Minimum TLS will be upgraded to 1.2.

What is changing?

After July 15, 2026, we will disallow minimum TLS value "None", for the creation of new SQL DB and MI resources using PowerShell, Azure CLI, and any other REST based interface. This configuration option has already been removed from the Portal during the deprecation of TLS versions 1.0 and 1.1.

Creating new Azure SQL Database and Managed Instance servers with MinTLS = None (which was previously considered the default) will no longer be a supported configuration. If the server parameter value for the minimum TLS is left blank, it will default to minimum TLS version 1.2.

Attempts to create an Azure SQL server with MinTLS = None will fail with an “Invalid operation” error and downgrades to None will be disallowed. While attempts to connect with TLS 1.0, 1.1 or unencrypted connections will fail with “Error: 47072/171 on Gateway.”

Effective date (retirement milestone)	MinTLS = None (0)	MinTLS left blank (defaults to supported minimum)
Before 8/31/25	Any + Unencrypted	Any + Unencrypted
After 8/31/25	1.2 + Unencrypted	1.2
After July 15, 2026	Invalid operation error (for new server creates) Downgrades will be disallowed TLS error: 47072/171 (for unencrypted connections)	1.2

In summary, after July 15, 2026, Azure SQL Database and Azure SQL Managed Instance will require all client connections to use TLS 1.2 or higher and unencrypted connections will be denied. The minimum TLS version setting will no longer accept the value "None" for new or existing servers and servers currently configured with this value will be upgraded to explicitly enforce TLS 1.2.

Who is impacted?

For most Azure SQL customers, there is no action required. Most clients already use TLS 1.2 or higher. After July 15, 2026, if your Azure SQL Database or Managed Instance is still configured with No Minimum TLS and using 1.0, 1.1 or unencrypted connections, it will automatically update to TLS 1.2 to reflect the current minimum protocol enforcement in client-server connectivity.

We do recommend you verify your client applications – especially any older or third-party client drivers – to ensure they can communicate with TLS 1.2 or above. In some rare cases, very old applications, such as an outdated JDBC driver or older .NET framework version, may need an update or need to enable TLS 1.2.

Conclusion

This deprecation is part of Azure’s broader security strategy to ensure encrypted connections are secure by modern encryption standards. TLS version 1.2 is more secure than older versions and is now the industry standard (required by regulations like PCI DSS and HIPAA). This change eliminates the use of unencrypted connections which ensure all database connections meet current security standards.

If you’ve already migrated to TLS 1.2 (as most customers have), you will most likely not notice any change, except that the No Minimum TLS option will disappear from configurations.

Azure SQL Audit Columns missing? Understanding AzureDiagnostics 500-Column Limit and Spill

Ashriti_Jamwal — Mon, 06 Apr 2026 07:11:20 GMT

👋 Introduction

Have you ever written a perfectly valid KQL query against the AzureDiagnostics table — only to find that one or more columns return empty results or don't exist at all? You double-check the column name, verify the resource filter, and everything looks right — yet the data simply isn't there.

This is one of the more silent and confusing behaviors of the AzureDiagnostics table in Azure Monitor Log Analytics, and it doesn't just affect one specific column — it can affect any column, including commonly queried ones like data_sensitivity_information_s, deadlock_xml_s, query_hash_s, error_message_s, and others.

In this post, I'll explain exactly why this happens, how to detect it, how to write resilient KQL queries that always retrieve your data.

🔍 The Scenario: Two Queries, Two Different Outcomes

Here's a real-world example from a customer case involving Azure SQL audit logs and data sensitivity classification.

Query 1 — The "Non-Working" Query:

AzureDiagnostics | where Category == 'SQLSecurityAuditEvents' | where ResourceId == '/SUBSCRIPTIONS/<SubscriptionID>/RESOURCEGROUPS/<RG>/PROVIDERS/MICROSOFT.SQL/SERVERS/<ServerName>/DATABASES/<DBName>' | project event_time_t, statement_s, succeeded_s, affected_rows_d, server_principal_name_s, client_ip_s, application_name_s, additional_information_s, data_sensitivity_information_s | order by event_time_t desc | take 100

data_sensitivity_information_s returns empty — or the column doesn't exist at all in the schema.

Not only restricted to one column as you see. Another column "affected_rows_d" failed to resolve.

Query 2 — The "Working" Query:

AzureDiagnostics | where Category == "SQLSecurityAuditEvents" | where ResourceId == "/SUBSCRIPTIONS/<SubscriptionID>/RESOURCEGROUPS/<RG>/PROVIDERS/MICROSOFT.SQL/SERVERS/<ServerName>/DATABASES/<DBName>" | extend DataSensitivityInfo = tostring(parse_json(AdditionalFields).data_sensitivity_information) | where isnotempty(DataSensitivityInfo) | project event_time_t, DataSensitivityInfo | take 100

This one works perfectly. The difference? It reads from AdditionalFields instead of directly from the column.

💡 Important: This is NOT a bug with data_sensitivity_information_s specifically. This behavior can happen to any column in the AzureDiagnostics table — and understanding why is key to writing reliable audit queries.

🧩 Root Cause: The AzureDiagnostics 500-Column Limit — A Default Platform Behavior

The AzureDiagnostics table in Azure Monitor Log Analytics is a shared, multi-resource table. It collects diagnostic data from every Azure resource type that sends logs to your workspace — Azure SQL, Key Vault, App Service, Storage Accounts, Firewalls, and many more. Each resource type contributes its own set of columns, all landing in this single table.

Because of this design, the AzureDiagnostics table enforces a hard limit of 500 columns per workspace. This is a default, platform-wide behavior — not specific to any one Azure service or column and is designed to avoid any data loss due to the number of active columns exceeding this 500 column limit.

Here's what happens when that limit is reached:

Workspace Column Count	Behavior
< 500 columns	New columns are created normally (e.g., data_sensitivity_information_s)
≥ 500 columns	Any new or overflow column data is silently redirected into a dynamic JSON property bag called AdditionalFields

⚠️ This redirection is completely silent. No error is thrown. No warning appears in your query results. The data is NOT lost — it's simply stored differently, inside AdditionalFields as a JSON key-value pair.

This means that in a busy Log Analytics workspace shared across many Azure resource types, virtually any column that exceeds the 500-column threshold will have its data moved to AdditionalFields — whether it's data_sensitivity_information_s, deadlock_xml_s, query_hash_s, error_message_s, or any other field your resource type emits.

⚠️ "Because AzureDiagnostics is a single shared table, all Azure resource types sending logs to your workspace — Azure Firewall, Key Vault, App Service, AKS, and others — contribute their columns to the same 500-column pool. As Microsoft's documentation notes, this is precisely why AzureDiagnostics is 'much more susceptible to exceeding the 500-column limit' compared to other tables — your SQL columns can spill even if your SQL workload alone would never breach the limit."

🔎 Step 1: Diagnose — Has Your Workspace Hit the 500-Column Limit?

Run this query in your Log Analytics workspace to check the current column count:

// Check total column count in AzureDiagnostics AzureDiagnostics | getschema | summarize TotalColumns = count(), RemainingCapacity = 500 - count(), LimitReached = iff(count() >= 500, "⚠️ YES - Data is going to AdditionalFields", "✅ NO - Under the limit")

Then check whether a specific column you're looking for exists in the schema at all:

// Replace the your_column_name with any column you're investigating AzureDiagnostics | getschema | where ColumnName == "your_column_name"

If this returns no rows, that column's data has been redirected to AdditionalFields. The same check applies to any column you suspect is missing.

Use this query to discover all fields currently spilled to AdditionalFields for SQL audit events:

✅ Step 2: Fix — Use AdditionalFields to Retrieve Any Spilled Column

Once you've confirmed the spill, update your queries to extract the missing field from AdditionalFields using parse_json. This approach works for any column that has been redirected.

Recommended Pattern — Resilient to Both States:

AzureDiagnostics | where Category == "SQLSecurityAuditEvents" | extend DataSensitivityInfo = coalesce(data_sensitivity_information_s, tostring(parse_json(AdditionalFields).data_sensitivity_information)), QueryHash = coalesce(query_hash_s, tostring(parse_json(AdditionalFields).query_hash)), AdditionalInfo = coalesce(additional_information_s, tostring(parse_json(AdditionalFields).additional_information)) | project event_time_t, statement_s, succeeded_s, server_principal_name_s, client_ip_s, application_name_s, DataSensitivityInfo, QueryHash, AdditionalInfo | order by event_time_t desc | take 100

A shorter version:

💡 coalesce() tries the direct column first. If null, it falls back to AdditionalFields. This makes your query portable across workspaces — whether at the limit or not.

📚 References

Expanding Azure Arc SQL Migration with a New Target: SQL Server on Azure Virtual Machines

danimir — Fri, 03 Apr 2026 22:55:58 GMT

Modernizing a SQL Server estate is rarely a single-step effort. It typically involves multiple phases, from discovery and assessment to migration and optimization, often spanning on-premises, hybrid, and cloud environments. SQL Server enabled by Azure Arc simplifies this process by bringing all migration steps into a single, cohesive experience in the Azure portal.

With the March 2026 release, this integrated experience is extended by adding SQL Server on Azure Virtual Machines as a new migration target in Azure Arc. Arc-enabled SQL Server instances can now be migrated not only to Azure SQL Managed Instance, but also to SQL Server running on Azure infrastructure, using the same unified workflow.

Expanding Choice Without Adding Complexity

By introducing SQL Server on Azure Virtual Machines as a migration target, Azure Arc now supports a broader range of migration strategies while preserving a single operational model. It becomes possible to choose between Azure SQL Managed Instance and SQL Server on Azure VMs without fragmenting migration tooling or processes.

The result is a flexible, scalable, and consistent migration experience that supports hybrid environments, reduces operational overhead, and enables modernization at a controlled and predictable pace.

One Integrated Migration Journey

A core value of SQL Server migration in Azure Arc is that the entire migration lifecycle is managed from one place. Once a SQL Server instance is enabled by Azure Arc, readiness can be assessed, a migration target selected, a migration method chosen, progress monitored, and cutover completed directly in the Azure portal.

This approach removes the need for disconnected tools or custom orchestration. The only prerequisite remains unchanged: the source SQL Server needs to be enabled by Azure Arc. From there, migration is fully integrated into the Azure Arc SQL experience.

A Consistent Experience Across Migration Targets

The migration experience for SQL Server on Azure Virtual Machines follows the same model already available for Azure SQL Managed Instance migrations in Azure Arc. The same guided workflow, migration dashboard, and monitoring capabilities are used regardless of the selected target.

This consistency is intentional. It allows teams to choose the destination that best fits their technical, operational, or regulatory requirements without having to learn a new migration process. Whether migrating to a fully managed PaaS service or to SQL Server on Azure infrastructure, the experience remains predictable and familiar.

Backup Log Shipping Migration to SQL Server in Azure VM

Migration to SQL Server on Azure Virtual Machines is based on backup and restore, specifically using log shipping mechanism. This is a well-established approach for online migrations that minimizes downtime while maintaining control over the cutover window.

In this model, database backups need to be uploaded from the source SQL Server to Azure Blob Storage. The migration engine will restore the initial full backup followed by ongoing transaction log and diff. backups. Azure Blob Storage acts as the intermediary staging location between the source and the target.

The Azure Blob Storage account and the target SQL Server running on an Azure Virtual Machine must be co-located in the same Azure region. This regional alignment is required to ensure efficient data transfer, reliable restore operations, and predictable migration performance.

Within the Azure Arc migration experience, a simple and guided UX is used to select the Azure Blob Storage container that holds the backup files. Both the selected storage account and the Azure VM hosting SQL Server must reside in the same Azure region.

Once the migration job is started, Azure Arc automatically restores the backup files to SQL Server on the Azure VM. As new log backups are uploaded to Blob Storage, they are continuously detected and applied to the target database, keeping it closely synchronized with the source.

Controlled Cutover on Your Terms

This automated restore process continues until the final cutover is initiated. When the cutover command is issued, Azure Arc applies the final backup to the target SQL Server on the Azure Virtual Machine and completes the migration.

The target database is then brought online, and applications can be redirected to the new environment. This controlled cutover model allows downtime to be planned precisely, rather than being dictated by long-running restore operations.

Getting started

To get started, Arc enable you SQL Server. Then, in the Azure portal, navigate to your Arc enabled SQL Server and select Database migration under the Migration menu on the left. For more information, see the SQL Server migration in Azure Arc documentation.

Connect to Azure SQL Database using a custom domain name with Microsoft Entra ID authentication

Sunil-Nair — Fri, 03 Apr 2026 11:02:36 GMT

Many of us might prefer to connect to Azure SQL Server using a custom domain name (like devsqlserver.mycompany.com) rather than the default fully qualified domain name (devsqlserver.database.windows.net), often because of application-specific or compliance reasons. This article details how you can accomplish this when logging in with Microsoft Entra ID (for example, user@mycompany.com) in Azure SQL Database specific environment. Frequently, users encounter errors similar to the one described below during this process.

Before you start: If you use SQL authentication (SQL username/password), the steps are different. Refer the following article for that scenario:

How to use different domain name to connect to Azure SQL DB Server | Microsoft Community Hub

With SQL authentication, you can include the server name in the login (for example, username@servername). With Microsoft Entra ID authentication, you don’t do that—so your custom DNS name must follow one important rule.

Key requirement for Microsoft Entra ID authentication

In an Azure SQL Database (PaaS) environment, the platform relies on the server name portion of the Fully Qualified Domain Name (FQDN) to correctly route incoming connection requests to the appropriate logical server.

When you use a custom DNS name, it is important that the name starts with the exact Azure SQL server name (the part before .database.windows.net).

Why this is required:

Azure SQL Database is a multi-tenant PaaS service, where multiple logical servers are hosted behind shared infrastructure.
During the connection process (especially with Microsoft Entra ID authentication), Azure SQL uses the server name extracted from the FQDN to:

Identify the correct logical server
Route the connection internally within the platform
Validate the authentication context

This behavior aligns with how Azure SQL endpoints are designed and resolved within Microsoft’s managed infrastructure.

If your custom DNS name doesn’t start with the Azure SQL server name, Azure can’t route the connection to the correct server. Sign-in may fail and you might see error 40532 (as shown above). To fix this, change the custom DNS name so it starts with your Azure SQL server name.

Example: if your server is devsqlserver.database.windows.net, your custom name must start with 'devsqlserver'

devsqlserver.mycompany.com

devsqlserver.contoso.com

devsqlserver.mydomain.com

Step-by-step: set up and connect

Pick the custom name. It must start with your server name. Example: use devsqlserver.mycompany.com (not othername.mycompany.com).
Create DNS records for the custom name. Create a CNAME or DNS alias to point the custom name to your Azure SQL server endpoint (public) or to the private endpoint IP (private) as per the blog mentioned above.
Check DNS from your computer. Make sure devsqlserver.mycompany.com resolves to the right address before you try to connect.
Connect with Microsoft Entra ID. In SSMS/Azure Data Studio, set Server to your custom server name and select a Microsoft Entra ID authentication option (for example, Universal with MFA).
Sign in and connect. Use your Entra ID (for example, user@mycompany.com).

Example:

Also, when you connect to Azure SQL Database using a custom domain name, you might see the following error:

“The target principal name is incorrect”

Example:

This happens because Azure SQL’s SSL/TLS certificate is issued for the default server name (for example, servername.database.windows.net), not for your custom DNS name.

During the secure connection process, the client validates that the server name you are connecting to matches the name in the certificate. Since the custom domain does not match the certificate, this validation fails, resulting in the error.

This is expected behavior and is part of standard security checks to prevent connecting to an untrusted or impersonated server.

To proceed with the connection, you can configure the client to trust the server certificate by:

Setting Trust Server Certificate = True in the client settings, or
Adding TrustServerCertificate=True in the connection string

This bypasses the strict name validation and allows the connection to succeed.

Note: Please use the latest client drivers (ODBC/JDBC/.NET, etc.). In some old driver versions, the 'TrustServerCertificate' setting may not work properly, and you may still face connection issues with the same 'target principal name is incorrect' error. So, it is always better to keep drivers updated for smooth connectivity with Azure SQL.

Applies to both public and private endpoints: This naming requirement and approach work whether you connect over the public endpoint or through a private endpoint for Azure SQL Database scenario, as long as DNS resolution for the custom name is set up correctly for your network.

Handling Unique Constraint Conflicts in Logical Replication

gauri-kasar — Thu, 02 Apr 2026 16:33:58 GMT

Authors: Ashutosh Sharma, Senior Software Engineer, and Gauri Kasar, Product Manager

Logical replication can keep your PostgreSQL environments in sync, helping replicate selected tables with minimal impact on the primary workload. But what happens when your subscriber hits a duplicate key error and replication grinds to a halt? If you’ve seen a unique‑constraint violation while replicating between Azure Database for PostgreSQL servers, you’re not alone. This blog covers common causes, prevention tips, and practical recovery options.

In PostgreSQL logical replication, the subscriber can fail with a unique-constraint error when it tries to apply a change that would create a duplicate key.

duplicate key value violates unique constraint

Understanding why this happens?

When an INSERT or UPDATE would create a value that already exists in a column (or set of columns) protected by a UNIQUE constraint (including a PRIMARY KEY). In logical replication, this most commonly occurs because of local writes on the subscriber or if the table is being subscribed from multiple publishers. These conflicts are resolved on the subscriber side.

Local writes on the subscriber: a row with the same primary key/unique key is inserted on the subscriber before the apply worker processes the corresponding change from the publisher.
Multi-origin / multi-master without conflict-free keys: two origins generate (or replicate) the same unique key.
Initial data synchronization issues: the subscriber already contains data when the subscription is created with initial copy enabled, resulting in duplicate inserts during the initial table sync.

How to avoid this?

Avoid local writes on subscribed tables (treat the subscriber as read-only for replicated relations).
Avoid subscribing to the same table from multiple publishers unless you have explicit conflict handling and a conflict-free key design.

Enabling server logs can help you identify and troubleshoot unique‑constraint conflicts more effectively. Refer to the official documentation to configure and access PostgreSQL logs.

How to handle conflicts (recovery options)

Option 1: Delete the conflicting row on the subscriber

Use the subscriber logs to identify the key (or row) causing the conflict, then delete the row on the subscriber with a DELETE statement. Resume apply and repeat if more conflicts appear.

Option 2: Use conflict logs and skip the conflicting transaction (PostgreSQL 17+)

Starting with PostgreSQL 17, logical replication provides detailed conflict logging on the subscriber, making it easier to understand why replication stopped and which transaction caused the failure. When a replicated INSERT would violate a non‑deferrable unique constraint on the subscriber for example, when a row with the same key already exists the apply worker detects this as an insert_exists conflict and stops replication. In this case, PostgreSQL logs the conflict along with the transaction’s finish LSN, which uniquely identifies the failing transaction.

ERROR: conflict detected on relation "public.t2": conflict=insert_exists ... in transaction 754, finished at 0/034F4090 ALTER SUBSCRIPTION <subscription_name> SKIP (lsn = '0/034F4090');

Option 3: Rebuild (re-sync) the table

Rebuilding (re‑syncing) a table is the safest and most deterministic way to resolve logical replication conflicts caused by pre‑existing data differences or local writes on the subscriber. This approach is especially useful when a table repeatedly fails with unique‑constraint violations and it is unclear which rows are out of sync.

Step 1 (subscriber): Disable the subscription.

ALTER SUBSCRIPTION <subscription_name> DISABLE;

Step 2 (subscriber): Remove the local copy of the table so it can be re-copied.

TRUNCATE TABLE <conflicting_table>;

Step 3 (publisher): Ensure the publication will (re)send the table (one approach is to recreate the publication entry for that table).

ALTER PUBLICATION <pub_with_conflicting_table> DROP TABLE <conflicting_table>; CREATE PUBLICATION <pub_with_conflicting_table_rebuild> FOR TABLE <conflicting_table>;

Step 4 (subscriber): Create a new subscription (or refresh the existing one) to re-copy the table.

CREATE SUBSCRIPTION <sub_rebuild> CONNECTION '<connection_string>' PUBLICATION <pub_with_conflicting_table_rebuild>;

Step 5 (subscriber): Re-enable the original subscription (if applicable).

ALTER SUBSCRIPTION <subscription_name> ENABLE;

Conclusion

In most cases, these conflicts occur due to local changes on the subscriber or differences in data that existed before logical replication was fully synchronized. It is recommended to avoid direct modifications on subscribed tables and ensure that the replication setup is properly planned, especially when working with tables that have unique constraints.

Script to Export All Azure SQL Whitelisted Public IPs Within Your Subscription

Anuradha_A — Fri, 03 Apr 2026 10:12:19 GMT

Recently, I worked on an interesting customer case where they had a requirement to export all the IP addresses that are whitelisted on the Azure SQL Servers within their subscription.

Below is the script that helped customer to export all server-level (public) firewall rules (whitelisted IP ranges) for every Azure SQL logical server in a subscription into a single CSV file.

Set-AzContext -SubscriptionId "sub_ID" # Ensure context exists $context = Get-AzContext if (-not $context) { throw "No Azure context found. Please run Connect-AzAccount." } $subId = $context.Subscription.Id # Get servers safely $servers = Get-AzSqlServer if (-not $servers) { Write-Output "No SQL Servers found in this subscription." return } $results = New-Object System.Collections.Generic.List[object] foreach ($server in $servers) { # Skip invalid entries (prevents prompt issue) if (-not $server.ServerName -or -not $server.ResourceGroupName) { Write-Warning "Skipping invalid server object" continue } try { Write-Output "Processing: $($server.ServerName)" $firewallRules = Get-AzSqlServerFirewallRule ` -ResourceGroupName $server.ResourceGroupName ` -ServerName $server.ServerName ` -ErrorAction Stop foreach ($rule in $firewallRules) { $results.Add([PSCustomObject]@{ SubscriptionId = $subId ResourceGroup = $server.ResourceGroupName SqlServerName = $server.ServerName FirewallRuleName = $rule.FirewallRuleName StartIP = $rule.StartIpAddress EndIP = $rule.EndIpAddress }) } } catch { Write-Warning "Failed for server $($server.ServerName): $($_.Exception.Message)" $results.Add([PSCustomObject]@{ SubscriptionId = $subId ResourceGroup = $server.ResourceGroupName SqlServerName = $server.ServerName FirewallRuleName = "ERROR_READING_RULES" StartIP = "" EndIP = "" }) } } # Export $path = "./AzureSqlServer_PublicFirewallIPs.csv" $results | Export-Csv -Path $path -NoTypeInformation Write-Output "Export completed: $path"

Once the script executed successfully, follow the steps below to download the CSV file.

Clicked on Download
Provide the path as ./AzureSqlServer_PublicFirewallIPs.csv and initiated download

Overview of what this PowerShell script will exactly do.

It first sets the target subscription and captures the subscription ID.
It retrieves all Azure SQL logical servers in that subscription.
For each server, it fetches the server‑level firewall rules (whitelisted IP addresses and ranges).
Each firewall rule is recorded with details such as subscription ID, resource group, server name, rule name, start IP, and end IP.
If any server fails during retrieval (for example, due to permission issues), the script logs a warning and continues processing the remaining servers.
Finally, all collected data is exported to a CSV file (AzureSqlServer_PublicFirewallIPs.csv) in the Cloud Shell directory.

This allows you to centrally audit and review all whitelisted IPs across Azure SQL Servers in a subscription without stopping execution due to individual server errors.

Understanding and Monitoring Class 2 Transactions in Azure SQL Database

Mohamed_Baioumy_MSFT — Wed, 01 Apr 2026 11:51:59 GMT

During a recent customer engagement, we investigated sustained transaction log growth in Azure SQL Database without obvious large user transactions. The customer was familiar with PostgreSQL diagnostics and wanted to understand how similar insights can be obtained in Azure SQL Database—especially around Class 2 (system) transactions.

This post summarizes what we discussed, explains why Azure SQL behaves differently, and walks through practical DMV‑based monitoring patterns you can use today.

Azure SQL Database vs. PostgreSQL: Diagnostic Model Differences

One of the first clarifications we made is that Azure SQL Database does not expose diagnostic settings equivalent to PostgreSQL’s system‑level log diagnostics.

Azure SQL Database is a fully managed PaaS service, and many internal operations—such as checkpoints, version store cleanup, and background maintenance—are abstracted from direct control. Instead of low‑level engine logs, Azure SQL provides cumulative Dynamic Management Views (DMVs) that expose the effects of system activity rather than the internal implementation.

What Are Class 2 Transactions?

In Azure SQL Database, Class 2 transactions generally refer to system‑generated transactions, not directly initiated by user workloads. These commonly include:

Checkpoint operations
Version store cleanup
Ghost record cleanup
Background metadata maintenance

Although they are not user‑driven, these transactions still generate transaction log activity, which can be surprising when log usage grows steadily without large user transactions.

Key DMVs to Monitor Class 2 Activity

1. Transaction Log Usage

SELECT * FROM sys.dm_db_log_space_usage;

This DMV provides:

Total log size
Used log space
Used log percentage

If log usage grows steadily without large user transactions, it is often a signal that background system activity (Class 2 transactions) is responsible.

Checkpoint Activity

SELECT * FROM sys.dm_exec_requests WHERE command = 'CHECKPOINT';

Frequent checkpoints result in:

More frequent log flushes
Increased system log writes

In Azure SQL Database, checkpoint frequency is system‑managed and cannot be tuned through configuration or diagnostic settings.

Version Store Usage (Common Class 2 Contributor)

SELECT * FROM sys.dm_tran_version_store_space_usage;

High version store usage often leads to:

Background cleanup tasks
Increased system transactions
Additional transaction log generation

This is especially common in workloads using:

Snapshot Isolation
Read Committed Snapshot Isolation (RCSI)
Long‑running transactions or readers

Automating Monitoring with Azure Elastic Jobs

Because these DMVs are cumulative, capturing them over time is key. During the call, we discussed automating data collection using Azure Elastic Jobs.

Elastic Jobs allow you to:

Schedule DMV snapshots
Store historical trends
Correlate spikes with workload patterns

Microsoft provides full guidance on creating and managing Elastic Jobs using T‑SQL here:
Create and manage Elastic Jobs using T‑SQL

Index Management and Class 2 Impact

Index maintenance can indirectly increase Class 2 activity by:

Increasing version store usage
Triggering additional background cleanup

Instead of manual index tuning, we recommended enabling Query Performance Insight – Index recommendations in the Azure Portal. This allows Azure SQL Database to automatically:

Suggest index creation
Suggest index removal based on real workload patterns.

Why Checkpoints Cannot Be Tuned

A common question is whether checkpoint frequency can be reduced to lower system log activity.

In Azure SQL Database:

Checkpoints are engine‑managed
There is no diagnostic or configuration setting to control their frequency
This design ensures platform stability and predictable recovery behavior

As a result, monitoring—not tuning—is the correct approach.

Practical Takeaways

From this case, the key lessons are:

Not all transaction log growth is user‑driven
Class 2 transactions are a normal part of Azure SQL Database
DMVs provide the best visibility into system behavior
Trend‑based monitoring is more valuable than point‑in‑time checks
Automation via Elastic Jobs is essential for long‑term analysis

Conclusion

Class 2 transactions are often misunderstood because they operate quietly in the background. By using the right DMVs and collecting data over time, you can clearly distinguish expected system behavior from genuine workload issues.

If you’re coming from PostgreSQL or on‑prem SQL Server, the key mindset shift is this:
Azure SQL Database exposes outcomes, not internals—and that’s by design.

No code left behind: How AI streamlines Oracle-to-PostgreSQL migration

TeneilLawrence — Tue, 31 Mar 2026 15:00:00 GMT

Coauthored by Jonathon Frost, Aditya Duvuri and Shriram Muthukrishnan

More and more organizations are choosing PostgreSQL over proprietary database platforms such as Oracle, and for good reasons. It’s fully open source and community supported with a steady pace of innovation. It’s also preferred by developers for its extensibility and flexibility, often being used for vector data along with relational data to support modern applications and agents. Still, organizations considering a shift from Oracle to PostgreSQL, may hesitate due to the complexity that often accompanies an enterprise-scale migration project. Challenges such as incompatible data types, language mismatches, and the risk of breaking critical applications are hard to ignore.

Recently, the Azure Postgres team released a new, free tool for migrations from Oracle to PostgreSQL that was designed to address these challenges, making the decision to migrate a lot less risky. The new AI-assisted Oracle-to-PostgreSQL migration tool, available in public preview via the PostgreSQL extension for Visual Studio Code, brings automation, validation, and AI-powered migration assistance into a single, user-friendly interface.

Meet your new migration assistant

The AI-assisted Oracle to PostgreSQL migration tool dramatically simplifies moving off Oracle databases. Accessible through VS Code, the tool uses intelligent automation, powered by GitHub Copilot, to convert Oracle database schemas and PL/SQL code into PostgreSQL-compatible formats. It can analyze Oracle schema, and automatically translate table definitions, data types, and even stored procedures/triggers into PostgreSQL equivalents speeding up migrations that once took months of manual effort.

By handling the heavy lifting of schema and code conversion, this tool allows teams to focus on higher-level testing and optimization rather than tedious code rewrites. Users are already reporting that migrations are now faster, safer, and more transparent. The tool is simple, free, and ready for you to use today. Let’s take a look at how it works by covering the following:

Creating the migration project
Setting up the connections
AI-assisted schema migration
Reviewing schema migration report
AI-assisted application migration
Reviewing application migration report

Step by step with the AI-assisted Oracle-to-PostgreSQL migration tool

Step 1 – Create the project in VS Code

Start by installing or updating the PostgreSQL extension for VS Code from the marketplace. Open the PostgreSQL extension panel and click “Create Migration Project.” You’ll name your project, which will create a folder to store all migration artifacts. This folder will house extracted and converted files, organized for version control and collaboration.

Step 2 - Connect to your databases and AI model

Before beginning the migration, you’ll need to connect to the Oracle databases and select an OpenAI model to leverage during the process. Enter the connection details for your source Oracle database, credentials, and the schema to migrate. Then, select a PostgreSQL scratch database. This temporary environment is used to validate converted DDL in real time. Next, you will be prompted to select an OpenAI model.

Step 3 – Begin schema migration

Once you’ve set up your connections, click the button to start the schema migration. The tool performs an extraction of all relevant Oracle database objects: tables, views, packages, procedures, and more. The extracted DDL is saved as files in your project folder. This file-based approach functions like a software project, enabling change tracking, collaboration, and source control.

Enter - AI assistance

This is where the AI takes over. The tool breaks the extracted schema into manageable chunks, and each chunk is processed by a multi-agent orchestration system:

The Migration Specialist Agent converts Oracle DDL to PostgreSQL.
The Migration Critic Agent validates the conversion by executing it in the PostgreSQL scratch database.
The Documentation Agent captures follow up review tasks, metadata, and coding notes for later integration with the application code migration process.

Each chunk is converted, validated, and deployed. If validation fails, the agents auto correct and retry. This self-healing loop ensures high conversion accuracy. Essentially, the tool conducts compile-time validation against a live PostgreSQL instance to catch issues early and reduce downstream surprises.

Checkpoint - review the schema migration report

Some complex objects, like Oracle packages with intricate PL/SQL, may not convert cleanly on the first pass. These are flagged as “review tasks.” You can invoke GitHub Copilot’s agent mode directly from VS Code to assist. The tool constructs a composite prompt with the original Oracle DDL, the partially converted PostgreSQL version, and any validation errors. This context-rich prompt enables Copilot to generate more accurate fixes.

With the schema fully converted, you can compare the original Oracle and new PostgreSQL versions side by side. Right-click any object in the project folder and select “Compare File Pair." You can also use the “Visualize Schema” feature to see a graphical representation of the converted schema. This is ideal for verifying tables, relationships, and constraints.

Once the schema migration is complete, the tool generates a detailed report that includes:

Total number of objects converted
Conversion success rate
PostgreSQL version and extensions used
List of converted objects by type
Any flagged review tasks

This report serves as both a validation summary and an audit artifact. It helps confirm success and identify any follow-up actions. If you have compliance or change management requirements you need to meet, this documentation is essential.

Step 4 – Begin application migration

The next phase that the tool supports is updating the application code that interacts with the schema. Migrations often stall when code is overlooked or when traditional tools treat SQL statements as simple strings rather than part of a cohesive system. The AI-assisted Oracle-to-PostgreSQL migration tool’s application conversion feature takes a more holistic, context-aware approach.

Before starting, you’ll need to configure GitHub Copilot Agent Mode with a capable AI model. Then, navigate to the ‘application_code’ directory typically found in .github/postgres-migration/<project_name>/application_code, and copy your source code into this directory. Keeping your application and converted schema together provides the AI with the structural context it needs to refactor your code accurately. To start the app migration, this time you’d select the "Migrate Application" button. Then select the folder containing your source code and the converted schema.

Enter - AI assistance

The AI orchestrator will analyze your application’s database interactions against the new Postgres schema and generate a series of transformation tasks. These tasks address SQL dialect changes, data access modifications, and library updates. This process goes beyond a simple search-and-replace operation. The AI queries your migrated PostgreSQL database to gain grounded context of your converted schema, and ensures that things like function signatures, data types, and ORM models are migrated correctly in the application code.

Checkpoint - review the app migration report

When the AI finishes converting your application, it produces a detailed summary. The report lists which files were migrated, notes any unresolved tasks, and outlines how the changes map to the database schema. This audit-ready document can help DBAs and developers collaborate effectively on follow-up actions and integration testing.

You can use VS Code’s built-in diff viewer to compare each migrated file with its original. Right-click on a migrated file and select "Compare App Migration File Pairs" to open a side-by-side view. This comparison highlights differences in SQL queries, driver imports, and other code changes, allowing you to verify the updates.

Wrapping up the migration project

During schema migration, the tool created detailed coding notes summarizing data-type mappings, constraints, and package transformations. These notes are essential for understanding why specific changes were made and for guiding the application conversion. Use them as reference points when validating and refining the AI-generated application code.

Destination - PostgreSQL on Azure

The AI-assisted Oracle-to-PostgreSQL migration tool brings together automation, validation, and AI to make Oracle-to-PostgreSQL migrations faster, safer, and more transparent. With schema extraction, multi-agent orchestration, app conversion, real-time validation, and detailed reporting, it provides a clear, confident path to modernization so you can start taking advantage of the benefits of open-source Postgres.

What’s in store

On the other side of a successful migration project to PostgreSQL on Azure, you get:

First-class support in Azure
Significantly lower total cost of ownership from eliminating license fees and reducing vendor lock-in
Unmatched extensibility, with support for custom data types, procedural languages and powerful extensions like PostGIS, TimescaleDB, pgvector, Azure AI, and DiskANN
Frequent updates and cutting-edge features delivered via a vibrant open-source community

Whether you’re migrating a single schema or leading a broader replatforming initiative, the AI-assisted Oracle-to-PostgreSQL migration tool helps you move forward with confidence without sacrificing control or visibility.

Learn more about starting your own migration project.

PostgreSQL Buffer Cache Analysis

Gayathri_Paderla — Tue, 31 Mar 2026 12:51:22 GMT

PostgreSQL performance is often dictated not just by query design or indexing strategy, but by how effectively the database leverages memory. At the heart of this memory usage lies shared_buffers—PostgreSQL’s primary buffer cache. Understanding how well this cache is utilized can make the difference between a system that scales smoothly and one that struggles under load.

In this post, we’ll walk you through a practical, data-driven approach to analyzing PostgreSQL buffer cache behavior using native statistics and the pg_buffercache extension. The goal is to answer a few critical questions:

Is the current shared_buffers configuration sufficient?
Are high-value tables and indexes actually being served from memory?
Is PostgreSQL spending too much time going to disk when it shouldn’t?

By the end, you’ll have a repeatable methodology to assess cache efficiency and make informed tuning decisions.

Why Buffer Cache Analysis Matters

PostgreSQL relies heavily on its buffer cache to minimize disk I/O. Every time a query needs a data or index page, PostgreSQL first checks whether that page already exists in shared_buffers. If it does, the page is served directly from memory—fast and efficient. If not, PostgreSQL must fetch it from disk (or the OS page cache), which is significantly slower.

While metrics like query latency and IOPS can tell you that performance is degraded, buffer cache analysis helps explain why. It allows you to:

Validate whether frequently accessed objects stay hot in cache
Identify cache pollution caused by large, low-value tables
Determine whether increasing shared_buffers would provide real benefits or just waste memory

Inspecting Shared Buffers with pg_buffercache

The pg_buffercache extension provides a real-time view into PostgreSQL’s shared buffers. Unlike cumulative statistics, it shows what is in memory right now—which relations are cached, how many blocks they occupy, and how frequently those buffers are reused.

Enabling the Extension

pg_buffercache is not enabled by default and requires superuser privileges:

CREATE EXTENSION pg_buffercache;

Once enabled, you can directly query the contents of shared buffers across databases, tables, and indexes.

Analyzing Cache Distribution

Understanding where your shared buffers are being consumed is the first step toward meaningful tuning.

Database-Level Cache Distribution

This query shows how shared buffers are distributed across databases in the server:

SELECT CASE WHEN c.reldatabase IS NULL THEN '' WHEN c.reldatabase = 0 THEN '' ELSE d.datname END AS database, count(*) AS cached_blocks FROM pg_buffercache AS c LEFT JOIN pg_database AS d ON c.reldatabase = d.oid WHERE datname NOT LIKE 'template%' GROUP BY d.datname, c.reldatabase ORDER BY d.datname, c.reldatabase;

This is particularly useful in multi-database environments where one workload may be evicting cache pages needed by another.

Table and Index-Level Cache Consumption

To understand which relations, dominate the cache, the following query breaks buffer usage down by tables and indexes:

SELECT c.relname, c.relkind, count(*) FROM pg_database AS a, pg_buffercache AS b, pg_class AS c WHERE c.relfilenode = b.relfilenode AND b.reldatabase = a.oid GROUP BY 1, 2 ORDER BY 3 DESC, 1;

This helps answer an important question: Are your most business-critical tables and indexes actually resident in memory, or are they constantly being evicted?

If large, rarely used tables consume a disproportionate share of buffers, it may indicate cache churn or the need for workload isolation.

Understanding Buffer Usage Count (Hot vs Cold Data)

Each buffer in shared memory carries a usage count, which reflects how frequently it has been accessed before eviction. Higher values indicate hotter data.

SELECT c.relname, c.relkind, usagecount, count(*) AS buffers FROM pg_database AS a, pg_buffercache AS b, pg_class AS c WHERE c.relfilenode = b.relfilenode AND b.reldatabase = a.oid AND a.datname = current_database() GROUP BY 1, 2, 3 ORDER BY 3 DESC, 1;

A healthy system typically shows a meaningful number of buffers with higher usage counts (for example, 4–5), indicating frequently reused data that benefits from caching.

Buffer Cache Percentages: Putting Numbers in Context

Raw buffer counts are useful, but percentages make interpretation easier. The following query shows:

How much of shared_buffers each relation occupies
What percentage of the relation itself is cached

SELECT c.relname, pg_size_pretty(count(*) * 8192) AS buffered, round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer, 1) AS buffers_percent, round(100.0 * count(*) * 8192 / pg_relation_size(c.oid), 1) AS percent_of_relation FROM pg_class c JOIN pg_buffercache b ON b.relfilenode = c.relfilenode JOIN pg_database d ON b.reldatabase = d.oid AND d.datname = current_database() GROUP BY c.oid, c.relname ORDER BY 3 DESC LIMIT 10;

This view is especially powerful when validating whether performance-critical objects are adequately cached relative to their size.

Complementing Cache Views with I/O Statistics

While pg_buffercache shows the current state of memory, I/O statistics reveal long-term trends. PostgreSQL exposes these via pg_statio_user_tables and pg_statio_user_indexes.

Table Heap Hit Ratios

SELECT relname, heap_blks_hit::numeric / (heap_blks_hit + heap_blks_read) AS hit_pct, heap_blks_hit, heap_blks_read FROM pg_catalog.pg_statio_user_tables WHERE (heap_blks_hit + heap_blks_read) > 0 ORDER BY hit_pct;

Hit ratios close to 1 indicate that table data is largely served from memory rather than disk.

Index Hit Ratios

SELECT relname, idx_blks_hit::numeric / (idx_blks_hit + idx_blks_read) AS hit_pct, idx_blks_hit, idx_blks_read FROM pg_catalog.pg_statio_user_tables WHERE (idx_blks_hit + idx_blks_read) > 0 ORDER BY hit_pct;

Poor index hit ratios often point to insufficient cache or inefficient query patterns that bypass indexes.

Including TOAST and Index Reads

For large objects, TOAST activity can significantly impact I/O. This query provides a more holistic view:

SELECT *, (heap_blks_read + toast_blks_read + tidx_blks_read) AS total_blocks_read, (heap_blks_hit + toast_blks_hit + tidx_blks_hit) AS total_blocks_hit FROM pg_catalog.pg_statio_user_tables;

This helps identify indexes that are frequently read from disk and may benefit from better caching or query rewrites.

How to Interpret the Results

When reviewing buffer cache and I/O metrics, keep the following guidelines in mind:

Validate cache residency of critical objects: If business-critical tables and indexes occupy a meaningful share of shared_buffers, your cache sizing is likely reasonable.
Correlate buffer data with hit ratios: High hit ratios in pg_statio_user_tables and pg_statio_user_indexes confirm effective caching. Persistently low ratios may justify increasing shared_buffers.
Analyze usage count distribution: A healthy number of buffers with higher usage counts indicates hot data benefiting from cache reuse.
Avoid over-tuning: If most buffers have low usage counts but hit ratios remain high, increasing shared_buffers further may not yield measurable gains.

Conclusion

Buffer cache analysis bridges the gap between theory and reality in PostgreSQL performance tuning. By combining real-time cache inspection with long-term I/O statistics, you gain a clear picture of how memory is actually used—and whether changes to shared_buffers will deliver tangible benefits.

Rather than tuning memory blindly, this approach lets you optimize with confidence, grounded in data that reflects your real workload.