| Author |
Topic |
|
conedm
Starting Member
33 Posts |
 Posted - 2011-10-28 : 12:31:01
|
| I have 2 servers in a workgroup that I am testing out mirroring. I do not have a witness server so it is not able to automatically failover. Is there a way for my applications to still do some sort of failover? If not what is the purpose of mirroring? I cannot accesss the database on the mirror instance because it is being mirrored.As you can tell I am no expert at SQL Server. Everything looks like it is working according to the Database Mirroring MonitorAny help would be greatly appreciated. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-29 : 03:34:39
|
| I don't think application itself would do any kind of automatic fail-over on its own but yes it can recognize that a failover has happened(if it is failover aware).For automatic failover you need to have a witness server but even an express edition of SQL Server(which is free)can act as an witness server.PBUH |
 |
|
|
conedm
Starting Member
33 Posts |
Posted - 2011-11-01 : 11:45:34
|
| My questions would then be...1. In a non-witness mirroring scenario, if the principal server fails, how do you access the mirrored databases. They are in a locked state because they are mirrored. How do I make them available for other applications to access them.2. If I set up a witness server, what would not failover... what I mean is, if the principal server has a complete power failure and is 100% offline, does the system do a failover still? What scenarios would failover and what scenarios would not.3. How is data routed in a witness scenario? Does all traffic need to be pointed to the witness and it writes to the principal and mirror servers? If so, does that mean a failure in the witness server would knock the whole thing down?Thank you in advance. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-01 : 12:31:43
|
quote:
Originally posted by conedm
My questions would then be...1. In a non-witness mirroring scenario, if the principal server fails, how do you access the mirrored databases. They are in a locked state because they are mirrored. How do I make them available for other applications to access them.
Yes they will be locked down until you don't bring the mirror server online.I dont remember the exact syntax but you have to run something like this on the mirror instance.Alter db set partner OFFAlter db with recoveryAs I said earlier if your application is failover aware it will automatically connect to the mirror in case of a failover.For example for .Net you can mention this in your connection string after the DataSource "Failover Partner=YourMirrorServer"quote:
2. If I set up a witness server, what would not failover... what I mean is, if the principal server has a complete power failure and is 100% offline, does the system do a failover still? What scenarios would failover and what scenarios would not.
Any database on which you have not set mirroring will not failover.If the mirror server does not receive a heartbeat till a determined period mirror will assume that principal has gone down and it will automatically failover.quote:
3. How is data routed in a witness scenario? Does all traffic need to be pointed to the witness and it writes to the principal and mirror servers? If so, does that mean a failure in the witness server would knock the whole thing down?
A quorum is formed between all the 3 objects in the mirroring and heartbeats are sent over to each other to check whether they are online.Something like pinging you do in a network.If witness goes down you loose the ability to do automatic failover.PBUH |
|
|
|
conedm
Starting Member
33 Posts |
Posted - 2011-11-01 : 13:22:04
|
| Thank you for that!!! This helps a lot. I am having one problem understanding the application requests and the mirroring involved. Let me clarify...My application requests records from Table A on Database A at IP address A (Principal)If Principal goes down, my application is still requesting IP A. How is it possible for the mirror (having a different IP address) to receive that request?Is the failover request string required in my application for it to work?Meaning that I have to have BOTH the mirroring w/ witness Quorum working AND applications requesting WITH failover ip addresses.Thank you Thank You Thank You!!!! |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-01 : 13:54:51
|
| If your principal goes down when you were requesting records from principal then at that instance the application will fail to get those records.If the failover(manual or automatic) happens then the application will have to resend the request(i.e user will have to refresh the browser page in case of web application). In update,insert or delete the scenario is different where you will have to restore tail log backup from principal to mirror to reflect the latest updates in the mirror.Any failover aware application will only query the principal and nothing else and when it fails to connect to the datasource(principal) in connection string it will then try to connect to the mirrorserver if you set the value of failover partner='MirrorServer'.PBUH |
|
|
|
conedm
Starting Member
33 Posts |
Posted - 2011-11-01 : 15:09:09
|
| OK thanks.Why would the failover parameter in my connection string not work? I am currently set up for Synchronized with high safety and NO witness. It just Times out. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
conedm
Starting Member
33 Posts |
Posted - 2011-11-01 : 15:40:52
|
| It worked! You two are amazing!!!!! Thank you!!!!I am an application developer not a DBA. You two have opened my eyes to the inner workings of SQL Server. Thanks Again |
|
|
|
Sachin.Nand
2937 Posts |
|
|
conedm
Starting Member
33 Posts |
Posted - 2011-11-07 : 11:14:19
|
| OK. Generally speaking I understand that when:1. My principal goes down the mirror pickcs up the principal role and the old principal becomes the mirror. I saw it do that in my testing. 2. In order to switch them back, I would have to run some sort of script to restore the records that went to the NEW principal (former mirror) back on to the old Principal (new mirror) before I switch their roles back to what they were originally.If that is correct...1. would a witness do all of that for me automatically or would I still need to intervene?I am the software developer for my company and we don't have a DBA. So pulling me off my projects to babysit failovers is not ideal, even if they are rare.Any perspective on this is greatly appreciated. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-11-07 : 11:24:46
|
quote:
In order to switch them back, I would have to run some sort of script to restore the records that went to the NEW principal
That's only true if the mirroring was broken (SET PARTNER OFF) or the transaction log got truncated manually. Any kind of failover will maintain the log sequence regardless of which server is principal.quote:
would a witness do all of that for me automatically or would I still need to intervene?
No. The only thing a witness does is create a quorum between principal and mirror. It doesn't actually initiate an automatic failover, it simply tells each server whether it can communicate with the other. If you set automatic failover then the other server in the quorum will do the failover as needed. A witness has no impact on manual failovers, which you can do any time. |
|
|
|
conedm
Starting Member
33 Posts |
Posted - 2011-11-07 : 11:51:28
|
| In my test I disconnected the principal from the network. Any SELECT statements I used returned a dataset from the mirror server just fine, and the mirror still showed as the mirror role. When I reconnected the Principal to the network it still held the principal role also. BUTWhen I disconnected the principal from the network and did any INSERT, UPDATE, or DELETE, the mirror changed itself to the Principal role and when the original principal came back on, it changed itself to the Mirror role.They did NOT revert back to their original roles.I had to script them back and the new data added to the old Mirror was not on the principal. |
|
|
|
conedm
Starting Member
33 Posts |
Posted - 2011-11-07 : 14:07:03
|
| Shouldn't it reconcile transaction logs and return to it's original roles? |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-08 : 04:54:45
|
| What is your setup ? Synchronous or Asynchronous ?In either of them any inflight transactions will be rolled back in case of failover and also you would have to manually swtich the roles.PBUH |
|
|
|
conedm
Starting Member
33 Posts |
Posted - 2011-11-09 : 10:40:01
|
| Synchronous.What do you mean by 'inflight transactions will be rolled back in case of failover'?Nothing rolled over to the old primary when it came back up as the mirror role. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-09 : 11:19:25
|
| Inflight means the logs are in transit from principal to mirror and during that time principal goes down.And remember in synchronous mirroring a transaction is meant complete when the logs have reached mirror and not necessarily played on mirror.PBUH |
|
|
|
conedm
Starting Member
33 Posts |
Posted - 2011-11-09 : 11:38:40
|
| OK but how do you get the records that were added,changed,deleted while the principal was down back on the principal when it comes back up? |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-09 : 12:20:30
|
| You mean the changes which were applied to principal which was before a mirror before the original principal went down ?PBUH |
|
|
|
conedm
Starting Member
33 Posts |
Posted - 2011-11-09 : 12:26:38
|
| The changes that took place after the principal went down, went to the mirror (which took the principal role on) and before the old principal came back up.Sorry for the confusion. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-09 : 12:31:51
|
| So those changes are to be on the mirror(which has now role switched to principal) and you will have to subsequently apply to the mirror (which was principal before) once it comes up.PBUH |
|
|
|
Next Page
|
Synchronous Mirroring W/out Failover SQL Server R2
Here is what you can do