SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Archieving Data


Archieving Data

Author
Message
OnlyOneRJ
OnlyOneRJ
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 690
Hi All,

need advice from the Experts ..

I have been asked to do archieving of the data..
because

The data in the databases are large in amount.. die to which if some one want to retriev report from that db the performance is very poor, the data takes time to come out
I do regular indexing but still no use..
I have data for almost 5 yrs in the DB & only 3 months data is required for reporting..


I thought of going for log shipping or replication but..
the scene here is that the columns in the tabkle are not fixed.. some times their is a requirenment to add more columns in it..w00t

Not if this is the scenario what should i do & how should i do that i only have 3 months data in production DB & rest data in some other server..
how do i go ahead & perform this activty every sunday??
what should i use & how to do that w00t

************************************
Every Dog has a Tail !!!!! :-D
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25889 Visits: 17514
It is rather difficult to offer a very solid solution because there are so few details posted. From your description though I don't know how you would be able to post much more details because it sounds like a very complicated and dynamic environment.

Have you looked at table partitioning? I am not sure if that is what you need but it might help point you in the direction of a solution.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1575 Visits: 1930
runal_jagtap (12/12/2012)
Hi All,

need advice from the Experts ..

I have been asked to do archieving of the data..
because

The data in the databases are large in amount.. die to which if some one want to retriev report from that db the performance is very poor, the data takes time to come out
I do regular indexing but still no use..
I have data for almost 5 yrs in the DB & only 3 months data is required for reporting..


I thought of going for log shipping or replication but..
the scene here is that the columns in the tabkle are not fixed.. some times their is a requirenment to add more columns in it..w00t

Not if this is the scenario what should i do & how should i do that i only have 3 months data in production DB & rest data in some other server..
how do i go ahead & perform this activty every sunday??
what should i use & how to do that w00t


Do not get me wrong for the comment I am about to make. But if you see yourself changing the table's schema too frequently (adding new columns) you may have serious database or normalization problems. You or your team may go back and revise the attributes you need and why. Altering table's schema is doable, but if you are saying this is a frequent request, I think you have an issue and will impact any data archiving process later on the road.

Having said that, I just did something similar recently. I created an identical table on same database. Then I altered or created an Index (depends of your case) where the date or identity column will be my Clustered Index. From there... you have two choices ...

-Move data from specific range of date to the archiving table

OR

-Export the source table via BCP and import what you need to new one, via bcp as well

If you are lucky or blessed and you have Ent. edition, you can use SQL partitioning as well.

Once any of above is done, you can DELETE live data (old one or the one that you moved) in chunks, again, taking advantage of the Index on the date or ID column, and delete the old data from the live table.

Just be sure you have a proper backup prior deleting anything. I tested mine on a dummy database. It is easier than you think, depending of how many rows or data you are planning to move or archive.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85036 Visits: 41074
runal_jagtap (12/12/2012)
The data in the databases are large in amount.. die to which if some one want to retriev report from that db the performance is very poor, the data takes time to come out
I do regular indexing but still no use..
I have data for almost 5 yrs in the DB & only 3 months data is required for reporting..

the scene here is that the columns in the tabkle are not fixed.. some times their is a requirenment to add more columns in it..w00t



Before you spend a lot of time trying to develop and archiving plan, understand that performance problems are because you have a lot of data. It's because you have queries that don't work correctly with a large amount of data.

Considering that you're also talking about them adding columns, I have to ask, what are the columns for? Are they, by any chance, columns of data based on some timeperiod like day, month, or week?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search