235 ) Azure migration Problems and solutions

 235 ) Azure migration Problems and solutions

Data Migration Troubleshooting: 

--------------------------------------------------------------------------------

Problem 1 : Preventing and Handling Lost Data Rows

--------------------------------------------------------------------------------

Migrating data from an on-premises SQL Server (SSMS) database to Azure requires a structured approach to prevent silent truncation errors, unhandled network timeouts, and missing rows.

Here is a detailed, step-by-step implementation guide to handle and prevent data loss during your migration.

Step 1: Establish Pre-Migration Baselines

Before moving a single byte of data, you need an immutable record of what exists in your source database. This establishes your source of truth for tables like Customers, Policies, and Claims.

• Capture Exact Row Counts: Run a snapshot count script on the source database.

• Generate Checksum Hashes: Use CHECKSUM or HASHBYTES to verify that data content hasn't changed.

• Actionable SQL Script:

  SQL

  -- Run on SourceDB to capture baseline metrics
  SELECT 
      'Customers' AS TableName, 
      COUNT(*) AS TotalRows, 
      CHECKSUM_AGG(CHECKSUM(*)) AS ChecksumValue 
  FROM dbo.Customers
  UNION ALL
  SELECT 'Policies', COUNT(*), CHECKSUM_AGG(CHECKSUM(*)) FROM dbo.Policies
  UNION ALL
  SELECT 'Claims', COUNT(*), CHECKSUM_AGG(CHECKSUM(*)) FROM dbo.Claims;
  

Save these results in a secure log or temporary table so you can compare them against the target later.

Step 2: Use Enterprise-Grade Pipelines

Avoid ad-hoc scripts or manual bulk inserts, which lack built-in resiliency against network drops. Instead, leverage Azure Data Factory (ADF) or the Azure Database Migration Service (DMS).

• Configure ADF Copy Activity: Set up a pipeline with a source dataset pointing to your SQL Server and a sink pointing to your Azure SQL Database.

• Enable Fault Tolerance: In ADF settings, configure the Fault Tolerance tab to skip incompatible rows or log incompatible data to Azure Blob Storage rather than crashing the entire pipeline.

• Configure Automatic Retries: Set retry policies on pipeline activities (e.g., 3 retries with a 30-second interval) to gracefully handle transient network timeouts or dropped packets.

• Monitor Staging: Use Azure Integration Runtime with staging enabled to optimize bulk loading performance.

Step 3: Execute Post-Migration Reconciliation

Immediately after the data load finishes, run automated validation scripts on the target Azure database to compare row counts and structural integrity against your pre-migration baselines.

• Row-Count Verification: Ensure every single row arrived successfully.

• Actionable SQL Script:

  SQL

  -- Run this post-migration reconciliation query
  SELECT 
      'Customers' AS TableName, 
      (SELECT COUNT(*) FROM SourceDB.dbo.Customers) AS SourceCount, 
      (SELECT COUNT(*) FROM TargetDB.dbo.Customers) AS TargetCount,
      (SELECT COUNT(*) FROM SourceDB.dbo.Customers) - (SELECT COUNT(*) FROM TargetDB.dbo.Customers) AS Variance
  UNION ALL
  SELECT 
      'Policies', 
      (SELECT COUNT(*) FROM SourceDB.dbo.Policies), 
      (SELECT COUNT(*) FROM TargetDB.dbo.Policies),
      (SELECT COUNT(*) FROM SourceDB.dbo.Policies) - (SELECT COUNT(*) FROM TargetDB.dbo.Policies)
  UNION ALL
  SELECT 
      'Claims', 
      (SELECT COUNT(*) FROM SourceDB.dbo.Claims), 
      (SELECT COUNT(*) FROM TargetDB.dbo.Claims),
      (SELECT COUNT(*) FROM SourceDB.dbo.Claims) - (SELECT COUNT(*) FROM TargetDB.dbo.Claims);
  

• Review Variances: If any variance is greater than 0, investigate the ADF error logs or run column-level checksum comparisons to pinpoint exactly which rows failed to migrate.

--------------------------------------------------------------------------------

PROBLEM 2 : 

--------------------------------------------------------------------------------

Handling Transaction Log Explosions and Out-of-Disk Space Errors (Error 9002)

Pushing millions or billions of rows into Azure SQL using unoptimized methods causes massive transaction log growth, quickly exhausting storage limits and triggering Error 9002.

Here is a detailed, step-by-step implementation guide to prevent and resolve transaction log exhaustion during large-scale data migrations.

Step 1: Temporarily Scale Up the Target Tier

Before starting a heavy data load, artificially inflate the resource limits of your target Azure SQL database to absorb high log generation rates without hitting ceilings.

• Upgrade to Business Critical or Hyperscale: Move the target database to a higher vCore tier or a tier designed for heavy I/O (such as Business Critical, which offers faster local SSD storage and higher transaction log throughput limits).
  
  
• Scale via Azure Portal or CLI:
  
  
  Bash

  az sql db update --resource-group MyResourceGroup --server MyServer --name TargetDB --edition BusinessCritical --compute-gen Gen5 --capacity 8
  

• Post-Migration Downscale: Once the bulk migration and index rebuild phases are fully complete, scale the database tier back down to your standard production configuration to optimize ongoing costs.
  
  

Step 2: Leverage Bulk Loading Utilities

Standard row-by-row INSERT statements log every single row individually, bloating the log file. Use bulk-optimized mechanisms that minimize logging overhead.

• Utilize the bcp Utility: The native Bulk Copy Program (bcp) writes data directly to data files with minimal logging when combined with appropriate batch parameters.
  
  
  • Example command:
    
    
    Bash

    bcp SourceDB.dbo.Claims in ClaimsData.dat -S TargetServer.database.windows.net -U username -P password -b 50000 -n -c
    

• Configure ADF for Bulk Insertion: If using Azure Data Factory, ensure the sink dataset leverages bulk copy options (such as Copy Command or staging blob storage) rather than a slow row-by-row writer.
  
  

Step 3: Implement Chunked Batching

Attempting to load a massive table like Claims in a single monolithic transaction will inevitably overwhelm the transaction log. Break the load down into manageable chunks using primary key ID ranges.

• Define Batch Windows: Divide the source table into blocks of 1 million rows using primary key boundaries.
  
  
• Actionable T-SQL Script (Chunked Insertion Loop):
  
  
  SQL

  DECLARE @BatchSize INT = 1000000;
  DECLARE @MinID BIGINT, @MaxID BIGINT;
  
  SELECT @MinID = MIN(ClaimID), @MaxID = MAX(ClaimID) FROM SourceDB.dbo.Claims;
  
  WHILE @MinID <= @MaxID
  BEGIN
      INSERT INTO TargetDB.dbo.Claims
      SELECT * FROM SourceDB.dbo.Claims
      WHERE ClaimID BETWEEN @MinID AND @MinID + @BatchSize - 1;
  
      -- Optional: checkpoint or log progress here
      SET @MinID = @MinID + @BatchSize;
  END
  

• Truncate Transaction Logs Between Chunks: If necessary, run checkpoint commands or brief pauses between large batch blocks to allow Azure SQL to flush the transaction log safely.

---------------------------------------------------

PROBLEM 3 : Handling Foreign Key Constraint Violations and Orphan Records

---------------------------------------------------

Migrating related tables out of sequence causes foreign key constraint violations when child records (such as Policies referencing Customers, or Claims referencing Policies) arrive at the target database before their corresponding parent records exist.

Here is a detailed, step-by-step implementation guide to manage relational dependencies and prevent orphan records during your migration.

Step 1: Enforce Strict Dependency Sequencing

If you choose to keep constraints active during the migration, you must enforce a strict, hierarchical ingestion pipeline to ensure parent data always exists before child data is inserted.

• Define the Ingestion Hierarchy: Always sequence your migration workflows in this precise order:
  
  
  1. Master/Parent Tables: Load independent entities first (e.g., Customers).
     
     
  2. Primary Child Tables: Load tables that reference the master entities next (e.g., Policies).
     
     
  3. Secondary Child Tables: Load dependent detail tables last (e.g., Claims).
     
     
• Configure Orchestration: In tools like Azure Data Factory, chain your pipeline activities sequentially using dependency conditions (e.g., run the Policies copy activity only upon successful completion of the Customers copy activity).
  
  

Step 2: Temporarily Disable Constraints on the Target

For large-scale or parallelized bulk loads, maintaining strict sequencing can slow down performance or complicate pipeline management. Disabling constraints temporarily allows you to load all tables concurrently.

• Preparation: Before starting the bulk data load, execute a script on your target Azure SQL database to disable foreign key checks.
  
  
• Actionable T-SQL Script:
  
  
  SQL

  -- Disable foreign key constraints on child tables in the target database
  ALTER TABLE dbo.Policies NOCHECK CONSTRAINT ALL;
  ALTER TABLE dbo.Claims NOCHECK CONSTRAINT ALL;
  

• Benefit: This eliminates overhead checks during insertion, dramatically accelerating bulk copy speeds and preventing abrupt pipeline failures caused by out-of-order data packets.
  
  

Step 3: Re-Enable and Validate Integrity

Once all tables are fully loaded, you must re-enable the constraints and force the database engine to check existing data for any structural violations or orphan records.

• Re-activation and Verification: Use the CHECK command, which instructs SQL Server to validate all existing rows against the constraint rules rather than just future inserts.
  
  
• Actionable T-SQL Script:
  
  
  SQL

  -- Re-enable and validate constraints against existing target data
  ALTER TABLE dbo.Policies CHECK CONSTRAINT ALL;
  ALTER TABLE dbo.Claims CHECK CONSTRAINT ALL;
  

• Handling Violations: If the script throws an error during re-activation, it means orphan records exist (e.g., a claim references a PolicyID that was never migrated). Run an outer join query between the child and parent tables to isolate the rogue rows, clean them up, and re-run the validation.