Replication for reporting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jerry Brenner - 31 Jan 2006 00:52 GMT

We're trying to create a replication scheme for a simple data warehouse for
reporting. I've been looking at the Books Online for SQL Server 2005 and
feel like I've been going around in circles. Here's a simple case that we
are having problems with. The prototype for the reporting was done against a
development copy of the operational database, using a set of views. We want
to move the reporting to a separate server. Books Online says that we should
use transactional replication. We need to index the views on the reporting
server for performance reasons. However, we don't want to materialize the
views on the production server because we're trying to avoid the overhead
there. I replicated the views and found out that I can't create indexes on
them on the reporting server.

Is this something that can be done via replication? If so, any clues? If
not, is this something that we need to use DTS for?

Reply to this Message

Michael Hotek - 31 Jan 2006 05:02 GMT

It's actually not a replication issue. In order to be able to index a view,
it has to meet a long list of very strict requirements. Unfortunately, when
the snapshot is executed, it does not set all of the things that need to be
done. So, your best bet is to create the structure on the subscriber and
then configure the snapshot for transactional replication to only send the
data and not the schema.

Signature

Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.

> We're trying to create a replication scheme for a simple data warehouse
> for
[quoted text clipped - 17 lines]
> If
> not, is this something that we need to use DTS for?

Reply to this Message