OLTP db vs reporting db?

OLTP db vs reporting db?

Post by Stephane Via » Thu, 29 May 2003 17:58:08



i have an application that is used for data entry (OLPT), so i would
structure the indexes one way. Then i need to replicate that data and
metadata to a production databases where its used for reporting (a public
web app where they simply do selects) so the indexes and table structure
might be different (should be different ?). Whats the best approach to doing
this?

Currently, im simply doing a snapshot replication of the OLPT database to
the production server used for reporting, but were looking for ways to
increase perofrmance of the reporting server app by usingmore indexes and
denormalizing the tables, etc.

thanks
stephane

 
 
 

OLTP db vs reporting db?

Post by Carlos Eduardo Roja » Fri, 30 May 2003 01:31:22


The way we have it setup in my shop is that we have production database
servers (OLTP) replicating (transactional replication) to reporting servers,
which are read only (this is managed through permissions). This allows us to
create additional indexes on the reporting database to improve the
performance of reports.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example


Quote:> i have an application that is used for data entry (OLPT), so i would
> structure the indexes one way. Then i need to replicate that data and
> metadata to a production databases where its used for reporting (a public
> web app where they simply do selects) so the indexes and table structure
> might be different (should be different ?). Whats the best approach to
doing
> this?

> Currently, im simply doing a snapshot replication of the OLPT database to
> the production server used for reporting, but were looking for ways to
> increase perofrmance of the reporting server app by usingmore indexes and
> denormalizing the tables, etc.

> thanks
> stephane


 
 
 

OLTP db vs reporting db?

Post by Hilary Cotte » Fri, 30 May 2003 09:47:39


when you create your publication there is a way in the specify articles
dialog (select the table defaults option), to leave the existing table on
the subscriber intact.  Any indexes you place and update there will not be
whacked in the snapshot process.  You can have entirely different indexes on
the subscriber tables.  In fact I have PK's on both my subscriber and
publisher tables. We need this to replicate from the subscriber to another
subscriber using a 3rd party product.



> The way we have it setup in my shop is that we have production database
> servers (OLTP) replicating (transactional replication) to reporting
servers,
> which are read only (this is managed through permissions). This allows us
to
> create additional indexes on the reporting database to improve the
> performance of reports.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 Programming by Example



> > i have an application that is used for data entry (OLPT), so i would
> > structure the indexes one way. Then i need to replicate that data and
> > metadata to a production databases where its used for reporting (a
public
> > web app where they simply do selects) so the indexes and table structure
> > might be different (should be different ?). Whats the best approach to
> doing
> > this?

> > Currently, im simply doing a snapshot replication of the OLPT database
to
> > the production server used for reporting, but were looking for ways to
> > increase perofrmance of the reporting server app by usingmore indexes
and
> > denormalizing the tables, etc.

> > thanks
> > stephane

 
 
 

1. Visual Basic App using Foxpro DB vs Access DB vs SQL Server DB

I'm planning to create a VB application and am trying to figure out how
to best store the DB.  The DB tables can have as much as 500,000 records
in them and can be well over 100MB in size.

Which DB format will work best with VB.  I want the queries to run
quick.  Will storing the DB in Foxpro take advantage of FoxPro's
Rushmore technology.  What about an SQL Server?

Thanks

Sailesh

2. drop table in a stored procedure

3. OLTP Server vs Reporting Server.

4. pgsql/src/backend/utils/cache (relcache.c)

5. relational/hiearchical db vs. object oriented db

6. Philadelphia Area Sr. Programmer Analyst

7. SQL 6.5 DB Restoring vs SQL 7.0 DB Restoring

8. Paradox & WordPerfect 7 SGML

9. Active Data db V.S. DataWarehouse db

10. Huge difference between OLE-db across network vs local DB

11. Active Data db VS. DataWarehouse db

12. OLE DB vs ODBC for Oracle DB access

13. DB vs db