Monitoring Azure SQL Data Sync Errors Using PowerShell
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 Azure SQL Data Sync Error Monitoring GitHub Repository What is SQL Data Sync for Azure?Connect to Azure SQL Database using a custom domain name with Microsoft Entra ID authentication
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.Azure SQL Hyperscale: Understanding PITR Retention vs Azure Portal Restore UI
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 appropriatelyWhen Azure Portal/CLI Can’t Delete an Azure SQL DB: Check the Database Name (Unsupported Characters)
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.How to Get Database‑Wise Session Details in an Azure SQL Elastic Pool Using T‑SQL
When you run multiple databases inside an Azure SQL Database elastic pool, it’s common to hit questions like: Which database is using the most sessions right now? Are we getting close to the pool’s session limit? Which application(s) are opening connections? Is connection pooling configured correctly? The Azure portal can be helpful, but you don’t always have portal access—and even when you do, you may want a quick, scriptable approach you can run from SSMS / Azure Data Studio / sqlcmd. This post provides copy‑paste T‑SQL queries to: check pool‑level session pressure, list active sessions “by database”, summarize active session counts per database, and capture a connection inventory—and then ties it all back to one of the most common root causes of high session counts: connection pooling behavior in the application. What you should know up front (setting expectations) 1) Elastic pool DMVs give you pool context from inside any pooled database The DMV sys.dm_elastic_pool_resource_stats returns usage for the elastic pool that contains the current database, including concurrent session utilization, and it can be queried from any user database in the same elastic pool. 2) Connection/session DMVs can show pool‑wide connections (with sufficient permissions) Microsoft documentation notes you can use sys.dm_exec_connections to retrieve connection details—and if a database is in an elastic pool and you have sufficient permissions, the view returns the set of connections for all databases in the elastic pool. It also calls out sys.dm_exec_sessions as a companion DMV for session details. If you run the queries below and only see your own session, it typically indicates a permissions scope limitation (the documentation notes this behavior for DMV visibility). Quick “Which query should I run?” guide Are we close to pool session limits? → Query A Which database is busy right now (active work)? → Query B Give me a ranked list of active sessions per database → Query C Which apps/hosts/users are connecting? → Query D Query A — Check elastic pool session pressure (near real‑time) Run this in any user database in the elastic pool: SELECT TOP (60) end_time, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, max_worker_percent, max_session_percent, used_storage_percent FROM sys.dm_elastic_pool_resource_stats ORDER BY end_time DESC; How to interpret it max_session_percent tells you how close your pool is to its session limit (peak session utilization in the interval). This DMV is intended for real‑time monitoring and troubleshooting and retains data for ~40 minutes. Query B — Active sessions by database (best for “what’s happening right now?”) This query focuses on sessions that are currently executing requests, and attributes them to a database by using the request’s SQL context (DB_NAME(st.dbid)). The st.dbid approach is widely used in troubleshooting patterns to show the execution context database. SELECT DB_NAME(st.dbid) AS database_name, s.session_id, s.login_name, s.host_name, s.program_name, s.client_interface_name, c.net_transport, c.encrypt_option, c.auth_scheme, c.connect_time, s.login_time, r.status AS request_status, r.command, r.start_time, r.cpu_time, r.total_elapsed_time FROM sys.dm_exec_requests AS r JOIN sys.dm_exec_sessions AS s ON r.session_id = s.session_id JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE s.is_user_process = 1 AND r.session_id <> @@SPID ORDER BY database_name, r.cpu_time DESC; What customers typically use this for Identify which database has the most active work right now See which client program and host are responsible Spot heavy or long‑running requests using cpu_time and total_elapsed_time Query C — Count active sessions per database (simple ranked view) If you want a quick summary like “DB1 has 18 active sessions; DB2 has 5…” WITH active_pool_sessions AS ( SELECT DB_NAME(st.dbid) AS database_name, r.session_id FROM sys.dm_exec_requests AS r JOIN sys.dm_exec_sessions AS s ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE s.is_user_process = 1 ) SELECT database_name, COUNT(*) AS active_sessions FROM active_pool_sessions GROUP BY database_name ORDER BY active_sessions DESC; This is a great “top list” during incidents and uses the same execution context mapping pattern (st.dbid). Query D — Connection inventory (who is connected?) Use this when you suspect connection storms, too many open sessions, or connection pooling issues. SELECT c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.original_login_name, c.connect_time, s.login_time FROM sys.dm_exec_connections AS c JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id WHERE s.is_user_process = 1 ORDER BY c.connect_time DESC; Microsoft documentation provides this exact join pattern (connections + sessions) as the baseline way to retrieve connection metadata and notes elastic pool behavior when permissions allow. Connection pooling: the #1 reason session counts spike (and how to fix it) Now that you can see sessions and connections, here’s the most common “why”: connection pooling configuration and behavior in the application. What connection pooling is? Creating a new database connection includes several time‑consuming steps: establishing a physical channel, handshake, parsing the connection string, authenticating, and other checks. To reduce that overhead, ADO.NET uses connection pooling: when your application calls Open(), the pooler tries to reuse an existing physical connection; when your application calls Close()/Dispose, the connection is returned to the pool instead of being physically closed—ready for reuse on the next Open(). Important (and often misunderstood): pooling is client‑side, but it has a very real effect on how many concurrent sessions you consume in an elastic pool. Common pooling pitfalls that cause “too many sessions” 1) Connections are not being returned to the pool (connection leaks) Pooling relies on the application calling Close()/Dispose so the pooler can return the connection to the pool for reuse. If connections aren’t closed properly, the pool can’t reuse them, and your app may keep creating new ones. What it looks like in SQL: Query D shows a growing number of sessions from the same program/host over time. Practical fix: Ensure every DB usage pattern disposes the connection (e.g., using blocks in .NET). (General best practice; the pooling mechanism’s reliance on Close/return is documented.) 2) Pool fragmentation (you accidentally create multiple pools) ADO.NET keeps separate pools for different configurations. Connections are separated into pools by connection string and (when integrated security is used) by Windows identity. Pools can also vary based on transaction enlistment and credential instances. What this means: Small differences in connection strings across services/environments can create multiple pools—each with its own connections—so total sessions can be much higher than expected. What it looks like in SQL: Query D shows many sessions from the same overall application family but with slightly different connection contexts (different apps/services). Practical fix: Keep connection strings consistent across instances where possible (same keywords, same security settings, same app identity strategy). (The “separate pools by configuration” concept is documented.) 3) “Max pool size reached” (client-side pool exhaustion) mistaken for Azure SQL limits A Microsoft Tech Community troubleshooting post shows that if you set a small Max Pool Size, you can hit client-side errors such as: “Timeout expired… prior to obtaining a connection from the pool… all pooled connections were in use and max pool size was reached.” That is not the same as hitting an Azure SQL tier limit—it’s the application waiting because it can’t obtain a connection from its own pool. How to differentiate quickly If you see “max pool size reached” / “timeout obtaining connection from the pool” → client pooling pressure. If max_session_percent in Query A is consistently high → pool-level session pressure. 4) Holding connections open longer than necessary Even with pooling enabled, if your application opens a connection and then holds it while doing non-database work, those connections remain “in use” and can’t return to the pool—causing waits and more concurrent sessions under load. (This follows directly from the documented “Open returns a pooled connection; Close returns it to the pool” behavior.) What it looks like in SQL: Query D shows many sessions from the same application. Query B/C shows many active sessions tied to one database during spikes. Practical fix: Open connections as late as possible; close as early as possible around each DB unit of work. (General best practice derived from pooling mechanics.) A simple customer checklist (quick wins) Confirm every DB call closes/disposes the connection so it can return to the pool. Avoid varying connection strings unnecessarily (prevents pool fragmentation). If you see pool wait errors (“max pool size reached”), treat it as an application pooling signal first. Use the T‑SQL queries above to validate: pool pressure (Query A) busiest databases / active sessions (Query B/C) connection sources (Query D) Wrap‑up With the queries in this post, you can troubleshoot elastic pool session behavior without relying on the Azure portal: Query A: real-time pool session/worker pressure Query B/C: database-wise view of active workload (most actionable during incidents) Query D: connection inventory (great for pooling issues and connection storms) And when session counts spike, don’t overlook the application side: connection pooling behavior (leaks, fragmentation, pool sizing, and holding connections open) is one of the most common drivers.Lessons Learned #539: Azure SQL DB Scale-Down from S3 to S2 Can Fail When Change Feed Is Enabled
Recently, I worked on a service request where a customer reported that an Azure SQL Database could not be scaled down from Standard S3 to Standard S2. The operation failed with the following message: "An unexpected error occurred while processing the request". During the troubleshooting process, we reviewed the database configuration to identify any setting that could prevent the scale-down operation. As part of that review, we executed the query select * from sys.databases and observed that the column is_change_feed_enabled had a value different from 0. This indicated that Change Feed was enabled on the database and, according to the current documentation, this setting is not supported when scaling down to Standard S0, S1, or S2 After disabling Change Feed by running EXEC sys.sp_change_feed_disable_db; we were able to complete the scale-down operation successfully.When and How to Update Statistics in Azure SQL Database
Accurate statistics are a cornerstone of good query performance in Azure SQL Database. While the platform automatically manages statistics in most scenarios, there are real‑world cases where manual intervention is not only recommended—but essential. This article explains when, why, and how to update statistics in Azure SQL Database, with practical samples and real customer scenarios drawn from production support cases. Microsoft Learn reference (overview): https://learn.microsoft.com/sql/relational-databases/statistics/statistics Why Statistics Matter SQL Server’s query optimizer relies on statistics to estimate row counts, choose join strategies, allocate memory grants, and decide whether to run operations in parallel. When statistics are stale or inaccurate, even well‑indexed queries can suddenly degrade. In Azure SQL Database: AUTO_CREATE_STATISTICS is enabled and managed by the platform AUTO_UPDATE_STATISTICS runs asynchronously Because updates are async, queries may continue running with outdated cardinality estimates until statistics refresh completes. https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide When Manual Statistics Updates Are Required 1. After Large Data Changes (ETL / Batch Operations) Customer scenario A nightly ETL job bulk‑inserts millions of rows into a fact table. The following morning, reports time out and logical reads spike. Why it happens Auto‑update thresholds are based on row‑count changes and may not trigger immediately—especially for append‑only or skewed data. Recommended action UPDATE STATISTICS dbo.FactSales; Target only the critical statistic if known: UPDATE STATISTICS dbo.FactSales (IX_FactSales_CreatedDate); 2. Query Plan Regression Without Schema Changes Customer scenario A stable query suddenly switches from a Nested Loops join to a Hash Join, increasing CPU usage and BUFFERIO waits. Root cause Statistics no longer reflect current data distribution. Recommended action UPDATE STATISTICS dbo.Customer WITH FULLSCAN; Learn more: https://learn.microsoft.com/sql/relational-databases/statistics/update-statistics 3. After Restore Operations (PITR / Geo‑Restore / Database Copy) Customer scenario After a Point‑in‑Time Restore (PITR) on a Hyperscale database, queries run slower despite healthy platform telemetry. Why it happens Statistics are restored as‑is, but workload patterns often change after the restore point. Auto‑update statistics may lag behind. Recommended action EXEC sp_updatestats; Prioritize heavily accessed tables first on large databases. Learn more: https://learn.microsoft.com/azure/azure-sql/database/recovery-using-backups Query Store Comparison: Before vs After Updating Statistics One of the most effective ways to validate the impact of statistics updates is Query Store. Before update (typical signs): Sudden plan change for the same query text Increased logical reads and CPU time Change in join strategy or memory grant After statistics update: Optimizer selects a more efficient plan Logical reads reduced CPU and duration stabilize Example workflow -- Capture runtime stats SELECT * FROM sys.query_store_runtime_stats WHERE query_id = <QueryID>; -- Update statistics UPDATE STATISTICS dbo.Orders; -- Force recompilation EXEC sp_recompile 'dbo.Orders'; Query Store reference: https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store Decision Flow: When Should I Update Statistics? Performance regression observed? | v Query plan changed without schema change? | Yes | v Recent data change / restore / ETL? | Yes | v Update targeted statistics If NO at any step, rely on automatic statistics and continue monitoring. What NOT to Do ❌ Do not run blanket WITH FULLSCAN on all tables FULLSCAN is CPU and IO expensive, especially on large or Hyperscale databases. ❌ Do not schedule frequent database‑wide sp_updatestats jobs This can introduce unnecessary workload and plan churn. ❌ Do not update statistics blindly without investigation Always validate plan regression or stale estimates using Query Store or execution plans. Checking Statistics Freshness SELECT OBJECT_NAME(s.object_id) AS table_name, s.name AS stats_name, sp.last_updated, sp.rows, sp.rows_sampled FROM sys.stats s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp ORDER BY sp.last_updated; DMV reference: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-stats-properties-transact-sql Best Practices Summary ✅ Prefer targeted statistics updates ✅ Update stats after bulk data changes or restores ✅ Validate results using Query Store ✅ Avoid unnecessary FULLSCAN operations ✅ Use stats updates as a diagnostic and remediation step, not routine maintenance Conclusion Although Azure SQL Database manages statistics automatically, asynchronous updates and changing workload patterns can result in sub‑optimal query plans. Manually updating statistics after significant data changes, restore operations, or observed plan regressions is a safe and effective best practice to restore optimal query performance.Cannot enable Change Data Capture (CDC) on Azure SQL Database: Msg 22830 + Error 40529 (SUSER_SNAME)
Issue While enabling CDC at the database level on Azure SQL Database using: EXEC sys.sp_cdc_enable_db; GO the operation fails. Error The customer observed the following failure when running sys.sp_cdc_enable_db: Msg 22830: Could not update the metadata that indicates the database is enabled for Change Data Capture. Failure occurred when executing drop user cdc Error 40529: "Built-in function 'SUSER_SNAME' in impersonation context is not supported in this version of SQL Server." What we checked (quick validation) Before applying any changes, we confirmed CDC wasn’t partially enabled and no CDC artifacts were created: -- Is CDC enabled for this database? SELECT name, is_cdc_enabled FROM sys.databases WHERE name = DB_NAME(); -- Does CDC schema exist? SELECT name FROM sys.schemas WHERE name = 'cdc'; -- Does CDC user exist? SELECT name FROM sys.database_principals WHERE name = 'cdc'; These checks were used during troubleshooting in the SR. (Also note: Microsoft Learn documents that enabling CDC creates the cdc schema/user and requires exclusive use of that schema/user.) Cause In this case, the failure aligned with a known Azure SQL Database CDC scenario: enabling CDC can fail if there is an active database-level trigger that calls SUSER_SNAME(). To identify active database-level triggers, we used: SELECT name, object_id FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND is_disabled = 0; Resolution / Workaround The customer resolved the issue by: Identifying the active database-level trigger. Disabling the trigger temporarily. Enabling CDC at the database level and then enabling CDC on the required tables. Re-enabling the trigger after CDC was successfully enabled. Post-resolution verification After enabling CDC, you can validate the state using: -- Confirm CDC enabled at DB level SELECT name, is_cdc_enabled FROM sys.databases WHERE name = DB_NAME(); And for table-level tracking, Microsoft Learn recommends checking the is_tracked_by_cdc column in sys.tables. Notes / Requirements To enable CDC for Azure SQL Database, db_owner is required. Azure SQL Database uses a CDC scheduler (instead of SQL Server Agent jobs) for capture/cleanup.DORA exit planning for Azure SQL Database: a practical, “general guidance” blueprint
Why this matters: Under the EU Digital Operational Resilience Act (DORA), many financial entities are strengthening requirements around ICT risk management, third‑party risk oversight, and—critically—exit planning / substitutability. Microsoft provides resources to help customers navigate DORA, including a DORA compliance hub in the Microsoft Trust Center. This post distills general guidance based on a real-world support thread where a customer requested a formal advisory describing an exit strategy for an Azure SQL Database workload (including a large database scenario). (Note: The content here is intentionally generalized and not legal advice—always align with your compliance team and regulators.) The customer asked Microsoft Support for a formal response to support DORA regulatory expectations, focusing on data portability, exit planning, and substitution capabilities for workloads running on Azure SQL Database. The support response framed the need as: a regulatory submission use case under DORA, where Microsoft can provide official references and describe the technical capabilities enabling portability and exit. while customers remain responsible for defining, documenting, testing, and periodically validating their exit procedures. Microsoft’s DORA resources: where to pull “regulatory artifacts” from A key part of the Support Request response was pointing to Microsoft’s formal compliance resources: Microsoft publishes DORA-related guidance and operational resilience materials via the Microsoft Trust Center and makes compliance documentation available via the Service Trust Portal for supervisory/audit processes. Microsoft also maintains a DORA compliance hub in the Trust Center aimed at helping financial institutions meet DORA requirements. Microsoft Learn provides an overview of DORA, scope, and key areas for customer consideration. Navigating DORA compliance | Microsoft Trust Center Practical takeaway: For DORA evidence packs, align your narrative to the regulator’s questions, and use Trust Center / Service Trust Portal materials as the “Microsoft-published” backbone, then attach your customer-owned exit runbooks and test evidence. Data ownership and portability: the foundation of an exit plan In the ticket’s advisory, Microsoft Support emphasized: Azure SQL Database is built on the SQL Server engine, and customers retain ownership of their data. The service supports portability through SQL Server–compatible schemas, T‑SQL, and documented export/restore mechanisms, reducing dependency on proprietary formats. How to use this in a DORA exit narrative: Frame “reversibility” as standards-based data and schema portability (SQL/T‑SQL + documented export/import). That’s exactly the type of substitutability narrative many regulators want to see. Supported exit strategy building blocks (Azure SQL Database → on‑prem SQL Server) The Support Request response described the exit approach at a high level, using supported, documented capabilities: Exporting database schema and data using SQL Server–compatible formats Restoring or importing into an on‑prem SQL Server environment with functional equivalence Maintaining security controls (auth, encryption in transit/at rest, integrity protections) during transition Validating restored data and application functionality as part of exit testing One concrete, Microsoft-documented portability method for Azure SQL Database is exporting to a BACPAC (schema + data), which can later be imported into SQL Server. BACPAC: what Microsoft documentation explicitly calls out (and why it matters for “exit planning”) Microsoft Learn documents: A BACPAC contains metadata and data and can be stored in Azure Blob storage or local storage and later imported into Azure SQL Database, Azure SQL Managed Instance, or SQL Server. Export a BACPAC File - SQL Server | Microsoft Learn For transactional consistency, ensure no write activity during export or export from a transactionally consistent copy. Blob-storage exports have a maximum BACPAC size of 200 GB; larger exports should go to local storage using SqlPackage. BACPAC is not intended as a backup/restore mechanism; Azure SQL has built-in automated backups. DORA relevance: BACPAC is a strong “portability evidence” artifact because it is explicitly positioned for “archiving” or “moving to another platform,” including SQL Server. sql-docs/azure-sql/database/database-export.md at live · MicrosoftDocs/sql-docs Large databases: why “one-button export” may not be your plan The Support Request thread highlighted a “large database” scenario and referenced that Microsoft documentation describes high-level migration patterns such as offline export/import and staged validation for large databases. In practice, if your database is far beyond BACPAC’s typical constraints (for example, BACPAC export to blob capped at 200 GB), your exit plan should explicitly describe: a staged approach (e.g., dry-run validation environment, phased cutover planning), capacity planning (network bandwidth, validation windows), and a testing cadence that produces regulator-friendly evidence. The ticket response also emphasized that customers should plan for sufficient time and capacity for transfer and validation (especially for large databases). Customer responsibilities under DORA (the part regulators care about most) A key statement from the Support Request advisory is worth repeating as general guidance: Microsoft provides the technical capabilities enabling data portability and exit, but customers remain responsible for defining, documenting, testing, and periodically validating exit procedures—including planning timelines, allocating sufficient capacity, executing test exits, and maintaining evidence for regulatory review. This aligns well with DORA’s intent and Microsoft’s broader DORA guidance narrative: DORA requires operational resilience outcomes, and organizations must integrate cloud capabilities into their governance and controls. A simple DORA-ready “exit plan checklist” you can adapt Below is a general checklist you can use to structure your exit plan documentation and evidence pack—aligned with what was emphasized in the Support Request: Scope & dependencies Identify the Azure SQL Database workloads, dependent applications, and data flows to be included in the exit plan. (Customer-owned documentation and evidence) Portability mechanism(s) Reference documented portability options such as schema+data export mechanisms (e.g., BACPAC) where applicable. Security controls during transition Document how auth and encryption controls are maintained during transfer and restoration validation. Validation plan Define how you will validate data integrity and application functionality in the target environment. Scale planning (large DBs) Document transfer capacity planning, timelines, and staged validation where needed. Evidence & audit trail Store test outputs, run logs, and references to Microsoft Trust Center / Service Trust Portal materials used in submissions. From the SR’s formal advisory perspective, the message is consistent: Azure SQL Database supports data portability and exits planning via SQL Server–compatible design and documented export/import mechanisms, Microsoft provides DORA-oriented compliance materials via the Trust Center and Service Trust Portal. and customers should own the exit runbook, testing, and evidence required for regulatory review.Multiple secondaries for Azure SQL Database failover groups (Public Preview)
Azure SQL Database failover groups are a business continuity capability that helps you manage geo-replication and coordinated failover of one or more databases from a primary Azure SQL logical server to a secondary logical server in another region. A key benefit is automatic endpoint redirection: your application can keep using the same listener endpoints, and connections are routed to the current primary after a geo-failover. Until recently, an Azure SQL Database failover group supported only one secondary server. Microsoft has now announced public preview support for up to four secondaries per failover group—unlocking more flexible DR designs, regional read scale-out patterns, and richer HA/BC architectures. Why this matters: the “one-secondary” constraint is gone Traditional failover group topologies were straightforward: one primary ↔ one secondary. That model is great for many DR plans, but it can be limiting when you want to: Distribute read-only workloads across multiple regions, Maintain multiple failover targets for broader regional resilience, Support complex compliance or geo-distribution requirements, or Migrate regions without sacrificing existing protection. With multiple secondaries, you can now design a failover group that better matches how globally distributed applications actually run—while still benefiting from the simplicity of the failover group abstraction and stable listener endpoints. What’s new in Public Preview With this enhancement, you can now: Create up to four secondary servers for a single failover group. Place these secondaries in the same or different Azure regions (with important guidance for read-only routing—covered below). Choose which secondary should receive traffic for the read-only listener endpoint (<fog-name>.secondary.database.windows.net). Fail over to any secondary (planned or forced), giving you more operational flexibility during incidents or DR drills. Supported service tiers for multiple secondaries (per the announcement): Standard, General Purpose, Premium, Business Critical, and Hyperscale. Core concept refresher: listener endpoints (and why they’re central) Failover groups expose two stable DNS-based listener endpoints: Read-write listener: <fog-name>.database.windows.net Read-only listener: <fog-name>.secondary.database.windows.net These endpoints are DNS CNAME records created when the failover group is created. After failover, DNS updates redirect the listener(s) to the new role holder. A few practical details that matter in real operations: The Learn documentation notes the DNS TTL is 30 seconds for primary and secondary listener records, which influences how quickly clients pick up endpoint changes (subject to client-side DNS caching behavior). For read-only workloads, it’s recommended to indicate read intent in the connection string using ApplicationIntent=ReadOnly. By default, failover of the read-only listener is disabled (to avoid impacting primary performance when the secondary is offline), which can be a key consideration for read-only availability expectations. Architecture: one failover group, up to four secondaries Here’s a simple way to visualize the topology: With multiple secondaries, you designate one secondary as the read-only listener endpoint target. All read-only listener traffic routes to that chosen secondary. If a failover group has only one secondary, the read-only endpoint defaults to it. How to get started (Azure portal) Creating the initial failover group is unchanged. To add additional secondaries, the announcement outlines these portal steps: Go to your Azure SQL logical server in the Azure portal. Open Failover groups under Data management. Select your existing failover group. Click Add server to add another secondary server. In the side panel, select: The secondary server to add, and The read-only listener endpoint target (a dropdown that lists existing and newly added secondaries). Click Select, then Save to apply the configuration—seeding begins for databases in the group to the new secondary. Important note from the announcement: if you intend to serve read workloads via the read-only listener, the designated read-only target should not be in the same region as the primary. PowerShell examples (create / modify / fail over) The announcement provides PowerShell examples to create and update a failover group with multiple secondaries: Create a failover group with multiple secondaries New-AzSqlDatabaseFailoverGroup ` -ResourceGroupName "myrg" ` -ServerName "primaryserver" ` -PartnerServerName "secondaryserver1" ` -FailoverGroupName "myfailovergroup" ` -FailoverPolicy "Manual" ` -PartnerServerList @("secondary_uri_1","secondary_uri_2","secondary_uri_3","secondary_uri_4") ` -ReadOnlyEndpointTargetServer "secondary_uri_1" The secondary_uri_n values are Azure resource IDs in the form: /subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.Sql/servers/<server> Add/replace the secondary server list on an existing failover group Set-AzSqlDatabaseFailoverGroup ` -ResourceGroupName "myrg" ` -ServerName "primaryserver" ` -FailoverGroupName "myfailovergroup" ` -FailoverPolicy "Manual" ` -PartnerServerList @("secondary_uri_1","secondary_uri_2","secondary_uri_3","secondary_uri_4") ` -ReadOnlyEndpointTargetServer "secondary_uri_1" Perform a failover Use Switch-AzSqlDatabaseFailoverGroup (example shown in the announcement): Switch-AzSqlDatabaseFailoverGroup ` -ResourceGroupName "myrg" ` -ServerName "secondaryserver1" ` -FailoverGroupName "myfailovergroup" Planned vs. forced failover (operational clarity) The preview announcement highlights portal actions for: Failover (planned / synchronized) Forced failover (unplanned / potential data loss) The Learn documentation further clarifies that Failover performs full synchronization (no data loss) and requires the primary to be accessible, while Forced failover can result in data loss because replication is asynchronous and doesn’t wait for final propagation. Limitations and notes (preview behavior) From the announcement: Up to four secondaries per failover group. Each secondary must be on a different logical server than the primary. Secondaries can be in the same or different regions. The read-only listener endpoint target should be in a different region from the primary if you want to use the read-only listener for read workloads. Failover group name must be globally unique within .database.windows.net. Chaining (geo-replica of a geo-replica) isn’t supported. Backup storage redundancy and zone redundancy inheritance behaviors vary by tier; the announcement calls out: For non-Hyperscale, zone redundancy isn’t enabled by default on secondaries (enable after creation). For Hyperscale, secondaries inherit HA settings from their respective primaries. Best practices (practical guidance) The announcement and documentation recommend several patterns worth emphasizing: Prefer paired regions when feasible for better performance than unpaired regions. Test regularly using planned failovers (DR drills) so you validate your end-to-end plan. Monitor replication lag to ensure your RPO expectations are being met. Keep failover group scope reasonable (number of databases impacts failover duration; the documentation advises limiting databases per group and using multiple groups if needed). Use the right endpoint for the right workload: Writes → <fog-name>.database.windows.net (read-write listener) Reads → <fog-name>.secondary.database.windows.net (read-only listener) Monitoring example: sys.dm_geo_replication_link_status The DMV sys.dm_geo_replication_link_status returns a row per replication link and includes fields like partner_server, partner_database, last_replication, and replication_lag_sec, which you can use to track replication health. A simple query (run in the user database participating in geo-replication) is: SELECT partner_server, partner_database, last_replication, replication_lag_sec FROM sys.dm_geo_replication_link_status; (These columns and their meanings are documented in the DMV reference.) Closing thoughts Multiple secondaries for Azure SQL Database failover groups is a meaningful step forward for customers running globally distributed applications. It combines the operational simplicity of failover groups—stable listener endpoints and coordinated failover—with added architectural freedom: more DR targets, more read scale-out options, and smoother migration pathways. If you’re already using failover groups today, this preview is a great opportunity to revisit your topology and ask: Where would additional geo-secondaries reduce risk or improve performance for my read-heavy workloads? If you’re designing new, this capability expands what “good” looks like for resilient, regionally distributed data tiers. Learn more (official resources) Multiple secondaries for failover groups is now in public preview Failover groups overview & best practices (Azure SQL Database) Configure a failover group (Azure SQL Database) sys.dm_geo_replication_link_status DMV reference
