Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Archieving Data Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 6:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, 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..

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








************************************
Every Dog has a Tail !!!!!
Post #1395642
Posted Wednesday, December 12, 2012 7:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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 Moden's 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)
Post #1395666
Posted Friday, December 14, 2012 12:44 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 415, Visits: 1,333
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..

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


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.
Post #1396788
Posted Friday, December 14, 2012 8:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:00 PM
Points: 35,549, Visits: 32,145
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..



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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1396856
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse