We have a Data Warehouse on a SQL 2005 Enterprise 64 bit Clustered SAN Environment. We have a need to maintain a separate database on a separate SAN which has all the same data contained in the Data Warehouse (this includes the Staging Data as well as BI data) but on a rolling 6 month basis (only the most recent 6 months of data). We do have an Inserted Date column on these tables in the DW.
Would Snapshot Replication be appropriate for this scenario with a filter to pull only the most recent 6 months of data? The Largest tables have about 26 million and 16 million rows, respectively for the most recent 6 months. Does this seem like too much for a Snapshot. Might Transactional be better?
A few hundred thousand rows are processed daily for these larger tables in batch processing at 6:00 a.m.
I'm new to replication, so any help would be much appreciated. Our alternative is to create ETL Scripts to handle the Process. Thanks.