SQL Server Database is enabled for Database Mirroring, but the database lacks quorum, and cannot be opened – Error 955
SQL Server Database is enabled for Database Mirroring, but the database lacks quorum, and cannot be opened – Error 955
In a SQL Server mirroring configuration that is running synchronously (High-Safety Mode), with the witness to allow automatic failover, here is what happens if one of the partners or the witness becomes unavailable:
- If the principal server becomes unavailable and databases are synchronized, automatic failover occurs, and the mirror server switches to the role of principal.
- If the mirror server becomes unavailable, the principal and witness continue.
- If the witness server becomes unavailable, both partners must be available for the mirroring to continue.
In a mirroring configuration when a witness is set, the mirroring session requires quorum to continue. At least two server instances need to be available for the mirroring database to be made available.
That is why it is recommended that you remove the witness from the session if you expect the witness to remain disconnected for a long time.
Remove the witness from the mirroring session before you disconnect both mirror and witness server from the principal.
If the principal is disconnected from both the mirror server and the witness server, the principal database becomes unavailable. This happens because the principal doesn't know whether the mirror and the witness servers are still connected to each other and the mirror has become the new principal. Database will automatically become available again and the mirroring will continue, when either the mirror or the witness or both become available, and the quorum is established again.
If the principal is disconnected from both the mirror server and the witness server, SQL Server Management Studio will show the database as "Principal, Disconnected / In Recovery":
If you try to access it you will get the following error:
Msg 955, Level 14, State 1, Line 1
Database Database_Name is enabled for Database Mirroring, but the database lacks quorum: the database cannot be opened. Check the partner and witness connections if configured.
Database is not accessible:
In the SQL Server log, you will see the error message for the user that tried to connect to the inaccessible database:
Message
Error: 18456, Severity: 14, State: 38.
Login failed for user 'Domain_Name\Login_Name'. Reason: Failed to open the explicitly specified database.
In a situation where both the witness and the mirror servers are down (and neither of them can be brought online to establish the quorum again), and you want to bring the principal that lacks quorum online, you will need to remove database mirroring.
Removing witness from the mirroring session will not work:
Msg 1431, Level 16, State 4, Line 1
Neither the partner nor the witness server instance for database "test_db" is available. Reissue the command when at least one of the instances becomes available.
In this case you will need to remove database mirroring to make the database accessible again:
ALTER DATABASE [Database_Name] SET PARTNER OFF
Comments
Post a Comment