How to best mimic system versioned tables in Synapse Dedicated Pool when migrating from Azure SQL Server?

Derek Horrall 196 Reputation points
2023-03-24T08:30:53.97+00:00

We are looking to migrate a database from Azure SQL Server to Synapse Dedicated pool. We use system-versioned tables (aka Temporal tables) in Azure SQL Server. Synapse Dedicated Pools do not support system versioned tables out of the box it seems. Any recommendations on how to handle this?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,422 questions
0 comments
Accepted answer
  1. Ashley Roy 80 Reputation points
    2023-03-24T08:33:15.2433333+00:00

    One solution is to use a separate database table to store the historical records of the database table. This database table should have the same columns and data types as the main database table. When a row is updated or deleted from the main database table, you should also insert/update the historical record in the separate database table. This way, you can query both database tables to get the historical version of a row. You can also use triggers to automate the process of inserting/updating the historical records.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. BhargavaGunnam-MSFT 26,496 Reputation points Microsoft Employee
    2023-04-04T00:45:40.6266667+00:00

    Hello Derek Horrall,
    Here are other approaches.

    • Disable system-versioning: Before migrating the data, disable system-versioning on the source Azure SQL Server tables. This will convert the temporal tables into regular tables.
    • Migrate data: Migrate the data from Azure SQL Server to Synapse Dedicated Pool using tools like Azure Data Factory or PolyBase.
    • Implement custom versioning: Since Synapse dedicated SQL pools do not support system-versioned tables, you can implement custom versioning logic using triggers or stored procedures to maintain the history of changes in separate history tables.