none
Temporal Table Migration from VM to Azure SQL RRS feed

  • Question

  • I recently migrated my SQL 2019 database from a VM into Azure SQL.
    I used the MS Data Migration tool, but unfortunately, it wouldn't migrate data from Temporal Tables.

    So. I just used the tool to create the table schemas and then used SSIS to move the data.  

    Since my existing history table had data in it, I wanted to keep the SysStartDate and SysEndDate fields.  In order to do this, I had to disable SYSTEM_VERSIONING in my Azure SQL database as well as DROP the PERIOD on the table.

    The data migration was a success so I re-created my PERIOD on the table but when I tried to enable SYSTEM_VERSIONING with a specified history table, I get the following error:

    >Msg 13573, Level 16, State 0, Line 34<br>
    Setting SYSTEM_VERSIONING to ON failed because history table 'xxxxxHistory'<br> contains overlapping records.

    I find this odd because the existing tables were originally joined as a temporal table so I don't understand why there would be a conflict now.

        ALTER TABLE xxx.xxx 
        ADD PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)

        ALTER TABLE xxx.xxx 
        SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=xxx.xxxHistory))

    I expect to get a successful temporal table. Instead, I get the following error: 

    >Msg 13573, Level 16, State 0, Line 34<br>
    Setting SYSTEM_VERSIONING to ON failed because history table 'xxxxxHistory'<br> contains overlapping records.

    I ran the following query to identify the overlaps but I don't get any:

        SELECT 
        xxxxKeyNumeric
        ,SysStartTime
        ,SysEndTime
        FROM 
        xxxx.xxxxhistory o
        WHERE EXISTS
        (
            SELECT 
       
        FROM 
        xxxx.xxxxhistory o2
            WHERE 
        o2.xxxxKeyNumeric = o.xxxxKeyNumeric
        AND o2.SysStartTime <= o.SysEndTime
        AND o.SysStartTime <= o2.SysEndTime
        AND o2.xxxxPK != o.xxxxPK
        )
        ORDER BY 
        o.xxxxKeyNumeric, 
        o.SysStartTime
    Wednesday, July 17, 2019 5:10 PM

All replies

  • Hi johnny51714,

     

    >>I find this odd because the existing tables were originally joined as a temporal table so I don't understand why there would be a conflict now.

     

    When using temporal tables, the system performs a number of consistency checks to ensure the schema complies with the requirements for temporal and the data is consistent, and remains consistent.

     

    Before SYSTEM_VERSIONING is set to ON, a set of checks are performed on the history table and the current table. These checks are grouped into schema checks and data checks (if history table is not empty). In addition, the system also performs a runtime consistency check.

     

    For more details, please refer  to https://docs.microsoft.com/zh-cn/sql/relational-databases/tables/temporal-table-system-consistency-checks?view=sql-server-2017

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 18, 2019 6:26 AM