How to failover in a database mirroring session when the safety mode is off and no witness

Question

I'm testing SQL Server database mirroring and below is my topology.

• node2, principal
• node1, mirror

+-------------+--------------------------------------+-----------------+----------------------+----------------+---------------------+-------------------------+------------------------+-----------------------------+---------------------------+--------------------------+----------------------------+------------------------+-------------------------+------------------------------+------------------------+------------------------------+----------------------+---------------------------+--------------------------+---------------------------+
| database_id |            mirroring_guid            | mirroring_state | mirroring_state_desc | mirroring_role | mirroring_role_desc | mirroring_role_sequence | mirroring_safety_level | mirroring_safety_level_desc | mirroring_safety_sequence |  mirroring_partner_name  | mirroring_partner_instance | mirroring_witness_name | mirroring_witness_state | mirroring_witness_state_desc | mirroring_failover_lsn | mirroring_connection_timeout | mirroring_redo_queue | mirroring_redo_queue_type | mirroring_end_of_log_lsn | mirroring_replication_lsn |
+-------------+--------------------------------------+-----------------+----------------------+----------------+---------------------+-------------------------+------------------------+-----------------------------+---------------------------+--------------------------+----------------------------+------------------------+-------------------------+------------------------------+------------------------+------------------------------+----------------------+---------------------------+--------------------------+---------------------------+
|           1 | NULL                                 | NULL            | NULL                 | NULL           | NULL                | NULL                    | NULL                   | NULL                        | NULL                      | NULL                     | NULL                       | NULL                   | NULL                    | NULL                         | NULL                   | NULL                         | NULL                 | NULL                      | NULL                     | NULL                      |
|           2 | NULL                                 | NULL            | NULL                 | NULL           | NULL                | NULL                    | NULL                   | NULL                        | NULL                      | NULL                     | NULL                       | NULL                   | NULL                    | NULL                         | NULL                   | NULL                         | NULL                 | NULL                      | NULL                     | NULL                      |
|           3 | NULL                                 | NULL            | NULL                 | NULL           | NULL                | NULL                    | NULL                   | NULL                        | NULL                      | NULL                     | NULL                       | NULL                   | NULL                    | NULL                         | NULL                   | NULL                         | NULL                 | NULL                      | NULL                     | NULL                      |
|           4 | NULL                                 | NULL            | NULL                 | NULL           | NULL                | NULL                    | NULL                   | NULL                        | NULL                      | NULL                     | NULL                       | NULL                   | NULL                    | NULL                         | NULL                   | NULL                         | NULL                 | NULL                      | NULL                     | NULL                      |
|           5 | A66E5470-F1ED-47D5-83F4-C86A1F8E7833 | 4               | SYNCHRONIZED         | 1              | PRINCIPAL           | 4                       | 1                      | OFF                         | 6                         | TCP://NODE1.dev.com:5022 | NODE1                      |                        | 0                       | UNKNOWN                      | 40000000038100001      | 10                           | NULL                 | UNLIMITED                 | 40000000038100001        | 40000000038100001         |
+-------------+--------------------------------------+-----------------+----------------------+----------------+---------------------+-------------------------+------------------------+-----------------------------+---------------------------+--------------------------+----------------------------+------------------------+-------------------------+------------------------------+------------------------+------------------------------+----------------------+---------------------------+--------------------------+---------------------------+

Note that the safety level is off and I don't have a witness. Can I do a failover in this state? I tried:

ALTER DATABASE AdventureWorks2016 SET PARTNER FAILOVER;

But got this error:

Msg 1477, Level 16, State 1, Line 26
The database mirroring safety level must be FULL to manually failover database 
"AdventureWorks2016".  Set safety level to FULL and retry.

I also tried:

ALTER DATABASE AdventureWorks2016 SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

But got this error:

Msg 1455, Level 16, State 2, Line 27
The database mirroring service cannot be forced for database "AdventureWorks2016" because
the database is not in the correct state to become the principal database.

I checked the document and it seems there is no other commands to failover. Does that mean failover is impossible in this state and I have to change the safety level?

Answer 1

You are using (the deprecated feature) Mirroring, in "high performance" mode (aka, asynchronous mode). Per the docs:

The only form of role switching is forced service (with possible data loss).

Thus, you are correct that you must use FORCE_SERVICE_ALLOW_DATA_LOSS when failing over, or you must first switch to "high safety mode" (aka synchronous mode). From the documentation on that portion of the ALTER DATABASE mirroring commands:

FORCE_SERVICE_ALLOW_DATA_LOSS is available only on the mirror server and only under all the following conditions:

• The principal server is down.
• WITNESS is set to OFF or the witness is connected to the mirror server.

Note from the quote above that FORCE_SERVICE_ALLOW_DATA_LOSS must be run on the secondary mirror instance and not the primary.

Regarding failing back to the original server after forcing a failover in asynchronous mode, the docs continue:

Forcing service suspends the session, temporarily preserving all the data in the original principal database. Once the original principal is in service and able to communicate with the new principal server, the database administrator can resume service. When the session resumes, any unsent log records and the corresponding updates are lost.

I do not think that this portion of the docs make clear that failing back to the former primary is not always possible.

If your secondary mirror is not fully synchronized (and if it is in asynchronous "high performance" mode, one should assume that it is NEVER fully synchronized) when you force a failover, then you cannot fail back without fully reinitializing the other server from backup.

The reason is that when you are not synchronized--and there is a committed transaction on Server A that has not yet been sent to Server B at the time of failover, that "orphan" transaction will result in Server A and Server B having a fork where the data has diverged. SQL Server has no way of reconciling what to do with the committed data on Server A that never made it to Server B. Should the data be merged together? What if there is a conflict? Should the committed data on Server A be rolled back? SQL Server answers these by identifying that there was a fork, and refusing to resume synchronization.

You should switch to "high safety" (synchronous) mode for planned mirroring failovers.

If you run normally in "high performance" (asynchronous) mode, then you should momentarily switch to "high safety mode" (synchronous) then fail over, then you can switch back to "high performance" (asynchronous) mode.

Leaving the database in asynchronous "high performance" mode and forcing the failover is a task that should be used only when you are prepared for committed data to be lost, and are prepared to fully reinitialize mirroring by restoring a full backup to the former primary.