| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2007-05-09 : 10:31:15
|
| Background Info:We have two buildings next door to each other.Currently we have a 64-bit Database Server with 16 gig of memory in one building.We use a program called Double-Take to do disk level replication to another Server with similar hardware in the other building [over a 1 gigabit (10 times as fast as your standard Ethernet) cable].The idea is that if one building "goes down", the other one can keep things going. Problem is, the second server is passive - all the services are stopped. All that hardware of the second server is going to waste. There's also no seamless switchover in the event of a disaster. The Services would have to be started on the passive server and the cache will be cold. No maintenance tasks would ever have run on it either.Next time our hardware gets upgraded, I'll have to put my recommendations forward. I'll probably be required to continue to support this idea of one building having servers that replicate what is in the other one (with one building holding all the passive servers). But I would hope to offer some improvements. How about this?Both buildings have a 64-bit server that plugs in to their own SAN (with one SAN being passive).The 2 Servers (one in each building) get clustered into one logical server in order to utilize all the available processing power.If one building goes down, you are down to one server. If it's the Active Building that went down, the Passive Server needs to know to look at the Passive SAN.What you recon? Is that a good or viable plan? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-09 : 11:02:45
|
| Clustering doesn't give you load balancing, passive node in the cluster does nothing. |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-09 : 16:27:20
|
| True, so to correct:Both buildings have a 64-bit server that plugs in to their own SAN (with one SAN being passive).The 2 Servers (one in each building) get load balanced into one logical server in order to utilize all the available processing power.If one building goes down, you are down to one server. If it's the Active Building that went down, the "Passive" Server needs to know to look at the Passive SAN.Can that be done? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-05-09 : 17:34:14
|
| SQL Server does not support clusters that have the same database active on multiple nodes at the same time. You can have a cluster where multiple nodes have databases active, but only one server can have a particular database active at any one time.The high availability solutions that SQL Server supports are clustering and database mirroring, but they are not solutions for balancing the workload across multiple servers, except for distributing active databases across multiple nodes. Clustering depends on shared storage, so I don’t see the place for the “passive SAN” in this setup. Database mirroring mirrors the database with SQL Server, so I don’t think there is a place there for the SAN to SAN replication that you describe. I cannot think of any benefit the passive SAN gives, except as a very expensive way to mirror your backups offsite.These seem like things that should have been resolved before installing this setup.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-10 : 04:20:42
|
| "We use a program called Double-Take to do disk level replication ..."is that real-time replication of all disk writes? or a somewhat after-the-fact duplication of changes?Kristen |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-10 : 13:02:24
|
quote:
Originally posted by Kristen
"We use a program called Double-Take to do disk level replication ..."is that real-time replication of all disk writes? or a somewhat after-the-fact duplication of changes?Kristen
real-time. it's quite slick |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-10 : 13:06:06
|
quote:
Originally posted by Michael Valentine JonesThese seem like things that should have been resolved before installing this setup.
How right you are!Unfortunately, I was not involved in the decision process.Back to the challenge at hand: Are you saying there is no way of having a real-time replicated site that makes use of the passive server's processing power while both sites are up? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-10 : 13:08:48
|
No built-in solutions, I think. Perhaps you can be creative and code your application to use multiple servers? Its a long shot, probably not even worth it  Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-10 : 13:27:00
|
| "real-time. it's quite slick"OK, I believe you!However, doesn't the gigabit network create a bottleneck compared to the CPU-to-Controller-to-Disks link?Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-10 : 13:59:04
|
| Why not scrap your SAN replication and just use synchronous database mirroring? You could leverage both SANs and both servers with this scenario. ServerA: Database1 (principal), Database2 (mirror), ...ServerB: Database1 (mirror), Database2 (principal), ...Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-05-10 : 16:06:37
|
quote:
Originally posted by coolerbob
quote:
Originally posted by Michael Valentine JonesThese seem like things that should have been resolved before installing this setup.
How right you are!Unfortunately, I was not involved in the decision process.Back to the challenge at hand: Are you saying there is no way of having a real-time replicated site that makes use of the passive server's processing power while both sites are up?
SAN to SAN replication is just a volume level raw block copy, so how could the passive server be updating it while it is being replicated from another site? The database is updating data via the file system, and has no knowledge of this low level process.SQL Server has no facility for two servers to be able to update the same database at the same time. In order to do this, it would need a distributed lock manager at either the SQL Server or Windows level. DEC inplemented this in the RDB database under the VMS OS using an OS level distributed lock manager in 1984, so it has be done by other vendors. I believe that Oracle supports databases active on multiple nodes with an add on-clustering feature (double the price!).What Microsoft calls clustering is a real misuse of the term. It is really just a server fail-over facility to quickly make a backup server active when the primary fails.CODO ERGO SUM |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-10 : 16:38:14
|
| Or you can set peer to peer replication to have two active sql instances, but need to figure out how to distribute users between them. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-10 : 16:50:58
|
quote:
Originally posted by coolerbobProblem is, the second server is passive - all the services are stopped. All that hardware of the second server is going to waste. There's also no seamless switchover in the event of a disaster. The Services would have to be started on the passive server and the cache will be cold. No maintenance tasks would ever have run on it either.
There are high availability solutions but in all of them, the secondary server is just stand-by and you cannot leverage its power to load balance your primary server. Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-11 : 07:05:33
|
quote:
Originally posted by tkizer
Why not scrap your SAN replication and just use synchronous database mirroring? You could leverage both SANs and both servers with this scenario. ServerA: Database1 (principal), Database2 (mirror), ...ServerB: Database1 (mirror), Database2 (principal), ...Tara Kizerhttp://weblogs.sqlteam.com/tarad/
I understand what everyone else has remarked other than the suggestion you have made here Tara. Could someone elaborate on this idea a bit? I don't quite follow. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-11 : 09:49:05
|
| If you like to leverage both server's power, can setup db mirroring like said. Some dbs are live on servera while others are live on serverb, they all have mirrored copy on the other server. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-11 : 12:14:15
|
| In my example Database1 would be live on ServerA and Database2 would be live on ServerB. You'd mirror these databases to the other server for HA needs. You could either split these databases into separate instances or have them be on the same instance. Database mirroring doesn't care.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-11 : 17:09:20
|
| OK thanks Tara, I'll read up more on this approach. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-16 : 03:50:12
|
| Tara, if I'm understanding this right, this approach has little benefit over our Double Take solution: The principle is live and the mirror is passive. All that makes it better is that instead of the whole Server being Passive (Double Take), you can have just an individual database be passive (SQL Server Mirroring).I presume that when the database is passive, you can't access it - not until you have done your failover.If that is the case, this particular situation wont help in our case because we have quite a few cross-database joins. If we are joining from Database1 (principle) to Database2 (mirror), you wouldn't be able to.If I'm right, we'll bin this idea of trying to load balance. Instead, we will use many-to-one Double Take replication. So everyone accesses our 2 live database servers (OLTP & Reporting/OLAP) in Building A. But we replicate the Two Active Database Servers (Building A) to One Passive Database Server (Building B). We then just accept that performance wont be as good in the event of a failover. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-16 : 10:15:28
|
| You'll not get load balance with db mirroring. In face, ther is no good load balancing solution for sql server unless the db is read only. |
|
|
|
Hitesh Shah
Yak Posting Veteran
80 Posts |
Posted - 2007-06-19 : 03:54:20
|
| Can't the mirrored database be readonly for reporting application in a high availability clustering solution .If I were to use the mirrored database for reading , are there any other options . |
|
|
|
Hitesh Shah
Yak Posting Veteran
80 Posts |
Posted - 2007-06-19 : 03:57:40
|
| Also if i were to use analysis services , build cubes , browse the same in mirrored server , can it be done . |
|
|
|
Next Page
|
Can it be done?