We have a warehouse database that contains a year of data up to now. I want to create report database that represents the last 3 months of data for reporting purposes. I want to be able to keep the two databases in sync. Right now, every 10 minutes I execute a package that will grab the most recent rows from the warehouse and adds them to the report db. The problem is that I only get new rows but not new updates.
I would like to know what are the various ways of solving this scenario.
Thanks
You should probably read about replication, or ask your DB admin about it.
Is it possible to add columns to this database? You could add a Last_Activity column to the DB and the write a trigger that updates the date/timestamp on that row to reflect the latest edit. For any new entries, the date/time would reflect the timestamp when the row was added.
This way, when you grab the last three months, you'd be grabbing the last three months' activity, not just the new stuff.
If you are using SQL 2000 or below, replication is your best bet. Since you are doing this every ten minutes, you should definitely look at transactional replication.
If you are using SQL 2005 or greater, you have more options available to you. Database snapshots, log shipping, and mirroring as SQLMenace suggested above. The suitability of these vary depending on your hardware. You will have to do some research to pick the optimal one for your needs.