Database states for each server are kept during the database mirroring session, recorded on each partner server, and reported by the sys.database_mirroring catalog view. The mirroring_state column returns a number for the state, and the mirroring_state_desc column returns the descriptive name for the state. State information about the witness is also reported from the same catalog view.
In addition to the states reported for each database, there are three phrases that are useful in describing the servers and databases involved in database mirroring.
1. Exposed - The data on the principal is exposed when it is processing transactions but no log data is being sent to the mirror. When a principal database is exposed, it 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 the exposed state. Also, the principal's transaction log cannot be truncated, so the log file will be growing indefinitely.
2. Cannot serve the database - When a principal server does not allow any user connections to the database and any transactions to be processed. When a witness has been set, if the principal server cannot form a quorum with another server, it will stop serving the database. It will not allow user connections and transactions on the principal database, and will disconnect all current users. As soon as it can form a quorum again, it will return to serving the database.
3. Isolated - A server is isolated when it cannot contact any of the other servers in the database mirroring session, and they cannot contact it. A server may be operational but communication lines are down between it and both other servers in the database mirroring session. In that case, we'll call the server isolated. If a witness has been set, then, if the principal server becomes isolated, it will no longer be able to serve the database, because there is no server in the session with which it can form a quorum.
When safety is FULL, the principal first enters the SYNCHRONIZING state and as soon as it synchronizes with the mirror, both partners enter the SYNCHRONIZED state. When safety is OFF, the partner databases start with the SYNCHRONIZING state. Once the mirror has caught up, the state goes to SYNCHRONIZED and stays there regardless of how far behind it is. 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.
* 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
Comments