Data archiving to additional server
We have two identical SQL Server 2005 databases. We need to archive data to another server over a network. What would be the best way to accomplish this? This is primarily required because data needs to be archived for at least five years. We don’t need the archive server for backup purposes, just for archiving the data. Please advise what will be the best way to do this.
Software/Hardware used:
Software/Hardware used:
ASKED:
April 8, 2010 6:15 PM
UPDATED:
April 12, 2010 2:23 PM
RELATED QUESTIONS
- Lotus Notes Client Archive
- Best way to back up SQL Server 2008 database?
- I want to know if it is possible to transfer data from Iseries to a web site and upload some back. This is for our bank and we do not want to use FTP
- Booting generic machines to the network & installing image of Windows 7
- LTO-1 to LTO-4 Migration
Answer Wiki:
Do you need the data in the primary server as well as the archve?
if so use log shippping or replication to copy the data across to the archive server
if not setup a SSIS task to move the data on a regular basis
To see all answers submitted to the Answer Wiki: View Answer History.
No, data is only required in the archive. Data older than 6 months will be deleted from the primary server, but data must be kept in the archive for up to 5 years. Must I setup an SSIS taks for each table that needs to be archived? I guess so. so basically – create a table on the archive server for each table on the primary server for which data must be archived. create SSIS package to archive new data received since the last run on the SSIS package.