(Feel free to tell me the correct name for this... it is a common scenario!)
We have a production SQL Server 2008 box. One table is heavily transactional. It is also purged every few days (old records are deleted).
We want to set up a 2nd SQL server to receive all the rows from this table and archive them for later analysis. We need to archive all rows, including rows that are purged from the production table. E.g. we want to push over insert and update, but not delete.
- DB itself is about 1TB
- The table we would like to archive: record size is about 1K
- The table we would like to archive: 50K-150K records/day are created (and later purged)
We are looking for:
- Data to move at least once each 24 hours. (more frequently is fine, but not reqd)
- Simple to set up. (the three-box transaction replication looks rather top heavy)
- If possible, the receiving system could be a different ver/flavor of SQL (e.g. receiving system could be SQL Server 2012 express -- yes, I know about the SQL Express db size limit--just looking for flexibility)
I thought there would be a proc out there that does this, but I haven't found one yet.