Single point of failure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lauren - 30 Aug 2006 15:46 GMT

Hi,

After some very helpful advice from this forum, I have set up Merge
replication on 2 SQL Server and 1 SQL Express machines. One of the SQL
Servers has been configured as the publisher and the other one and the SQL
Express as subscribers.

This grouping of machines will be repeated throughout our system with each
publisher also replicating to the other publishers. I haven't done the second
bit yet so I am hoping this makes sense? I assume this would mean setting
each publisher up as a subscriber to each other publisher as well - I think I
must be on the wrong track here as it sounds very inefficient? I am sure
there is a better way?

I am also concerned about the fact that each publisher is a single point of
failure in this setup. We can't risk that in our system. I know about the
alternate sync partner but am a bit concerned about going down that route as
it is deprecated and we will likely be moving to new versions whenever they
become available for the lifetime of our development project.

How do I deal with these issues?

Thankyou,
Lauren

Reply to this Message

Hilary Cotter - 30 Aug 2006 16:45 GMT

Clustering is probably your best bet. Alternate sync partners will be
supported until the next version. They do have limitations though.

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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

> Hi,
>
[quoted text clipped - 25 lines]
> Thankyou,
> Lauren

Reply to this Message

Lauren - 31 Aug 2006 10:29 GMT

Is there any reason why I couldn't set up 2 servers at each site to be both
publisher and subscriber to each other and to the other publishers in other
sites or is this a horribly inefficient way of doing things. I suppose what I
am asking is is it possible to setup merge replication in a peer-to-peer
style? Would that be significantly less efficient than peer-to-peer itself?

Lauren Eaton

> Clustering is probably your best bet. Alternate sync partners will be
> supported until the next version. They do have limitations though.
[quoted text clipped - 28 lines]
> > Thankyou,
> > Lauren

Reply to this Message

Hilary Cotter - 31 Aug 2006 12:55 GMT

There is no reason you don't continue with your existing setup. It will
work. However I assume you have an app currently connected to SQL Server A.
When SQL Server A goes down you will have to point this app to SQL Server B.
Then you will have to have the SQL Express client resubscribe to SQL Server
B.

Then when you failback you have to point your app back to SQL Server A, and
resubscribe SQL Express to SQL Server A.

If you were to cluster you would not have to modify the app or drop the
subscription and recreate it each time you failed over.

Signature

> Is there any reason why I couldn't set up 2 servers at each site to be
> both
[quoted text clipped - 47 lines]
>> > Thankyou,
>> > Lauren

Reply to this Message

Lauren - 31 Aug 2006 13:55 GMT

Kind of, my app is connected to the Express DB which has subscribed to
replication from Server A. I want to ensure that I can still receive
replications from B or C when A goes down.

Regards,
Lauren

> There is no reason you don't continue with your existing setup. It will
> work. However I assume you have an app currently connected to SQL Server A.
[quoted text clipped - 59 lines]
> >> > Thankyou,
> >> > Lauren

Reply to this Message

Hilary Cotter - 31 Aug 2006 14:12 GMT

that's going to be hard. Failover will be fine. Failback from Server B to
Server A will be difficult.

Signature

> Kind of, my app is connected to the Express DB which has subscribed to
> replication from Server A. I want to ensure that I can still receive
[quoted text clipped - 78 lines]
>> >> > Thankyou,
>> >> > Lauren

Reply to this Message

Lauren - 31 Aug 2006 14:19 GMT

Why does the failback cause difficulties?

Lauren

> that's going to be hard. Failover will be fine. Failback from Server B to
> Server A will be difficult.
[quoted text clipped - 81 lines]
> >> >> > Thankyou,
> >> >> > Lauren

Reply to this Message

Hilary Cotter - 31 Aug 2006 14:27 GMT

Sometimes its hard to drop subscriptions to existing publications where the
publisher is offline. Its also hard sometimes to kick all your users off
while you doing the resubscribe, and you will expose yourself to data loss
here.

Signature

> Why does the failback cause difficulties?
>
[quoted text clipped - 102 lines]
>> >> >> > Thankyou,
>> >> >> > Lauren

Reply to this Message

Lauren - 31 Aug 2006 14:44 GMT

OK - so you would recommend to go with clustering?

Lauren

> Sometimes its hard to drop subscriptions to existing publications where the
> publisher is offline. Its also hard sometimes to kick all your users off
[quoted text clipped - 107 lines]
> >> >> >> > Thankyou,
> >> >> >> > Lauren

Reply to this Message

Hilary Cotter - 31 Aug 2006 21:15 GMT

Do you have clustering experience? A large part of deploying a successful
clustering solution is having good clustering resources on site.

Signature

> OK - so you would recommend to go with clustering?
>
[quoted text clipped - 126 lines]
>> >> >> >> > Thankyou,
>> >> >> >> > Lauren

Reply to this Message