Replication for reporting -- sql server replication

Jerry Brenner
1/30/2006 4:52:27 PM

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

Michael Hotek
1/30/2006 11:02:22 PM

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.

--
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.

[quoted text, click to view]

sql server replication : Replication for reporting