Multi cluster synchronisation Active/Active configuration

Question

Multi cluster synchronisation Active/Active configuration

liteswitch

SSCommitted

Points: 1824
More actions
April 20, 2012 at 4:29 am

#272961

Hi all,
I've been asked a interesting question by management, and I'm trying to work out how it would work.
Currently, we have Windows 208R2 and SQL 2008R2 running on a 2 node cluster using SAN storage. A standard approach.
Now, I've been asked how we can scale this out, to have two clusters simultaneously live but with the databases synchronised, like the following
Cluster A
2 SQL cluster nodes attached to SAN A
Cluster B
2 SQL cluster nodes attached to SAN B
The two clusters may or may not be in the same physical datacentre. However, if I wanted both clusters to be online and available simultaneously, with the data synchronised (possibly two way synchronised, to changes at eaither location are synced to the other location) between the two clusters, how would I achieve this?
My original thinking was database mirroring between the two clusters, but that wouldn't give me both clusters online at the same time, as mirroring is one way only at any point in time. Would some kind of replication between clusters work, or would I have to look at synchronisation at the SAN/LUN level instead to get this to work?
Ta,
Dan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

For simplicity in the set up there's no reason for this to be two clusters. This can be a single cluster spread over two sites using SAN replication to mirror at the disk level. There are other 3rd part products which can mirror disks if you cant justify the costs of the SAN solution.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 2

I jumped the gun above by assuming this was for business continuity.

liteswitch (4/20/2012)
The two clusters may or may not be in the same physical datacentre. However, if I wanted both clusters to be online and available simultaneously, with the data synchronised (possibly two way synchronised, to changes at eaither location are synced to the other location) between the two clusters, how would I achieve this?
My original thinking was database mirroring between the two clusters, but that wouldn't give me both clusters online at the same time, as mirroring is one way only at any point in time. Would some kind of replication between clusters work

The only way I can think of to achieve this option (I'm assuming this is for read/write) is to use peer to peer replication between clusters. Mirroring could bring the secondary database online using a database snapshot but this would be read only. You would have to keep dropping/recreating snapshots to get up to date data

liteswitch (4/20/2012)
, or would I have to look at synchronisation at the SAN/LUN level instead to get this to work?

You can do this as a read only volume using Scalable Shared Databases. This cannot be done in a read/write state

http://msdn.microsoft.com/en-us/library/ms345584.aspx

liteswitch SSCommitted Points: 1824 More actions · Answer 3

Thanks for the responses. And yes, just to clarify, my requirement would be to have both clusters available for read/write operations simultaneously, with the write operations synchronised between the two somehow, and that's what the tricky part!

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 4

liteswitch (4/20/2012)
Thanks for the responses. And yes, just to clarify, my requirement would be to have both clusters available for read/write operations simultaneously, with the write operations synchronised between the two somehow, and that's what the tricky part!

For true independant operation of the servers you'll need peer to peer transactional replication then. You could use merge but that would make one server a publisher and the other subscriber. In this scenario i'd go for peer to peer over this.

Transactional replication with updatable subscribers isnt robust enough for this in my opinion.

liteswitch SSCommitted Points: 1824 More actions · Answer 5

Yeah, I'm coming to that same conclusion myself, that peer to peer transaction replication is likely to be the solution here I think. Now I've just got to think about any potential issues around conflicts, but I think that might not be too bad..

SQLisAwe5oMe SSChampion Points: 14027 More actions · Answer 6

MysteryJimbo (4/20/2012)
liteswitch (4/20/2012)
Thanks for the responses. And yes, just to clarify, my requirement would be to have both clusters available for read/write operations simultaneously, with the write operations synchronised between the two somehow, and that's what the tricky part!
For true independant operation of the servers you'll need peer to peer transactional replication then. You could use merge but that would make one server a publisher and the other subscriber. In this scenario i'd go for peer to peer over this.
Transactional replication with updatable subscribers isnt robust enough for this in my opinion.

Hi, in peer to peer transactional replication, both databses would act as publisher? Please advise.

Thanks,

TA.

Regards,
SQLisAwe5oMe.