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).

Answer 2

Naturally for most of the enterprise class applications, the transaction database is always kept separated from the reporting database. The transaction system is tuned for OLTP and the reporting database might be denormalised to suit the need of the reporting scenarios. So it is almost a natural suggestion.

Answer 3

Be careful with doing frequent backups -- that could lead to a lot of downtime!

Common Solutions

It is indeed a common practice to create a separate instance just for reporting.

Some folks even go a step further and put reporting on a separate physical machine or cluster to further isolate that part of the load.

Both of those can be handled with replication (which avoids the downtime problem). Or you could just do a nightly backup and report against that.

I would also like to mention that the high-end approach to this is data warehousing, where you essentially transform this new reporting database into a read-optimized repository that's more efficient to report against. That tends to be very time-consuming to implement, so it is not the quick fix that you're looking for.

Final Thoughts

One last thing: I've seen some shops on the cusp of this problem try to avoid dealing with it. Here's the takeaway: reporting tends to spike at certain times of the month or year, so if you're normally on the verge of killing your database server, the last week of the month might push you over the edge!

This question is very similar: http://stackoverflow.com/questions/190512/sql-server-separate-database-for-reports

Answer 4

Before you do a forklift upgrade, you might see if putting

...from sometable WITH (NOLOCK)

on your reporting queries mitigates the problem. It may at least buy you some time to figure out what is optimal.