| Author |
Topic |
|
Van
Constraint Violating Yak Guru
462 Posts |
 Posted - 2011-10-11 : 09:42:03
|
| I've been tasked to figure out how to set up a particular database to have no downtime. I understand clustering and have it set up on this database (servers). Of course they share storage and all and if one db server goes down, the other picks up and all is fine. However, what if the SAN goes down and there is no storage (the db itself is gone)? Ok, so I assume a standby database and server at the DR site would be best for this. It could be updated with log shipping or SAN replication. My question is how to make failover to that DR server/database automatic in the event that production storage (or worse the whole building) goes down? That DR server/database can't be part of the cluster since it's seperate storage right? How would I need to go about automating all of this? Can someone give me some options that I have?Thanks for your help, it is much appreciated.Van |
|
|
stockholmpost
Starting Member
5 Posts |
Posted - 2011-10-11 : 10:16:45
|
| Best solution HA, when you have SAN |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-11 : 13:14:34
|
| You can use DB Mirroring where the witness server will do the automatic fail over.PBUH |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-10-12 : 14:55:57
|
| Does HA and/or Mirroring also provided automatic failover back to the main site once the issue is resolved? Or will a backup/restore from DR to the main site need to be done first? Thanks again for the help. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-10-12 : 15:12:48
|
| Basicaly my scenario is this:If our prod building goes down at say 2am, they want DR to pick up and start taking the work. Then if our prod building comes back up at say 5am, they want the prod building to pick back up and start taking the work. They want all of this to be automated and occur without any human intervention. I seems that database mirroring would be best to keep DR in sync and failover to if prod goes down, but what about going back to prod once the issue is resolved? How is that automated to get the data changes that occured while DR was taking the work and then make prod take over?Thanks,Van |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2011-10-12 : 16:04:25
|
| Once prod goes down, DR will become prinicpal server. So when prod become available it will work as DR. Reverse failover is not automatic it is manual process. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-10-12 : 16:21:26
|
| Thanks for the info. One more question about mirroring. With clustering you have the cluster name that you access (I think it's called the SQL Network Name). This is how you access SQL regardless of which node has it. Is it the same with mirroring? Is there a place when you configure mirroring that you put in a generic server name that you use to connect so that you don't have to worry about changing your connection string if the DR server takes the principal role?Thanks |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-13 : 03:55:39
|
quote:
Originally posted by Van
Basicaly my scenario is this:If our prod building goes down at say 2am, they want DR to pick up and start taking the work. Then if our prod building comes back up at say 5am, they want the prod building to pick back up and start taking the work. They want all of this to be automated and occur without any human intervention. I seems that database mirroring would be best to keep DR in sync and failover to if prod goes down, but what about going back to prod once the issue is resolved? How is that automated to get the data changes that occured while DR was taking the work and then make prod take over?Thanks,Van
Why do you want the original prod to takeover after it is up and running ?In case of failover the mirror will takeover and the original principal which had gone down will work as a mirror in case it is up.For automatice failover you need to have a witness server which will do the failover.Also if want a very minimal latency you have to set up synchronous mirroring i.e transaction safety full.Ofcourse it does come with some performance bottleneck.PBUH |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-13 : 04:02:49
|
quote:
Originally posted by Van
Thanks for the info. One more question about mirroring. With clustering you have the cluster name that you access (I think it's called the SQL Network Name). This is how you access SQL regardless of which node has it. Is it the same with mirroring? Is there a place when you configure mirroring that you put in a generic server name that you use to connect so that you don't have to worry about changing your connection string if the DR server takes the principal role?Thanks
For clustering you dont have to specify anything.Once heartbeat is not received for some period of time (default is 60 second I believe) one of the passive node will automatically failover and become active and the active will become passive.Thats the reason when you install a cluster you have to mention a static IP and a cluster name.Any application will treat that cluster name as a single server no matter how many nodes are present in the cluster.PBUH |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-10-13 : 08:18:42
|
quote:
Originally posted by Sachin.Nand
quote:
Originally posted by Van
Thanks for the info. One more question about mirroring. With clustering you have the cluster name that you access (I think it's called the SQL Network Name). This is how you access SQL regardless of which node has it. Is it the same with mirroring? Is there a place when you configure mirroring that you put in a generic server name that you use to connect so that you don't have to worry about changing your connection string if the DR server takes the principal role?Thanks
For clustering you dont have to specify anything.Once heartbeat is not received for some period of time (default is 60 second I believe) one of the passive node will automatically failover and become active and the active will become passive.Thats the reason when you install a cluster you have to mention a static IP and a cluster name.Any application will treat that cluster name as a single server no matter how many nodes are present in the cluster.PBUH
Correct. So when mirroring and the mirrored server takes over and becomes the principal, it has a different server name than the original principal server. This will break all the apps because they will be trying to connect to a server with the name of the original principal. Is there a way to have the mirrored server (now the principal) take on the name of the original principal server? With mirroring is there a way to set them up sort of like clustering where they share a SQL Network Name and no matter which one is the principal, they use that SQL Network Name to connect?Thanks |
|
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2011-10-13 : 08:55:20
|
| Depending on the type of App you can specify a mirror server. For example, if you have a web app you can specify the IP address of the mirror server in the connection string. Otherwise you are relying on the app to be 'mirror aware'.The other option is to set up something in DNS.----------------------------Junior DBA learning the ropes |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-10-13 : 11:54:23
|
| I think I'll have to talk to the networking folks to set something up in DNS. Thanks for all the info and help everyone.Van |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-14 : 04:14:11
|
quote:
Originally posted by Van
quote:
Originally posted by Sachin.Nand
quote:
Originally posted by Van
Thanks for the info. One more question about mirroring. With clustering you have the cluster name that you access (I think it's called the SQL Network Name). This is how you access SQL regardless of which node has it. Is it the same with mirroring? Is there a place when you configure mirroring that you put in a generic server name that you use to connect so that you don't have to worry about changing your connection string if the DR server takes the principal role?Thanks
For clustering you dont have to specify anything.Once heartbeat is not received for some period of time (default is 60 second I believe) one of the passive node will automatically failover and become active and the active will become passive.Thats the reason when you install a cluster you have to mention a static IP and a cluster name.Any application will treat that cluster name as a single server no matter how many nodes are present in the cluster.PBUH
Correct. So when mirroring and the mirrored server takes over and becomes the principal, it has a different server name than the original principal server. This will break all the apps because they will be trying to connect to a server with the name of the original principal. Is there a way to have the mirrored server (now the principal) take on the name of the original principal server? With mirroring is there a way to set them up sort of like clustering where they share a SQL Network Name and no matter which one is the principal, they use that SQL Network Name to connect?Thanks
If you are using .Net application then you will have to just add the following into the connection string in the config fileFailover Partner="YourMirrorServer"PBUH |
|
|
|
|
Automatic failover/standby