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

Question

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?

Answer 1

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.

Answer 2

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.