Thursday Architecture Lesson #1
Azure Compute: VM, App Service, Container Apps or AKS? Don't memorize the services. Remember this: V → VM Need maximum control or have a legacy workload. A → App Service Want managed hosting for web apps or APIs. C → Container Apps Want containers without taking on Kubernetes complexity. K → AKS You genuinely need Kubernetes capabilities. 🧠 The Architect's Rule: “Don't choose AKS because you can. Choose AKS because you need it.” The right architecture isn't the one with the most powerful technology. It's the one that gives the business the right balance of: Capability • Cost • Security • Scalability • Operational complexity For example, if a simple web application can run effectively on App Service, introducing Kubernetes may add complexity without adding meaningful business value. The best architects don't ask: “What is the most advanced technology we can use?” They ask: “What is the simplest architecture that meets the requirements?” 💬 Your turn: If you were modernizing a traditional 3-tier application today, which would you choose first — VM, App Service, Container Apps or AKS? And most importantly, why?Getting Started with Azure SQL Data Sync Using User-Assigned Managed Identity (UAMI)
Azure SQL Data Sync is a powerful service that enables data synchronization across multiple Azure SQL Databases. Traditionally, Data Sync relied on SQL Authentication, requiring administrators to manage usernames and passwords for both hub and member databases. With the introduction of User-Assigned Managed Identity (UAMI) support, Azure SQL Data Sync now provides a more secure and modern authentication model built on Microsoft Entra ID. This enhancement reduces credential management overhead, eliminates password storage concerns, and helps organizations strengthen their security posture. In this article, we'll explore how to onboard Azure SQL Data Sync with UAMI, configure the required permissions, create synchronization components, and review best practices for long-term management. Why Use UAMI with Azure SQL Data Sync? User-Assigned Managed Identity offers several advantages over traditional SQL authentication: Eliminates password storage and rotation requirements Reduces credential exposure risks Integrates with Microsoft Entra ID authentication Supports centralized identity management Improves compliance and security posture Simplifies operational maintenance Whether you're deploying a new synchronization topology or modernizing an existing Data Sync environment, UAMI provides a secure cloud-native alternative to password-based authentication. Architecture Overview A typical UAMI-enabled Azure SQL Data Sync deployment looks like this: Authentication Method: Microsoft Entra ID via User-Assigned Managed Identity The same managed identity can be used by Data Sync to authenticate against all participating databases. Prerequisites Before configuring Azure SQL Data Sync with UAMI, ensure the following prerequisites are completed: 1. Enable Microsoft Entra Authentication The Azure SQL Server hosting both Hub and Member databases must have Microsoft Entra authentication enabled and an Entra administrator configured. 2. Create a User-Assigned Managed Identity Create a UAMI within Azure and collect the following information: Managed Identity Name Client ID Resource ID These values will be required during configuration. 3. Install the Required PowerShell Module UAMI support requires the Azure SQL preview PowerShell module: Install-Module Az.Sql -RequiredVersion 6.6.0-preview ` -AllowPrerelease ` -Force ` -AllowClobber Or a later version that includes Data Sync UAMI support. Phase 1: Configure Database Access Before Data Sync can use a managed identity, the identity must be granted access to all databases participating in synchronization. Connect to each Hub and Member database using a Microsoft Entra administrator account and create a user for the managed identity. Example: DECLARE @MSIname SYSNAME = '<UAMI_NAME>'; DECLARE @dbName SYSNAME = DB_NAME(); DECLARE @clientId UNIQUEIDENTIFIER = '<CLIENT_ID>'; -- Create User -- Grant db_datareader -- Grant db_datawriter -- Grant CONTROL permissions The UAMI must be created and granted permissions on every database participating in synchronization. Phase 2: Create the Sync Group After permissions are configured, create the Sync Group using UAMI authentication. Example PowerShell: New-AzSqlSyncGroup ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $hubDatabase ` -Name $syncGroup ` -HubDatabaseAuthenticationType UserAssigned ` -ResourceId $identityResourceId Key Parameters Parameter Description HubDatabaseAuthenticationType Authentication method for Hub Database UserAssigned Enables UAMI authentication ResourceId Full ARM Resource ID of the UAMI Phase 3: Add Sync Members Once the Sync Group is created, add member databases and specify UAMI authentication. Example: New-AzSqlSyncMember ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $hubDatabase ` -SyncGroupName $syncGroup ` -Name $memberName ` -MemberDatabaseAuthenticationType UserAssigned ` -ResourceId $identityResourceId At this stage, both Hub and Member databases are configured to authenticate using the managed identity. Phase 4: Configure Synchronization Refresh Hub Schema Refresh the schema metadata before selecting tables for synchronization. Update-AzSqlSyncSchema ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $hubDatabase ` -SyncGroupName $syncGroup Configure the Synchronization Schema Data Sync requires an explicit schema definition specifying which tables and columns will participate in synchronization. Example schema: { "Tables": [ { "QuotedName": "[dbo].[contacts]", "Columns": [ { "QuotedName": "[id]" }, { "QuotedName": "[name]" } ] } ] } Apply the schema: Update-AzSqlSyncGroup ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $hubDatabase ` -Name $syncGroup ` -HubDatabaseAuthenticationType UserAssigned ` -ResourceId $identityResourceId ` -SchemaFile "C:\schema.json" Trigger Synchronization After configuration is complete, trigger the initial synchronization. Start-AzSqlSyncGroupSync ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $hubDatabase ` -SyncGroupName $syncGroup Phase 5: Rotating UAMIs Over time, organizations may need to replace an existing managed identity. UAMI_v1 → UAMI_v2 Only one UAMI can be assigned to a Sync Group or Sync Member at a time. To rotate identities: Grant required database permissions to the new UAMI. Remove the current UAMI. Assign the new UAMI in the same operation. Update Sync Group Update-AzSqlSyncGroup ` -HubDatabaseAuthenticationType UserAssigned ` -ResourceId $newUamiResourceId ` -RemoveIdentityResourceId $oldUamiResourceId Update Sync Member Update-AzSqlSyncMember ` -MemberDatabaseAuthenticationType UserAssigned ` -ResourceId $newUamiResourceId ` -RemoveIdentityResourceId $oldUamiResourceId Common Pitfalls During implementation, the following issues are commonly encountered: Pitfall #1: Missing Entra Administrator If the SQL Server does not have an Entra administrator configured, UAMI authentication will fail. Pitfall #2: Missing Database Permissions The managed identity must be created and granted permissions in every participating database. Pitfall #3: Multiple UAMIs Assigned Azure SQL Data Sync supports only one UAMI per Sync Group or Sync Member at a time. Pitfall #4: Identity Included During PATCH Updates When updating schemas through REST APIs, avoid re-submitting the identity block if the managed identity is already assigned. This may result in a DataSyncMultipleIdentities error. Validation Checklist Before moving to production, verify the following: Microsoft Entra administrator configured UAMI created successfully UAMI user created in all Hub and Member databases Required permissions granted Sync Group configured with UserAssigned authentication Sync Members configured with UserAssigned authentication Schema refreshed successfully Synchronization schema applied Initial synchronization completed successfully Data synchronized correctly between Hub and Member databases Best Practices For secure and scalable deployments: Prefer UAMI over SQL Authentication for new deployments. Use dedicated managed identities for Data Sync workloads. Follow least-privilege access principles where possible. Test configuration changes in non-production environments first. Maintain documentation for identity ownership and rotation procedures. Monitor synchronization health after identity updates. Standardize naming conventions for managed identities. References The following Microsoft resources provide additional details for implementing Azure SQL Data Sync with User-Assigned Managed Identities: 1. Configure Microsoft Entra Authentication for Azure SQL Database Configure an Entra administrator for Azure SQL Database, which is a prerequisite for UAMI-based authentication. https://learn.microsoft.com/azure/azure-sql/database/authentication-aad-configure 2. Manage User-Assigned Managed Identities Learn how to create and manage User-Assigned Managed Identities in Azure. Managed identities eliminate the need to manage credentials in code and can be reused across multiple Azure resources. Manage user-assigned managed identities using the Azure portal explains the process and prerequisites. [Manage use...soft Learn | Learn.Microsoft.com] 3. Connect to Azure SQL Using Microsoft Entra Authentication Configure and validate Entra-based connectivity for Azure SQL Database before granting permissions to the managed identity. https://learn.microsoft.com/azure/azure-sql/database/authentication-microsoft-entra-connect-to-azure-sql 4. Azure SQL Data Sync PowerShell Documentation Review PowerShell cmdlets used to create Sync Groups, Sync Members, refresh schemas, and trigger synchronization. 5. Azure SQL REST API Documentation Use REST APIs for automation and Infrastructure-as-Code deployment scenarios involving Azure SQL Data Sync. Conclusion User-Assigned Managed Identity support represents a significant security enhancement for Azure SQL Data Sync. By eliminating dependency on stored credentials and leveraging Microsoft Entra authentication, organizations can improve security, simplify operations, and reduce administrative overhead. Whether you're building a new synchronization topology or migrating from SQL Authentication, UAMI provides a modern, scalable, and cloud-native authentication model for Azure SQL Data Sync. As cloud environments continue to adopt identity-first security practices, implementing UAMI for Azure SQL Data Sync is an important step toward a more secure and manageable data platform.Azure SQL Data Sync Fails with "Cannot Insert NULL": Understanding the Root Cause
Azure SQL Data Sync is a powerful service that enables data synchronization across multiple Azure SQL Databases. While synchronization failures are relatively uncommon, one error that administrators occasionally encounter is SQL Server Error 515, indicating that a NULL value cannot be inserted into a non-nullable column. At first glance, this appears to be a straightforward data-quality problem. However, in many cases, the actual root cause lies elsewhere: inconsistencies between Azure SQL Data Sync tracking metadata and the underlying source data. This article explains: Common causes of Error 515 during synchronization How to troubleshoot the issue How to identify invalid tracking records Safe mitigation approaches to restore synchronization The Error A synchronization operation may fail with an error similar to the following: SqlException Error Code: -2146232060 SqlError Number: 515 Message: Cannot insert the value NULL into column 'column_name', table 'dbo.table_name'; column does not allow nulls. INSERT fails. SqlError Number: 3621 The statement has been terminated. Although the error references a NULL value being inserted into a destination table, the root cause is not always missing data. In many cases, the issue originates from synchronization metadata maintained by Azure SQL Data Sync. How Azure SQL Data Sync Tracks Changes Azure SQL Data Sync relies on internal tracking tables to detect and replicate data changes between Hub and Member databases. Whenever rows are inserted, updated, or deleted, synchronization metadata is recorded in tracking tables. Data Sync uses this metadata to determine what changes need to be propagated to other databases. If the tracking metadata becomes inconsistent with the actual source table contents, Data Sync may attempt to synchronize invalid records, resulting in failures such as: Cannot insert the value NULL into column... Common Root Causes Scenario 1: Schema Mismatch Between Databases One of the most common causes of synchronization failures is a schema mismatch between synchronized databases. For example: Database Column Definition Hub NULL Allowed Member A NOT NULL Member B NOT NULL If Data Sync replicates a row containing a NULL value from the Hub database, synchronization will fail when the destination database does not allow NULL values. Areas to Validate Ensure the following are identical across all synchronized databases: Column nullability (NULL vs NOT NULL) Data types Column length Constraints Primary key definitions Even small schema differences can cause synchronization failures. Scenario 2: Invalid Tracking Metadata A less obvious but frequently encountered scenario involves orphaned records in Data Sync tracking tables. This can occur when: Primary key values are updated directly Data is modified outside expected application workflows Historical tracking records become disconnected from source data Synchronization metadata references rows that no longer exist When Data Sync processes these stale entries, synchronization may fail with Error 515 even though the source data itself appears valid. Troubleshooting Process Step 1: Verify Column Definitions Begin by examining the affected table and column identified in the error message. For example: sp_help 'dbo.table_name' Review the schema on both Hub and Member databases and verify that: The affected column has the same definition everywhere NULL settings are identical Data types and lengths match If discrepancies exist, align the schemas across all synchronized databases before proceeding. Step 2: Review the Table Schema If the schema appears consistent, review the complete definition of the affected table. Pay particular attention to: Primary key columns Identity columns Constraints Nullable settings Identifying the primary key is especially important for the next validation step. Step 3: Check for Orphaned Tracking Records Run the following query against both Hub and Member databases. Replace: table_name primary_key with the actual table and primary key column names. SELECT COUNT(*) FROM DataSync.table_name_dss_tracking t WHERE sync_row_is_tombstone = 0 AND NOT EXISTS ( SELECT * FROM dbo.table_name s WHERE t.primary_key = s.primary_key ); For tables with composite primary keys, include all key columns in the comparison. How to Interpret the Results Result > 0 One or more orphaned tracking records exist. This indicates that the tracking table contains entries that reference records no longer present in the source table. This is a strong indicator that invalid synchronization metadata is causing the failure. Result = 0 No orphaned records were detected. If the synchronization error persists, further investigation should focus on schema consistency, data quality, and additional synchronization diagnostics. Mitigation Option 1: Correct Schema Differences If schema inconsistencies are found: Align the table definition across all synchronized databases. Ensure NULL and NOT NULL settings are consistent. Verify primary key definitions match. Reinitialize synchronization if necessary. After schema alignment, synchronization can typically resume successfully. Mitigation Option 2: Clean Invalid Tracking Data If orphaned tracking records are identified, remove the invalid synchronization metadata. Important: Always validate and test cleanup operations in a non-production environment before executing them in production. The following query removes tracking entries that no longer correspond to records in the source table: DELETE FROM DataSync.table_name_dss_tracking WHERE sync_row_is_tombstone = 0 AND NOT EXISTS ( SELECT * FROM dbo.table_name s WHERE DataSync.table_name_dss_tracking.primary_key = s.primary_key ); Replace: table_name primary_key with the appropriate values for your environment. After cleanup, Data Sync can rebuild valid change tracking information and synchronization typically returns to a healthy state. Additional Validation Query The following query can help identify historical deletion records that exist in tracking tables: SELECT tr.id1 FROM DataSync.table2_dss_tracking tr LEFT JOIN dbo.table2 orig ON tr.id1 = orig.id1 WHERE tr.sync_row_is_tombstone = 1 AND orig.id1 IS NULL AND tr.last_change_datetime > DATEADD(day, -20, GETUTCDATE()); This can provide additional insight into how synchronization metadata is tracking deleted records. Understanding the Underlying Cause The most important takeaway is that the NULL value reported in the synchronization error is often not the actual problem. A common sequence looks like this: A primary key value is modified directly. UPDATE dbo.table_name SET primary_key = new_value; Data Sync tracking metadata continues to reference the original key value. The source table and tracking table become inconsistent. During synchronization, Data Sync attempts to process the stale tracking record. The synchronization operation fails and surfaces a "Cannot insert the value NULL into column" error. In these scenarios, cleaning invalid tracking records resolves the inconsistency and restores successful synchronization. Best Practices to Prevent Recurrence To minimize the likelihood of synchronization failures: Keep schemas identical across all synchronized databases Avoid updating primary key values whenever possible Use surrogate keys for synchronized tables Validate schema consistency before deploying schema changes Periodically investigate Data Sync tracking tables when troubleshooting synchronization failures Test schema modifications in non-production environments before deployment Conclusion When Azure SQL Data Sync reports a: Cannot insert the value NULL into column... error, it is important not to assume that the problem is caused by missing data in the source table. A structured troubleshooting approach should include: Verifying schema consistency across synchronized databases Reviewing primary key definitions Investigating Data Sync tracking tables for orphaned records Cleaning invalid synchronization metadata when appropriate In many real-world cases, stale tracking records are the true root cause. Identifying and removing these invalid entries can restore synchronization quickly and avoid unnecessary application or schema changes. Have you encountered similar Azure SQL Data Sync issues in your environment? Share your experience and troubleshooting techniques in the comments below.Troubleshooting Azure SQL Data Sync Failure: SQL Error 8106 During Bulk Insert
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.Why Long-Term Retention (LTR) Backups Don’t Attach After a PITR Restore in Azure SQL Database
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
