Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
How big is the database? –  Kin Dec 18 '13 at 16:56
    
@Kin OP enhanced –  samsmith 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? –  Thomas Stringer Dec 18 '13 at 17:11
    
@ThomasStringer Yes, we want the archive table to include (keep) data that was deleted on prod. OP updated. –  samsmith Dec 18 '13 at 20:32
add comment

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

share|improve this answer
    
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? –  samsmith 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 Dec 19 '13 at 15:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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