DB architecture

Hi there.
We are building a new mission critical application in our company, using SQL
server 2000 as the RDBMS. The new database is replacing a legacy system that
used to run in two platforms: the day to day operations (OLTP) was
maintained in a small DB2 database running on a OS/2 PC (only 1 week of
data) and the rest was moved periodically to an iseries IBM server (DB2).
all the OLTP was done on the PC, and most of the reporting was done against
the iseries DB2 database.
So far we only have one database to replace the two systems mentioned. We
are planning to either separate the data and have the reporting done in
another SQL server machine (with the same schema,and using log shipping) or
create a set of tables that would pre-process the information and would be
used by the reporting tools. When planning for theses, we created a set of
views tha are being use by our current reports (this layering protects us if
we need to change the underlying schema). The current DB is highly
normalized and I don't think would operate well for OLAP. Is the log
shipping approach recommended? Is there a better alternative? I wouldn'
like to have to maintain two different schemas.
Thank you,
Pedro."PeyoQuintero" <pedroquintero@.earthlink.net> wrote in message
news:u73cc.10784$NL4.2990@.newsread3.news.atl.earthlink.net...
> Hi there.
> We are building a new mission critical application in our company, using
SQL
> server 2000 as the RDBMS. The new database is replacing a legacy system
that
> used to run in two platforms: the day to day operations (OLTP) was
> maintained in a small DB2 database running on a OS/2 PC (only 1 week of
> data) and the rest was moved periodically to an iseries IBM server (DB2).
> all the OLTP was done on the PC, and most of the reporting was done
against
> the iseries DB2 database.
> So far we only have one database to replace the two systems mentioned. We
> are planning to either separate the data and have the reporting done in
> another SQL server machine (with the same schema,and using log shipping)
or
> create a set of tables that would pre-process the information and would be
> used by the reporting tools. When planning for theses, we created a set of
> views tha are being use by our current reports (this layering protects us
if
> we need to change the underlying schema). The current DB is highly
> normalized and I don't think would operate well for OLAP. Is the log
> shipping approach recommended? Is there a better alternative? I wouldn'
> like to have to maintain two different schemas.
> Thank you,
>
The answer, as so often with these sorts of questions, is "It depends". If
you want the maximum performance then on the OLTP database have a highly
normalized schema and few indexes. Normalized data means the integrity of
your data is easily maintained.
On the OLAP database, if no-one is updating the data directly, then you
already know the data is correct. Normalization is no longer required, and
the speed of your queries can be improved by creating tables that reflect
your views. No messy or slow joins for SQL to deal with. Bung in your
indexes to speed the filtering and grouping of your data. Include
calculated and aggragated data directly in your tables.
To maintain these different schemas create DTS jobs to transform and move
your data from one database to the other.
Of course, the downside of this approach, as you've identified, is
maintaining two schemas, but the OLAP database (apart from the automated DTS
jobs) is a read-only database, and therefore should not need that much
maintaining once up and running. Also, log shipping will typically have
less latency, but in your old model you imply that there was a weekly
upload, so that would not be an issue.
Log Shipping works only if both databases have the same schema. It is much
simpler than DTS, but less flexible as well.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004