# SQL Server DBA

::  ::  ::  ::  ::

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,

 1 2 ALTER DATABASE dbname SET SAFETY FULL ALTER DATABASE dbname SET SAFETY OFF

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:

 1 2 ALTER DATABASE dbname SET PARTNER OFF RESTORE DATABASE dbname WITH RECOVERY

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:

 1 ALTER DATABASE dbname SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

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,
 1 CREATE LOGIN WITH PASSWORD ='password',SID ='sid for same login on principal server'