Archiving data to a seperate database.

7 Apr 2006 6:43 PM

MarkieMark

Hi all,

Would appreciate some thoughts on the best way to do this scenario.
This is not a large database in the number of tables, but has about 100,000
new rows per day. Data is inserted only, and no queries / SPs are being run
against the data.

Database 1 (transactional database)
Holds all current data, and all data up to 100 days old.

Database 2 (historic database)
Holds all data and is max 15 mins older than Database 1.

So, what would be the best way to keep the historic database current?
Replication, triggers, agent running SPs, keeping it within 15mins sync to
the transactional database, and deleting records older than 100 days from the
transactional database....

Would appreciate anyones thoughts on this....

-Mark

7 Apr 2006 8:48 PM

Anith Sen

For 100000 rows, in general, one could write a simple job & schedule it to
run every day. The job can have a script that copies the historical data to
the destination & purges it from the source.

--
Anith

7 Apr 2006 8:53 PM

Arnie Rowland

My choices...

Part 1: Removing from Active db.
   Use a nightly job to delete all records over 100 days (You do have a Date
Added Column? Is it indexed?)

Part 2: Moving new data to 'Historic' db
   Trigger that copies the INSERTED table to the 'Historic' db.
   For this application, a trigger would be have the same or less
   operational impact, and the maintenance would be significantly
   less. This assumes, of course, that security and connection
   issues allow the use of a Trigger.

--
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quoteHide quote

"MarkieMark" <u20627@uwe> wrote in message news:5e6ed66fd1456@uwe...
> Hi all,
>
> Would appreciate some thoughts on the best way to do this scenario.
> This is not a large database in the number of tables, but has about
> 100,000
> new rows per day. Data is inserted only, and no queries / SPs are being
> run
> against the data.
>
> Database 1 (transactional database)
> Holds all current data, and all data up to 100 days old.
>
> Database 2 (historic database)
> Holds all data and is max 15 mins older than Database 1.
>
> So, what would be the best way to keep the historic database current?
> Replication, triggers, agent running SPs, keeping it within 15mins sync to
> the transactional database, and deleting records older than 100 days from
> the
> transactional database....
>
> Would appreciate anyones thoughts on this....
>
> -Mark

7 Apr 2006 9:27 PM

MarkieMark

Arnie,

My thoughts were with triggers to. The only issue I had was if the historic
database went off-line for some reason etc., that the databases would get out
of sync. Haven't been able to get my head round that little problem yet.

-Mark