Best Approach to Archieve DB

Question

Post reply

Best Approach to Archieve DB

OnlyOneRJ

Hall of Fame

Points: 3894
More actions
March 18, 2013 at 3:39 am

#275300

Example -
We have a DB which have loads of data & we want to archiev it to some Server in such a way that
Scenario - Source DB have 5 yrs Data in it, we want to move the 5 yrs Data to Destination DB, & then will keep only one month data in Source DB, that means on monthly basis we will have less data in Sorce DB.
Please help me a best approach??
************************************
Every Dog has a Tail !!!!! :-D

Viewing 15 posts - 1 through 15 (of 33 total)

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

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 1

4 View ... 0 replies :w00t:

************************************
Every Dog has a Tail !!!!! :-D

david.alcock SSCertifiable Points: 6021 More actions · Answer 2

We have done similar things in the past, essentially using SSIS to archive/move data to a new server. On enterprise you could partition the data instead.

'Only he who wanders finds new paths'

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 3

David, can you tell me how did u perform this activity via SSIS... please explain in details

************************************
Every Dog has a Tail !!!!! :-D

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

If you can make changes to your architecture, you might want to look into partitioning the data. That makes it very easy to move data out of one database and into another. That's pretty much what it's designed to do. But, it has a number of caveats, the biggest being that you can ensure that retrieval of your data is always going to filter by the same structure that you use to partition the data. If you can't guarantee that, I wouldn't suggest this approach. Otherwise, it's a very viable method. This is an introductory article from Microsoft. There are other resources online to learn more.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 5

I did a Import export activity for transfeering the Tables/data to other DB/server and saved the Packgae as SSIS package to the File Location..

Now how do i move ahead...

1) Tables & data imported to destination server via import export

2) SSIS package stored in some location.

So the destination server have till date data from source server..

after a month how should i nmake changes & execute the package, so that the Only Updated or new data should be imported to destination server :w00t:

************************************
Every Dog has a Tail !!!!! :-D

david.alcock SSCertifiable Points: 6021 More actions · Answer 6

Apologies, I am not sure what you are asking.

If it what action to take next, then it depends on the requirements of the business perhaps. Is the data that you have moved still 'active', if so then yes you want the up to date data on there and perhaps some form of audit trail to determine the changes.

As for saving the package, there is a specific area in msdb for packages that you can utilise when saving packages.

'Only he who wanders finds new paths'

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 7

david.alcock (3/18/2013)
Apologies, I am not sure what you are asking.
If it what action to take next, then it depends on the requirements of the business perhaps. Is the data that you have moved still 'active', if so then yes you want the up to date data on there and perhaps some form of audit trail to determine the changes.
As for saving the package, there is a specific area in msdb for packages that you can utilise when saving packages.

David, can you tell me how did u perform this activity via SSIS... please explain in details

************************************
Every Dog has a Tail !!!!! :-D

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 8

Grant Fritchey (3/18/2013)
If you can make changes to your architecture, you might want to look into partitioning the data.

You can't switch partition to a switching table that is in different database. Switching table and partition must reside on the same filegroup in order for switch partitions to work.

The only benefit would be much faster delete (drop partition) of the data you already copied - worth using.

I would do a partition switch, bcp-out switching table to a file in native format, bcp-in that file to destination db switching table, and then switch that into destination partitioned table. That is by far the fastest method.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

david.alcock SSCertifiable Points: 6021 More actions · Answer 9

Well we were mainly moving to new structures so the migration was two part, moving recent data to a faster server and the older data shifted to cheaper archive storage. SSIS was utilised in very simple form, a data flow with source, destination and lookup tasks were the main components as well as script tasks for some customisation. Lookup tasks are very handy for incremental loads as you may be doing.

Highly recommend you give this a read if you havent already: http://www.sqlservercentral.com/articles/Stairway+Series/76390/

We didnt have enterprise otherwise I would have implemented some form of partitioning for sure.

'Only he who wanders finds new paths'

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 10

runal_jagtap (3/18/2013)
4 View ... 0 replies :w00t:

14 minutes... broad subject. Need more patience. 😉

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

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 11

runal_jagtap (3/18/2013)
Example -
We have a DB which have loads of data & we want to archiev it to some Server in such a way that
Scenario - Source DB have 5 yrs Data in it, we want to move the 5 yrs Data to Destination DB, & then will keep only one month data in Source DB, that means on monthly basis we will have less data in Sorce DB.
Please help me a best approach??

Will you ever have the need to access the data that's more than a month old through a GUI or stored procedure?

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

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 12

Jeff, No we would never need a data a month old, incase if someone needs the data we can retrieve it from Archive DB..

but whats the best approach???:w00t:

************************************
Every Dog has a Tail !!!!! :-D

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 13

One very effective approach would be to "partition" the table but that's only if you have the Enterprise Edition and, IIRC, if you don't mind the "archive" being in the same database. What that will do for you is make it almost instant to "archive" a previous month and it will reduce the nightly maintenance because you won't have to rebuild or reorganize the idexes for the entire table because, if you do it right, the indexes are partitioned, as well.

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

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 14

Thanks Jeff 🙂

Currently me planning to go with SSIS Package Automation...

Well see how i succedd.. incase any query i will come here again.. stating

65 views & 0 replies :w00t: ( 😀 )

************************************
Every Dog has a Tail !!!!! :-D