Posted by Ed Murphy on 04/18/07 01:46

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?

[Back to original message]