"Hilary Cotter" wrote:

> Distributed transactions are great - only two pitfalls of them which will
> probably make them unusable for you
>
> 1) They add to the latency of every transaction - so instead of your
> transactions taking 43 ms, they will probably be taking 143 ms - which makes
> doesn't make them scalable. The other problem with them is that if you have
> to roll back a transaction, typically you would use the ADO buffer, and
> there is only so much data the ADO buffer can buff.
>
> 2) most firewall administrator's won't open up the firewall ports to allow
> distributed transactions over the internet which means you will probably
> want to use a VPN which means even more latency.
>
> Triggers over the network are way sexy - a couple of problems with them -
> the latency of firing a trigger over the internet make them simply not
> scalable and if your destination server goes down, the transaction (and
> others) will hang - typically for 20 s or so before failing. Hence they are
> not considered to be a good practice.
>
> So - I would recommend transactional replication.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kimberly" <Kimberly@discussions.microsoft.com> wrote in message
> news:258E5095-524F-4A98-A56E-6C12A4214A9A@microsoft.com...
> > I am planning a new deployment of SQL Server 2K. The scenario has a SQL
> > Server at our co-host facility to support our ecommerce activites and a
> SQL
> > Svr at our manufacturing facility to support our order fulfillment
> > activities. The ecommerce datbase is a subset of tables from our
> > manufactuirng/CRM database.
> >
> > The manufacturing/CRM database will need to be able to access and update
> the
> > tables contained within the ecommerce database. I have been considering
> using
> > Distributed Transactions and linked servers.The other option is to set
> > triggers on each database to keep the two databases in sync. I would need
> to
> > copy records whenever a new order is created or a customer creates or
> manages
> > their account on the ecommerce DB . I would also have to set triggers on
> the
> > manufacturing/CRM DB to push data back to the ecommerce DB when an order
> > status is updated and/or when an order is shipped. I can also see some
> > custmer account management triggers that may be needed. The other option
> is
> > to replicate the database every few minutes (5, 10, 15 ???).
> >
> > I'm sure there are other options as well. What is the recommended practice
> > for deploying in this scenario?
>
>