We have been using Replication in a strange way for 5 years now. I call it
strange because I would not have set it up that way, but it was done and
remains so till date.

The idea then was to bring scalability and failover protection to the databases
by setting up n SQL server 2000 boxes and applying merge replication accross
all of them. It worked and continues to work, however as you can imagine
the overhead of replication as the servers increase is killing the system.

What we are thinking of doing is breaking the replication and replacing it
with 'Manual replication' in a way. The idea is to take advantage of the
fact that we know how our database is organized and use DTS to copy data
accross to a couple of other databases that actually need the data, instead
of just replicating it accross all the multiple servers we have.

We could argue about other alternatives, but that would be unending without
knowing the whole history, thus I will come to the solution we are thinking
of.

To accomplish this I was suggesting the following. I want to know if the
community thinks its viable. (All this in the context of SQL Server 2000)

1. We will have a column on each table we want copied, which indicates if
the row has changed (RDirty).
2. The column will have a default value, and an update trigger, which will
set the value for the column .
3. A DTS package will be written, which will copy these rows and either insert
or update it on the other database as required.
4. Since we use Guids for all the tables we will think of replicating, We
should not have a problem with possibility of duplicate primary keys.

I am concerned about the reliability of DTS. Since we have never used it
before.

Any feedback and thoughts would be highly appreciated.

Thanks
Mohammed