236 ) Azure insurance migration project

 236 ) Azure insurance migration project 

 Here is the complete, beginner-friendly guide broken down from scratch. Instead of assuming prior Azure knowledge, this version explains what services to use, how to find them in the Azure portal, and walks through every single click and command step-by-step for your Customers, Policies, and Claims tables.

Step 1: Assess SSMS Insurance Database Compatibility, Define Schemas, and Prepare Sample Data

Before moving anything to the cloud, we need to check if your current local database setup works in Azure, set up your table structures using surrogate keys (auto-incrementing unique IDs), and create test data.

What are Surrogate Keys?

Surrogate keys (CustomerID, PolicyID, ClaimID) are unique sequential numbers automatically generated by the database (IDENTITY(1,1)) so every single customer, policy, and claim has a distinct, non-repeating identifier, even if two customers share the exact same name.

Run This in SSMS to Create Your Tables and Sample Data:

SQL

-- 1. Customers Table (Surrogate Key: CustomerID)
CREATE TABLE Customers (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    FullName VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Phone VARCHAR(20),
    CreatedAt DATETIME DEFAULT GETDATE()
);

INSERT INTO Customers (FullName, Email, Phone) VALUES 
('Rajesh Kumar', 'rajesh.kumar@example.com', '+919876543210'),
('Priya Sharma', 'priya.sharma@example.com', '+919123456789'),
('Venkat Rao', 'venkat.rao@example.com', '+919988776655');

-- 2. Policies Table (Surrogate Key: PolicyID, Foreign Key: CustomerID)
CREATE TABLE Policies (
    PolicyID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID),
    PolicyType VARCHAR(50) NOT NULL, -- e.g., Auto, Health, Life
    PremiumAmount DECIMAL(18,2) NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL
);

INSERT INTO Policies (CustomerID, PolicyType, PremiumAmount, StartDate, EndDate) VALUES 
(1, 'Auto', 15000.00, '2025-01-01', '2026-01-01'),
(2, 'Health', 25000.00, '2025-03-15', '2026-03-15'),
(3, 'Life', 40000.00, '2025-06-01', '2035-06-01');

-- 3. Claims Table (Surrogate Key: ClaimID, Foreign Key: PolicyID)
CREATE TABLE Claims (
    ClaimID INT IDENTITY(1,1) PRIMARY KEY,
    PolicyID INT FOREIGN KEY REFERENCES Policies(PolicyID),
    ClaimDate DATE NOT NULL,
    ClaimAmount DECIMAL(18,2) NOT NULL,
    ClaimStatus VARCHAR(30) NOT NULL -- Approved, Pending, Rejected
);

INSERT INTO Claims (PolicyID, ClaimDate, ClaimAmount, ClaimStatus) VALUES 
(1, '2025-08-10', 5000.00, 'Approved'),
(2, '2025-11-20', 12000.00, 'Pending'),
(3, '2026-02-05', 75000.00, 'Approved');

Assessment Check in SSMS:

• Download and install the latest SSMS (SQL Server Management Studio).
  
  
• Connect to your local SQL Server instance.
  
  
• Right-click your database, select Migrate SQL Server (or use the Data Migration Assistant tool) to check for compatibility blockers like Windows Authentication dependencies or cross-database queries.
  
  

Step 2: Create an Azure SQL Database Target

Azure has different database services depending on your needs:

• Azure SQL Database / Azure SQL Managed Instance: Used for OLTP (Online Transaction Processing)—handling everyday live applications where customers buy policies and file claims quickly row by row.
  
  
• Azure Synapse Analytics / Fabric: Used for DWH (Data Warehousing)—handling heavy analytical reporting and business intelligence across millions of rows.
  
  

For your insurance project's core operational tables (Customers, Policies, Claims), we will use Azure SQL Database.

How to Create It via the Azure Portal (No-Code Method):

1. Open your web browser, go to portal.azure.com, and log in with your Microsoft account.
   
   
2. At the top search bar, type "SQL databases" and click on it from the drop-down results.
   
   
3. Click the + Create button in the top left.
   
   
4. Configure Basics:
   
   
   • Subscription: Choose your active Azure subscription.
     
     
   • Resource Group: Click Create new and name it rg-insurance-migration.
     
     
   • Database name: Enter InsuranceDB.
     
     
   • Server: Click Create new, name your server insurance-azure-sql-server, pick a location closest to you (e.g., Central US or East US), select SQL authentication, enter an admin username (sqladmin) and a strong password, then click OK.
     
     
5. Click Review + create, wait for validation to pass, and click Create.
   
   

(Alternatively, if you prefer using the command line tool built into Azure, you can open Cloud Shell in the portal and run the script provided earlier).

Step 3: Provision Table Schema and Preserve Surrogate Keys on Azure

Now that your empty cloud database exists, we need to push the table blueprints (Customers, Policies, Claims) into it while ensuring the surrogate keys (IDENTITY) stay intact.

1. Open SSMS on your desktop.
   
   
2. Right-click your local insurance database containing the tables and select Tasks $\rightarrow$ Generate Scripts.
   
   
3. Click through the wizard, select Choose specific database objects, and check boxes for:
   
   
   • dbo.Customers
     
     
   • dbo.Policies
     
     
   • dbo.Claims
     
     
4. On the Set Scripting Options page, click Advanced:
   
   
   • Scroll down to Script for Database Engine Type and change it to Azure SQL Database.
     
     
   • Ensure Script Identity is set to True (this ensures your surrogate auto-increment keys transfer properly).
     
     
5. Save the script to your computer as a .sql file.
   
   
6. In SSMS, click Connect $\rightarrow$ Database Engine, and connect to your newly created Azure server (insurance-azure-sql-server.database.windows.net) using your SQL admin credentials (sqladmin and your password).
   
   
7. Open the script you just saved, select your cloud InsuranceDB from the dropdown at the top of SSMS, and click Execute. Your tables are now built in Azure!
   
   

Step 4: Migrate Data for Customers, Policies, and Claims

Now we move the actual rows of data from your local machine to the cloud database in the correct order to respect parent-child relationships.

1. In SSMS, right-click your local source database and select Tasks $\rightarrow$ Export Data... (this opens the SQL Server Import and Export Wizard).
   
   
2. Choose a Data Source: Select Microsoft OLE DB Provider for SQL Server (or SQL Server Native Client), point to your local server, and select your database using Windows or SQL Authentication.
   
   
3. Choose a Destination: Select Microsoft OLE DB Provider for SQL Server, type in your Azure SQL Server name (insurance-azure-sql-server.database.windows.net), choose SQL Server Authentication, enter sqladmin and your password, and select the InsuranceDB database.
   
   
4. Select Source Tables and Views: Check Customers, Policies, and Claims.
   
   
5. Execution Order Requirement: Because Policies relies on Customers, and Claims relies on Policies, ensure data goes across sequentially:
   
   
   • Step A: Migrate Customers first.
     
     
   • Step B: Migrate Policies second.
     
     
   • Step C: Migrate Claims third.
     
     
6. Run the package and let the data transfer complete.
   
   

Step 5: Post-Migration Validation & Cutover

Finally, you must verify that all data made it safely to Azure without breaking any relationships or missing rows.

1. In SSMS, open a new query window connected to your Azure SQL Database (InsuranceDB).
   
   
2. Run this validation script to check row counts and ensure everything matches your source database:
   
   
   SQL

   -- 1. Check Row Counts across all three tables
   SELECT 'Customers' AS TableName, COUNT(*) AS TotalRows FROM Customers UNION ALL
   SELECT 'Policies', COUNT(*) FROM Policies UNION ALL
   SELECT 'Claims', COUNT(*) FROM Claims;
   

3. Run this relationship validation query to ensure no orphan records exist (meaning every policy ties to a real customer, and every claim ties to a real policy):
   
   
   SQL

   -- 2. Verify Referential Integrity via Surrogate Keys
   SELECT c.CustomerID, p.PolicyID, cl.ClaimID
   FROM Customers c
   LEFT JOIN Policies p ON c.CustomerID = p.CustomerID
   LEFT JOIN Claims cl ON p.PolicyID = cl.PolicyID;
   

4. Cutover: Update your insurance application or web portal connection strings to point to your new Azure SQL Server instance. Your migration is complete!

***************************************

Problems faced in migration 

***************************************

Here is a detailed breakdown of 4 major problems encountered when migrating large-scale insurance tables (Customers, Policies, and Claims)—starting with your specified Problem 1—along with step-by-step solutions for each:

Problem 1 : Losing data rows while migration

• The Cause: Unhandled network timeouts, silent truncation errors due to data type mismatches, or missing rows during bulk copying can cause data discrepancies between the source SSMS database and the target Azure database.
  
  
• How to handle :
  
  
  1. Establish Pre-Migration Baselines: Run exact row-count and checksum scripts on your source tables (Customers, Policies, Claims) before moving any data.
     
     
  2. Use Enterprise-Grade Pipelines: Avoid ad-hoc manual scripts; use Azure Data Factory (ADF) or Azure Database Migration Service (DMS), which feature built-in error logging and automatic retries for dropped packets.
     
     
  3. Execute Post-Migration Reconciliation: Run automated validation queries comparing source and target row counts and checksum hashes immediately after the load:
     
     
     SQL

     SELECT 'Customers' AS TableName, (SELECT COUNT(*) FROM SourceDB..Customers) AS SourceCount, (SELECT COUNT(*) FROM TargetDB..Customers) AS TargetCount UNION ALL
     SELECT 'Policies', (SELECT COUNT(*) FROM SourceDB..Policies), (SELECT COUNT(*) FROM TargetDB..Policies) UNION ALL
     SELECT 'Claims', (SELECT COUNT(*) FROM SourceDB..Claims), (SELECT COUNT(*) FROM TargetDB..Claims);
     

Problem 2 : Transaction log explosion and out-of-disk space errors

• The Cause: When pushing millions or billions of rows into Azure SQL, standard row-by-row insert statements log every single operation, rapidly exhausting transaction log files and causing migration failure (Error 9002).
  
  
• How to handle :
  
  
  1. Scale Up Target Tier Temporarily: Temporarily scale up your target Azure SQL database tier (e.g., to a higher Business Critical or Hyperscale tier) to increase the maximum log generation throughput limit during the migration window.
     
     
  2. Leverage Bulk Loading Utilities: Use bulk data copy tools (bcp utility) or Azure Data Factory copy activities configured for high-performance bulk inserts, which minimize transaction logging overhead.
     
     
  3. Implement Chunked Batching: Break massive tables (like Claims) into smaller data blocks using primary key ID ranges (e.g., 1 million rows at a time) rather than attempting a single atomic transaction.
     
     

Problem 3 : Foreign key constraint violations and orphan records

• The Cause: Trying to migrate child tables (Policies referencing Customers, or Claims referencing Policies) out of sequence or loading rows where parent IDs do not yet exist in the target database triggers foreign key constraint violations.
  
  
• How to handle :
  
  
  1. Enforce Strict Dependency Sequencing: Always execute migrations in strict hierarchical order: Master records (Customers) $\rightarrow$ Primary Child (Policies) $\rightarrow$ Secondary Child (Claims).
     
     
  2. Temporarily Disable Constraints: Disable foreign key checks on the target database before starting the heavy bulk data load:
     
     
     SQL

     ALTER TABLE Claims NOCHECK CONSTRAINT ALL;
     ALTER TABLE Policies NOCHECK CONSTRAINT ALL;
     

  3. Re-enable and Validate: Once all data rows are successfully loaded into their respective tables, re-enable the constraints and verify integrity:
     
     
     SQL

     ALTER TABLE Policies CHECK CONSTRAINT ALL;
     ALTER TABLE Claims CHECK CONSTRAINT ALL;
     

Problem 4 : Production system performance degradation and locking during cutover

• The Cause: Running heavy migration extraction scripts directly against an active production database locks tables, creates high I/O latency, and slows down live applications processing customer policies and claims.
  
  
• How to handle :
  
  
  1. Take an Isolated Snapshot or Backup: Instead of reading live production tables directly during peak hours, take a compressed .bak backup or read from a read-only replica/secondary database.
     
     
  2. Schedule During Off-Peak Windows: Plan the final data synchronization and cutover phases during low-traffic operational windows (e.g., late weekends).
     
     
  3. Use Online Replication / CDC: For near-zero downtime migrations, set up Change Data Capture (CDC) or transactional replication to keep the Azure target synced with live source updates in real time until the final application pointer switchover.