Re: Alternative to Transactional Replication

Hi Dave!

Basically the insert is wrapped in a transaction when you fire a trigger.
Any update done over a network incurs network hop which will add latency to
all transactions. For example there is a replication topology called
immediate updating where precisely what you are trying to do is implemented.
For a client of mine we found that transaction latency increased from 50 ms
(average) to 150 ms average. Over a WAN you can imagine the increase in
latency.

The real problem is that if there is any network interruption the calling
transaction (which could be the trigger) will hang for up to 20 s before
giving you an access denied error message and rolling back the trigger and
the precipitating trigger action.

So, triggers over the network simply aren't scalable and seriously degrade
performance.

HTH

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



"daveberm" <david.bermingham@xxxxxxxxxxxx> wrote in message
news:1172974417.463883.123990@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Mar 2, 9:59 pm, "Hilary Cotter" <hilary.cot...@xxxxxxxxx> wrote:
triggers work abysmally over a network, let alone a wan.

--
Hilary Cotter

Looking for a SQL Server replication
book?http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL
FTShttp://www.indexserverfaq.com

"daveberm" <david.berming...@xxxxxxxxxxxx> wrote in message

news:1172767518.597593.64730@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx



I have a customer who has about 80 SQL 2005 servers in remote offices
with various WAN links. The distribution of the SQL servers are as
follows:

- 15 site servers (publishers) must update to a single regional server
(central subscriber), there are about 5 regional servers, each with
their own set of publishers, for a total of about 75 publishers and 5
subscribers.

- There is a single SQL server in central HQ which must get updates
from all of the regional servers, for a complete picture of all the
data from the site servers.

- The amount of updates at each site are minimal and only occur once
or twice per day. However, when the update does occur, it should be
pushed out with minimal delay to the regional server as well as the
central HQ server.

- The data at the regional and HQ locations must be available at all
times for reporting purposes. No updates will be performed at the
regional or HQ locations.

- SQL Transactional Replication in a central subscriber model has been
investigated as a possible solution, but has been eliminated as a
possibility at this time for various reasons.

What am considering is a solution similar to the following.

Create a Trigger (http://msdn2.microsoft.com/en-us/library/
ms189799.aspx) that automatically updates a linked server upon update
of a table or view at the site server. Each site office would have a
linked server back to the SQL server in the regional office as well as
the SQL server in the central HQ office. This would ensure that each
time the data is updated, it is automatically updated on the remote
servers as well.

Any thoughts on my solution? Will it work?

Thanks for your input.- Hide quoted text -

- Show quoted text -

Thanks Hilary. I'm in no way a Transact SQL guru, so please forgive
my ignorance. Here is the logic I was considering...

If a local table is updated, have a trigger that detects that update
and runs an INSERT query to update a linked table, which is on the
other side of a WAN.

So here are some more questions...

Is it the INSERT to the linked table that is abysmal? Assuming the
link is stable, is it just that it is slow or are there other issues?

I suppose any update to a linked table across a WAN is abysmal,
regardless of whether it is run by a trigger or by the application
itself, would you agree?

Does transactional replication address the performance issues of
updates over a WAN, or does it suffer from he same restraints?

What if it is just a very small transaction, say just an INSERT of a
single row that occures 2-3 times a day, do you still have
reservations about that kind of implementation over a WAN?

So given my situation, 15 sites with databases that need to update
small amounts of data 2-3 times a day to a master database (for
reporting purposes) across WAN links, what would you recommend?

And finally, are you available on a consulting basis? If so, I may
need your assistance if things get hairy.

David A. Bermingham, MCSE, MCSA:Messaging
Director of Product Management
www.steeleye.com



.

Relevant Pages

  • Re: Alternative to Transactional Replication
    ... Basically the insert is wrapped in a transaction when you fire a trigger. ... Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html ... reporting purposes) across WAN links, ...
    (microsoft.public.sqlserver.replication)
  • Re: Help with Master/Detail UI in ADPs
    ... Yes I would plan on dropping DAO use in adps. ... The link about recordsets is quite informative. ... is more native to the way the ADP will access the SQL Server data. ... very tight control of the transaction, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: SQL 7 vs. 2000 issue -trigger and nulls
    ... >We're having trouble with a trigger updating some tables. ... course be locked by the current transaction, ... locking data and updating rows when the COMMIT inside the trigger is ... I seriously hope that SQL Server 7.0 simply disregarded these two ...
    (microsoft.public.sqlserver.mseq)
  • Re: Help with Master/Detail UI in ADPs
    ... I would look into dropping all DAO code in favor of ADO. ... is more native to the way the ADP will access the SQL Server data. ... very tight control of the transaction, ... bind it to the Detail subform. ...
    (microsoft.public.access.adp.sqlserver)
  • [3/3] POHMELFS: core files.
    ... new file mode 100644 ... Each transaction contains all information needed to process given command ... +system and put data where it belongs (like page or inode cache). ... are asynchronous and are sent to the server during system writeback. ...
    (Linux-Kernel)