Alternative to Transactional Replication

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.
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 FTS
http://www.indexserverfaq.com
"daveberm" <david.bermingham@.steeleye.com> wrote in message
news:1172767518.597593.64730@.8g2000cwh.googlegroup s.com...
>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.
>
|||On Mar 2, 9:59 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> 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...@.steeleye.com> wrote in message
> news:1172767518.597593.64730@.8g2000cwh.googlegroup s.com...
>
>
>
>
>
>
> - 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
|||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@.steeleye.com> wrote in message
news:1172974417.463883.123990@.s48g2000cws.googlegr oups.com...
> On Mar 2, 9:59 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> 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
>
|||On Mar 5, 8:59 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> 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 FTShttp://www.indexserverfaq.com
> "daveberm" <david.berming...@.steeleye.com> wrote in message
> news:1172974417.463883.123990@.s48g2000cws.googlegr oups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Thanks Hilary.
How does Transactional Replication handle latency and instability of
WAN links? What happens to the replication if a link between
Publisger and Subscriber is down for a period of time? Is
Transactional Replication recommended when Publishers and Subscribers
are on the other side of WAN links?
In my case, data needs to be updated generally with 2-3 minutes of the
original update to the Publisher. Is Transaction Replication useful
in this case, considing WAN links are in place?
Thanks again for your insight.
Dave