Azure Database Support Blog articles

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

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

Overview

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

Symptom

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

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

Connection ID: 'XYZ'

Scenario Details

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

Root Cause

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

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

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

Resolution

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

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

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

ALTER LOGIN [sqlmiadmin] ENABLE; GO

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

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

Note

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

Additional Resources

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

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

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

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

Disclaimer

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

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

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

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

Summary

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

LongTermRetentionMigrationRequestNotSupported
LTR backup migration copy feature is not supported on subscription

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

The Common Scenario

A customer has:

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

They attempt:

Copy-AzSqlDatabaseLongTermRetentionBackup

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

Why This Error Happens

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

Copy-AzSqlDatabaseLongTermRetentionBackup is NOT a general-purpose feature

This API is:

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

In other words:

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

Because of this, most subscriptions - will receive:

LongTermRetentionMigrationRequestNotSupported

The Correct & Supported Solution

Good news:

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

Azure SQL allows you to:

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

Supported Approach: Restore LTR Backup Directly

Use Restore-AzSqlDatabase with the -FromLongTermRetentionBackup switch.

Example (PowerShell)

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

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

How This Works (Important Concept)

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

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

When Is LTR Backup Copy Actually Used?

Only in this scenario:

Microsoft-initiated region decommissioning

In that case:

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

Outside of this scenario, the API is intentionally restricted.

Key Takeaways

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

Final Recommendation for Customers

If customers encounter this error:

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

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

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

Summary

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

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

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

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

The Problem Scenario

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

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

Key observations from affected cases:

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

Why This Happens

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

If these roles (or related permissions) are:

Missing
Partially deleted
Left in an inconsistent state

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

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

How to Detect the Issue

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

1. Run the Data Sync Health Checker

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

Common warnings include:

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

This confirms the root cause is role and permission inconsistency.

Supported and Effective Resolution

Step 1: Verify Roles Are Missing

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

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

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

Step 2: Fully Clean Up Leftover Data Sync Objects

Do this only if the database is not actively syncing

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

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

Step 3: Recreate the Sync Group (Critical Step)

Do not manually recreate roles or permissions
Instead:

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

During this process, Azure will automatically:

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

Key Takeaways

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

Final Recommendation

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

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

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

Data Migration - From SQL MI to SQL DB migration

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

Overview

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

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

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

High-level steps

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

Pre-migration assessment (SQL MI → SQL DB)

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

Here is check list to consider:

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

Migrate schema and database objects

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

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

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

Deploy ADF

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

Provision Windows VM – used to host SHIR

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

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

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

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

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

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

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

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

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

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

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

Configure Linked Service (connections to source and target)

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

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

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

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

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

Enter required properties to connect to SQL MI.

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

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

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

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

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

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

Configure Ingestion and run data migration

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

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

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

The Copy Data tool page

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

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

Select Next at the [Apply filter] page

Select the destination

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

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

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

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

Checking migration status

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

Validation and cutover

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

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

Notes and Observations

Migrating database objects

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

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

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

Online migration

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

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

Summary

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

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

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

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

Overview

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

Creating instance failover group failed.

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

Prerequisites Review

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

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

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

Scenario Details

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

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

Root Cause

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

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

Resolution

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

To achieve this:

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

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

After fixing the issue, failover group was successfully created.

Additional Resources

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

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

Disclaimer

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

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

How to take copy only backups with SQL Managed Instance.

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

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

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

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

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

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

Note the Key Identifier:

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

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

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

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

In the Key Vault:

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

2) Choose the SQL MI managed identity.

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

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

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

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

In the storage account that hosts your backup container:

1) Go to Access control (IAM).

2) Choose the SQL MI managed identity.

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

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

Step 4: Add the Encryption Key to SQL Managed Instance

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

A) From Azure portal:

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

B) From Azure Cloud Shell/PowerShell:

First, add the key first with this command:

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

Then set it as the TDE protector:

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

Step 5: Create a Credential for the Target Blob Container

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

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

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

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

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

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

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

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

1) Error#1:

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

This error is caused by:

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

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

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

2) Error#2:

Msg 3201, Level 16, State 1, Line 1

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

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

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

3) Error#3:

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

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

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

Example:

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

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

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

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

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

Overview

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

In one such scenario, a customer observed:

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

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

This article explains:

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

How Azure SQL Database Auditing Works?

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

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

BATCH_COMPLETED_GROUP
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
FAILED_DATABASE_AUTHENTICATION_GROUP

These groups audit:

All query execution activity
Successful authentication attempts
Failed authentication attempts

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

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

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

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

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

For example:

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

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

Observed Behavior in a Newly Created Test Server

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

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

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

Enabling AL / CR / DR Action IDs

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

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

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

After applying this configuration:

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

Ensuring Consistent Compliance Across PaaS Servers

To standardize audit logging behavior across environments:

Step 1: Compare AuditActionGroups

Run the following command on all servers:

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

Step 2: Align AuditActionGroups

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

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

Step 3: Validate

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

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

Accepted values:

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

Links:

Get-AzSqlServerAudit (Az.Sql) | Microsoft Learn

Set-AzSqlServerAudit (Az.Sql) | Microsoft Learn

Monitoring Azure SQL Data Sync Errors Using PowerShell

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

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

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

❗ Lack of proactive monitoring for sync failures or errors

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

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

Why Monitoring Azure SQL Data Sync Matters

Azure SQL Data Sync works by synchronizing data between:

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

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

Monitoring these logs enables:

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

Prerequisites

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

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

Install and import the required module if not already available:

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

Authenticate to Azure:

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

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

Monitoring Sync Group Status

To retrieve Sync Group details, define the required variables:

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

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

Monitoring Sync Activity Using Logs (Recommended)

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

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

This command retrieves:

Sync operation timestamps
Sync status
Error messages
Activity details

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

Retrieving the Last Successful Sync Time

To determine the most recent successful synchronization operation:

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

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

Filtering for Synchronization Errors

To identify failed or problematic sync operations:

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

Filtering logs by error type allows for:

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

Key Takeaways

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

References

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

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

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

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

Symptom

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

Error

You may see an error similar to:

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

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

What’s actually happening (Root Cause)

1) Initial sync uses temp files on the agent machine

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

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

In practice, you’ll often see:

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

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

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

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

Mitigation (Recommended)

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

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

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

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

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

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

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

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

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

General Windows guidance indicates:

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

A common approach is:

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

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

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

Validation checklist (after the change)

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

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

Classification

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

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

Helpful references

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

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

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

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

Sample Error:

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

Scenario Overview

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

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

Further investigation revealed the following SQL exception:

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

Initial Troubleshooting Steps

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

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

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

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

Root Cause Analysis

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

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

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

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

Mitigation Approach

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

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

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

Best Practices to Avoid Similar Issues

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

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

Final Thoughts

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

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

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.

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.

Understanding Azure SQL Data Sync Firewall Requirements

Mohamed_Baioumy_MSFT — Mon, 30 Mar 2026 10:26:52 GMT

Why IP Whitelisting Is Required and What Customers Should Know

Azure SQL Data Sync is commonly used to synchronize data between on‑premises SQL Server databases and Azure SQL Database. While the setup experience is generally straightforward, customers sometimes encounter connectivity or configuration issues that are rooted in network security and firewall behavior.

This blog explains why Azure SQL Data Sync requires firewall exceptions, what type of IP addresses may appear in audit logs, and how to approach this topic from a security and documentation standpoint—based on real troubleshooting discussions within the Azure SQL Data Sync ecosystem.

The Scenario: Sync Agent Configuration Fails Despite Valid Setup

A frequently reported issue occurs when the Azure SQL Data Sync Agent (installed on an on‑premises server) fails to save its configuration. The error typically indicates that a valid agent key is required—even when:

The agent key was freshly generated from the Azure SQL Data Sync portal
Connection tests succeed
The agent has been reinstalled or the server restarted
New sync groups were created

Despite these efforts, synchronization does not proceed until a specific public IP address is allowed through the Azure SQL Database firewall.

Why Firewall Rules Matter for Azure SQL Data Sync

Azure SQL Database is protected by a server‑level firewall that blocks all inbound traffic by default. Any external client—including the Data Sync Agent—must be explicitly allowed to connect.

In Azure SQL Data Sync:

The Data Sync Agent runs on‑premises
It connects outbound over TCP port 1433
It uses the public endpoint of the Azure SQL logical server
The Azure SQL firewall must allow the public IP address used by the agent

If this IP is not allowed, the agent cannot complete configuration or perform synchronization operations—even if authentication and permissions are otherwise correct.

Identifying the Required IP Address

In the referenced discussion, the required IP address was identified by reviewing Azure SQL audit logs, which revealed connection attempts being blocked at the firewall layer. Once this IP address was added to the Azure SQL server firewall rules, synchronization completed successfully.

This highlights an important point:

Audit logs can be a reliable way to identify which IP address must be whitelisted when Data Sync connectivity fails.

Is This IP Address Owned by Microsoft? Can It Change?

A natural follow‑up question is whether the observed IP address is Microsoft‑owned, and whether it can change.

From the discussion:

Azure SQL Data Sync relies on Microsoft‑managed service infrastructure
Some outbound connectivity may originate from Azure service IP ranges
Microsoft publishes official IP ranges and service tags for transparency

However, documentation does not guarantee that a single static IP will always be used. Customers should therefore treat firewall configuration as a network security requirement, not a one‑time exception.

Related Microsoft Resources

While Azure SQL Data Sync documentation focuses on setup and troubleshooting, firewall requirements are often implicit rather than explicitly called out.

The following Microsoft resources were referenced in the discussion to help customers understand Azure service IP ownership and ranges:

These resources can help security teams validate Microsoft‑owned IPs and plan firewall policies accordingly.

Key Takeaways for Customers

✅ Azure SQL Data Sync requires firewall access to Azure SQL Database
✅ The public IP used by the Data Sync Agent must be explicitly allowed
✅ Audit logs are useful for identifying blocked IPs
✅ IP addresses may belong to Microsoft infrastructure and can change over time
✅ Firewall configuration is a security prerequisite, not an optional step

Closing Thoughts

Azure SQL Data Sync operates securely by design, leveraging Azure SQL Database firewall protections. While this can introduce configuration challenges, understanding the network flow and firewall requirements can significantly reduce setup friction and troubleshooting time.

If you're implementing Azure SQL Data Sync in a locked‑down network environment, we recommend involving your network and security teams early and validating firewall rules as part of the initial deployment checklist.

Troubleshooting Azure SQL Data Sync Groups Stuck in Progressing State

Mohamed_Baioumy_MSFT — Mon, 30 Mar 2026 09:59:23 GMT

Azure SQL Data Sync is commonly used to synchronize data across Azure SQL Databases and on‑premises SQL Server environments. While the service works well in many scenarios, customers may occasionally encounter a situation where a Sync Group remains stuck in a “Progressing” state and cannot be started, stopped, or refreshed.

This blog walks through a real-world troubleshooting scenario, highlights the root cause, and outlines practical remediation steps based on actual support investigation and collaboration.

Problem Overview

In this scenario, the customer reported that:

The Sync Group was stuck in “Progressing” for multiple days
Sync operations could not be started or stopped
Tables could not be refreshed or reconfigured
Azure Activity Logs showed operations as Succeeded, yet sync never progressed
Our backend telemetry showed the Sync Group as Active, while hub and member databases were in Reprovisioning state

The last successful sync occurred on XX day, after which the sync pipeline stopped making progress.

Initial Investigation Findings

During the investigation, several key observations were made:

1. High DATA IO Utilization

Telemetry and backend checks revealed that DATA IO utilization was pegged at 100% on one of the sync member databases starting XX day.

Despite no noticeable change in application workload, the database was under sustained IO pressure, which directly impacted Data Sync operations.

2. Deadlocks During Sync Processing

Our backend telemetry showed repeated deadlock errors:

Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim.

These deadlocks were observed for multiple Sync Member IDs starting the same day IO saturation began.

This aligned with the hypothesis that resource contention, not a Data Sync service failure, was the underlying issue.

3. Metadata Database Was Healthy

The Sync metadata database was running on a serverless Azure SQL Database (1 vCore) and showed healthy resource usage, ruling it out as a bottleneck.

Recommended Troubleshooting Steps

Based on the findings, the following steps were recommended and validated:

✅ Step 1: Address Database Resource Constraints First

Before attempting to recreate or reset the Sync Group, the focus was placed on resolving DATA IO saturation on the affected database.

Actions included:

Scaling up the database (DTUs / vCores)
Monitoring IO utilization after scaling
Ensuring sufficient headroom for sync operations

This was identified as the primary remediation step.

✅ Step 2: Use the Azure SQL Data Sync Health Checker

The Azure SQL Data Sync Health Checker was recommended to validate:

Sync metadata integrity
Table-level configuration issues
Agent and connectivity status

GitHub tool: AzureSQLDataSyncHealthChecker

✅ Step 3: Validate Sync Group and Agent State via PowerShell

PowerShell was used to confirm:

Sync Group state
Last successful sync time
On‑premises Sync Agent connectivity

Example commands used:

Get-AzureRmSqlSyncGroup ` -ResourceGroupName "ResourceGroup01" ` -ServerName "Server01" ` -DatabaseName "Database01" | Format-List

Get-AzureRmSqlSyncAgent ` -ResourceGroupName "ResourceGroup01" ` -ServerName "Server01" | Select ResourceGroupName, SyncState, LastSyncTime

Resolution

After the customer increased the database size, DATA IO utilization dropped, sync operations resumed normally, and the customer confirmed that the issue was resolved.

Why Long-Term Retention (LTR) Backups Don’t Attach After a PITR Restore in Azure SQL Database

Mohamed_Baioumy_MSFT — Mon, 30 Mar 2026 09:46:25 GMT

Summary

Customers sometimes expect that after performing a Point‑in‑Time Restore (PITR) and renaming the restored database back to its original name, existing Long‑Term Retention (LTR) backups will automatically appear and continue from where they left off.

This behavior may have worked in older or legacy environments, but in modern Azure SQL Database deployments—especially across new servers or subscriptions—this expectation can lead to confusion.

This article explains why LTR backups do not attach to restored databases, even if the database name is reused, and what customers should expect instead.

The Scenario

The discussion originated from a common migration pattern:

A customer has an Azure SQL Database with LTR policies configured (for example, monthly backups retained for 10 years).
The customer performs a Point‑in‑Time Restore (PITR) of that database.
After the restore, the database is renamed to match the original database name.
The customer expects the existing LTR backups to appear under the restored database.

In legacy environments, this behavior appeared to work. However, in newer Azure SQL Database deployments, the LTR backups are not visible after the restore and rename process.

Key Technical Detail: LTR Is Not Based on Database Name

The most important concept to understand is this:

LTR backups are associated with the database’s logical database ID—not the database name.

Each Azure SQL Database is assigned a unique logical database ID at creation time. When a PITR restore is performed:

A new database is created
It receives a new logical database ID
Even if you rename the database to match the original name, the logical ID remains different

As a result, the restored database is treated as a completely new database from an LTR perspective, and it does not inherit the historical LTR backup chain.

Why Renaming the Database Does Not Help

Renaming a database only changes its display name. It does not change:

The logical database ID
The internal association used by the LTR system

Because LTR configuration and backup visibility are tied to the logical database ID, renaming alone cannot reattach historical LTR backups.

Subscription Boundaries Matter

Another important clarification raised in the discussion:

LTR backups are scoped to the subscription where the database was created
While you can restore LTR backups to a different server within the same subscription, you cannot carry historical LTR backups across subscriptions

If a customer migrates to a new subscription, the historical LTR chain from the old subscription cannot be reused or reattached. Only new LTR backups created after the move will exist in the new subscription.

What Customers Will Observe

After a PITR restore and rename:

✅ The database is successfully restored
✅ LTR policies can be configured again
❌ Historical LTR backups from the original database are not visible
❌ The restored database does not inherit old LTR backups, even if the name matches

This is expected behavior and aligns with the current Azure SQL Database architecture.

How to Validate LTR Backups Correctly

To avoid confusion caused by portal caching or UI expectations, customers can list LTR backups programmatically using PowerShell or Azure CLI, as documented in Microsoft Learn:

Azure SQL Database: Manage long-term backup retention
Azure SQL Database: Manage long-term backup retention - Azure SQL Database | Microsoft Learn

This confirms whether LTR backups exist for a specific logical database ID.

Best Practices and Recommendations

Do not rely on database renaming to preserve LTR history.
Treat any PITR restore as a new database from an LTR perspective.
If historical LTR backups must remain accessible:
- Keep the original database intact
- Restore LTR backups directly from the original database when needed
Plan migrations carefully, especially when moving across subscriptions, as LTR history cannot be migrated.

Final Thoughts

LTR backups are a powerful compliance and recovery feature in Azure SQL Database, but they are intentionally designed to be immutable and identity‑based, not name‑based.

Understanding that logical database ID—not database name—controls LTR association helps set correct expectations and avoids surprises during restores or migrations.

Frequently Asked Questions (FAQ)

Q1: Why don’t my existing LTR backups appear after I restore a database using PITR?

Because a Point‑in‑Time Restore (PITR) creates a new database with a new logical database ID. Long‑Term Retention (LTR) backups are associated with the database’s logical ID—not its name—so the restored database does not inherit the historical LTR backup chain.

Q2: If I rename the restored database to the original name, shouldn’t the LTR backups reappear?

No. Renaming a database only changes its display name. It does not change the logical database ID, which is what LTR uses to associate backups. As a result, renaming does not reattach existing LTR backups.

Q3: This used to work in our legacy environment—why is it different now?

In older environments, the behavior may have appeared to work due to differences in platform implementation. In current Azure SQL Database architecture, LTR association is strictly identity‑based, which ensures immutability, compliance, and predictable backup behavior.

Q4: Can I attach historical LTR backups to a restored database manually?

No. LTR backups are immutable and cannot be reattached or reassigned to a different logical database ID. This behavior is by design.

Q5: What happens if I move my database to a new subscription?

LTR backups are scoped to the subscription where the database was created. If you migrate to a new subscription:

Historical LTR backups from the old subscription cannot be carried over
Only new LTR backups created after the move will exist in the new subscription

Q6: Can I still restore from my old LTR backups?

Yes. As long as the original database (or its logical identity) still exists in the original subscription, you can restore directly from those LTR backups—even if a newer database with the same name exists elsewhere.

Q7: How can I verify which LTR backups actually exist?

The most reliable way is to list LTR backups programmatically using Azure PowerShell or Azure CLI, which queries backups by logical database ID rather than relying solely on portal views.
Refer to the official documentation:
Azure SQL Database – Manage long‑term backup retention

Q8: What is the recommended approach if we need long‑term recoverability after PITR?

Treat every PITR restore as a new database from an LTR perspective
Keep the original database intact if historical LTR backups must remain accessible
Plan subscription migrations carefully, as LTR history cannot be migrated

Azure SQL Hyperscale: Understanding PITR Retention vs Azure Portal Restore UI

Mohamed_Baioumy_MSFT — Thu, 26 Mar 2026 09:11:21 GMT

Overview

Customers using Azure SQL Database – Hyperscale may sometimes notice a discrepancy between the configured Point-in-Time Restore (PITR) retention period and what the Azure Portal displays as available restore points.

In some cases:

PITR retention is configured (for example, 7 days),
Yet the Azure Portal only shows restore points going back a shorter period (for example, 1–2 days),
And the restore UI may allow selecting dates earlier than the configured retention window without immediately showing an error.

This post explains why this happens, how to validate backup health, and what actions to take.

Key Observation

From investigation and internal validation, this behavior is not indicative of backup data loss. Instead, it is related to Azure Portal UI behavior, particularly for Hyperscale databases.

The backups themselves continue to exist and are managed correctly by the service.

Important Distinction: Portal UI vs Actual Backup State

What the Azure Portal Shows

The restore blade may show fewer restore points than expected.
The date picker may allow selecting dates outside the PITR retention window.
No immediate validation error may appear in the UI.

What Actually Happens

Backup retention is enforced at the service layer, not the portal.
If a restore is attempted outside the valid PITR window, the operation will fail during execution, even if the UI allows selection.
Hyperscale backup metadata is handled differently than General Purpose or Business Critical tiers.

Why This Happens with Hyperscale

There are a few important technical reasons:

Hyperscale backup architecture differs
Hyperscale uses a distributed storage and backup model optimized for scale and fast restore, which affects how metadata is surfaced.
Some DMVs are not supported
Views like sys.dm_database_backups, commonly used for backup visibility, do not support Hyperscale databases.
Azure Portal relies on metadata projections
The portal restore experience depends on backend projections that may lag or behave differently for Hyperscale, leading to UI inconsistencies.

How to Validate Backup Health (Recommended)

Instead of relying solely on the Azure Portal UI, use service-backed validation methods.

Option 1: PowerShell – Earliest Restore Point

You can confirm the earliest available restore point directly from the service:

# Set your variables $resourceGroupName = "RG-xxx-xxx-1" $serverName = "sql-xxx-xxx-01" $databaseName = "database_Prod" # Get earliest restore point $db = Get-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName $earliestRestore = $db.EarliestRestoreDate Write-Host "Earliest Restore Point: $earliestRestore" Write-Host "Days Available: $([math]::Round(((Get-Date) - $earliestRestore).TotalDays, 1)) days"

This reflects the true PITR boundary enforced by Azure SQL.

Option 2: Internal Telemetry / Backup Events (Engineering Validation)

Internal monitoring confirms:

Continuous backup events are present.
Coverage aligns with configured PITR retention.
Backup health remains ✅ Healthy even when the portal UI appears inconsistent.

Key takeaway: Backup data is intact and retention is honored.

Is There Any Risk of Data Loss?

No.

There is no evidence of backup loss or retention policy violation.
This is a visual/UX issue, not a data protection issue.

Recommended Actions

For Customers

✅ Trust the configured PITR retention, not just the portal display.
✅ Use PowerShell or Azure CLI to validate restore boundaries.
❌ Do not assume backup loss based on portal UI alone.

For Support / Engineering

Capture a browser network trace when encountering UI inconsistencies.
Raise an incident with the Azure Portal team for investigation and fix.
Reference Hyperscale-specific behavior during troubleshooting.

Summary

Topic	Status
PITR retention enforcement	✅ Correct
Backup data integrity	✅ Safe
Azure Portal restore UI	⚠️ May be misleading
Hyperscale backup visibility	✅ Validate via service tools

Final Thoughts

Azure SQL Hyperscale continues to provide robust, reliable backup and restore capabilities, even when the Azure Portal UI does not fully reflect the underlying state.

When in doubt:

Validate via service APIs
Rely on enforcement logic, not UI hints
Escalate portal inconsistencies appropriately

Hyperscale Azure SQL database shrink process- Tips & Tricks

Tancy — Wed, 25 Mar 2026 00:58:33 GMT

By Tanayankar Chakraborty & Dimitri Furman

Issue

We recently worked on a customer case involving challenges with shrinking an Azure SQL Database (Hyperscale). The primary concern was that the shrink operation was progressing slowly and was not reclaiming storage space as expected. In some scenarios, customers also encountered error messages during the shrink operation.

Observed Error

The cx noticed this error after executing the shrink command:

Sql error number: 1222. Error Message: Lock request time out period exceeded.

Lock request time out period exceeded.

In some cases, the operation was aborted with the following message:

The shrink operation was aborted because a page to be moved by shrink is in use by an active transaction on the primary replica or on one or more secondary replicas. Retry shrink later.

Recommendations and Mitigation Steps

Here are some of the Recommendations to be followed while handling the DB shrink of a Large Azure SQL DB:

The following best practices are recommended when performing shrink operations on large Azure SQL Database Hyperscale databases:

Assess Used vs. Allocated Space - Before initiating the shrink operation, review the used and allocated space for each data file and document the results:
SELECT file_id,

       CAST(SUM(FILEPROPERTY(name, 'SpaceUsed')) AS bigint) * 8 / 1024. AS space_used_mb,

       CAST(SUM(size) AS bigint) * 8 / 1024. AS space_allocated_mb

FROM sys.database_files

WHERE type_desc = 'ROWS'

GROUP BY file_id;
Shrink Using TRUNCATEONLY -
Run DBCC SHRINKFILE with the TRUNCATEONLY option for each data file. This can quickly reclaim unused space at the end of the file when applicable:

DBCC SHRINKFILE (<file_id>, TRUNCATEONLY);

To retrieve all applicable file IDs:

SELECT file_id

FROM sys.database_files

WHERE type_desc = 'ROWS';
Validate Space Reclamation - Re-run the space usage query to confirm whether the allocated space has been reduced.
Use Incremental Target Sizes -
If unused allocated space remains, specify a target size in gradual increments. For example, if a file is 1 GB with only 100 MB used, shrink the file incrementally:

DBCC SHRINKFILE (1, 900);

DBCC SHRINKFILE (1, 800);

Continue until the desired size is reached (The value above i.e. 800 or 900 is in MB).

Additional Best Practices:

1. Multiple DBCC SHRINKFILE operations can be run concurrently on the database, which may help reduce overall execution time. The problem is if we increase the number of parallel shrinks too much, they start blocking & deadlocking each other which is counterproductive. In such cases, using the WAIT_AT_LOW_PRIORITY option in DBCC SHRINKFILE can mitigate locking issues.

2. Shrink progress can be monitored using the following query:

SELECT command,

       percent_complete,

       status,

       wait_resource,

       session_id,

       wait_type,

       blocking_session_id,

       cpu_time,

       reads,

       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '

                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '

                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time

FROM sys.dm_exec_requests AS r

LEFT JOIN sys.databases AS d

ON r.database_id = d.database_id

WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

3. For future shrink operations, we recommend specifying a target size equal to space used plus approximately 3 GB. Very small target size windows often result in longer execution times and reduced effectiveness due to repeated preprocessing.

4. Perform shrink operations during off-business hours or a planned maintenance window, as active workloads during peak hours can cause blocking and timeouts.

5. Databases with large LOB or columnstore data may require additional time to shrink. Rebuilding columnstore indexes can improve shrink effectiveness, as they internally use LOB storage. Also, as stated in this document, If the DB has objects with LOBs, compacting them before shrink can help.

6. Index rebuilds should not be executed concurrently with shrink operations. Index rebuilds require free space and may trigger file growth, which counteracts shrink efforts. Rebuild indexes first, then perform shrink operations. If page density is low Rebuild, If page density is high, no need to rebuild.

7. New data will always be distributed to the file with the max empty space according to the proportional fill algorithm. This is explained in detail here. There is no one-to-one mapping between tables and files, and deadlocks may sometimes occur during parallel shrink operations.

8. It must be noted that index rebuilds can improve shrink effectiveness, especially if page density is low. The key point here is that you need to rebuild before shrink. Hence, it is best to focus on rebuilding indexes with less than 80% page density, particularly for large tables, and to use sampled mode ( Please check the DMV sys.dm_db_index_physical_stats here for more info on sampled mode) for index statistics to avoid long-running queries. Please use the script here to find out the page density.

9. Columnstore indexes internally use LOB data types and can hinder shrinking if not rebuilt. Prioritizing columnstore index rebuilds can help as data at the end of the files can prevent truncation.

10. If needed, attempt shrink operations on two to three files at a time and adjust parallelism cautiously while monitoring for blocking or deadlocks.

References

Shrink for Azure SQL Database Hyperscale is now generally available | Microsoft Community Hub

DBCC SHRINKFILE (Transact-SQL) - SQL Server | Microsoft Learn

Database file space management - Azure SQL Database | Microsoft Learn

Maintain Indexes Optimally to Improve Performance and Reduce Resource Utilization - SQL Server | Microsoft Learn

Page and Extent Architecture Guide - SQL Server | Microsoft Learn

sys.dm_db_index_physical_stats (Transact-SQL) - SQL Server | Microsoft Learn

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

When Azure Portal/CLI Can’t Delete an Azure SQL DB: Check the Database Name (Unsupported Characters)

Mohamed_Baioumy_MSFT — Tue, 24 Mar 2026 14:27:45 GMT

Scenario (from a real service request)

A customer reported a General Purpose (Gen5, 2 vCores) Azure SQL Database that was incurring charges but could not be deleted using Azure Portal or Azure CLI.
CLI output showed two entries, including one whose database name included a forward slash (example display: xxxx-xxx-sql/xxx-xxx-db).

Symptoms you may see

The database appears in listing outputs, but deletion via ARM/CLI fails with invalid resource ID formatting.
The name looks like server/db (contains /), making it difficult for portal/CLI to target correctly.

Why this happens?

Databases created through T‑SQL/SSMS can sometimes allow characters that ARM-based creation would block, which can cause portal/CLI/ARM operations to fail for that database.
In SQL, identifiers that don’t follow “regular” naming rules must be used as delimited identifiers (e.g., wrapped in brackets).

The fix that worked

We advised the customer to delete the database using T‑SQL, enclosing the database name in square brackets (delimited identifier).
The customer confirmed the database was successfully dropped using this approach.

If you want to prevent this going forward

Prefer creating databases through portal/ARM/CLI, which enforces naming rules and avoids “unsupported character” edge cases.
If you must keep a database that has unsupported characters, Microsoft’s public guidance notes that the long-term workaround is to rename the database using T‑SQL to a compliant name so it can be managed normally via portal/CLI again

Key takeaway

If an Azure SQL Database becomes “undeletable” through portal/CLI and the name contains unusual characters (like '<,>,*,%,&,:,\,/,?'), it may still be fully manageable from T‑SQL using delimited identifiers—and that can be the cleanest way to unblock deletion and stop unexpected costs.