Hi,
In SQL 2005, with SQL Clustering:
1. Can a THREE-node active/active/active cluster be setup? If yes, is
setting up a three-node a/a/a cluster recommended? Any pitfalls or gotchas
that one needs to be aware of?
With Replication:
1. Can a subscriber in turn be a publisher to another subscriber? i.e. a
subscriber subsribes to a publication from one publisher and then in turn
creates a similar publication, which is subscribed by another subscriber.
2. Can replication (immediate and/or queued updating) be configured between
THREE servers? i.e. each server is a publisher and subcriber to the other two
servers.
We are working on a global strategy to identify how best to keep data in
sync between three geographically located SQL servers, while maintaining
latency as low as possible.
Peer-to-Peer replication was investigated as well, but it did not fit with
our company's strategic plan as one of our requirement is that database
updates can be done at any server, at anytime. Peer-to-Peer allows updates
for a given row only at one database until it has been syncronized with its
peers, which can be done by directing updates for a set of rows to a
particular server.
Your guidance, thougths, and suggestions are very valued and appreciated.
TIA
Regards,<DIV>"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:7C72EA17-1D74-42FA-BA28-6A0CBDC5535C@.microsoft.com...</DIV>> Hi,
> In SQL 2005, with SQL Clustering:
> 1. Can a THREE-node active/active/active cluster be setup? If yes, is
> setting up a three-node a/a/a cluster recommended? Any pitfalls or gotchas
> that one needs to be aware of?
Yes, although we don't really call it active/active anymore. It's just an
N-node cluster. It has a number of cluster nodes and a number of SQL
instances (virtual servers). The biggest problem with running SQL instances
on all your cluster nodes is that you can have performance problems when you
fail over. Often you add an extra passive node to hold enough capacity in
reserve for failover. This is generally referred to as an N+1 cluster (N
"active" nodes, 1 "passive").
> With Replication:
> 1. Can a subscriber in turn be a publisher to another subscriber? i.e. a
> subscriber subsribes to a publication from one publisher and then in turn
> creates a similar publication, which is subscribed by another subscriber.
> 2. Can replication (immediate and/or queued updating) be configured
> between
> THREE servers? i.e. each server is a publisher and subcriber to the other
> two
> servers.
> We are working on a global strategy to identify how best to keep data in
> sync between three geographically located SQL servers, while maintaining
> latency as low as possible.
> Peer-to-Peer replication was investigated as well, but it did not fit with
> our company's strategic plan as one of our requirement is that database
> updates can be done at any server, at anytime. Peer-to-Peer allows updates
> for a given row only at one database until it has been syncronized with
> its
> peers, which can be done by directing updates for a set of rows to a
> particular server.
You've got it wrong with Peer-to-Peer. Peer-to-Peer allows update to any
server at anytime. It just has no mechanism to protect you if you make
conflicting changes at nearly the same time, one of the changes will be
lost. The situation you are describing with multiple publishers would
behave in a similar manner.
David|||1) Yes, the gotcha is that at any one time a node may have to support all
three instances which means it will have to be pretty beefy to offer optimal
performance to all three instances
Replication
1) Yes, its called republishing. It normally used to provide hierarchies in
merge replication to scale out.
2) No, but you might want to look at bi-directional transactional
replication which can be used to do this. It would provide bi-directional
replication between node a and node b, and node a and node c, but not
between node b and node c unless you implement some pretty complex
filtering.
When Peer to peer out of the box encounters an error its distribution agents
will stop until you fix the error. You can run it in the continue on data
consistency errors profile, but doing this will leave your databases in an
inconsistent state.
--
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
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:7C72EA17-1D74-42FA-BA28-6A0CBDC5535C@.microsoft.com...
> Hi,
> In SQL 2005, with SQL Clustering:
> 1. Can a THREE-node active/active/active cluster be setup? If yes, is
> setting up a three-node a/a/a cluster recommended? Any pitfalls or gotchas
> that one needs to be aware of?
> With Replication:
> 1. Can a subscriber in turn be a publisher to another subscriber? i.e. a
> subscriber subsribes to a publication from one publisher and then in turn
> creates a similar publication, which is subscribed by another subscriber.
> 2. Can replication (immediate and/or queued updating) be configured
> between
> THREE servers? i.e. each server is a publisher and subcriber to the other
> two
> servers.
> We are working on a global strategy to identify how best to keep data in
> sync between three geographically located SQL servers, while maintaining
> latency as low as possible.
> Peer-to-Peer replication was investigated as well, but it did not fit with
> our company's strategic plan as one of our requirement is that database
> updates can be done at any server, at anytime. Peer-to-Peer allows updates
> for a given row only at one database until it has been syncronized with
> its
> peers, which can be done by directing updates for a set of rows to a
> particular server.
> Your guidance, thougths, and suggestions are very valued and appreciated.
> TIA
> Regards,|||Hi Rob,
the only thing I'd add to the other comments is that merge replication
essentially covers point (2). The terminology is in conflict but the
functionality is the same ie there would only be need for one publisher, and
the pthers would be subscribers, but for the most part, there isn't a
distinction between these roles in merge replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Thanks Hilary.
For replication Q. 2: You may have already answered this but I was wondering
if a bi-directional replication were to exists between node A and B, could
node B republish the data to node C unidirectionally?
Regards,
- Rob.
"Hilary Cotter" wrote:
> 1) Yes, the gotcha is that at any one time a node may have to support all
> three instances which means it will have to be pretty beefy to offer optimal
> performance to all three instances
> Replication
> 1) Yes, its called republishing. It normally used to provide hierarchies in
> merge replication to scale out.
> 2) No, but you might want to look at bi-directional transactional
> replication which can be used to do this. It would provide bi-directional
> replication between node a and node b, and node a and node c, but not
> between node b and node c unless you implement some pretty complex
> filtering.
> When Peer to peer out of the box encounters an error its distribution agents
> will stop until you fix the error. You can run it in the continue on data
> consistency errors profile, but doing this will leave your databases in an
> inconsistent state.
> --
> 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
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:7C72EA17-1D74-42FA-BA28-6A0CBDC5535C@.microsoft.com...
> > Hi,
> >
> > In SQL 2005, with SQL Clustering:
> > 1. Can a THREE-node active/active/active cluster be setup? If yes, is
> > setting up a three-node a/a/a cluster recommended? Any pitfalls or gotchas
> > that one needs to be aware of?
> >
> > With Replication:
> > 1. Can a subscriber in turn be a publisher to another subscriber? i.e. a
> > subscriber subsribes to a publication from one publisher and then in turn
> > creates a similar publication, which is subscribed by another subscriber.
> > 2. Can replication (immediate and/or queued updating) be configured
> > between
> > THREE servers? i.e. each server is a publisher and subcriber to the other
> > two
> > servers.
> >
> > We are working on a global strategy to identify how best to keep data in
> > sync between three geographically located SQL servers, while maintaining
> > latency as low as possible.
> >
> > Peer-to-Peer replication was investigated as well, but it did not fit with
> > our company's strategic plan as one of our requirement is that database
> > updates can be done at any server, at anytime. Peer-to-Peer allows updates
> > for a given row only at one database until it has been syncronized with
> > its
> > peers, which can be done by directing updates for a set of rows to a
> > particular server.
> >
> > Your guidance, thougths, and suggestions are very valued and appreciated.
> >
> > TIA
> >
> > Regards,
>
>
