asked
Archiving complete tables
Hello All, SQL instance - SQL server 2005 I have some tables that get created every day, 1 for each day transactions. I have many such tables(atleast 500-1000). I need to archive say tables older than 3 months. When the tables are archived (i.e. copied from main instance to archive instance, I need the original tables to be deleted from the primary DB. questions: 1. I am assuming T-sql scripts is the best way to go, am I right? or do we need to use SSIS? 2. is there any specific logic / method that I may need to use? any help is highly appreciated.
If I were setting this up, I think I'd use SSIS instead of straight T-SQL. Because you're moving the data between servers, straight T-SQL requires linked servers which can be somewhat problematic. Also, setting up retry logic or any other specialized logic within the processing will be more of a pain than it would be within SSIS where you have a lot more control. This means you can pull the data and then drop the table rather than running delete statements which might lead to large transactions within the system. It should be considerably faster too.
thanks Grant.. i will try and work out the SSIS solution and come back to you ..
This can be done with triggers: 1. Create a Archive table for each table created 2. Create a After delete trigger on each table that inserts the data into a archive table before deleting 3. Create a proc working with a date parameter that deletes records from those tables, and then drops the original tables.
If the tables have identical structure, than the table source as well es destination is fully configurable. So you can pass the name of table to be procesed. ALso if your tables are being created, deleted and you have particular naming scenario, you can query the system views (sys.tables) to get list of tables to be processed etc. There is a lot of possibilities.
I think the triggers way is not so good way of doing it. I would prefer the SSIS ways as @Grant suggests. In case of triggers, you will have much more effort. - You need to create the triggers - When you delete the rows using DELETE, the log file will grow rapidly in case of full or bulk recovery and even in simple recover if the tables are very large. Of course this will also slow down the system. - Than you have to delete the triggers and table. **In case of SSIS you simply transfer the data and drop the tables. You can have one configurable SSIS package for each table structure or even single configurable package for multiple table structures, which is easy reusable. **
Using SSIS does sound like a good option, but in our case we did it with triggers on very large Transaction tables in the Production database. The tables had to remain and old entries within the tables had to be moved to archive tables. The reason for doing it this way was that we could delete Transactions based on date, and deleting in batches to prevent SQL from locking up or slowing down. Not sure if you are able delete in Batches with SSIS.
In case you only need to move part of the records to archive, than the delete trigger can handle this, But again if whole table content should be moved, than it is better to simply copy the records to new destination (SSIS or even INSERT..SELECT) and TRUNCATE the table, instead of deletes. Also the triggers will be a problem if the records should be moved for the archival purpose, but regular deletes should occur without moving to archive. Of course in SSIS you can delete in batches.
while exporting tables, the number of tables to be exported will be different for each run (mayb 1 new table for each day). Is there task in SSIS which allows us this flexibility to select tables dynamically?
15 People are following this question.
