The Real-World Guide to Migrating SQL Server to Databricks

I remember the first time a CIO looked me in the eye and said, "We're going all-in on the cloud. I need you to lead the migration of our core SQL Server data warehouse to Databricks." On the surface, it sounded straightforward. We'd move some tables, convert some stored procedures, and call it a day. I was wrong. Terribly wrong.

That first project was a trial by fire. It taught me that migrating from a highly structured, on-premises, relational world like SQL Server to a distributed, cloud-native lakehouse platform like Databricks is not a "lift and shift." It's a fundamental paradigm shift. It's trading the rigid safety of a relational database management system (RDBMS) for the immense scale and flexibility of distributed computing on open data formats.

Since then, I've led numerous production migrations from SQL Server to Databricks for companies in finance, retail, and healthcare. I've seen what works, what breaks spectacularly, and what silently drains your cloud budget. This isn't a theoretical guide. This is my playbook, forged from late nights spent debugging Spark jobs, untangling decade-old T-SQL, and explaining to stakeholders why their favorite query was suddenly 100 times slower.

If you’re facing this journey, my goal is to give you the map I wish I’d had. We’ll go deep into the weeds, look at real code, and dissect the failures so you can avoid them.

Phase 1: Before You Move a Single Byte: The Critical Discovery Phase

The biggest mistake I see teams make is underestimating the complexity of their own SQL Server estate. They jump straight into data movement without a complete inventory. This is a recipe for disaster. You can't migrate what you don't understand. Your first step is to become an archeologist of your own systems.

Building Your Migration Inventory with Python

Your SQL Server environment is more than just tables. It's a web of views, stored procedures, functions, triggers, linked servers, and SQL Server Agent jobs. You need a complete, data-driven inventory of every single object. Manually creating this list is impossible and error-prone. We automate this.

On one project, we initially missed a set of arcane user-defined functions (UDFs) that were critical for financial calculations. The business users' reports were wrong for two weeks post-migration before anyone noticed. We learned our lesson: inventory everything.

Here’s a Python script, similar to what I use as a starting point, to connect to SQL Server and pull a detailed object inventory. This script uses pyodbc and pandas to extract the information and save it to a CSV file for analysis.

    import pyodbc
    import pandas as pd
    import logging

    # --- Configuration ---
    SERVER = 'your_sql_server_name.database.windows.net'
    DATABASE = 'your_database_name'
    USERNAME = 'your_username'
    PASSWORD = 'your_password'
    DRIVER = '{ODBC Driver 17 for SQL Server}' # Make sure this driver is installed
    OUTPUT_CSV = 'sql_server_inventory.csv'

    # --- Logging Setup ---
    logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

    def get_db_connection():
        """Establishes and returns a database connection."""
        try:
            conn_str = f'DRIVER={DRIVER};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD}'
            conn = pyodbc.connect(conn_str)
            logging.info(f"Successfully connected to database: {DATABASE} on {SERVER}")
            return conn
        except pyodbc.Error as ex:
            sqlstate = ex.args[0]
            logging.error(f"Database connection failed. SQLSTATE: {sqlstate}. Error: {ex}")
            raise

    def fetch_inventory(conn):
        """Queries system tables to get a comprehensive list of all database objects."""

        # This query joins multiple system views to get a rich inventory.
        # It includes object type, schema, creation/modification dates, and the full definition for code objects.
        query = """
        SELECT
            o.name AS object_name,
            s.name AS schema_name,
            o.type_desc,
            o.create_date,
            o.modify_date,
            m.definition,
            (SELECT COUNT(*) FROM sys.dm_sql_referenced_entities(s.name + '.' + o.name, 'OBJECT')) AS referenced_entities_count,
            (SELECT COUNT(*) FROM sys.dm_sql_referencing_entities(s.name + '.' + o.name, 'OBJECT')) AS referencing_entities_count
        FROM
            sys.objects AS o
        JOIN
            sys.schemas AS s ON o.schema_id = s.schema_id
        LEFT JOIN
            sys.sql_modules AS m ON o.object_id = m.object_id
        WHERE
            o.is_ms_shipped = 0  -- Exclude Microsoft's system objects
            AND o.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE')
        ORDER BY
            s.name, o.type_desc, o.name;
        """

        logging.info("Executing inventory query...")
        try:
            df = pd.read_sql(query, conn)
            logging.info(f"Fetched {len(df)} objects from the database.")
            return df
        except Exception as e:
            logging.error(f"Failed to execute inventory query: {e}")
            raise

    def main():
        """Main execution function."""
        conn = None
        try:
            conn = get_db_connection()
            inventory_df = fetch_inventory(conn)

            # Add a placeholder for migration readiness - we'll populate this later
            inventory_df['readiness_score'] = 0
            inventory_df['migration_notes'] = ''

            inventory_df.to_csv(OUTPUT_CSV, index=False, encoding='utf-8-sig')
            logging.info(f"Inventory successfully exported to {OUTPUT_CSV}")

        except Exception as e:
            logging.error(f"An error occurred during the inventory process: {e}")
        finally:
            if conn:
                conn.close()
                logging.info("Database connection closed.")

    if __name__ == '__main__':
        main()

This script gives you a sql_server_inventory.csv file that is your single source of truth. It contains every table, view, stored procedure, and function, along with its definition. This is the foundation for everything that follows.

Unraveling the Spaghetti: Dependency and Usage Analysis

Now you have a list of objects. The next question is: how do they connect? Which procedures update which tables? Which views depend on other views? Running sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities as shown in the script is a good start, but to truly visualize it, you need to build a dependency graph.

For a complex migration, I've used Python libraries like networkx to process the output from these DYNAMIC MANAGEMENT VIEWS (DMVs) and build a graph. This allows you to find:
* Orphaned Objects: Procedures and tables that are no longer called by anything. Low-hanging fruit for decommissioning, not migration.
* Critical Paths: The core chains of execution that power key business processes.
* Logical Groupings: Tightly coupled objects that must be migrated together as a single "work package."

Visualizing this graph for stakeholders is incredibly powerful. When they see a web of dependencies originating from a single, 20-year-old stored procedure, they finally understand why this isn't a weekend job.

The Migration Readiness Report

With the inventory and dependency map in hand, you can create a readiness report. This is where you triage the entire estate. I extend the initial Python script to parse the definition column for known T-SQL anti-patterns that are difficult to migrate to Spark.

Here's a conceptual addition to the script to perform a basic readiness analysis:

    # (Inside your main script, after fetching the inventory_df)

    def analyze_readiness(df):
        """Analyzes T-SQL definitions for migration complexity."""

        # Patterns that are difficult or impossible to directly translate to Spark SQL
        ANTI_PATTERNS = {
            'CURSOR': 10,
            'WHILE': 5,
            'GOTO': 10,
            '#temp': 3,          # Temporary tables
            'sp_executesql': 8,  # Dynamic SQL
            'xp_cmdshell': 10,   # External commands (Major blocker)
            'TRANSACTION': 7,    # Multi-statement transactions
            'MERGE': 2,          # Can be mapped to Delta MERGE, but needs review
            'ROW_NUMBER() OVER': 1 # Generally easy, but good to flag for review
        }

        def score_object(row):
            score = 0
            notes = []
            if pd.notna(row['definition']):
                sql_code = row['definition'].upper()
                for pattern, weight in ANTI_PATTERNS.items():
                    if pattern in sql_code:
                        score += weight
                        notes.append(f"Contains '{pattern}'")

            # Add more rules here, e.g., based on line count
            if pd.notna(row['definition']) and len(row['definition']) > 2000:
                score += 5
                notes.append("Large object ( > 2000 lines)")

            return score, '; '.join(notes)

        df[['readiness_score', 'migration_notes']] = df.apply(
            score_object, axis=1, result_type='expand'
        )
        return df

    # --- In your main function ---
    inventory_df = fetch_inventory(conn)
    inventory_df = analyze_readiness(inventory_df)
    inventory_df.to_csv(OUTPUT_CSV, index=False, encoding='utf-8-sig')

This simple scoring system immediately highlights the monsters in your closet. A stored procedure with a score of 25 (e.g., uses a CURSOR, WHILE loop, and TRANSACTION) is not a candidate for automated conversion. It requires a full manual rewrite. This report allows you to prioritize, estimate effort, and set realistic expectations with the business.

Phase 2: The Battleground: Tackling Common Migration Challenges Head-On

With your plan in place, the real work begins. This is where you'll encounter a series of technical hurdles. I've grouped them into the categories where I've seen the most projects stumble.

1. Connectivity & Drivers: The First Handshake

The Problem: You can't even read the data. Your first Spark job fails with a cryptic java.sql.SQLException: No suitable driver found or a connection timeout.

Why it Happens:
On a project for a financial services client, their on-premises SQL Server was locked down behind a corporate firewall. The Databricks workspace in Azure couldn't reach it. In another case, the team used an old, unofficial SQL Server JDBC driver that didn't support Azure AD authentication, which was a security mandate.

How We Diagnosed It:
* Network First: From a notebook in Databricks, we ran %sh telnet your_sql_server_name 1433. If it doesn't connect, it's a networking issue. You need to work with your network team to set up VNet peering, ExpressRoute, or an IP allowlist on the SQL Server firewall.
* Driver & Auth: The full error stack trace in the Spark driver logs is your best friend. It will usually point to an authentication method mismatch or a class-not-found error for the driver. We were trying to use a username/password while the server only accepted Azure AD Integrated authentication.

Remediation and Best Practices:
* Use the Official Driver: Always use the latest official Microsoft JDBC Driver for SQL Server. Upload the JAR file to your Databricks workspace and attach it to your clusters.
* Standardize Authentication: For production, avoid embedding usernames and passwords in notebooks. Use Azure Key Vault to store secrets and retrieve them using dbutils.secrets.get(). Better yet, use Azure AD Service Principals for authentication. The connection string looks different, and it's far more secure.
* Isolate with a Test Notebook: Have a simple "Connectivity Test" notebook that does nothing but connect and run SELECT 1. Run this on any new cluster to validate connectivity before starting complex data movement jobs.

2. Schema & Data Type Mismatches: The Silent Data Corruptors

The Problem: The migration job completes without errors, but the data in Databricks is wrong. Numbers have lost precision, dates are nonsensical, or text is truncated.

Why it Happens:
SQL Server has a very specific set of data types. Spark, for performance, tries to infer schemas, and it doesn't always get it right.
* MONEY in SQL Server was inferred as a Double in Spark, leading to floating-point rounding errors in financial reports. A disaster.
* DATETIMEOFFSET was read as a string because the JDBC driver didn't know how to map it to a Spark Timestamp.
* NVARCHAR(MAX) was being read into a Spark DataFrame, and a downstream process that expected a certain structure failed.

How We Diagnosed It:
* Data Validation is Non-Negotiable: We built automated validation jobs. For every table migrated, we ran a script that compared the source (SQL Server) and target (Databricks Delta table).
* SELECT COUNT(*) FROM table
* SELECT COUNT(DISTINCT pk_column) FROM table
* SELECT SUM(numeric_column) FROM table
* SELECT MAX(date_column) FROM table
* Any discrepancy immediately flagged the table for investigation. We would then sample a few "bad" rows and compare them side-by-side to find the specific data type issue.

Remediation and Best Practices:
* Never Rely on Schema Inference for Production: It's great for exploration, but a liability for migration. Always explicitly define the schema.
* Create a Type Mapping Dictionary: Maintain a mapping of SQL Server types to Databricks/Spark types.
* DECIMAL(p, s) -> DecimalType(p, s)
* MONEY -> DecimalType(19, 4)
* INT -> IntegerType()
* DATETIME2 -> TimestampType()
* UNIQUEIDENTIFIER -> StringType()
* Use the .schema() Option: When reading from SQL Server using PySpark, define the schema programmatically.

    from pyspark.sql.types import StructType, StructField, StringType, IntegerType, DecimalType

    # Manually define the schema based on your mapping
    # This prevents Spark from guessing wrong
    explicit_schema = StructType([
        StructField("CustomerID", IntegerType(), False),
        StructField("CompanyName", StringType(), True),
        StructField("AccountBalance", DecimalType(19, 4), True)
    ])

    # Use this schema when reading
    df = spark.read \
        .format("jdbc") \
        .option("url", jdbc_url) \
        .option("dbtable", "dbo.Customers") \
        .option("user", username) \
        .option("password", password) \
        .schema(explicit_schema) \
        .load()

This simple step prevents a world of pain and ensures data fidelity, which is the cornerstone of any successful migration.

3. T-SQL to Spark SQL/PySpark: More Than Just a Syntax Swap

This is the heart of the migration effort and where most of the manual work lies. You cannot auto-convert complex T-SQL and expect it to work well. You must deconstruct the logic and re-implement it using Spark's distributed patterns.

Sub-Challenge: The 5,000-Line "God" Stored Procedure
Every legacy system has one. It runs the entire nightly batch. It's full of temp tables, cursors, complex branching logic (IF/ELSE), and multi-statement transactions.

Why it's a Problem: You can't just copy-paste this into a Spark SQL query. Spark doesn't have procedural logic, loops, or variables in the same way. Trying to replicate it one-for-one results in an unmaintainable mess that performs terribly.
Our Approach: Deconstruct, Don't Convert.
1. Print it Out: We literally printed the entire procedure and laid it out on a large table.
2. Identify Logical Blocks: We used highlighters to mark distinct sections. "This block gets customer data," "this block calculates sales aggregates," "this block updates the final reporting table."
3. Map Blocks to Databricks Tasks: Each logical block becomes a separate function or notebook in a Databricks Job.
4. Replace Temp Tables with Delta Tables: A T-SQL #temp table is replaced by writing the intermediate DataFrame to a temporary Delta table. This gives you ACID properties and schema enforcement, making it more robust than a temporary view.
5. Replace Transactions with Delta's Atomic Operations: A T-SQL BEGIN TRAN...COMMIT block that updates, inserts, and deletes from a table is replaced by a single MERGE INTO command on a Delta table. Delta Lake guarantees that the MERGE operation is atomic.

Sub-Challenge: Cursors and Loops (Row-By-Agonizing-Row Processing)
T-SQL developers often use CURSOR or WHILE loops to iterate through a result set and perform an action on each row. This is the absolute antithesis of how Spark works.

Why it's a Problem: Running row-based logic in a distributed system is incredibly inefficient. It throws away all the benefits of parallel processing.
Our Approach: Think in Sets, Not Rows.
- T-SQL Cursor:

    DECLARE @CustomerID INT;
    DECLARE cur CURSOR FOR SELECT CustomerID FROM Customers WHERE IsActive = 0;
    OPEN cur;
    FETCH NEXT FROM cur INTO @CustomerID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Do some complex update based on the customer ID
        UPDATE Orders SET Status = 'ARCHIVED' WHERE CustomerID = @CustomerID;
        FETCH NEXT FROM cur INTO @CustomerID;
    END
    CLOSE cur;
    DEALLOCATE cur;

*   **The PySpark Way (Set-Based):**

    # Get the set of inactive customer IDs
    inactive_customers_df = spark.table("customers").filter("IsActive = 0").select("CustomerID")

    # Load the orders table
    orders_df = spark.table("orders_delta_table") 

    # Join the two sets and apply the transformation
    # This is a single, distributed operation
    updated_orders_df = orders_df.join(inactive_customers_df, "CustomerID", "left_semi") \
                                 .withColumn("Status", lit("ARCHIVED"))

    # Or, even better, use Delta MERGE to update in place
    from delta.tables import DeltaTable

    delta_orders = DeltaTable.forName(spark, "orders_delta_table")

    delta_orders.alias("o").merge(
        source=inactive_customers_df.alias("i"),
        condition="o.CustomerID = i.CustomerID"
    ).whenMatchedUpdate(
        set={"Status": "'ARCHIVED'"}
    ).execute()

This mindset shift from procedural, row-based logic to declarative, set-based transformations is the single most important skill for a migration engineer.

Automated Code Analysis for T-SQL
While full automated conversion is a myth for complex logic, you can automate the detection of problematic patterns. I use a Python script with regex to scan all the stored procedure definitions from my inventory CSV.

    import pandas as pd
    import re

    def find_tsql_patterns(sql_code):
        """Uses regex to find common T-SQL patterns in a block of code."""
        if not isinstance(sql_code, str):
            return {}

        patterns = {
            "cursors": len(re.findall(r'DECLARE\s+\w+\s+CURSOR\s+FOR', sql_code, re.IGNORECASE)),
            "while_loops": len(re.findall(r'WHILE', sql_code, re.IGNORECASE)),
            "temp_tables": len(re.findall(r'#\w+', sql_code, re.IGNORECASE)),
            "dynamic_sql": len(re.findall(r'sp_executesql|EXEC\s*\(', sql_code, re.IGNORECASE)),
            "transactions": len(re.findall(r'BEGIN\s+TRANSACTION|BEGIN\s+TRAN', sql_code, re.IGNORECASE)),
            "merge_statements": len(re.findall(r'MERGE\s+INTO|MERGE\s+TARGET', sql_code, re.IGNORECASE))
        }

        # Filter out zero-count patterns
        found_patterns = {k: v for k, v in patterns.items() if v > 0}
        return found_patterns

    # Load the inventory from Phase 1
    df = pd.read_csv('sql_server_inventory.csv')

    # Filter for objects with code definitions
    code_objects_df = df[df['type_desc'].isin(['SQL_STORED_PROCEDURE', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION', 'SQL_TRIGGER'])]

    # Apply the pattern finder
    code_objects_df['tsql_patterns'] = code_objects_df['definition'].apply(find_tsql_patterns)

    # Display results
    print(code_objects_df[['schema_name', 'object_name', 'type_desc', 'tsql_patterns']])

    # This gives you a quick hit-list of complex objects to review manually
    # e.g., schema_name | object_name        | tsql_patterns
    #       dbo         | usp_NightlyProcess | {'cursors': 2, 'while_loops': 5, 'temp_tables': 12}

This script doesn't convert anything, but it tells my team exactly where to focus their manual refactoring efforts. An object with a high count of cursors and temp tables goes to the top of the priority list for a complete redesign.

4. Performance & Scalability: Why Your Query is 100x Slower

The Problem: A query that ran in 2 minutes on SQL Server now takes 3 hours on a large Databricks cluster. Management is asking why they're paying more for worse performance.

Why it Happens:
SQL Server is an "engine." You give it a query, and its sophisticated optimizer uses indexes, columnstore statistics, and a cost-based plan to execute it efficiently. Databricks, running on Spark, is a distributed framework. It gives you the power, but you are the optimizer.

On one project, a simple join between a 2-billion-row fact table and a 500-row dimension table was taking hours. In SQL Server, this was instantaneous because of a clustered index. In Spark, it was triggering a massive "shuffle" operation, sending terabytes of data across the network between worker nodes.

How We Diagnosed It:
* The Spark UI is Your X-Ray Machine. This is non-negotiable. We opened the Spark UI for the slow job and went straight to the "SQL/DataFrame" tab. We looked at the query plan (DAG). We saw a huge shuffle read/write stage associated with the join. We also noticed "skew" — one or two tasks were taking hours while hundreds of others finished in minutes.

Remediation and Best Practices:
* Partitioning and Z-Ordering: For your large fact tables, PARTITION BY a low-cardinality column that is frequently used in filters (e.g., date, country). This allows Spark to skip reading entire directories of data (partition pruning). Then, ZORDER BY high-cardinality columns used in joins or filters (e.g., customer_id, product_id). This co-locates related data within files, dramatically speeding up reads.

    -- During table creation
    CREATE TABLE sales_delta
    ( ... )
    USING DELTA
    PARTITIONED BY (sales_date)

    -- Periodically run OPTIMIZE
    OPTIMIZE sales_delta ZORDER BY (customer_id)

Broadcasting Small Tables: For that fact-to-dimension join, the fix was simple. We told Spark to broadcast the small dimension table. This sends a copy of the entire 500-row table to every worker node, completely eliminating the expensive shuffle.

    from pyspark.sql.functions import broadcast

    # The broadcast hint tells Spark to avoid a shuffle
    fact_df.join(broadcast(dim_df), "dim_key")

Tackle Skew: If your data is skewed (e.g., one customer_id has 50% of all transactions), a standard join will send all that data to a single worker, which becomes a bottleneck. The fix is to "salt" the key. Add a random number to the join key on both sides of the join, which distributes the skewed data across multiple workers. This is an advanced technique but a lifesaver for certain datasets.
Enable Photon: For new workloads, use a Databricks Runtime that includes the Photon engine. It's a C++ vectorized query engine that can provide significant speedups for standard SQL-like workloads with zero code changes. We've seen 2-5x performance gains just by flipping this switch.

5. Security & Compliance: Rebuilding the Fortress

The Problem: You've migrated the data, but now you have a security nightmare. The granular, user-based permissions from SQL Server are gone. Everyone can see everything, or no one can see anything.

Why it Happens:
SQL Server security is often tightly integrated with Windows Active Directory, with permissions granted at the schema, table, and even row level (using Row-Level Security). Databricks has its own security model, and a direct mapping is not possible.

How We Diagnosed It:
This one is usually found during User Acceptance Testing (UAT). A user from the "Germany" sales team logs in and can suddenly see sales data for "France." Or an auditor asks for an access log, and we can't provide one with the required level of detail.

Remediation and Best Practices:
* Embrace Unity Catalog (UC): For any new migration, Unity Catalog is the answer. It provides a centralized, SQL-standard governance model for all your data assets.
* Map AD Groups to Databricks Groups: Replicate your security structure by syncing your Azure AD groups into Databricks.
* Use GRANT and REVOKE: Use standard SQL commands to manage permissions on catalogs, schemas, tables, and views. This feels very natural to a SQL Server DBA.

    -- Give the 'germany_sales' group read-only access to the sales table
    GRANT SELECT ON TABLE main.reporting.sales TO `germany_sales`;

    -- Deny access to a sensitive column
    GRANT SELECT(col1, col2, col4) ON TABLE main.reporting.pii_data TO `analysts`;

*   **Implement Row-Level Security and Column Masking:** UC supports dynamic views for row-level filtering and user-defined functions for column masking. This is how you rebuild the granular security you had in SQL Server.

Cluster Policies: Control what your users can do. Create policies that limit the size of clusters they can create, enforce tags for chargebacks, and restrict access to certain libraries or init scripts.
Audit Logs: Databricks provides comprehensive audit logs that track who accessed what data and when. Ingest these logs into your central SIEM (Security Information and Event Management) system to meet compliance requirements.

6. Orchestration & Scheduling: Replacing SQL Server Agent

The Problem: The data and logic are migrated, but nothing runs automatically. The entire ecosystem of nightly batches, dependent jobs, and scheduled reports is broken.

Why it Happens:
SQL Server Agent is a powerful and mature scheduler. Teams have often built incredibly complex workflows over many years. A simple migration overlooks this critical orchestration layer.

How We Diagnosed It:
The morning after the go-live, the business calls in a panic. "Where is our daily sales report?" We check the target tables and see they haven't been updated since the manual migration run. The nightly job never ran.

Remediation and Best Practices:
* Databricks Jobs for Native Workflows: For workflows that live entirely within Databricks, use the built-in Jobs scheduler. You can create a job with multiple tasks, where each task is a notebook, a Python script, or a SQL query. You can define dependencies between tasks (e.g., Task B runs only after Task A succeeds).
* Use Job Parameters: Don't hardcode things like dates or regions in your notebooks. Pass them in as parameters from the Databricks Job configuration. This makes your notebooks reusable and easier to test.
* External Orchestrators for Complex Systems: If your workflow involves more than just Databricks (e.g., it needs to call an API, check for a file in S3, and then trigger a Databricks job), use a more powerful orchestrator like Azure Data Factory (ADF) or Airflow. ADF has a native "Databricks Notebook" activity, making integration seamless. We often use ADF as the master conductor that calls Databricks to do the heavy lifting.
* Alerting and Monitoring: Configure your Databricks Jobs to send email or webhook notifications on failure. A silent failure is the worst kind of failure.

7. Cost & Resource Optimization: The Surprise Bill

The Problem: The first monthly cloud bill arrives, and it's 3x the C-suite's estimate. Suddenly, your successful technical migration is a financial failure.

Why it Happens:
Cloud costs are consumption-based. In the on-premises world, a SQL Server box is a fixed, sunk cost. In the cloud, every second a cluster is running, you're paying for it. New teams often make costly mistakes:
* Using large, "all-purpose" interactive clusters for scheduled jobs.
* Leaving clusters running 24/7.
* Not taking advantage of spot instances.

How We Diagnosed It:
The bill itself is the first clue. Databricks provides detailed usage reports that can be broken down by user and by tag. We quickly saw that a handful of developers had spun up huge clusters for "testing" and left them running for weeks. We also saw that our nightly jobs were using expensive, on-demand VMs when they could have used cheaper alternatives.

Remediation and Best Practices:
* Job Clusters are Your Default: For any automated, scheduled workload, use a "Job Cluster." This is an ephemeral cluster that spins up, runs your job, and then terminates automatically. You only pay for the time it's actually processing data. The small startup latency is a tiny price to pay for massive cost savings.
* Leverage Autoscaling and Spot Instances: Configure your clusters to autoscale. Set a minimum and maximum number of workers. Spark will automatically scale up to handle a heavy load and scale down when idle. For non-critical or fault-tolerant workloads, use spot instances. They can offer savings of up to 90% over on-demand prices, with the caveat that they can be reclaimed by the cloud provider. We use a mix: a few on-demand workers for stability (including the driver) and a large number of spot workers for scale.
* Cluster Policies: This is a governance lifesaver. An admin can create policies that say, "Users in the 'Data Science' group can only create clusters up to size X, and these clusters will automatically terminate after 2 hours of inactivity." This prevents runaway costs.
* Tag Everything: Enforce a tagging policy on all clusters. Tag them with the project name, team, and cost center. This makes chargebacks and cost analysis trivial.

My Playbook for a Successful SQL Server to Databricks Migration

After navigating these minefields on multiple projects, I've distilled my approach into a set of core principles. This is the advice I give every new client.

Automate Discovery, Ruthlessly. Your first phase should be 80% scripting and 20% analysis. The Python scripts for inventory and analysis aren't optional; they are the foundation of a predictable migration.
Deconstruct, Don't Just Translate. Resist the urge to find a "T-SQL to Spark" converter. For anything beyond a simple SELECT statement, you must re-architect the logic. Break down monolithic procedures into a pipeline of distinct, testable Spark transformations.
Embrace the Lakehouse Paradigm. Don't treat Databricks like SQL Server on the cloud. Use Delta Lake for all your tables from day one. Its ACID transactions, time travel, and performance optimizations (OPTIMIZE, ZORDER) are what make the platform robust and scalable.
Master the Spark UI. It's not optional for developers. Teach your team how to read a query plan, spot a shuffle, and diagnose skew. An hour in the Spark UI can save days of tuning.
Build a CI/CD Pipeline Early. Don't let your developers work only in the UI. Use the Databricks CLI or tools like dbx to integrate your notebooks and code into a proper source control and deployment pipeline (e.g., GitHub Actions, Azure DevOps).
Test Obsessively and Automatically.
- Unit Tests: For complex PySpark transformations, write unit tests using a local Spark session.
- Data Validation: Automate data reconciliation between source and target for every table. Check counts, sums, and checksums.
- Performance Benchmarks: Before decommissioning SQL Server, capture performance benchmarks for key queries. Run the same logical queries on Databricks to ensure you've met or exceeded the performance baseline.
Plan for a Hybrid State. You will not switch off SQL Server overnight. There will be a period where both systems run in parallel. Plan for this. Your orchestration (e.g., ADF) needs to manage this hybrid state, and you'll need robust incremental data sync jobs to keep the lakehouse up-to-date until the final cutover.

Conclusion

Migrating from SQL Server to Databricks is a formidable task, but it's also a transformative one. You are moving from a system designed for structured data and predictable workloads to a platform built for the scale and complexity of modern data and AI.

The journey is fraught with technical challenges, but every single one is solvable with the right methodology. It requires a shift in thinking—from the procedural, row-based world of RDBMS to the declarative, set-based world of distributed computing. If you invest heavily in automated discovery, deconstruct your logic instead of merely translating it, and embrace the tools of the new platform like Delta Lake and the Spark UI, you won't just be migrating a database. You'll be building the data foundation for the future of your business. The pain is temporary, but the platform you build will be powerful and enduring.

How to migrate sql server to Databricks ?