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.

I have a database that stores high frequent data, the data has a complex data model. Now I want to offload/archive this data in 15 minute intervals to another database server so user can do reporting on this data with a minimal delay. To improve performance I don't want to keep more than 2 days of data on the primary system. In a previous version of this system the data model was very simple so I used SSIS and transfered data from two tables based on a time stamp, however now with the complex data model I have some doubts that this approach will be efficient.

What strategies would be best for such an environment ? HADR would be nice, however with the deletion of the date on the primary it won't work since I want to keep all data on the reporting instance.

share|improve this question
1  
Do you want to delete data so it can be reported on elsewhere? If so have you considered not deleting data and just utilizing availability groups and an active secondary for reporting? –  Thomas Stringer Jun 25 '13 at 10:33
    
@nojetlag what edition of SQL Server 2012 are you using ? –  Kin Jun 25 '13 at 14:56
    
@ThomasStringer I want to delete the data on the primary system due to the amount of data I'm expecting. I assume that the smaller I keep this database the better it will perform and I don't need this data on that instance after the mentioned time period. After that they are only relevant on the reporting system. –  nojetlag Jun 26 '13 at 6:02
    
@Kin Enterprise Edition –  nojetlag Jun 26 '13 at 6:04
add comment

1 Answer

If performance is your only consideration if I were you I would consider partitioning your table(s) rather than just deleting the data. Then you could look into either log shipping (if you want the whole database to be updated) or replication (if you only want a couple of tables copied). As an alternative you could look into change data capture as a method of storing the changes you are making to the data. You can then pull this information out to apply it to your other database.

share|improve this answer
    
Since the OP wants to archive the data to another database for reporting, I dont see how Log shipping would help. –  Kin Jun 25 '13 at 14:58
    
You use Log Shipping to create a "reporting" database on another server. Since the OP only needs the data every 15 minutes you take a tran backup every 15 min, ship it and restore it to the new location. Then you are able to do your reporting from the new location. The big benefit is that it is cheep. No Enterprise license needed. The down side is that you are locked out (for reporting purposes) while doing the restores. It does assume that you want the whole database and not just a small percentage of it. –  Kenneth Fisher Jun 25 '13 at 15:21
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.