236 ) Azure insurance migration project - MYSQL to AZURE SQL

 236 ) Azure insurance migration project 

End-to-End Manual Migration Project Steps from MYSQL TO AZURE SQL 

1. Summary of the Complete Migration Guide (Steps 1 to 7)

   • Step 1: Define PK-Range Slicing for Extraction
     
     
     • Break down massive MySQL tables into manageable, smaller chunks using Primary Key (PK) ranges so your computer doesn't crash during export.
       
       
   • Step 2: Export Data to CSV Chunks
     
     
     • Export those sliced chunks from MySQL into individual CSV files saved on your local computer hard drive.
       
       
   • Step 3: Create the Empty Table in Azure SQL
     
     
     • Use SQL Server Management Studio (SSMS) as a remote control to connect to Azure SQL and run a simple CREATE TABLE script to build an empty table (loaded as an unordered "heap" for speed).
       
       
   • Step 4: Upload CSV Chunks to Azure Blob Storage
     
     
     • Create a secure cloud folder (Blob Storage account and container) in the Azure Portal and upload your local CSV chunk files into it so Azure SQL can access them.
       
       
   • Step 5: Import Data Using Bulk Insert & Automation Loop
     
     
     • Generate a secure cloud access key (SAS token) in Azure, save it inside your database in SSMS, and run an automated loop script to bulk-insert millions of rows straight from the cloud folder into your Azure SQL table in seconds.
       
       
   • Step 6: Verify Row Counts and Create Indexes
     
     
     • Run a quick row-count verification query in SSMS and build a clustered index on your table to make future searches and queries lightning-fast.
       
       
   • Step 7: Track and Compare Counts Using an Excel Sheet
     
     
     • Maintain a simple migration checklist in Excel or Google Sheets to log your original MySQL counts side-by-side with your final Azure SQL counts to guarantee a 100% successful migration.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

steps in detail

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

• Step 1 : Create Source-to-Target Mapping (STTM):

1. STTM : Build an Excel spreadsheet mapping every MySQL source column to its target Azure SQL column, explicitly documenting data type conversions, character lengths, constraints, and transformation rules
2. 
   

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

• Step 2  : Export Source Data Block-Wise:

1. Step 2: Export Source Data Block-Wise (For Billions of Records)

   When dealing with tables containing billions of rows, standard exports or offset pagination will fail or timeout. Use Primary Key (PK) range-based slicing automated via a script to generate clean CSV blocks:

   
   

   1. Find Primary Key Bounds: Query your source database to find the minimum and maximum ID values to establish your total data range:
      
      
      SQL

      SELECT MIN(id), MAX(id) FROM your_table;
      

   2. Determine Chunk Size: Set a safe row increment per file (typically 1 million to 5 million rows per CSV to balance file size, memory usage, and upload speeds).
      
      
   3. Automate with a Python Script: Run an automated script to loop through the ID ranges using BETWEEN clauses, writing out sequential CSV files (chunk_001.csv, chunk_002.csv, etc.) directly to disk:
      Python

      import mysql.connector
      import pandas as pd
      
      # Database connection configuration
      db = mysql.connector.connect(
          host="your_host", user="your_user", password="your_password", database="your_db"
      )
      
      chunk_size = 1000000
      min_id = 1  # Replace with your actual MIN(id)
      max_id = 500000000  # Replace with your actual MAX(id)
      
      current_start = min_id
      file_counter = 1
      
      while current_start <= max_id:
        current_end = current_start + chunk_size - 1
        query = f"SELECT * FROM your_table WHERE id BETWEEN {current_start} AND {current_end}"
      
        df = pd.read_sql(query, db)
        if not df.empty:
          file_name = f"table_chunk_{file_counter:03d}.csv"
          df.to_csv(file_name, index=False, encoding="utf-8")
          print(f"Exported: {file_name}")
      
        current_start = current_end + 1
        file_counter += 1
      
      db.close()  

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

• Step 3  : Export Source Data Block-Wise

Step 3: Provision Target Database Schema in Azure SQL

For beginners, the Source-to-Target Mapping (STTM) is simply a planning document (usually an Excel sheet) where you design how each column in your MySQL database will look when it arrives in Azure SQL.

Here is how you map your insurance_policy table from MySQL to Azure SQL and write the code for it.

1. Create Your STTM Mapping Table

Before writing any code, you lay out your columns in a spreadsheet like this:

Source Table (MySQL)	Source Column	MySQL Data Type & Size	Target Table (Azure SQL)	Target Column	Target Data Type & Size	Transformation / Notes
insurance_policy	policy_id	BIGINT	insurance_policy	policy_id	BIGINT	Primary Key, Not Null
insurance_policy	name	VARCHAR(255)	insurance_policy	name	VARCHAR(255)	Keep same size
insurance_policy	date	DATETIME	insurance_policy	date	DATETIME2	Upgrade to DATETIME2 for better precision
insurance_policy	amt	DECIMAL(10,2)	insurance_policy	amt	DECIMAL(10,2)	Monetary amount

2. Write the CREATE TABLE Script

Using the mapping from your STTM spreadsheet, you write your SQL Server script. For a table with billions of rows, you create it as a Heap (without a primary key or clustered index) first so that inserting billions of rows goes much faster.

SQL

CREATE TABLE insurance_policy (
    policy_id BIGINT NOT NULL,
    name VARCHAR(255),
    date DATETIME2,
    amt DECIMAL(10,2)
);

3. Execute the Script in Azure SQL

Step 3: Create the Empty Table in Azure SQL

1. Connect: We use SSMS on your computer to connect directly to your cloud database in Azure. So, open SSMS on your computer and log in using your Azure SQL server details.

2. Open Query Window: We need a blank page to write our instructions. So, click the New Query button at the top left of SSMS to open a blank white screen.

3. Paste Code: We need to tell Azure what table to build. So, paste this simple code into that blank screen:

   SQL

   CREATE TABLE insurance_policy (
       policy_id BIGINT NOT NULL,
       name VARCHAR(255),
       date DATETIME2,
       amt DECIMAL(10,2)
   );
   

4. Execute: We need to send this command to the cloud. So, click the Execute button at the top to create the empty table in Azure SQL.

1. Open SQL Server Management Studio (SSMS) or Azure Data Studio.
   
   
2. Connect to your Azure SQL Database.
   
   
3. Paste the CREATE TABLE script above into a new query window.
   
   
4. Click Execute to create your empty table, ready to receive your billions of rows from the CSV blocks.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Step 4: Upload Your CSV Chunks to Azure Blob Storage

. Why do we need to upload files first?

Azure SQL lives in the cloud, so it cannot read files directly from your personal laptop's hard drive. To move your massive CSV files into Azure SQL, we first need a temporary cloud folder that both you and Azure SQL can access.

This brings us to what Blob Storage is:

• Blob stands for Binary Large Object, but think of it simply as a secure cloud folder or a cloud flash drive where we will dump your CSV files so Azure SQL can grab them.
  
  

2. Create a Storage Account (The Cloud Storage Space)

We need to set up your personal storage space in Azure.

• Open Azure: Go to portal.azure.com in your web browser and log in with your account.
  
  
• Find Storage Accounts: In the search bar at the very top of the page, type Storage accounts and click on it when it appears.
  
  
• Start Creating: Click the blue + Create button (or + Add) near the top left.
  
  
• Fill in the Basic Details window:
  
  
  • Subscription: Choose your default subscription (usually auto-selected).
    
    
  • Resource Group: Click Create new and type a simple name like my-migration-rg, then click OK.
    
    
  • Storage account name: Type a unique lowercase name for your storage (for example: mycsvstorage12345).
    
    
  • Region: Choose a region closest to your location (e.g., Central US or South India).
    
    
  • Performance: Leave it as Standard.
    
    
  • Redundancy: Choose LRS (Locally-redundant storage, which is the cheapest and easiest option).
    
    
• Finish Creation: Click the Review + create button at the bottom. Once it finishes checking your details, click the blue Create button. Wait a minute for the deployment to finish, then click Go to resource.
  
  

3. Create a "Container" (The Actual Cloud Folder)

Inside your storage account, we need to create a specific folder (called a Container) to hold your CSV files.

• Go to Containers: On the left-hand menu of your storage account page, scroll down to the Data storage section and click on Containers.
  
  
• Add a Container: Click the + Container button at the top left.
  
  
• Fill in the New Container window:
  
  
  • Name: Type a folder name in lowercase, like csv-chunks.
    
    
  • Anonymous access level: Leave it as Private (no anonymous access) for security.
    
    
• Create it: Click the blue Create button at the bottom.
  
  

4. Upload Your CSV Files into the Cloud Folder

Now we move your CSV files from your computer into that cloud folder.

• Open your folder: Click on the container name you just created (csv-chunks) in the list.
  
  
• Upload: Click the Upload button with the folder/arrow icon near the top.
  
  
• Select files:
  
  
  • A panel will slide out on the right side. Click the Browse files (folder icon) button.
    
    
  • Navigate to your computer's hard drive where your exported CSV chunks (table_chunk_001.csv, etc.) are saved.
    
    
  • Select all your chunk files and click Open.
    
    
• Start Upload: Click the blue Upload button at the bottom of the right-side panel. Wait for the green success message to show that all your files are safely in the cloud.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Step 5: Import Data into Azure SQL Using Bulk Insert

Step 5: Import Data into Azure SQL Using Bulk Insert

Summary of Step 5 Actions:

• 5.1) Generate a Shared Access Signature (SAS) Key in Azure so your database has permission to open your cloud folder.
  
  
• 5.2) Create a Master Key and Database Credential in SSMS to securely save that cloud access key inside Azure SQL.
  
  
• 5.3) Write and run an Automation Loop Script that reads all your uploaded CSV files directly from the blob storage folder and inserts them into your table.
  
  

Understanding the Setup Before We Start:

• What is Blob Storage and why do we need it?
  Azure SQL lives in the cloud, so it cannot read files directly from your personal laptop's hard drive. Blob stands for Binary Large Object, but think of it simply as a secure cloud folder (or a cloud flash drive) where we dumped your CSV files in Step 4 so Azure SQL can grab them.
  
  
• What is Bulk Insert?
  Now that your CSV files are safely sitting in that cloud folder and your empty table (insurance_policy) is waiting in Azure SQL, we need to move the data. Bulk Insert is a high-speed command built into SQL Server that grabs millions of rows from a cloud file and dumps them into your database table all at once in seconds, rather than inserting rows one by one.
  
  

Detailed Guide for Step 5

5.1) Generate a Shared Access Signature (SAS) Key in Azure so your database has permission to open your cloud folder.

• Why this step is needed: Just like in Step 4 where we created a cloud folder to store your files, Azure SQL now needs a way to open that folder. Because your storage account is secure and private, Azure SQL cannot read it by default. In order to let Azure SQL open your cloud folder, we need to generate a secure digital key (called a Shared Access Signature or SAS token) that acts as a temporary password, giving your database the exact permission it needs to read your files.
  
  
• How to do it:
  
  
  1. Open your storage account in the Azure Portal.
     
     
  2. Click on Shared access signature on the left menu.
     
     
  3. Check boxes for Blob, Service, Container, Object, Read, and List, then set an expiration date.
     
     
  4. Click Generate SAS and connection string and copy the entire token starting with ?sv=....
     
     

5.2) Create a Master Key and Database Credential in SSMS to securely save that cloud access key inside Azure SQL.

• Why this step is needed: Once you have your cloud key, Azure SQL needs a safe place to store it so it can use it during the import. We use SSMS to open a query window and save this token inside your database as an encrypted credential.
  
  
• How to do it: Open SSMS, connect to your database, and run these two commands (make sure to paste your copied token into the secret):
  
  
  SQL

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword123!';
  
  CREATE DATABASE SCOPED CREDENTIAL [MyCloudStorageCredential]
  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
  SECRET = 'PASTE_YOUR_SAS_TOKEN_HERE';
  

5.3) Write and run an Automation Loop Script that reads all your uploaded CSV files directly from the blob storage folder and inserts them into your table.

• Why this step is needed: Because you have many split chunk files for your tables, you need to upload all the files into the blob folder first, and then the bulk load script will read the blob and upload them to the tables automatically. Writing a loop script in SSMS saves you from manually typing out the command for every single file.
  
  
• How to do it: Open a new query window in SSMS, paste the script below, update your storage account name and file count, and click Execute:
  SQL

  DECLARE @i INT = 1;
  DECLARE @max_files INT = 50; -- Change this to your total number of chunk files
  DECLARE @sql NVARCHAR(MAX);
  DECLARE @filename VARCHAR(255);
  
  WHILE @i <= @max_files
  BEGIN
      -- Automatically builds the filename web address in your Blob Storage: chunk_001.csv, chunk_002.csv, etc.
      SET @filename = 'https://yourstorageaccountname.blob.core.windows.net/csv-chunks/table_chunk_' + RIGHT('000' + CAST(@i AS VARCHAR(3)), 3) + '.csv';
  
      -- Build the BULK INSERT command dynamically to read from the cloud and load into the table
      SET @sql = '
      BULK INSERT insurance_policy
      FROM ''' + @filename + '''
      WITH (
          CREDENTIAL = [MyCloudStorageCredential],
          FORMAT = ''CSV'',
          FIRSTROW = 2,
          FIELDTERMINATOR = '','',
          ROWTERMINATOR = ''0x0a'',
          TABLOCK
      );';
  
      -- Run the command for the current file
      EXEC sp_executesql @sql;
  
      -- Move to the next file in the loop
      SET @i = @i + 1;
  END
  

•  );
  

• Repeat or Loop for Other Chunks: If you have multiple chunks (table_chunk_002.csv, etc.), change the filename in the script and run it again for each file until all billions of rows are loaded.

SQL

DECLARE @i INT = 1;
DECLARE @max_files INT = 50; -- Change this to your total number of chunk files
DECLARE @sql NVARCHAR(MAX);
DECLARE @filename VARCHAR(255);

WHILE @i <= @max_files
BEGIN
    -- Automatically builds the filename: table_chunk_001.csv, table_chunk_002.csv, etc.
    SET @filename = 'https://yourstorageaccountname.blob.core.windows.net/csv-chunks/table_chunk_' + RIGHT('000' + CAST(@i AS VARCHAR(3)), 3) + '.csv';

    -- Build the BULK INSERT command dynamically
    SET @sql = '
    BULK INSERT insurance_policy
    FROM ''' + @filename + '''
    WITH (
        CREDENTIAL = [MyCloudStorageCredential],
        FORMAT = ''CSV'',
        FIRSTROW = 2,
        FIELDTERMINATOR = '','',
        ROWTERMINATOR = ''0x0a'',
        TABLOCK
    );';

    -- Run the command for the current file
    EXEC sp_executesql @sql;

    -- Move to the next file
    SET @i = @i + 1;
END

e

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Step 6: Verify and Index Your Data After Migration

Summary of Step 6 Actions:

• 6.1) Write and run a quick verification query in SSMS to check your row counts and ensure all your billion records migrated successfully.
  
  
• 6.2) Create indexes on your table in Azure SQL to make searching and querying your data lightning-fast.
  
  

Understanding the Setup Before We Start:

• Why do we need this step?
  Just like in Step 3 where we loaded data into an empty table as a "heap" (an unordered pile of data for maximum speed during import), we intentionally left out indexes while bulk-inserting billions of rows. Now that your data is safely sitting in Azure SQL from Step 5, we need to verify everything is there and build indexes so your database performs efficiently.
  
  

Detailed Guide for Step 6

6.1) Write and run a quick verification query in SSMS to check your row counts.

• Why this step is needed: Before finishing up, we need to make sure all your chunk files from Blob Storage were successfully imported into Azure SQL without any missing rows.
  
  
• How to do it: Open a new query window in SSMS, paste this simple command, and click Execute:
  
  
  SQL

  SELECT COUNT(*) AS TotalRowsLoaded FROM insurance_policy;
  

  (Compare this number with your original total record count from MySQL to ensure a 100% match).
  
  

6.2) Create indexes on your table in Azure SQL to make searching and querying your data lightning-fast.

• Why this step is needed: Because we loaded your data as an unordered heap for maximum speed during the bulk insert in Step 5, searching for specific records right now would be slow. In order to make future queries fast, we need to build a clustered index (like an organized index at the back of a textbook) on your primary lookup column.
  
  
• How to do it: Open a new query window in SSMS, paste this command, and click Execute:
  SQL

  CREATE CLUSTERED INDEX IX_insurance_policy_id 
  ON insurance_policy (policy_id);
  

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

Step 7: Track and Compare Row Counts Using an Excel Sheet

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

Summary of Step 7 Actions:

• 7.1) Create a simple tracking Excel sheet before you start migrating to log the original table row counts from MySQL.
  
  
• 7.2) Record your post-migration counts from Azure SQL side-by-side in that same Excel sheet to verify a 100% successful migration.
  
  

Understanding the Setup Before We Start:

• Why do we need this step?
  Just like in Step 6 where we verified our data inside Azure SQL, we need a reliable way to know what the original count actually was. Since MySQL holds the billions of original rows, you cannot just guess the total. In order to keep track of multiple large tables and their chunks, we use a simple Excel tracking sheet as your migration checklist.
  
  

Detailed Guide for Step 7

7.1) Create a migration tracking Excel sheet to log your original MySQL row counts.

• Why this step is needed: Before you migrate any table, you must know your baseline. Creating an Excel sheet allows you to list every table name, its original MySQL row count, and its final Azure SQL row count so nothing gets lost.
  
  
• How to do it:
  
  
  1. Open Microsoft Excel or Google Sheets on your computer.
     
     
  2. Create simple column headers: Table Name, Original MySQL Count, Azure SQL Count, and Status.
     
     
  3. Before exporting your data, run a quick count query in MySQL for each table (e.g., SELECT COUNT(*) FROM insurance_policy;) and type that number into the Original MySQL Count column.
     
     

7.2) Record your Azure SQL counts side-by-side in Excel to verify the migration.

• Why this step is needed: Once you finish the Bulk Insert in Step 5 and run your check query in Step 6, you need a physical place to compare the numbers side-by-side to ensure every single row made it across safely.
  
  
• How to do it:
  
  
1. After running your verification query in SSMS (SELECT COUNT(*) FROM insurance_policy;), copy the final number.
   
   
2. Paste or type that number into the Azure SQL Count column in your Excel sheet right next to the original MySQL count.
   
   
3. If both numbers match completely, update the Status column to "Verified & Passed".

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

Step 8: Enterprise Best Practices (Cloud Monitoring & Data Governance) & The Data Modeler's Role

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

Summary of Step 8 Actions:

• 8.1) Set up Cloud Monitoring in Azure to keep an eye on database performance, storage usage, and system health during and after the migration.
  
  
• 8.2) Implement Data Governance by maintaining data dictionaries, tracking lineage, and ensuring data quality rules are met in your new Azure SQL environment.
  
  
• 8.3) Review the core responsibilities of a Data Modeler throughout this entire migration project.
  
  

Understanding the Setup Before We Start:

• Why do we need this step?
  Just like in Step 7 where we tracked our row counts in Excel, a real enterprise migration involves more than just copying data. In order to keep the system healthy and compliant long-term, we need cloud monitoring and data governance. Furthermore, as a data professional managing this architecture, understanding your exact role ensures the system is optimized from design to delivery.
  
  

Detailed Guide for Step 8

8.1) Set up Cloud Monitoring in Azure

• Why this step is needed: Once your billions of rows are loaded into Azure SQL, you cannot just walk away. You need automated alerts and dashboards to track CPU usage, memory, and database size so you can catch bottlenecks before they impact users.
  
  
• How to do it:
  
  
  1. Open your Azure SQL Database in the Azure Portal.
     
     
  2. Click on Metrics or Alerts on the left menu.
     
     
  3. Set up a simple alert (e.g., notify your email if DTU/CPU usage goes above 80% for more than 5 minutes).
     
     
  4. Use Azure's built-in Query Performance Insight to check if any future queries are running slow.
     
     

8.2) Implement Data Governance

• Why this step is needed: Moving data to a new cloud platform means you must maintain strict control over what data lives where, who has access to it, and what it means. Good governance prevents data chaos.
  
  
• How to do it:
  
  
  1. Data Dictionary & Glossary: Document your tables, columns, and data types (referencing your source data models built in tools like Erwin or ER/Studio).
     
     
  2. Security & Access Control: Ensure only authorized applications and users have access to sensitive columns (using Azure SQL security features like Dynamic Data Masking or Row-Level Security if needed).
     
     

What is the Job of a Data Modeler in This Task?

As a Data Modeler, your job goes far beyond just running scripts. Throughout this migration project, your core responsibilities include:

1. Designing the Target Schema: Deciding how tables should look in Azure SQL—determining primary keys, foreign keys, constraints, and whether to use a Star Schema, 3NF, or Data Vault architecture depending on the business need.
   
   
2. Strategy for Performance (Heaps vs. Indexes): Making the strategic architectural decision to load data into an unordered "heap" first for maximum speed during the bulk insert, and applying proper clustered/non-clustered indexes after the load is complete.
   
   
3. Managing Data Chunks & Slicing: Defining the Primary Key (PK) slicing strategy to chop up billions of rows safely so extraction and loading don't overwhelm system memory or storage limits.
   
   
4. Ensuring Data Integrity & Quality: Validating that the data types, structures, and business rules match seamlessly between the source MySQL system and the target Azure environment without data corruption or truncation.