Best replication strategy suggestion?

We are in the process of determining a sql db replication strategy. We're
looking at merge replication or log shipping but are in need of more
informed suggestions. It seems like merge replication would be ideal except
i've read that in the event one of the sql servers is unavailable the other
stops accepting data until some elaborate reconfiguration is made. It also
appears that merge replication requires modifying the app tables - not sure
if the vendor will support. Log shipping seems to allow for an easier
reconfiguration of the secondary in the event of a publisher failure but
it's still not very automatic. I've listed the SLA and specifications. Any
ideas or experiences would be most appreciated.
1.) We have two locations and must have a sql server in each location.
2.) There will be a steady stream of inserts only (no updates or deletes) to
one or both sql servers.
3.) The same data must always reside on both servers (or no more than a
minute off)
4.) The application communicates to a virtual dns name that will point to
one or both sql servers via round robin or a load balancer depending upon
the replication strategy.
5.) We would like to load balance across both sql servers and have them
frequently update each others db (maybe merge repl?) but if one of the sql
servers goes down the other must continue functioning with little or know
manual reconfiguration (maybe not merge - maybe log shipping and just one
getting the inserts?).
Thanks,
Jim
Load balancing with SQL Server is extremely hard to do unless it is totally
read only. You are probably better off just having the inserts all go to
one DB and using something like Log shipping or Clustering (if the locations
are close enough) for redundancy. SQL 2005 offers another option called
Database Mirroring that may work in your situation as well.
Andrew J. Kelly SQL MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>
|||Hi Jim
Only having inserts probably simplifies things a lot (depending on the db
design).
Another option you might consider, assuming you've got your key generation
strategy worked out well, might be to split your tables into partitioned
views & set up two push transactional publications (one each way) to handle
the replication. The benefit from this implemantion would be that either
node could go down without breaking the system. If you ran them both in
continuous mode, you'd probably be able to meet your < 1 minute latency
requirement without much difficulty as well..
Cheers,
Greg Linwood
SQL Server MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>