On SQL Server 2017 Std, I have an SSIS package that runs once a day on a 24×7 mission critical operational database to archive the largest transactional tables. The data is moved to tables on a separate archive database and the process runs smoothly with minimum locking contention on the live db (deletes are spread through a long period of time). Having a separate archiving database gives us many advantages: backup databases separately; refresh testing environments with current data only; improve query performance on current data, among others. The only drawback was code change (to add logic to union on archived data when requested by the user on the UI) and a slower cross-database query when needing to fetch archived data. We are upgrading to Enterprise edition and considering table partitioning for data archiving, but the more I read about it the more worried I get in relation to the implementation and maintenance overhead. The only real advantage I can see so far is the transparency to the application, not requiring code changes. Did any of you use table partitioning for data archiving? Is it really worth it on OLTP systems? Thanks very much in advance.
With partitioning you have a couple of additional options.
Switch older partitions to a separate table in the same database. Query is still not transparent, but advantage of is primarily that a partition switch is a metadata-only operation, only requiring a brief schema lock and not generating a significant amount of log writes.
Partition and leave the older rows in the partitioned table, perhaps setting an 'archived' status column. The older partitions can be compressed differently, and if there is some column in that differentiates the archived from non-archived rows and that column is universally used as a filter by the OLTP application, then you can create filtered indexes that differentially index the archived and non-archived rows.
And on SQL 2017 you have the simple option to make the table a Temporal Table. You would continue to DELETE older rows, but SQL Server will copy the deleted rows to the history table, manage the retention of archived rows, and provide AS OF views across the main and history table.
BTW Table and Index partitioning is available in every edition of SQL Server 2016 SP1+.