replication databases and reporting - best practices?
Tom Dacon
do very little OLAP, and quite simple OLAP at that. Instead we've got a set
of OLTP databases on our production server, and a distantly-located set of
reporting databases, which include databases replicated from the OLTP
databases. We create our reports with good old stored procedures and exports
to CSV files and Access databases for the end users. Primitive, I know, but
it's a small shop and our resources are limited. Not to mention that we don't
have a DBA to our name.
It's easy for me to find best practices for Reporting Services and report
design, but I've had little success in finding recommendations for such
small-scale operations as we're doing here. Any suggestions or pointers would
be extremely welcome.
As an example, here's a question that's arisen in our dev shop about the
advisibility of having tables and stored procedures, etc., residing in
replicated databases.
We're replicating OLTP database tables from our production servers to
another set of servers (our reporting environment) and doing all our
reporting from there. One opinion is that the replicated servers should have
absolutely nothing in them but the replicated data. Another is that there's
nothing wrong with adding other reporting tables and their stored procedures,
and running certain intermediate reporting processes on the replicated
databases.
The argument against putting anything else into them, as stated, is that it
makes it easy and quick to create new replication databases. The argument for
it, as stated, is that that's where the data is and it might as well be done
right there.
Any comments on this particular question?
And once again, any pointers to recommended best practices for structuring
our servers and databases would be appreciated.
Tom Dacon
Dacon Software Consulting