Separate tables/databases for reporting and CRUD operations

Question

Periodically users running reports are blocking users doing CRUD operations and causing time-outs. I'd like to create duplicate locations of the current tables for the report users.

I was thinking of creating a job that backs-up my app's database, and restores it to a reporting database on the same server so that users running reports will be separated from those doing CRUD ops. The job will run every 10 minutes or so. Initial tests show start to finish will be about 30 seconds. Disk space is not an issue.

Is this a good/bad idea? What pitfalls should I watch out for? Is there a better way to do this?

Answer 1

This sounds like a good idea. The only concern I'd have is do you need to update every 10 minutes? This could also slow things down while the update is running. Usually these are done overnight (to have the least impact to others), or if during the day, at only 3 fixed points (say 10 am, 1 pm, and 4pm).