December 12, 2012 at 6:43 am
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
December 12, 2012 at 7:25 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 14, 2012 at 12:44 pm
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.
December 14, 2012 at 8:05 pm
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 outI 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
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply