145 ) Which Azure features and project did you work on

 

Relevant Azure Features for Insurance Migration (SSMS to Azure)

• Azure SQL Database / Azure SQL Managed Instance: Fully managed relational database services used as the target replacement for on-premises SQL Server Management Studio (SSMS) databases, offering automated backups, high availability, and auto-scaling.

• Azure Data Factory (ADF): A cloud-scale ETL/ELT orchestration service used to build data pipelines for migrating legacy insurance data and syncing incremental changes.

• Azure Databricks: A collaborative Apache Spark-based analytics platform used for heavy data transformation, cleansing, and preparing large volumes of insurance claims and policy datasets.

• Azure Blob Storage / Azure Data Lake Storage (ADLS Gen2): Scalable cloud object storage used as a secure staging landing zone for raw policy documents, CSVs, and backup files before loading into target databases.

• Azure Key Vault: Secures and manages sensitive insurance credentials, database connection strings, and encryption keys in compliance with data privacy regulations (e.g., HIPAA/GDPR).

• Microsoft Purview: Manages enterprise data governance, data mapping, and automated data lineage tracking across legacy and cloud insurance data assets.

End-to-End Migration Steps (SSMS to Azure)

1. Assessment and Discovery:

   • Audit the existing on-premises SQL Server databases using tools like the Data Migration Assistant (DMA) to identify compatibility issues, unsupported features, and schema dependencies.

2. Infrastructure and Security Setup:

   • Provision Azure resources (Azure SQL, ADF, storage accounts) and configure networking, Virtual Networks (VNets), firewalls, and role-based access control (RBAC).

   • Secure connection strings and secrets inside Azure Key Vault.

3. Data Modeling and Architecture Preparation:

   • Adapt the existing SSMS physical schema for cloud optimization, ensuring proper indexing, partitioning strategies, and data vault or star-schema structures for analytics.

4. Data Migration (Schema and Data Transfer):

   • Use the Azure Database Migration Service (DMS) or ADF pipelines to perform initial schema migration, followed by full historical data loads (bulk copy) from SSMS to Azure SQL.

5. Data Synchronization and Testing:

   • Establish change data capture (CDC) or replication streams to keep the on-premises SSMS and cloud Azure databases synchronized during testing phases.

   • Run parallel test validation, performance benchmarking, and data quality checks.

6. Cutover and Go-Live:

• Stop incoming writes to the legacy SSMS system, complete the final delta synchronization, switch application connection strings to Azure, and launch the production environment.

..

Here is the step-by-step end-to-end guide to migrate your insurance tables (Policy, Customer, Claims, Agents) from on-premises SQL Server Management Studio (SSMS) to Azure SQL Database.

Table Formats & Schemas Reference

Before starting, here is the structure of the core insurance tables being migrated:

• customer(customerID, custName, company)
  
  
• policy(policyID, customerID, agentID, policyType, premiumAmt, effectiveDate)
  
  
• claims(claimID, policyID, claimDate, claimAmount, claimStatus)
  
  
• agents(agentID, agentName, region, commissionRate)
  
  

Step 1: Assessment and Discovery

Audit your on-premises SQL Server database to check for compatibility issues before moving to Azure SQL.

1. Download and Open Tool: Download and launch the Microsoft Data Migration Assistant (DMA) on your on-premises server.
   
   
2. Create Assessment Project:
   
   
   • Click New (+) -> Assessment.
     
     
   • Project name: InsuranceDB_Assessment.
     
     
   • Assessment type: SQL Server.
     
     
   • Target server type: Azure SQL Database.
     
     
3. Connect to Source: Enter your on-premises SSMS server name, authentication details, and select your insurance database.
   
   
4. Run Assessment: Click Start assessment. Review the report for any breaking changes (such as cross-database queries or deprecated T-SQL features) and fix them in SSMS before proceeding.
   
   

Step 2: Infrastructure and Security Setup

Provision your Azure cloud environment and secure credentials.

1. Provision Azure SQL Database:
   
   
   • Open Azure Portal -> Search for SQL databases -> Click Create.
     
     
   • Resource Group: rg-insurance-prod.
     
     
   • Database name: InsuranceCloudDB.
     
     
   • Server: Create new server -> Admin login: sqladmin, Password: <StrongPassword>, Location: East US.
     
     
   • Compute + storage: Select General Purpose (Serverless or Provisioned vCores). Click Review + create -> Create.
     
     
2. Configure Networking & Firewalls:
   
   
   • In your Azure SQL Server menu, click Networking under Security.
     
     
   • Set Public network access to Selected networks and add your office/developer IP address, or configure a Private Endpoint for secure VNet integration.
     
     
3. Secure Secrets in Azure Key Vault:
   
   
   • Search for Key vaults in Azure Portal -> Click Create (kv-insurance-secrets).
     
     
   • Go to Secrets -> Click Generate/Import to store your database connection strings securely:
     
     
     Plaintext

     Name: AzureSQLConnectionString
     Value: Server=tcp:insurance-server.database.windows.net,1433;Database=InsuranceCloudDB;User ID=sqladmin;Password=<StrongPassword>;Encrypt=True;
     

Step 3: Data Modeling and Architecture Preparation

Optimize your tables for cloud performance, indexing, and foreign key relationships. Run these optimization scripts in Azure Data Studio or SSMS connected to your new Azure SQL database:

SQL

-- 1. Create Customer Table
CREATE TABLE customer (
    customerID INT PRIMARY KEY,
    custName VARCHAR(100) NOT NULL,
    company VARCHAR(100)
);

-- 2. Create Agents Table
CREATE TABLE agents (
    agentID INT PRIMARY KEY,
    agentName VARCHAR(100) NOT NULL,
    region VARCHAR(50),
    commissionRate DECIMAL(5,2)
);

-- 3. Create Policy Table with Foreign Keys
CREATE TABLE policy (
    policyID INT PRIMARY KEY,
    customerID INT FOREIGN KEY REFERENCES customer(customerID),
    agentID INT FOREIGN KEY REFERENCES agents(agentID),
    policyType VARCHAR(50),
    premiumAmt DECIMAL(12,2),
    effectiveDate DATE
);

-- 4. Create Claims Table with Foreign Key & Index for Performance
CREATE TABLE claims (
    claimID INT PRIMARY KEY,
    policyID INT FOREIGN KEY REFERENCES policy(policyID),
    claimDate DATE,
    claimAmount DECIMAL(12,2),
    claimStatus VARCHAR(30)
);

-- Add non-clustered indexes for cloud query performance
CREATE INDEX IX_Policy_CustomerID ON policy(customerID);
CREATE INDEX IX_Claims_PolicyID ON claims(policyID);

Step 4: Data Migration (Schema and Data Transfer)

Use Azure Data Factory (ADF) to copy historical data from on-premises SSMS tables to Azure SQL.

1. Provision Azure Data Factory:
   
   
   • Search Data factories in Azure Portal -> Click Create (adf-insurance-migration).
     
     
2. Create Linked Services:
   
   
   • Open Azure Data Factory Studio -> Click Manage (toolbox icon) -> Linked services -> New.
     
     
   • Source Linked Service: Search for SQL Server -> Enter your on-premises server name, database name, and credentials via a self-hosted integration runtime.
     
     
   • Sink Linked Service: Search for Azure SQL Database -> Select your subscription, server InsuranceCloudDB, and authenticate.
     
     
3. Create Copy Pipeline:
   
   
   • Go to Author (pencil icon) -> Click + -> Pipeline -> Name it Pipe_Migrate_All_Tables.
     
     
   • Drag four Copy Data activities onto the canvas (one for each table: customer, agents, policy, claims).
     
     
   • Configuration for each activity: Set Source to the respective on-premises table and Sink to the matching Azure SQL table. Ensure column mapping matches (e.g., customerID -> customerID).
     
     
4. Run Pipeline: Click Add Trigger -> Trigger Now to execute the bulk load of all records.
   
   

Step 5: Data Synchronization and Testing

Keep your on-premises system and cloud database synced while testing.

1. Enable Change Data Capture (CDC) / Replication:
   
   
   • Configure Azure Database Migration Service (DMS) for continuous online migration, or use ADF scheduled delta loads using a LastModifiedDate column tracking.
     
     
2. Run Validation Scripts:
   
   
   • Run row-count checks on both on-premises SSMS and Azure SQL to ensure zero data loss:
     
     
     SQL

     SELECT 'Customer' AS TableName, COUNT(*) AS TotalRows FROM customer
     UNION ALL
     SELECT 'Agents', COUNT(*) FROM agents
     UNION ALL
     SELECT 'Policy', COUNT(*) FROM policy
     UNION ALL
     SELECT 'Claims', COUNT(*) FROM claims;
     

3. Perform Parallel Testing: Point your internal test applications to Azure SQL and run performance benchmarks on complex joins (e.g., querying claims per customer or agent commissions).
   
   

Step 6: Cutover and Go-Live

Switch your production ecosystem fully to Azure.

1. Schedule Maintenance Window: Announce a brief downtime window to users and stakeholders.
   
   
2. Stop Incoming Writes: Disable application write access to the legacy on-premises SQL Server database.
   
   
3. Final Delta Sync: Run your ADF pipeline one final time to capture any last-minute transactions.
   
   
4. Update Connection Strings: Retrieve your connection string from Azure Key Vault (kv-insurance-secrets) and update your production insurance application config files to point to InsuranceCloudDB.
   
   
5. Go-Live Verification: Launch the application, run a test policy creation and claim status check, and monitor system health via Azure Monitor.

Applications Involved in the Insurance Ecosystem

• Core Policy Administration System: Manages policy lifecycles, quotes, underwriting rules, and renewals.

• Claims Management System: Tracks incident reports, claims adjustments, payouts, and fraud detection workflows.

• Billing and Premium Management System: Handles recurring payments, invoicing, ledger reconciliation, and payment gateway integrations.

• Legacy SQL Server Management Studio (SSMS): The on-premises database management environment being migrated away from.

• Downstream BI & Reporting Tools (e.g., Power BI): Connects to the new Azure cloud data warehouse to generate executive dashboards, risk profiles, and regulatory reports.

Role of a Data Modeler in the Migration Project

• Legacy Schema Analysis: Reverse-engineer existing SSMS database schemas to understand legacy insurance entities, relationships, constraints, and business logic.

• Cloud-Optimized Data Design: Redesign tables, constraints, and relationships to fit the target cloud environment (whether migrating to transactional cloud SQL or dimensional data models in Azure Synapse/Databricks).

• Handling Complex Insurance Entities: Structure complex hierarchical insurance concepts, such as multi-party policies, coverage riders, claims-to-policy mappings, and historical audit trails.

• Implementing Data Governance Rules: Define standardized naming conventions, data types, surrogate keys, and data integrity constraints to ensure clean data flows into the new Azure architecture.

• Collaboration with Data Engineers: Partner with engineering teams to ensure physical data models support efficient ETL transformations, partitioning strategies, and indexing performance in Azure.