The new version of SQL Server Migration Assistant (SSMA) 7.0 now supports direct migration to several new features introduced in SQL Server 2016. One of these new features is the direct support for migrating Oracle Flashback Archive tables to Temporal Tables. This is very important because earlier versions of SSMA attempted to partially migrate Flashback Archive tables to normal tables in SQL Server and left it up to the user to manually implement the behavior with a relatively high cost.
Background on SQL Server Temporal Tables and Oracle Flashback Data Archive
SQL Server 2016 system-versioned Temporal Tables provide information about data stored in the table at any point in time. SQL Server uses a separate history table that tracks changes to the table data. A system-versioned Temporal Table is a new type of user table in SQL Server 2016 designed to keep a full history of data changes and allow easy point-in-time analysis. This type of Temporal Table is referred to as a system-versioned Temporal Table because the period of validity for each row is managed by the system (i.e. database engine).
Oracle was the first to implement a similar technology. It is called Flashback Data Archive, and it was introduced in Oracle version 11g. Note that there are some differences in the implementation of Oracle Flashback Data Archive and SQL Server Temporal Tables. Oracle Flashback features use the Automatic Undo Management system to obtain metadata and historical data for transactions. They rely on undo data, which are records of the effects of individual transactions.
In this video, we will show you how SSMA 7.0 converts Oracle Flashback Data Archive tables into SQL Server Temporal Tables.
Need help getting started? Check out our SSMA Jumpstart Offer!
Known issues migrating Oracle Flashback features
Note that SSMA does not copy history data from Oracle Flashback Data Archive tables. You need to manually copy the data during the migration process. Also, SSMA does not display the history table in the SQL Server metadata explorer because it’s treated as a system table — you can see it in SQL Server Management Studio.
SQL Server 2016 does not support all of the Oracle Flashback features, including:
- Oracle Flashback Transaction Query
- DBMS_FLASHBACK Package
- Flashback Transaction
- Flashback Data Archive
- Flashback Table
- Flashback Drop
- Flashback Database
SSMA also has limitations with some features of Oracle Flashback that need to be handled manually. For example, the CM0536 error indicates that converting a select statement from an Oracle flash table failed, which happens because SQL Server does not not support the System Change Number (SCN) period in Temporal Tables. Actually, it relates to Oracle Flashback Query. We can’t convert the SCN option in the VERSIONS BETWEEN clause.
Useful references for using SSMA for Oracle to SQL Server Migration, Oracle Flashback Technology and SQL Server 2016 Temporal Tables
- Oracle Database 12c Oracle Flashback Technology: http://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm
- SQL Server 2016 Temporal Tables: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables
- Download SSMA 7.0 for Oracle
- Check out our page on Oracle to SQL Server Migration to see how DB Best uses SSMA for migration projects
Stay tuned to our blog for more information about the new features in the SQL Server Migration Assistant 7.0.
