230 ) Azure project to migrate : SSMS TO AZURE CLOUD steps

 No, not all of the tools were used in the previous steps (Azure Databricks, Azure Blob Storage/ADLS Gen2, and Microsoft Purview were missing).

Outline of steps

Here is the complete, detailed migration guide for all steps, formatted with clear explanations, specific tools, and their exact purposes:

Step 1: Assessment and Discovery

Why we are doing this: In order to analyze our existing on-premises SSMS database for compatibility issues, unsupported features, and hidden schema dependencies before moving to the cloud, we need an assessment tool. For this analysis, we use:

• Microsoft Data Migration Assistant (DMA): used for data analysis, checking SQL compatibility, and identifying unsupported features in SSMS.
  
  
• Microsoft Purview: used for data classification, discovery, and mapping sensitive customer/policy data across your assets.
  
  

Step 2: Security & Credential Setup

Why we are doing this: In order to secure our database connection strings, authentication passwords, and encryption keys so they are never exposed in plain text during migration, we need a centralized vault. For this security management, we use:

• Azure Key Vault: used for securely storing, managing, and retrieving sensitive credentials, secrets, and database connection strings.
  
  

Step 3: Staging Raw Data Landing Zone

Why we are doing this: In order to export massive volumes of raw insurance records (customer, policy, claims, agents) from on-premises SSMS as CSV files or backups without impacting live operations, we need a scalable cloud storage area. For this temporary raw storage, we use:

• Azure Blob Storage / Azure Data Lake Storage (ADLS Gen2): used as a secure cloud storage staging zone to land raw data files before processing.
  
  

Step 4: Heavy Data Cleansing and Transformation

Why we are doing this: In order to clean, filter, normalize, and process large volumes of complex insurance claims and policy datasets before loading them into the final database, we need an advanced analytics engine. For heavy data transformations, we use:

• Azure Databricks: used as a collaborative, Spark-based analytics platform to run data cleansing scripts and prepare massive datasets.
  
  

Step 5: Target Database Setup

Why we are doing this: In order to host our core relational insurance tables in a fully managed, high-performance cloud database with automated backups and auto-scaling, we need a managed database service. For this primary destination, we use:

• Azure SQL Database (or Azure SQL Managed Instance): used as the target relational database replacement for on-premises SSMS to store structured tables (customer, policy, claims, agents).
  
  

Step 6: Orchestration and Data Loading

Why we are doing this: In order to automate the workflow of moving cleaned data from cloud storage and Databricks into our final Azure SQL database tables on a scheduled pipeline, we need a cloud ETL orchestration service. For this pipeline management, we use:

• Azure Data Factory (ADF): used as a cloud-scale ETL orchestration service to build data pipelines for moving and syncing data.
  
  

Step 7: Synchronization, Cutover, and Go-Live

Why we are doing this: In order to switch production applications from the old on-premises SSMS to the new cloud environment with zero data loss, we run final validation tests, sync delta changes, and point applications to Azure. For this final phase, we use:

• Azure Data Factory (ADF) & Azure Key Vault: used together to execute final delta synchronization and securely switch application connection strings to complete the go-live.

Here is the complete, updated end-to-end migration guide incorporating all 6 tools, formatted with the requested simple explanations of why we do each step and which tools are used.

Phase 1: Governance Discovery & Secret Setup

Why we are doing this step: In order to protect sensitive customer policy details, ensure compliance, and keep track of our database credentials safely, we need to set up enterprise security and data governance. For data governance, we use the tool called Microsoft Purview, and for storing secret connection strings safely, we use Azure Key Vault.

Steps:

1. Scan and Classify Data with Microsoft Purview:
   
   
   • Open Azure Portal -> Search for Microsoft Purview accounts -> Launch your Purview Studio.
     
     
   • Go to Data map -> Sources -> Register new source -> Select your on-premises SQL Server.
     
     
   • Run a New Scan to automatically discover customer and policy tables, classify sensitive data, and map data lineage.
     
     
2. Secure Credentials 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 source/target database connection strings securely so no hardcoded passwords are exposed.
     
     

Phase 2: Staging Raw Data Landing Zone

Why we are doing this step: In order to safely export massive volumes of raw insurance records (CSV files of policies, customer lists, and claims) from on-premises systems without slowing down live operations, we need a secure cloud storage staging area. For this temporary raw storage, we use the tool called Azure Blob Storage / Azure Data Lake Storage (ADLS Gen2).

Steps:

1. Provision Storage Account:
   
   
   • Open Azure Portal -> Search for Storage accounts -> Click Create (stinsurancerawdata).
     
     
   • Enable Hierarchical namespace (Azure Data Lake Storage Gen2) for advanced folder structures.
     
     
2. Create Staging Containers:
   
   
   • Go to your Storage Account -> Click Containers -> Click + Container and create two containers: raw-landing-zone and processed-data.
     
     
   • Export tables (customer, policy, claims, agents) from SSMS as CSV files and upload them into the raw-landing-zone container.
     
     

Phase 3: Heavy Data Cleansing and Transformation

Why we are doing this step: In order to clean, filter, and process large amounts of complex insurance claims and policy datasets before moving them into the final database, we need a powerful analytics engine. For heavy data transformations, we use the tool called Azure Databricks.

Steps:

1. Provision Databricks Workspace:
   
   
   • Search for Azure Databricks in Azure Portal -> Click Create (db-insurance-workspace).
     
     
2. Run Transformation Notebook:
   
   
   • Launch Databricks Workspace -> Create a new Cluster (Standard runtime).
     
     
   • Create a Python/PySpark Notebook to read the raw CSV files from ADLS Gen2, clean missing values, normalize claim statuses, and save the refined datasets back to the processed-data container:
     
     
     Python

     # Sample PySpark code to clean claims data
     df = spark.read.format("csv").option("header", "true").load("abfss://raw-landing-zone@stinsurancerawdata.dfs.core.windows.net/claims.csv")
     cleaned_df = df.na.drop(subset=["claimID"]).filter(df["claimAmount"] > 0)
     cleaned_df.write.format("parquet").mode("overwrite").save("abfss://stinsurancerawdata.dfs.core.windows.net/processed-data/claims_cleaned")
     

Phase 4: Target Database Setup

Why we are doing this step: In order to host our core insurance application data in a fully managed, high-performance cloud database with automated backups and auto-scaling, we need a relational cloud database. For this primary destination, we use the tool called Azure SQL Database (or Azure SQL Managed Instance).

Steps:

1. Provision Azure SQL Database:
   
   
   • Open Azure Portal -> Search for SQL databases -> Click Create (InsuranceCloudDB).
     
     
   • Select your Resource Group and configure server settings using admin credentials retrieved securely from Azure Key Vault.
     
     
2. Create Target Tables via SSMS/Azure Data Studio:
   
   
   • Connect to your new Azure SQL database and run the schema setup script:
     
     
     SQL

     CREATE TABLE customer (customerID INT PRIMARY KEY, custName VARCHAR(100), company VARCHAR(100));
     CREATE TABLE agents (agentID INT PRIMARY KEY, agentName VARCHAR(100), region VARCHAR(50), commissionRate DECIMAL(5,2));
     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);
     CREATE TABLE claims (claimID INT PRIMARY KEY, policyID INT FOREIGN KEY REFERENCES policy(policyID), claimDate DATE, claimAmount DECIMAL(12,2), claimStatus VARCHAR(30));
     

Phase 5: Orchestration and Data Loading

Why we are doing this step: In order to automate the workflow of moving cleaned data from cloud storage into our final database tables on a scheduled or trigger-based pipeline, we need a cloud ETL orchestration service. For this data pipeline orchestration, we use the tool called Azure Data Factory (ADF).

Steps:

1. Create Data Factory:
   
   
   • Search Data factories in Azure Portal -> Click Create (adf-insurance-migration).
     
     
2. Build Copy Pipeline:
   
   
   • Open Azure Data Factory Studio -> Create Linked Services connecting to Azure Data Lake Storage Gen2 (source) and Azure SQL Database (sink, pulling connection strings from Azure Key Vault).
     
     
   • Create a Pipeline containing Copy activities to move data from the processed parquet folders into your Azure SQL tables (customer, agents, policy, claims).
     
     
   • Click Add Trigger -> Trigger Now to execute the pipeline load.
     
     

Phase 6: Cutover and Go-Live

Why we are doing this step: In order to complete the migration and safely transition users and apps to the new cloud infrastructure without data loss, we must stop legacy writes, verify data integrity, and switch application connections.

Steps:

1. Run Validation Checks:
   
   
   • Run row count comparisons between SSMS and Azure SQL Database to verify complete data synchronization.
     
     
2. Switch Connection Strings:
   
   
   • Update your application configuration files to pull the final connection strings from Azure Key Vault, pointing all traffic to Azure SQL Database.
     
     
3. Decommission Legacy Systems:
   
   
• Monitor performance via Azure Monitor and safely power down the on-premises SSMS servers.

FAQ interview questions on above 

Here are the 10 technical interview questions along with their ideal answers based on the end-to-end SSMS to Azure migration project:

1. Assessment & Discovery

• Question: Why did you use Microsoft Data Migration Assistant (DMA) in Step 1, and what specific types of issues does it help identify before migrating from on-premises SSMS to Azure SQL Database?
  
  
• Answer: DMA is used to analyze the source database for compatibility issues. It scans the database schema, stored procedures, and T-SQL code to flag breaking changes, unsupported or deprecated features (like cross-database queries or specific CLR integrations), and schema dependencies that won't work out-of-the-box in Azure SQL Database, allowing us to fix them before attempting the migration.
  
  

2. Data Governance & Discovery

• Question: How does Microsoft Purview complement DMA during the initial assessment and discovery phase of an insurance database migration?
  
  
• Answer: While DMA focuses purely on technical database engine compatibility, Microsoft Purview is used for enterprise data governance and discovery. It scans all data assets to classify sensitive information (like PII, customer names, and policy details) and maps data lineage across systems, which is crucial for meeting compliance regulations like GDPR and HIPAA.
  
  

3. Security & Secret Management

• Question: Why is it considered a security risk to store connection strings directly in application code, and how does Azure Key Vault solve this during migration?
  
  
• Answer: Hardcoding connection strings exposes database credentials, usernames, and passwords to anyone with access to the code repository, creating a major security vulnerability. Azure Key Vault solves this by centralizing secrets management, allowing applications to securely fetch connection strings at runtime via managed identities without exposing plain-text credentials.
  
  

4. Staging & Landing Zones

• Question: In Step 3, why didn't you migrate data directly from SSMS to Azure SQL Database? What is the benefit of using Azure Data Lake Storage (ADLS Gen2) as an intermediate staging landing zone?
  
  
• Answer: Extracting massive volumes of operational data directly can cause performance bottlenecks and lock tables on a live production SSMS server. Staging raw data (such as CSV exports or backups) into ADLS Gen2 provides a safe, fault-tolerant landing zone, creates a raw historical backup, and allows heavy data cleansing before loading it into the target database.
  
  

5. Big Data Transformation

• Question: When would you choose Azure Databricks over a standard database query for transforming your insurance claims and policy datasets?
  
  
• Answer: Azure Databricks is chosen when dealing with massive volumes of data or complex, unstructured/semi-structured datasets that require distributed computing (using Apache Spark). While a standard SQL query handles relational updates well, Databricks is built for heavy data cleansing, large-scale transformations, and machine learning preparation across huge volumes of raw files.
  
  

6. Target Architecture Selection

• Question: What are the key advantages of migrating core tables (customer, policy, claims, agents) to Azure SQL Database compared to keeping them on an on-premises SSMS instance?
  
  
• Answer: Azure SQL Database provides fully managed operations, meaning automated patching, backups, and high availability out of the box. It also offers elastic scalability, allowing you to instantly scale up vCores or storage during peak workloads (like annual policy renewal seasons) without over-provisioning expensive physical hardware.
  
  

7. ETL & Pipeline Orchestration

• Question: How does Azure Data Factory (ADF) integrate with Azure Databricks and Azure SQL Database in an end-to-end data pipeline?
  
  
• Answer: ADF acts as the central orchestrator. It triggers the extraction of raw data into ADLS Gen2, invokes Azure Databricks notebooks to run data cleansing and transformation jobs, and finally orchestrates the copy activity to load the processed data into the target Azure SQL Database tables.
  
  

8. Handling Incremental Changes (Delta Sync)

• Question: During the transition phase, how do you handle ongoing transactions (inserts/updates) on your legacy SSMS tables so you don't lose data before final cutover?
  
  
• Answer: We handle ongoing transactions by implementing Change Data Capture (CDC) or by configuring ADF incremental load pipelines using watermark columns (such as LastModifiedDate or auto-incrementing IDs). This ensures that any new policies or claims added after the initial bulk load are synced over to Azure.
  
  

9. Validation and Testing

• Question: What validation steps would you perform after moving the policy and claims tables to Azure SQL to guarantee zero data loss?
  
  
• Answer: We perform a multi-tier validation:
  
  
  1. Row-count verification to ensure the number of records matches between SSMS and Azure SQL for each table.
     
     
  2. Aggregate checksum tests (e.g., comparing SUM(claimAmount) or SUM(premiumAmt)) to verify mathematical accuracy.
     
     
  3. Running parallel query performance tests to ensure foreign key relationships and indexes are functioning correctly.
     
     

10. Troubleshooting Cutover Failures

• Question: If an application throws a connectivity error immediately after switching its connection string to Azure SQL Database during go-live, what are the first three things you would troubleshoot?
  
  
• Answer:
  
  
1. Firewall / Network Rules: Check if the application's IP address or Virtual Network (VNet) is allowed in Azure SQL's firewall or private endpoint settings.
   
   
2. Credentials & Key Vault: Verify that the application is successfully fetching the correct username and password from Azure Key Vault and that the database user has proper permissions.
   
   
3. Connection String Syntax: Confirm that the connection string includes required parameters like Encrypt=True and specifies the correct server and database names.