0

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.

1 Answer 1

1

With partitioning you have a couple of additional options.

  1. 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.

  2. 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+.

4
  • True, but SQL 2016 RTM is long out of support. Jul 15, 2020 at 17:29
  • 1
    People still use SQL Server 2000, so I think the distinction will be important for some time to come. :-) Jul 15, 2020 at 17:39
  • Thanks very much for the reply. What I am looking for in table partitioning is to backup and restore the "current data" partition only, copying a much smaller database each time we need to refresh a support environment, which happens very frequently. However, I noticed that this can add a fair amount of complexity to backup maintenance, e.g. piecemeal, and I wanted to keep it simple.
    – M Barbieri
    Jul 17, 2020 at 7:00
  • I probably wouldn't bother with piecemeal restore for that. You could use a partitioned table and restore a full backup and truncate the partitions. You'd still need the extra space initially, but truncating the partitions is fast. Jul 17, 2020 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.