Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2004-03-03 : 16:59:32
|
| I've done quite a bit of reading on this subject thus far, and I think I've come to the conclusion that you can have Load Balancing OR Redundancy, but not both at the same time.What we want is this:1. Have two SQL servers in two different physical locations2. Both Servers have a copy of everything in case one server dies, the other one can handle the entire load3. Both Servers are doing work (active/active). When Server A writes a record, Server B gets that record as well.4. Failover must be totally automatic (similar to how MS clustering works now)Is this possible??Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 18:06:43
|
| It seems to me that you would use replication plus clustering. But I don't think that you'd want to use clustering across two different physical locations.Tara |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-03-03 : 18:11:49
|
| Tara, I think I found 90% of the solution in this doc:http://www.microsoft.com/windowsserver2003/techinfo/overview/clustergeo.mspxThere's one requirement that I left off my list by accident that even this document doesn't solve. How do you have applications in one location talk to the "local" database? In a Clustered environment, one server is active and the other is not (Active/Active SQL might be a solution here, but I'm not sure). If one server is active, applications in one location will be communicating over the "slow" link to the other location. That's not good.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 18:17:59
|
| Here is what we have:2 servers at each location, one primary location the other disaster recovery site. So we've got 4 servers. The 2 servers at the primary location are clustered and then the two servers at the disaster recovery site are clustered. We then have log shipping to keep the data relatively the same.Both servers at the primary location are active, but not by the same application. One server hosts one mission critical db, the other one hosts the other. In case of a failure, both will be on the same machine. In case of a disaster, we move to the other site. We use replication for the load balancing part for one small part of those apps (it's actually being implemented now).Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-03-03 : 18:24:35
|
| I'd love to have that setup. That would work perfectly for us, except our second site must be "active" too! If the second site was just for failover, it'd be no big deal. Both sites need to be active, failover between sites must be automatic, and the application servers in each location need to use the "local" servers and not whatever is the active server in the cluster.I think we'll have to farm this one out to a consultant :(Thanks for your advice and example Tara. It's much appriciated. There's not many people running systems of this scale.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 18:29:17
|
| We do use the disaster recovery site for one small portion of one application. We moved that data into its own database and replicate it. That way we can still use log shipping on the rest of the database. I'd be interested in how you implement this as maybe we can do something similar for that small portion of the application. Cuz I'm sure there is a better way. I know that Yukon will have data mirroring, but I'm not sure how or if that'll solve our problems.Tara |
|
|
|
|
|
|
|
|
Geographically Redundant Load Balanced Setup