“Archive” database options

Topic

New Reply

I have an application where I need to have two databases with the same information but one needs to keep the data for archiving and the main database needs to have a much shorter timeframe of data. I have looked into mirroring and replication but it seems to me that any changes (purging of data) done in the master database would then be carried over to the archive database. Is this correct? Is there a better way?

Main database is SQL2000
Archive database is SQL2008 running on Win2k8R2

Thank you for any help,

Reply | Quote

Replies

Does the Archive database need to have just Table data in it, or do you need
all the Views, Procs etc.
If just table data, you could possible script a transfer of data from one database to the other,
or maybe export the data from one as XML and then upload into the other,
from a centrally accessible location.
This way you could script Appends or Updates of data into the archive.

Cannot really offer a solution because I have no experience with SQL 2000 or 2008 only 2005.

There might be a far superior solution out there, (and there probably is), I’m just throwing ideas into the pot.

Reply | Quote

I have an application where I need to have two databases with the same information but one needs to keep the data for archiving and the main database needs to have a much shorter timeframe of data. I have looked into mirroring and replication but it seems to me that any changes (purging of data) done in the master database would then be carried over to the archive database. Is this correct? Is there a better way?

Main database is SQL2000
Archive database is SQL2008 running on Win2k8R2

The answer to your “is this correct” is YES. The answer to “is there a better way” depends on what your requirements are (i.e. legal, company regulation, discovery).

I’m reading that you do not want the archive database to be a copy or the main database. Is that correct?

Are you looking for the archive database to have a transactional history of the main database?

Joe

--Joe

Reply | Quote

Neither replication or mirroring will do what you want, as you surmised. I would probably create a trigger in the SQL Server 2000 database that copies the record before a delete and puts it in the archive database. However running 2000 on the “master” database and 2008 on the “archive” database may present some challenges. In general it works much better to do all of the administrative work from 2008 (or from 2005 if you have that) and connect to the 2000 database from SQL Server 2008. What you are looking at is not trivial however – and the best answer depends to some degree on the number of records in the table and how often they need to be copied across.

Reply | Quote

I do not need the archive to be a copy of the main. I would probably be fine with a daily update of the archive but would need to ensure there are no duplicate records copied across. The main database has been used as a data storage for a production facility and was used to serve up trending data for the operators and to pull historical reports on product that was run. The server I have it on was performing many other tasks so I purchased a new server and new instance of SQL (2008). I still need the old DB for the operators but will only need to keep about 2 weeks of rolling data in it. I plan to use the SQL2008 server as a web reporting and analysis setup to replace the historical aspects of the original. RIght now there are no regulatory requirements driving this but that could happen with some product we make in the future. The main purpose is for reporting and analysis. I know enough about SQL to get it running and tinker with views/spreadsheet reports. Very limited experience with triggers and complex queries.

Thank you for the replies

Reply | Quote

If all the tables have Primary Key fields, you ought to be able to prevent duplicates being transferred.

Reply | Quote