The originating database and server have the role of principal, and the receiving database and server have the role of mirror.
The principal and mirror servers must be separate instances of SQL Server 2005.
Some important items to note about database mirroring:
• The principal database must be in the FULL recovery model. Log records that result from bulk-logged operations cannot be sent to the mirror database.
• The mirror database must be initialized from a restore of the principal database with NORECOVERY, followed by restores in sequence of principal transaction log backups.
• The mirror database must have the same name as the principal database.
• Because the mirror database is in a recovering state, it cannot be accessed directly. You can create database snapshots on the mirror to indirectly read the mirror database at a point in time.
Transaction Safety
If transaction safety is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror's log disk. When safety is set OFF, the principal does not wait for acknowledgment from the mirror, and so the principal and mirror may not be fully synchronized (that is, the mirror may not quite keep up with the principal).
Synchronous transfer guarantees that all transactions in the mirror database's transaction log will be synchronized with the principal database's transaction log, and so the transactions are considered safely transferred. You set safety to FULL using
ALTER DATABASE [dbname] SET SAFETY FULL;
When safety is set to OFF, the communication between the principal and the mirror is asynchronous. The principal server will not wait for an acknowledgment from the mirror that the mirror has hardened a block of transaction records. The mirror will attempt to keep up with the principal, by recording transactions as quickly as possible, but some transactions may be lost if the principal suddenly fails and you force the mirror into service.
to get a list of all endpoints along with Port numbers
Select * from sys.tcp_endpoints
To get info about the state of mirroring....includes the lsn for failover..should be same as that in the mirror for failover with out data loss.
select * from sys.database_mirroring
Manually force a failover
ALTER DATABASE demo SET PARTNER FAILOVER
at the principal
Check the mirror failover lsn before doing this so as to ensure there is no data loss.
High Availability Mode
It requires that you set safety to FULL and define a witness server as part of the database mirroring session.
The High Availability mode is best used where you have fast and very reliable communication paths between the servers and you require automatic failover for a single database.
High Protection
The High Protection operating mode also has transactional safety FULL, but has no witness server as part of the mirroring session. The principal database does not need to form a quorum to serve the database. In this mode only a manual failover is possible, because there is no witness to fill the tie-breaker role.
High Performance
In the High Performance operating mode, transactional safety is OFF, and the transfer of log records is asynchronous. The principal server does not wait for an acknowledgement from the mirror that all transaction log records have been recorded on the mirror.
Manual failover is not enabled for the High Performance mode. The only type of failover allowed is forced service failover, which is also a manual operation:
ALTER DATABASE dbname SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
SQL Express and the Workgroup Edition can be used as a witness server, but they cannot be used as a partner server in database mirroring.
Principal Server States
When safety is FULL, the normal operating state for the principal database is the SYNCHRONIZED state. When safety is OFF, the normal operating state for the principal database starts as the SYNCHRONZING state. Once the mirror has caught up, the state goes to SYNCHRONIZED and stays there regardless of how far behind it is.
• If safety is FULL, the principal database always starts off in the SYNCHRONIZING state, and transitions to the SYNCHRONIZED state when the principal and mirror transactions logs are synchronized.
• If safety is FULL and the principal server is disconnected from the witness server but can still process transactions, the database is exposed.
Principal Database states with a witness set and with safety FULL and safety OFF
Principal Database states with no witness set and with safety FULL and safety OFF
For both safety settings, if the session is paused or there are redo errors on the mirror, the principal enters the SUSPENDED state. If the mirror becomes unavailable, the principal will enter the DISCONNECTED state.
In the DISCONNECTED and SUSPENDED states:
• When a witness has been set, if the principal can form a quorum with the witness or mirror server, the principal database is considered exposed. That means the principal database is active with user connections and processing transactions. However, no log records are being sent to the mirror database, and if the principal should fail, the mirror will not have any of the transactions from the principal from the point the principal entered that state. Also, the principal's transaction log cannot be truncated, so the log file will be growing indefinitely.
• When a witness has been set, if the principal cannot form a quorum with another server, it cannot serve the database. All users will be disconnected and no new transactions will be processed.
• When safety is OFF, the principal database is considered exposed, because no transaction log records are being sent to the mirror.
Mirror Server Database States
Witness Server States
