Setting Up a Reporting Database
Using a live OLTP database for complex reporting has a significant performance impact on the OLTP application. A common architectural design is to have a separate reporting database from your application's OLTP database. Before I get into reporting database options, a critical area to look at is tuning the queries for the reports. You should start there before introducing a more complex and costly environment. Just because a query is fast on a 10GB DB doesn't mean it will be on a 100GB DB. Assuming you've fine-tuned the queries and database, let's take a look at ways to create a reporting database. I've used several approaches in the past, each with great success. Like anything else, there are different solutions that fit different scenarios. I list a few here, but instead of breaking out pro's vs con's, which are subjective, I'll simply list the caveats to using each. There are several things to consider when choosing a reporting database s