I've been asked to look at trimming the amount of change information that we're currently storing in a temporal table. The background to this is:
When the organisation that I work for initially implemented SQL Server 2016, one of the new features that was embraced at the time was system versioning. The versioning has been set up on a number of tables in the database, but the one I'm specifically looking at is by far the biggest. The versioned table currently contains 16.6 million rows of data, with new data being added every day. The temporal history for that table currently sits at over 829 million rows. When the versioning was implemented, absolutely no thought was given to the housekeeping of it, which has resulted in emergency action needing to be taken on a couple of occasions to stop the temporal table from swamping the server.
I've had a number of discussions with the team that are the main users of the data on this particular server. The leader of that team has made it clear that we really only need to retain six months' worth of history, which would mean that the row count in the temporal table would drop from the 829 million to 335 million.
From the reading I've done, it looks as if we can implement a sensible data-retention plan on the temporal table, which would necessitate us upgrading to SQL Server 2019. I've had discussions with one of our DBAs who's indicated that he'd be in favour of us upgrading to Server 2022, since the upgrade path from 2016 to 2022 appears to be reasonably smooth. He apparently has a number of tools at his disposal that can be used to determine whether or not there are any elements of the existing database that won't react favourably to such an upgrade.
So, my main question is this: IF we upgrade the SQL Server version from 2016 to 2022, will it be possible to retrospectively apply data retention to the temporal table? At the moment, I'm concerned that implementing a six-month data retention period on the server and therefore triggering the "disposal" of nearly 500 million rows would put the server under extreme pressure while those records were being removed. Therefore I'm hoping that we can perhaps implement a retention policy which keeps 12 months' worth of data, and then gradually tweak the policy until we end up at the desired 6 months.
In my 20+ years of dealing with SQL Server, I've never dealt with system-versioning. Therefore I'm painfully aware that I may be asking silly questions on this.
TIA
