Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This is a pretty broad question. I know the definition of OLTP. But it seems like unless you have an environment that has a clear distinction between OLTP and OLAP, in the sense of a database that handles DML transactions and a replicated database that handles the reporting, then this is a bit of an abstraction.

Would it be safe to say that most production databases are OLAP and OLTP?

share|improve this question
up vote 8 down vote accepted

I would say that most production databases are OLTP with a little OLAP flavor. In a lot of shops the OLAP side of the world is getting more specialized and they really are performing the analytical functions outside of the normal application database. Are there shops that still plunk everything into one database? Of course. But as the capabilities improve (and Denali is no exception), I think you'll start to see a much more obvious divide.

share|improve this answer
Wouldn't you say that performing the OLAP portion outside of the application database can be taken as over-engineered in a lot of situations? Possibly MOST situations? – Thomas Stringer Sep 10 '11 at 3:18
No, I don't think so. It's subjective, sure, but if there is real cube-type processing, or things like PowerPivot, StreamInsight, reporting services, etc. I think it would be over-engineering to try and do those things inside the SQL Server engine. – Aaron Bertrand Sep 10 '11 at 3:21
Are you looking for a generality? There isn't one. It is going to be different from shop to shop and depend on a slew of factors including budget, expertise, motivation to learn new tech, how badly more advanced reporting is required, who requires it (high-paying customer or HR), etc. etc. – Aaron Bertrand Sep 10 '11 at 3:25
I am a 100% OLTP guy; I do not really pay a whole lot of attention to the BI/non-relational features at all, so I don't know of all the pros and cons to using them. I do know that a TON of people are excited about the BI/non-relational features in Denali, and these people range from information workers to CTOs to hardcore database people. What is your goal? It almost sounds like you're writing a thesis on why BI/non-relational features are a waste. I can assure you they're not. :-) – Aaron Bertrand Sep 10 '11 at 3:29
Another thing I'll add is that replication/log shipping/snapshots do not make the data easier to get at for the average person - they just make the data more available. There is a big difference - if you've used a cube browser and experienced how fast and easy it is to drill in to various aspects of the data, and then tried to tell a non-SQL person to write a query to get the same data off the snapshot ... just one example in probably hundreds of why BI isn't just a copy of the production database. – Aaron Bertrand Sep 10 '11 at 3:32

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.