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 ««1234»»»

Best Approach to Archieve DB Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 5:51 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 35,593, Visits: 32,184
runal_jagtap (3/18/2013)
4 View ... 0 replies


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

(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 #1432413
Posted Monday, March 18, 2013 5:56 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 35,593, Visits: 32,184
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."

(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 #1432415
Posted Tuesday, March 19, 2013 6:09 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
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???








************************************
Every Dog has a Tail !!!!!
Post #1432591
Posted Tuesday, March 19, 2013 7:56 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 35,593, Visits: 32,184
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."

(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 #1432659
Posted Tuesday, March 19, 2013 8:21 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
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 ( )








************************************
Every Dog has a Tail !!!!!
Post #1432676
Posted Tuesday, March 19, 2013 9:40 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 35,593, Visits: 32,184
runal_jagtap (3/19/2013)
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 ( )


So, what's the plan? Will the archive table be in a different database or the same database> I'm asking because although SSIS will certainly do the job, it seems like real overkill.


--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 #1432743
Posted Thursday, March 21, 2013 2:27 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
Jeff, Yup The Srever & the Database would be different...

Why it would be overkill explain me.. coz i am going to do it.. i dont know how to do it..








************************************
Every Dog has a Tail !!!!!
Post #1433630
Posted Thursday, March 21, 2013 3:51 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, November 22, 2014 9:37 AM
Points: 223, Visits: 884
SSIS is normally utilised when the copy/move is complex, if it involves data transformations, validations etc.

'Only he who wanders finds new paths'
Post #1433682
Posted Thursday, March 21, 2013 3:56 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
My Task is mentioned belwo..

Copy all data from Source to destination DB
Every month only the updated or new data needs to be transffered to destination..



Will SSIS help in this?








************************************
Every Dog has a Tail !!!!!
Post #1433684
Posted Thursday, March 21, 2013 12:02 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 35,593, Visits: 32,184
runal_jagtap (3/21/2013)
Jeff, Yup The Srever & the Database would be different...

Why it would be overkill explain me.. coz i am going to do it.. i dont know how to do it..


If you've already decided to use SSIS, then my suggestion of making a simple job do a simple insert to the archive and a simple delete from the source would probably underwhelm everyone's gumption. Like I said, doing this through SSIS is real overkill.


--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 #1433949
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse