Can you explain what happens in this situation.

Imagine I have a Sql Server Mirroring set-up with 2 databases and 1 witness.

Database clients have in their ConnectionStrings the Principal= and Failover=.

Now imagine if the clients can't connect to Principal for some reason (e.g. Network error) but can for the Failover.

Will they automatically try the failover? What happens from the SQL Server perspective. What happens from the clients perspective?



When to failover is decided by the Witness server not but the Primary or Secondary servers in a mirroring session.

The Secondary Server will come online if the Witness Server cannot see the Primary Server. The Secondary server only gets transactions from the Primary server and nothing else. If the Primary Server stops sending Transactions the secondary server will just simply sit there in restoring mode and do nothing, But if Primary servers becomes invisible for Witness Server thats when the failover happens and the Witness server will make Secondary server to come online.

Actually this the whole purpose of having a Witness server(Auto-Failover). If the secondary server could make these decisions on it own we would not need a witness server at all :) .

  • So if client can't connect to the SQL Server Principal but the Witness can see the Principal Online the clients will have a SQL Connection error? – Carlos Magno Rosa May 25 '15 at 10:53
  • When you say Client are you referring to the Secondary Server in a Mirroring session or the users of the database? – M.Ali May 25 '15 at 10:56
  • I mean the users. – Carlos Magno Rosa May 25 '15 at 12:52
  • Well if the Primary database goes down and witness server doesn't failover for any reason, users will not be able to see secondary server. – M.Ali May 25 '15 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy