How to archive your database data in “Stealth Mode”.
Enterprise Database Administration & Deployment
I know I should be archiving some of this data. My boss wants me to archive some of this data. But the database is so complicated. It has so many foreign keys. There are so many level of hierarchy. When could I ever schedule an outage or how can I do this without impacting production. It’s so risky. What if I mess up?
Learn how to design and implement a “Database Archiving Strategy” in a graceful, almost unnoticed manner I call “Stealth Mode”. The method is this. Reverse engineer your database so you know the hierarchy levels of your database. Determine the sequence order in which tables must be archive. Use transaction code, include simple double checks. Use indexed temp tables to minimize impact on other tables and to increase performance and speed. Archive in very small batches that run in 1 to 5 seconds, repeat many times during the lowest activity part of the night. Never drop and reapply foreign keys. Minimize locking and blocking and yield to more important tasks.
Good understanding of T-SQL. Good understanding of foreign keys and the parent / child table relationships.