Is it possible to Auto Archive Old Data into Read Only partitions on a very large table with existing PS/PF but then also allow continued automatic pruning/dropping of those read only partitions i.e. dropping data after 3 years for GDPR...
So whilst I can see it is possible to a) auto archive old data into a read only filegroup (to backup once and save from normal backups going forward) and b) automatically drop partitions that fall out of set retention periods
I'm not sure that I can combine the two? It also seems you cannot switch data into a partition that isn't on the same FG (the proposed method for archival) - but equally if its in the same FG I can't make it read only(?) - Ideally it would automatically work out what date was 3yrs/2rs ago and alter the PS/PF to redirect that data into an archive file - either natively or post move made read only.
We have up to 3 CDR tables that share the same FG/FS. Partitioned by date, CCI, currently spread across 4 generic files. Controlled through an automated process to forward create partitions 0 and importantly here - drop partitions that go past retention period. Issue is these are huge tables and take forever in backups which they don't need to be in everyday - one backup of say 2018, and 2019 would be fine... but I'd need to maintain that auto dropping mechanism to comply with GDPR - so potentially need a daily level archive file that is then dropped? (Assuming can drop a read only parititon, and dynamically make today minus 2 years read only on a rolling basis?
Hope that makes sense - running on sql server 2017 btw.