Safety FULL with Witness :

Well the answer for this ‘depends on the mode in which mirroring is configured’. If mirroring is configured in High Availability mode (Full safety) then we don’t need to worry about failover as the mirror server will form a quorum with witness and will initiate an automatic failover. The safety level can be set using the below command,

Safety FULL without Witness :

This scenario provides high safety, but automatic failover is not allowed. This mode is called as High Protection mode. In the event of failure of the principal, the database service becomes unavailable. You need manual intervention to make the database service available. You must break the mirroring session and then recover the mirror database.

For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

Safety OFF :

In the event of failure of the principal, the database service becomes unavailable. You can perform a force service to make the database service available on the mirror. However, since the safety level is OFF, it is possible that there were transactions that didnt make it to the mirror at the time of the failure of the principal. These transactions will be lost. Therefore, manual failover with safety OFF involves acknowledging the possibility of data loss.

For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

Once the database on Server_A becomes operational, it automatically assumes the role of the mirror. However, the mirroring session remains SUSPENDED, and you will need to manually RESUME the mirroring session.

Login Failures while connecting to new principal database after failover ?

After configuring database mirroring in SQL Server 2005 and performing failover, the original mirror database now becomes the new principal database. We might have even created the same login (as in principal) in original mirror server prior to failover. But after failover if we try to connect or if the application tries to connect, the following error will be returned,
Cannot open database requested by the login. The login failed.
In that case we need to map the login to the user in the database using the procedure sp_change_users_login after which the application or the user will be able to successfully connect to the new principal database.

This problem occurs because the SIDs the SQL Server logins on each server do not match. Although the names for the logins are the same, the login is resolved via the SID. This is not a problem with Windows/Domain user/group logins because the SIDs for these logins are created based on the domain SID for the user/group, and hence will be the same for the same given user/group no matter what SQL Server the user/group is added to.

In order to make the sp_change_users_login synchronization step unnecessary, we need to create the SQL Server logins on the mirror server not only with the same name, but also with the same SID as on the principal server. This can be accomplished by using the SID specification in the ‘CREATE LOGIN statement when creating the logins on the mirror server. Here is an example where we create a the same login in mirror server as the one in principal server.

To retrieve the SID for each login from the principal server query the sys.sql_logins catalog view.

You can also create all the logins with same SID in mirror server from principal server using sp_helprevlogin procedure. Consider this step as pre-requisite for configuring db mirroring.