I am looking for a solution giving me automated archiving of SQL Server databases based on a smart interpretation of table relationships that is able to archive records from related tables minimizing (or making unnecessary) to write custom ETL code.
So far the only solution I have been able to find (by Google) is Informatica Data Archive
In the DataSheet I can read:
Smart Automated Partitioning: This edition automates database partitioning based on business entities, placing records from related tables in the same table space, not just individual tables. Your IT team can leverage underlying database partitioning functionality to manage both partitioning and archiving from a central console.
Sounds pretty much like what I am looking for...¿Are there any others like this from other vendors? According to this book there should be many tools offering this.. but I can not find anything...
If there is no other prepackaged solutions I would also be interested in information on how to build my own... (althought I realize it might not be feasible)
Basically I am looking for a tool that enables a developer to do this (obtained from here):
- The ETL developer defines the source and destination of the extraction process, including servers, relational databases, and tables.
- For each table, the schema (columns, index, and constraints) can either be automatically retrieved by the system or manually specified by the ETL developer.
- The ETL developer specifies the mapping between source and destination at the database, table, and column level.
- The ETL developer selects either a full or delta load.
- The ETL developer configures an orchestration process. For each table, ETL developers define one or multiple steps for performing the extraction.
- The ETL developer specifies how ETL packages should be executed—at a scheduled time or on demand.
- The system dynamically generates one or more SSIS packages.
- The system deploys the SSIS packages to a distributed execution environment.
- The system executes ETL jobs and captures the job status.
I found this abandoned opensource project: MDDE unfortunately it is also pretty much undocumented (this is the usage information I could find) unsupported and obsolete (apparently is designed to work only with SQL Server 2005 and I need something for SQL Server 2008 R2)
OTOH: I am surprised that there is no "archiving" tag in this site...