(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.

  • How big is the database?
    – Kin Shah
    Dec 18 '13 at 16:56
  • @Kin OP enhanced Dec 18 '13 at 17:03
  • Do you want the archive table to have all of the data, including the data that was deleted from the source table? Dec 18 '13 at 17:11
  • @ThomasStringer Yes, we want the archive table to include (keep) data that was deleted on prod. OP updated. Dec 18 '13 at 20:32


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.

  • Thanks! I am a tad wary of transactional replication, because it sounds hairy (brentozar.com/archive/2013/09/… ). Can you even do a two-box implementation of transactional replication, or is it reqd to do 3 box? Dec 19 '13 at 2:48
  • @samsmith the article you pointed out is a general assumption as I believe that everyone is having a different environment and requirements. Yes you can do a replication with a local distributor on the same server with publisher and the subscriber is a different server. Your scenario is something which wont have too much overhead as you are just replicating 1 table. Only the initial snapshot will take some time - depending on the amount of data, n/w bandwidth and the location. Test it out and you will understand that it is easy to set up.
    – Kin Shah
    Dec 19 '13 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.