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 is used for current operations. The amount of data is starting to get a bit to much for the applications using it.

Most of the data is older and is very rarely needed.

I am wondering if there is a way to configure SQL Server to keep a mirror of the original database, but allow me to occasionally purge out old data (and not have it purge from the copy).

NOTE: My database is currently SQL Server 2008 R2, but we will be upgrading to SQL Server 2012 soon.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

Define the data is getting to be a bit much? I've got clients with 1B+ rows in a table and no performance problems at all.

You could use SQL Server replication to replicate inserts and updates to a remote machine, but basically have it ignore the deletes.

Before I'd start looking at hacking together something like that I'd probably look at index tuning or query rewriting to see what I can do to resolve the performance problems with the queries that I've got.

share|improve this answer
    
On of my larger tables has 15M rows. It has been live for less than a year and will need to be live for many many years to come. The performance is acceptable for now, be we are starting to notice some slow downs. –  Vaccano Apr 2 '13 at 18:04
    
Thank you for the idea on replication. I did not know it could be setup to only replicate updates and inserts. –  Vaccano Apr 2 '13 at 18:04
    
It's not a normal configuration option. You'd have to edit the delete stored procedure so that the stored procedure doesn't actually do anything. –  mrdenny Apr 2 '13 at 20:44
add comment

You should also look at partitioning your data. Log shipping is another option for a reporting database.

There are other factors to think about as well, such as how many rows are being retrieved, how big are the indexes? How often do you rebuild them? When you append to a table all the time the indexes get unbalanced and performance suffers.

The other thought that comes to mind is to prepare the data for a datamart and remove it from the active db. In the end what ever makes the most sense from a business perspective is likely your best answer. If business has no vision to use the old data I would still keep it, but perhaps not on line.

share|improve this answer
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.