Reporting on your OLTP system

Just a quick note on this topic since it really is not a best practice and I am sure no one does it. ;)

Business requirements > best practices?
Even if you have a reporting server dedicated for ...err... reports, the business might dictate real time data in those reports. That may or may no be possible on a reporting server but that is another post. Most of the time, the reporting server is not real time. At the same time, the OLTP data is not optimized reporting and if it is, transactional performance decreases. The solution usually dictates some reporting functionality being added to your production OLTP server. Maybe not at a DSS level but an adhoc query built by a web page. A company's order history, an store inventory, or even an advanced search web page. You know the one:

select * from widgets

where color is null and size is null and flavor is null........ and (title like '%ice cream%' or description like '%ice cream%' or keywords like '%ice cream%')

Is that really a reporting database?

So you have a copy of your OLTP database either through mirroring\snapshots, logshipping or replication that you call a reporting database. What does that buy you? Well, we you are offloading reporting right. Kinda. You separate your reporting queries and your OLTP queries but here are some downsides:

• The reporting database is not optimized for reporting. This tends to be really apparent if reporting is critical.
• The OLTP database now has read activity on the log(except for logshipping)
• The reporting database still takes all of the write traffic in one way or another.
• The OLTP database may lose resources. For example, less disks get dedicated to it. Or worse, reporting and OLTP end up on the same physical SAN disk basically doubling the IO.

So what is the solution? 

The real solution is to design an incremental ETL process that loads to a report optimized database. If a closer to real time data is required, triggers, modified replication or possibly asynchronous triggers might be the way to go. I am not going to pretend to know how to architect a solution like that. However, it is safe to say that a nightly or real time solution will require some serious dev work. This includes creating the ETL process and rewriting the application to use the report optimized database.

Paint yourself into a corner?

Say you have a home grown database that has gone from megabytes to 100's of gigabytes. Separating reporting functions to a new database is going to take time. However, SQL Server 2008 provides new features that scream consolidation. Not only instance consolidation but functionality consolidation.

• Resource Governor is the backbone of a consolidation strategy.
• Filtered indexes - Index the different workloads while impacting writes as little as possible.
• Data Compression -  combine with partitioning and shrink the reporting data while leaving the really hot data uncompressed.
• Not just SQL 2008 but don't forget about indexed views and indexed persisted computed columns.

The other assumption of consolidation is larger hardware. If you are not spending money on a copy of OLTP reporting server, you can get a larger OLTP box.

Warning: this is forward looking since I don't have any production SQL Server 2008 servers yet. :)

Conclusion

Not to sound like a Microsoft fan boy but SQL Server 2008 Enterprise Edition provides a lot of benefits for mixed workload boxes. However, the real point of the post is that making a copy of the OLTP database for reporting may not provide gains one would expect.

email it! |   |   |   |  | 

Permalink     1 Comments  

Rate this Post: 12345
COMMENTS:

Another problem with reporting on an OLTP server is that it restricts your ability to make schema updates on your OLTP box. Imagine having dozens/hundreds/thousands of reports in varying reporting systems (Crystal, SSRS, BusinessObjects, etc) all tied to your OLTP schema. When it comes time for a version upgrade on your OLTP software (homegrown or proprietary) you'll be in a world of hurt.

Another one (last one for today) is that it restricts your ability to merge with other companies. If your reporting data is abstracted away from your OLTP, then it's easier to combine data from multiple OLTP sources - like from all those other companies you're going to acquire. Or be acquired by. It's all the same, heh.

posted @ Wednesday, August 27, 2008 9:15 AM by Brent Ozar

---

Name (required)

Email (required)

Website

Notify me of followup comments via e-mail

Simple BBCode can be used like [url=http://example.com]Example[/url] and [B]