Sql-server – SQL Server Mirroring (with Witness) and Client Timeouts

failovermirroringsql server

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?

Thanks.

Best Answer

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 :) .

Related Solutions

Sql-server – SQL Server 2008 Mirroring Pauses without warning

If you query the sys.database_mirroring catalog view you should find out the status of database mirroring from the mirroring_state field. Try this:

select 
    DB_NAME(database_id),
    mirroring_state
from sys.database_mirroring

When it "pauses", what does the mirroring_state show? Here is a legend:

0 = Suspended

1 = Disconnected from the other partner

2 = Synchronizing

3 = Pending Failover

4 = Synchronized

5 = The partners are not synchronized. Failover is not possible now.

6 = The partners are synchronized. Failover is potentially possible. For information about the requirements for failover see, Synchronous Database Mirroring (High-Safety Mode).

NULL = Database is inaccessible or is not mirrored.

Sql-server – Database Mirroring of SQL server

Your .LDF log is growing, as it cannot be truncated because the mirror is unavailable. Eventually you'll run out of space unless you either break the mirroring or bring the mirror back online.

Best Answer

Related Solutions

Sql-server – SQL Server 2008 Mirroring Pauses without warning

Sql-server – Database Mirroring of SQL server

Related Question