(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.
Some stats:
- 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.
Best Answer
Caution:
Since you will be using SQL Server 2012 express edition (and sql 2008R2), it has a limit of 10GB per database size (for previous versions, it is still 4GB). So if your table is not going to grow that big with all the archive data for many years, you are good uptill it hits 10GB limit.
My first suggestion would be to use Transactional replication. It wont have that much of overhead as you are just replicating 1 table. If you set the agent to run continuously or every 5 mins, then you will have up-to-date data on the destination (subscriber). You just have to remember to not replicate delete statements.
Secondly, you can use SSIS and schedule it using SQL Agent Job to incrementally load the data into the destination server.