How to lift and shift huge on premise data warehouse to azure synapse dedicated SQL pool

Question

Cannot find any proper document or way to lift and shift on premise database to azure synapse dedicated SQL pool . Is it not possible to do it directly?

Answer 1

Hi Dev R,

Thanks for reaching out to Microsoft Q&A.

Lifting and shifting a large on-prem data warehouse to an synapse dedicated SQL pool is possible, though not necessarily direct due to the differences between traditional on-prem dbs and synapse sql pool (which is optimized for massively parallel processing and big data analytics).

Here's a general approach you can take:

Steps to Lift and Shift:

1. Assessment and Planning:

• DAta warehouse structure: review your current on-premise data warehouse (schemas, data types, indexes, etc.) And compare them with azure synapse capabilities.
• Data volume: estimate the size of the data, table distribution, and partitioning strategy.
• Data dependencies: identify any external dependencies like etl processes, stored procedures, or linked services.

2. Schema Conversion:

• Export schema: use tools like ssms or other db tools to export your schema (tables, views, indexes, etc.) to a script.
• Adjust for synapse compatibility: modify the schema for compatibility with azure synapse sql pool.
• Not all on-prem sql server features (like certain data types, triggers, or stored procedures) will directly transfer.
• For ex, synapse does not support traditional indexing like sql server, and you may need to adjust to clustered columnstore indexes or distribution methods (hash, replicate, or round_robin).

3. Data Migration Tools:

• Azure data factory (adf): ADF can be a primary tool for migrating data to synapse. You can use the copy data tool to pull data from on-premise sources to synapse. Adf supports parallelism and can handle huge datasets efficiently.
• Data migration assistant (DMA): use dma to assess the readiness of your data warehouse for migration and identify potential compatibility issues.
• Azure synapse pathway: this tool can convert sql scripts from sql server (or other databases) into a format compatible with synapse sql pool.

4. Data Movement Strategy:

• Staged approach: if the data is huge, consider staging the data in azure blob storage or data lake, then using polybase or adf to import into synapse.
• Batch Data Movement: Break the data into manageable batches for transfer. Use the polybase or bulk insert mechanism to import data.
• Incremental Loads: Post-initial migration, set up mechanisms to handle incremental loads or changes from on-premise databases to synapse using tools like adf.

5. ETL/ELT Adjustments:

• If your etl processes are tightly coupled with the on-premise database, you might need to refactor them to leverage synapse pipelines or data factory.

6. Post-Migration Validation:

• Once data is migrated, run validation checks for data consistency.
• Test queries to ensure performance aligns with expectations and apply optimization techniques like partitioning or tuning distribution methods.

Note:

• Azure Synapse does not fully support all T-SQL functionalities available in SQL Server, especially when it comes to procedural code.
• Post-migration, you might need to refactor your queries and procedures for optimal performance within Synapse's massively parallel processing environment.

While a direct "lift and shift" approach might not be feasible due to platform differences, the migration process using ADF, PolyBase, and other tools is well supported. Consider breaking down the migration into schema conversion, data movement, and post-migration optimization.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.