Archive for the ‘Database Mirroring’ Category

Monitoring Database Mirroring

January 12, 2015 Leave a comment

Couple of days back I was working for a client on creating a database mirroring monitor report to show how log has been sent to DR in last couple of hour. How much log is left to be sent based on the send rate and long will it.

Stored Procedure sp_dbmmonitorresults in msdb database gives all this detail for Principal database

sp_dbmmonitorresults database_name , rows_to_return , update_status


Exec msdb..sp_dbmmonitorresults ‘SGPData’,1,0


database_name :  Database Name

Specifies the quantity of rows returned:
0 = Last row
1 = Rows last two hours
2 = Rows last four hours
3 = Rows last eight hours
4 = Rows last day
5 = Rows last two days
6 = Last 100 rows
7 = Last 500 rows
8 = Last 1,000 rows
9 = Last 1,000,000 rows



Specifies that before returning results the procedure:

0 = Does not update the status for the database. The results are computed using just the last two rows, the age of which depends on when the status table was refreshed.

1 = Updates the status for the database by calling sp_dbmmonitorupdate before computing the results. However, if the status table has been updated within the previous 15 seconds, or the user is not a member of the sysadmin fixed server role, sp_dbmmonitorresults runs without updating the status.




Categories: Database Mirroring

Database Mirroring Sync type (Operating Mode)

November 14, 2014 Leave a comment
select DB_NAME(database_id) dbname,CASE mirroring_safety_level WHEN 0 THEN 'Unknown' WHEN 1 THEN'asynchronous' WHEN 2 THEN 'synchronous' END from sys.database_mirroring


Categories: Database Mirroring

Database Mirroring: Transaction Hardening behavior when Mirror Fails (Synchronous mode)

August 25, 2011 Leave a comment


If we configure Database mirroring for Synchronous mode where principal waits for an acknowledgement from the mirror server before settling a commit or rollback, what would happen if the mirror server fails?


When transaction safety is set to FULL, the database mirroring session runs in high-safety mode and operates synchronously after an initial synchronizing phase.

The mirror server synchronizes the mirror database with the principal database to achieve synchronous mirror operation session. When the session begins, the principal server begins sending its active log to the mirror server. The mirror server writes all of the incoming log records to disk as quickly as possible. As soon as all of the received log records have been written to disk, the databases are synchronized. As long as the partners remain in communication, the databases remain synchronized.

After synchronization finishes, every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction’s log to disk. Note the wait for this message increases the latency of the transaction.

The time required for synchronization depends essentially on how far the mirror database was behind the principal database at the start of the session (measured by the number of log records initially received from the principal server), the work load on the principal database, and the speed of the mirror system. After a session is synchronized, the hardened log that has yet to be redone on the mirror database remains in the redo queue.

As soon as the mirror database becomes synchronized, the state of both the copies of the database changes to SYNCHRONIZED.

Synchronous operation is maintained in the following manner:

  1. On receiving a transaction from a client, the principal server writes the log for the transaction to the transaction log.
  2. The principal server writes the transaction to the database and, concurrently, sends the log record to the mirror server. The principal server waits for an acknowledgement from the mirror server before confirming either of the following to the client: a transaction commit or a rollback.
  3. The mirror server hardens the log to disk and returns an acknowledgement to the principal server.
  4. On receiving the acknowledgement from the mirror server, the principal server sends a confirmation message to the client.

High-safety mode protects your data by requiring the data to be synchronized between two places. All the committed transactions are guaranteed to be written to disk on the mirror server.

High-Safety Mode without Automatic Failover

The following figure shows the configuration of high-safety mode without automatic failover. The configuration consists of only the two partners.

When the partners are connected and the database is already synchronized, manual failover is supported. If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data)( When mirror fails, it switches to Asynchronous mode until mirror comes back online and catches up, Once Principal and Mirror synchronized Mirroring mode switch back to Synchronous mode.). If the principal server is lost, the mirror is suspended, but service can be forced to the mirror server (with possible data loss).

High-Safety Mode with Automatic Failover

Automatic failover provides high availability by ensuring that the database is still served after the loss of one server. Automatic failover requires that the session possess a third server instance, the witness, which ideally resides on a third computer. The following figure shows the configuration of a high-safety mode session that supports automatic failover.

Unlike the two partners, the witness does not serve the database. The witness simply supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server.

When a witness is set, the session requires quorum—a relationship between at least two server instances that allows the database to be made available.

Automatic failover requires the following conditions:

  • The database is already synchronized.
  • The failure occurs while all three server instances are connected, and the witness and mirror server remain connected.

The loss of a partner has the following effect:

  • If the principal server becomes unavailable under the above conditions, automatic failover occurs. The mirror server switches to the role of principal, and it offers its database as the principal database.
  • If the principal server becomes unavailable when those conditions are not met, forcing service (with possible data loss) might be possible. For more information, see Forced Service (with Possible Data Loss).
  • If the only mirror server becomes unavailable, the principal and witness continue.

If the session loses its witness, quorum requires both partners. If either partner loses quorum, both partners lose quorum, and the database becomes unavailable until quorum is re-established. This quorum requirement makes sure that in the absence of a witness the database never runs exposed, that is without being mirrored.






Categories: Database Mirroring