Posted by Anthony Paul on 04/18/07 12:49

Hello Ed,

That's a very good idea, if it turns out that the filter isn't
dynamically configurable then perhaps using a view as a filter and
replicating the view can compensate since views can be modified at any
time. However, I doubt that replication can be done on a view rather
than on a table. I'll have to check it out!

Regards,

Anthony

On Apr 17, 9:46 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
> Anthony Paul wrote:
> > I am involved in a scenario where there is a huge (SQL Server 2005)
> > production database containing tables that are updated multiple times
> > per second. End-user reports need to be generated against the data in
> > this database, and so the powers-that-be came to the conclusion that a
> > reporting database is necessary in order to offload report processing
> > from production; of course, this means that data will have to be
> > replicated to the reporting database. However, we do not need all of
> > the data in the production database, and perhaps a filtering criteria
> > can be established where only certain rows are replicated over to the
> > reporting database as they're inserted (and possibly updated/deleted).
> > The current though process is that the programmers designing the
> > queries/reports will know exactly what data they need from production
> > and be able to modify the replication criteria as needed. For example,
> > programmer A might write a report where the data he needs can be
> > expressed in a simple replication criteria for table T where column X
> > = "WOOD" and column Y = "MAHOGANY". Programmer B might come along a
> > month later and write a report whose relies on the same table T where
> > column X = "METAL" and column Z in (12, 24, 36). Programmer B will
> > have to modify Programmer A's replication criteria in such a way as to
> > accomodate both reports, in this case something like "Copy rows from
> > table T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X =
> > "METAL" and col Z in (12, 24, 36))". The example I gave is really
> > trivial of course but is sufficient to give you an idea of what the
> > current thought-process is.
>
> > I assume that this is a requirement that many of you may have
> > encountered in the past and I am wondering what solutions you were
> > able to come up with. Personally, I believe that the above method is
> > prone to error (in this case the use of triggers to specify
> > replication criteria) and I'd much rather use replication services to
> > copy tables in their entirety. However, this does not seem to be an
> > option in my case due to the sheer size of certain tables. Is there
> > anything out there that performs replication based on complex
> > programmer defined criteria? Are triggers a viable alternative? Any
> > alternative out-of-the-box solutions?
>
> Is it possible to create views, then configure things so that just
> those views are replicated as tables on the second server?- Hide quoted text -
>
> - Show quoted text -

[Back to original message]