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 3:39 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
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 !!!!!
Post #1432123
Posted Monday, March 18, 2013 3:52 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
4 View ... 0 replies







************************************
Every Dog has a Tail !!!!!
Post #1432124
Posted Monday, March 18, 2013 4:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:02 AM
Points: 211, Visits: 842
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'
Post #1432125
Posted Monday, March 18, 2013 4:35 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
David, can you tell me how did u perform this activity via SSIS... please explain in details







************************************
Every Dog has a Tail !!!!!
Post #1432132
Posted Monday, March 18, 2013 5:12 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 15,501, Visits: 27,887
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1432139
Posted Monday, March 18, 2013 6:15 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
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








************************************
Every Dog has a Tail !!!!!
Post #1432161
Posted Monday, March 18, 2013 8:10 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:02 AM
Points: 211, Visits: 842
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'
Post #1432197
Posted Monday, March 18, 2013 8:43 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
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 !!!!!
Post #1432213
Posted Monday, March 18, 2013 8:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:57 AM
Points: 369, Visits: 1,208
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
Post #1432217
Posted Monday, March 18, 2013 2:55 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:02 AM
Points: 211, Visits: 842
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'
Post #1432351
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse