Archieving Data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » Archieving Data

Archieving Data

Rate Topic

Display Mode

Topic Options

Author

Message

OnlyOneRJ

Posted Wednesday, December 12, 2012 6:43 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158, Visits: 443

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 !!!!!

Post #1395642

Sean Lange

Posted Wednesday, December 12, 2012 7:25 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 8,584, Visits: 8,225

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

Post #1395666

sql-lover

Posted Friday, December 14, 2012 12:44 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:37 PM
Points: 228, Visits: 725

runal_jagtap (12/12/2012)

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

Jeff Moden

Posted Friday, December 14, 2012 8:05 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 32,903, Visits: 26,784

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1396856

« Prev Topic | Next Topic »

Permissions