replication and lots of questions

I keep going in circles on this...
We are trying to scale our system out. We want to have a
copy of our database available on each of our servers.
One server acts as a load machine, the rest answer IIS
requests hitting their local SQL database. Client
requests are forwarded based off network load balancing.
Updates are not frequently made to the database, but they
do happen on occasion. These updates would be made on one
of the servers servicing the requests from the client
(could be any of say 5 servers). I need this change to
be updated immediately on all other servers services
client requests.
So how do I do this? I can setup triggers. A trigger
fire whenever an update is made to the database, updating
the data in all the coresponding tables on the other
servers. Does this lead to an infinite loop, where this
trigger, triggers the same trigger on another machine,
triggering the same update back?
Transactional Replication? Can you have each server be a
publisher and subscriber. Will there be the same problem,
with one replication triggering the other to replicate.
I could use an instead of trigger to make the changes on
a 'Master' server, that will then replicate the changes to
the rest of the servers. Could be the same problem
though?
Merge Replication... I could do this, but less frequently,
and updates would not be replicated very often.
Has anyone ever done this. Are there any better options.
I am rather new to sql server.
-Jackjackb
What is a size of your database?
Have you tried snapshot replication?
<jackb@.qsi.xxx> wrote in message
news:0b0a01c344b7$57c81460$a001280a@.phx.gbl...
> I keep going in circles on this...
> We are trying to scale our system out. We want to have a
> copy of our database available on each of our servers.
> One server acts as a load machine, the rest answer IIS
> requests hitting their local SQL database. Client
> requests are forwarded based off network load balancing.
> Updates are not frequently made to the database, but they
> do happen on occasion. These updates would be made on one
> of the servers servicing the requests from the client
> (could be any of say 5 servers). I need this change to
> be updated immediately on all other servers services
> client requests.
> So how do I do this? I can setup triggers. A trigger
> fire whenever an update is made to the database, updating
> the data in all the coresponding tables on the other
> servers. Does this lead to an infinite loop, where this
> trigger, triggers the same trigger on another machine,
> triggering the same update back?
> Transactional Replication? Can you have each server be a
> publisher and subscriber. Will there be the same problem,
> with one replication triggering the other to replicate.
> I could use an instead of trigger to make the changes on
> a 'Master' server, that will then replicate the changes to
> the rest of the servers. Could be the same problem
> though?
> Merge Replication... I could do this, but less frequently,
> and updates would not be replicated very often.
> Has anyone ever done this. Are there any better options.
> I am rather new to sql server.
> -Jack|||Writing a distributed transaction in a trigger might be a problem, because
if one server is down, updates will NOT be allowed on any server...
You might try transactional replication with queued updating subscribers...
That will give you near real time updates, but if the publisher server is
down, NO replication will occur, so you need to make sure you have good
uptime on that particular box..
<jackb@.qsi.xxx> wrote in message
news:0b0a01c344b7$57c81460$a001280a@.phx.gbl...
> I keep going in circles on this...
> We are trying to scale our system out. We want to have a
> copy of our database available on each of our servers.
> One server acts as a load machine, the rest answer IIS
> requests hitting their local SQL database. Client
> requests are forwarded based off network load balancing.
> Updates are not frequently made to the database, but they
> do happen on occasion. These updates would be made on one
> of the servers servicing the requests from the client
> (could be any of say 5 servers). I need this change to
> be updated immediately on all other servers services
> client requests.
> So how do I do this? I can setup triggers. A trigger
> fire whenever an update is made to the database, updating
> the data in all the coresponding tables on the other
> servers. Does this lead to an infinite loop, where this
> trigger, triggers the same trigger on another machine,
> triggering the same update back?
> Transactional Replication? Can you have each server be a
> publisher and subscriber. Will there be the same problem,
> with one replication triggering the other to replicate.
> I could use an instead of trigger to make the changes on
> a 'Master' server, that will then replicate the changes to
> the rest of the servers. Could be the same problem
> though?
> Merge Replication... I could do this, but less frequently,
> and updates would not be replicated very often.
> Has anyone ever done this. Are there any better options.
> I am rather new to sql server.
> -Jack|||about 50GB, so snapshot will not work so well
>--Original Message--
>jackb
>What is a size of your database?
>Have you tried snapshot replication?
><jackb@.qsi.xxx> wrote in message
>news:0b0a01c344b7$57c81460$a001280a@.phx.gbl...
>> I keep going in circles on this...
>> We are trying to scale our system out. We want to have
a
>> copy of our database available on each of our servers.
>> One server acts as a load machine, the rest answer IIS
>> requests hitting their local SQL database. Client
>> requests are forwarded based off network load balancing.
>> Updates are not frequently made to the database, but
they
>> do happen on occasion. These updates would be made on
one
>> of the servers servicing the requests from the client
>> (could be any of say 5 servers). I need this change to
>> be updated immediately on all other servers services
>> client requests.
>> So how do I do this? I can setup triggers. A trigger
>> fire whenever an update is made to the database,
updating
>> the data in all the coresponding tables on the other
>> servers. Does this lead to an infinite loop, where this
>> trigger, triggers the same trigger on another machine,
>> triggering the same update back?
>> Transactional Replication? Can you have each server be
a
>> publisher and subscriber. Will there be the same
problem,
>> with one replication triggering the other to replicate.
>> I could use an instead of trigger to make the changes on
>> a 'Master' server, that will then replicate the changes
to
>> the rest of the servers. Could be the same problem
>> though?
>> Merge Replication... I could do this, but less
frequently,
>> and updates would not be replicated very often.
>> Has anyone ever done this. Are there any better
options.
>> I am rather new to sql server.
>> -Jack
>
>.
>