Indicator: Connection between a primary SQL database and its mirror database has been lost
Impact: Medium
In a clustering environment, database mirroring in SQL Server allows you to keep a copy, or mirror, of a SQL Server database on a standby server. Mirroring ensures that two separate copies of the data exist at all times, providing high availability and complete data redundancy. AimBetter monitor is reporting when a connection between the primary database and a mirror has been broken for longer than the designated time.
Default settings are:
| CRITICAL : | ||
| MEDIUM : | ||
| LOW : | 10 minutes | |
Expected behavior: There is no standard metric for this alert. Many normal activities may cause the connection between the two databases to break (for example, backup, virus scan) but unexpected delays need to be investigated, since they may be caused by problems with a key resource, such as storage or network bandwidth. Read more about the way the health of a cluster is monitored here.
Possible causes
All network bandwidth is consumed Priority: High
Recommended action :
Check what traffic occupies the bandwidth the most. Where possible, reschedule non-essential activity (e.g virus scans, backup etc.) to hours of lowest SQL demand.
Storage space is not available Priority: High
Recommended action :
Check available space – for more information, see our post about host disk space here.
Background
Database mirroring maintains two copies of a single database that must reside on different server instances of the SQL Server database engines that reside on computers in different locations. The relationship is known as a database mirroring session between these server instances.
One server instance, referred to as the principal server, serves the database to clients. The other instance, called the mirror server, acts as a hot or warm standby server.
The principal and mirror servers communicate and cooperate as partners in a database mirroring session where one partner performs the principal role, and the other partner performs the mirror role. The principal server’s copy of the database is the current principal database and the mirror server’s copy of the database is the current mirror database.
Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence.
It is important to distinguish between the two main modes of mirroring:
High-performance mode
The database mirroring session operates asynchronously and uses only the principal server and mirror server. The only form of role switching is forced service (with possible data loss).
High-safety mode
The database mirroring session operates synchronously and, optionally, uses a witness, as well as the principal server and mirror server.
