How to archive old data from sql database

Question

How to archive old data from sql database

FullStack

Say Hey Kid

Points: 674
More actions
April 1, 2017 at 1:49 am

#320059

I have some data being written to a sql database, by requirement I need to retain the data for 1yr. After that year is up then I can archive the data off so it can be stored off site, I would also like to purge the data once it's been archived. What is the best way to go about doing this?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

table paritioning is frequently used to meet this kind of requirement.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

If you don't want to go the table-partitioning route, then write a proc to move items to accumulator tables on a daily basis and delete them from the mains. Once a month, backup the accumulator tables and then truncate them.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

I was thinking of the stored procedure route. I really prefer partitioning because it is so much easier and it can take so much less time to run when done right.

That said, the cheaper solution is the stored procedure route (well, minus development time). 😉

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

SQLRNNR - Saturday, April 1, 2017 8:58 PM
I was thinking of the stored procedure route. I really prefer partitioning because it is so much easier and it can take so much less time to run when done right.
That said, the cheaper solution is the stored procedure route (well, minus development time). 😉

Agreed on the speed of partitioning and the wonderful tool known as "SWITCH".

I do have to ask, though. Are you using 1 file/filegroup per partition? And then do you ever rebuild indexes on a partition? If so, what are you doing to overcome the problem of having wasted free-space in each partition to the tune of about 120% of whatever the largest rebuilt index was?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Jeff Moden - Saturday, April 1, 2017 9:17 PM
SQLRNNR - Saturday, April 1, 2017 8:58 PM
I was thinking of the stored procedure route. I really prefer partitioning because it is so much easier and it can take so much less time to run when done right.
That said, the cheaper solution is the stored procedure route (well, minus development time). 😉
Agreed on the speed of partitioning and the wonderful tool known as "SWITCH".
I do have to ask, though. Are you using 1 file/filegroup per partition? And then do you ever rebuild indexes on a partition? If so, what are you doing to overcome the problem of having wasted free-space in each partition to the tune of about 120% of whatever the largest rebuilt index was?

I do defrag indexes in the most active partitions only. I don't end up with that amount of free space but that said, I don't mind the extra free space either. We have sliding windows and rotate through the files nightly. Some days that file may require more space than other days depending on the data volume of the day.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

SQLRNNR - Saturday, April 1, 2017 9:50 PM
Jeff Moden - Saturday, April 1, 2017 9:17 PM
SQLRNNR - Saturday, April 1, 2017 8:58 PM
I was thinking of the stored procedure route. I really prefer partitioning because it is so much easier and it can take so much less time to run when done right.
That said, the cheaper solution is the stored procedure route (well, minus development time). 😉
Agreed on the speed of partitioning and the wonderful tool known as "SWITCH".
I do have to ask, though. Are you using 1 file/filegroup per partition? And then do you ever rebuild indexes on a partition? If so, what are you doing to overcome the problem of having wasted free-space in each partition to the tune of about 120% of whatever the largest rebuilt index was?
I do defrag indexes in the most active partitions only. I don't end up with that amount of free space but that said, I don't mind the extra free space either. We have sliding windows and rotate through the files nightly. Some days that file may require more space than other days depending on the data volume of the day.

Ah... I didn't qualify what I was thinking and my apologies for that. The partitioning that was heavy on my mind was for the eventual conversion of older partitions (1 file per filegroup per month) to Read_Only so that they'd no longer need to be backed up (we're required to keep everything in the table forever and it's approaching a TB in size). I go through a bit of a complicated jag at the end of each month to rebuild the indexes in the partition about to be set to Read_Only so that we're not condemning a fair bit of free space to a Read_Only status. I thought that whatever you might be doing for index maintenance on partitioned tables might make it a bit easier.

I absolutely agree that having free space in active partitions is a good thing.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Jeff Moden - Saturday, April 1, 2017 8:48 PM
If you don't want to go the table-partitioning route, then write a proc to move items to accumulator tables on a daily basis and delete them from the mains. Once a month, backup the accumulator tables and then truncate them.

i would prefer to move rows to some sort of warehouse and trim the warehouse to retain only the required rows ongoing

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

Perry Whittle - Monday, April 3, 2017 7:19 AM
Jeff Moden - Saturday, April 1, 2017 8:48 PM
If you don't want to go the table-partitioning route, then write a proc to move items to accumulator tables on a daily basis and delete them from the mains. Once a month, backup the accumulator tables and then truncate them.
i would prefer to move rows to some sort of warehouse and trim the warehouse to retain only the required rows ongoing

I'm just a little confused, Perry. In your example, is the warehouse the archive data or the main data that the users hit on?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

FullStack Say Hey Kid Points: 674 More actions · Answer 9

SQLRNNR - Saturday, April 1, 2017 5:48 PM
table paritioning is frequently used to meet this kind of requirement.

I'm not a DBA by any means but I know my way around mssql & sql management studio, I've been learning as things come up. Anyway would this be a good set of instructions to follow https://docs.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes ?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

This one may be a lot better for help in learning partitioning.

https://www.brentozar.com/archive/2013/01/sql-server-table-partitioning-tutorial-videos-and-scripts/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events