4 ms·

That's not a good idea. The usage patterns for a production database and one that runs reporting are very different. Reporting has long running queries with com

That's not a good idea. The usage patterns for a production database and one that runs reporting are very different. Reporting has long running queries with complex joins, production has many parallel short queries. If you start mixing the two, you can no longer reliably tune your database. For example you would want the alert for slow queries set set to a different time out on production and on reporting. Also, you complicate locking down access to the database. A reporting database can typically contain less sensitive info, reporting would not have password (hashes) for user accounts for example.

mwigdahl 7y ago
I don't think Brent was necessarily saying it was a good idea, just something that is commonly seen. At my own company we try hard to get customers to run reports against replicas/extracts rather than production, but some customers insist that they absolutely _need_ to pull in live data from production. So they run complex reports against a production OLTP schema and wonder why the system is running slow...
- jsjohnst 7y ago
  > I don't think Brent was necessarily saying it was a good idea Reading other comments Brent’s made here, I’m not so sure.
  - BrentOzar 7y ago
    > Reading other comments Brent’s made here, I’m not so sure. No no, it's not a good idea, but it's just the reality that I usually have to deal with. I wish I could lay down rules like "no analyst ever gets the rights to query the database directly," but all it takes is one analyst to be buddy-buddy with the company owner, and produce a few reports that have really high business value, and then next thing you know, that analyst has sysadmin rights to every database, and anybody who tries to be a barrier to that analyst's success is "slowing the business down."
SigmundA 7y ago
Pretty trival to setup read replicas using log shipping in MSSQL and postgresql to offload analytical loads to secondary servers. I work on a monolith that does this, but its usually not even necessary a single db server on modern hardware with proper resource governing can handle quite a bit.

mwigdahl 7y ago

I don't think Brent was necessarily saying it was a good idea, just something that is commonly seen. At my own company we try hard to get customers to run reports against replicas/extracts rather than production, but some customers insist that they absolutely _need_ to pull in live data from production. So they run complex reports against a production OLTP schema and wonder why the system is running slow...

jsjohnst 7y ago
> I don't think Brent was necessarily saying it was a good idea Reading other comments Brent’s made here, I’m not so sure.
- BrentOzar 7y ago
  > Reading other comments Brent’s made here, I’m not so sure. No no, it's not a good idea, but it's just the reality that I usually have to deal with. I wish I could lay down rules like "no analyst ever gets the rights to query the database directly," but all it takes is one analyst to be buddy-buddy with the company owner, and produce a few reports that have really high business value, and then next thing you know, that analyst has sysadmin rights to every database, and anybody who tries to be a barrier to that analyst's success is "slowing the business down."

SigmundA 7y ago

Pretty trival to setup read replicas using log shipping in MSSQL and postgresql to offload analytical loads to secondary servers. I work on a monolith that does this, but its usually not even necessary a single db server on modern hardware with proper resource governing can handle quite a bit.