Re: Alternative to Transactional Replication

Tech-Archive recommends: Fix windows errors by optimizing your registry

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: Site Server Requirements.
    ... availability must be able to operate independently; if the WAN link is ... Your requirements are driving much of the design. ... I didn't comprehend what the second server per site was for, ... sites use NT Workgroups for critical server authentication and will not be ...
    (microsoft.public.windows.server.active_directory)
  • Re: Is UserLevel Security Really Necessary?
    ... read-write database. ... use over a WAN. ... client application to a server DB at a central site ... security considerations will be different. ...
    (comp.databases.ms-access)
  • Re: TCP Window Size
    ... terminal server, so window size wouldn't be the problem. ... network I/O, active clients, etc. ... Multiple parallel WAN links are being load ...
    (comp.dcom.sys.cisco)
  • Re: server lockup issue - narrowed down
    ... to delete the extra wan miniports installed i had to ... I used devcon.exe to reinstall the wan miniport and once that was done, ... works fine from clients and server. ... You cannot start the Routing and Remote Access service on a Windows 2000 ...
    (microsoft.public.windows.server.sbs)
  • Re: Two Server Implementation using Exchange 2003 Standard
    ... We have to establish the WAN to support our primary ... The LA users use Citrix Terminal Service Client to ... their Inbox until it arrives at their server... ...
    (microsoft.public.exchange.setup)