we are in the process of building ODS - Operational Data source, ODS will contain only current 3yrs data, whereas Production server contain 10yrs old data.
What I need is a smaller sized database which contain data for last 3yrs than the production database which contain data for 10yrs.
I need a way to have the ODS server storing only the most recent data. Any ideas or recommendations on this?
is it possible to delete data from a read only standby database that imports shipped log files?
I know this shouldn't be possible because the deletion would change the LSN and the log shipping should break.
We try replication but require more maintanance.
we can create ETLs but they may kill performance of the prod server.