February 19, 2009 at 1:23 pm
Please put up with my newbie questions. I have recently been tasked with our DBA duties. Prior to this it was whomever on our team (along with different vendors) working with our local SQL boxes :crazy:
I have two sites (A & B) connected via an OC-3 (could easily be upgraded if needed). Currently our only SQL server (at least for the purpose of this discussion) resides at site A, but is utilized by applications/users at site A & B.
We are in the process of disaster preparation. For this scenario the WAN link between site A & B has been severed (no real redundant connection available, or if there was, it would likely be down too - think earthquake, hurricane, etc.). Each site is now without connectivity.
I would like to do several things. First, I would like to have a more redundant SQL environment on a regular day - I was thinking mirroring (one server at each site). This is great if one server goes down, but I'm not sure what would occur in the case of severed connectivity.
Is there a way to allow each server to become primary? I realize we will have have some data headaches later, with each site committing changes to the data. Thankfully there is a very slim chance they would be editing the same records. Although, it would be great if there were a way to store/compare transaction logs after the fact and merge the two (now non-synchronized databases).
Any thoughts??? Is my head too far in the clouds, thinking this could work?
Thanks,
Nicholaus
February 19, 2009 at 2:00 pm
Only thing that I can think about that would work in your scenario is MERGE.
-Roy
February 20, 2009 at 9:19 am
I agree with Roy. This sounds like a classic example of a scenario that would require merge replication.
As long as your requirement is that updates need to take place at both sites A and B, merge replication is probably your best. You'll just need to make sure to carefully build your rules surrounding conflict resolution.
Also, keep in mind that merge replication will add a slight amount of CPU overhead to your SQL servers.
Regards, Jim C
February 20, 2009 at 10:22 am
Ahhhh, I seem to have left out one thing. I would also like to have redundancy for a single server failing.
If the server at Site A dies, I would like for clients at sites A and B to utilize the server at site B (obviously network connectivity is not lost in this circumstance).
Is this a possibility, or am I stuck with a set of mirrored servers at each site performing Merge replication from Site A to Site B???
Thanks,
Nicholaus
February 20, 2009 at 10:31 am
As its Name implies MERGE will "merge" changes bidirectionally. So all you need is to point your clients to the right server.
One way of doing this is by using DNS aliases. In your scenario you will just redirect the alias of Server A to Server B and you are done.
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply