We have a system that is currently used by customers that have versions of SQL from 2008 R2 all the way to 2019. We want to start introducing, in a limited way, Temporal Tables, even for customers who are using SQL versions earlier than 2016.
Our plan is to create the necessary History tables, which are identical to the respective root tables, except for two main differences:
1) 2 additional columns,
SysStartTime datetime2 NOT NULL DEFAULT SYSUTCDATETIME() and
SysEndTime datetime2 NOT NULL DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ), added to the new History table. For 2016 usage, these columns would also be added to the root table, but we're using triggers for the pre-2016 version, and we can't be modifying the
SysEndTime columns in the middle of the trigger.
2) No primary key, foreign keys, or other constraints on the History table, although from what I've read, indexes are permitted, though these likely would need to be dropped before converting to SQL Server 2016.
Then, for the root table, create an Update/Delete trigger that will populate the history table when data is updated or deleted.
My questions are:
1) Will this methodology be able to be migrated without loss of data when a client upgrades to SQL 2016 or newer?
2) What would those migration steps be?
I believe we would need to have two schemas for our database, one for clients pre-2016 that uses the home-grown pseudo-temporal tables, and one for 2016 clients (or newer) that uses the built-in functionality, with a series of scripts that can take them from one form to the other, but with a solid upgrade plan, I believe this would be manageable.