Database mirroring maintains two copies of a single database that must reside on different server instances of the SQL Server database engines on computers in different locations. The relationship between these server instances is known as a database mirroring session.
One server instance, the principal server, serves the database to clients. The other, the mirror server, acts as a hot or warm standby server.
Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database in 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:
1- High-performance mode
The database mirroring session operates asynchronously, replicating data from the principal database to the mirror database in a way that does not require the primary database to wait for the mirror database to confirm that it has successfully received and written the changes. This method allows the primary database to continue processing transactions without waiting for the secondary database, improving performance and reducing latency for write operations. However, it comes with the trade-off of potentially having a slight lag between the primary and secondary databases.
2- High-safety mode
The database mirroring session operates synchronously, requiring the principal database to wait for confirmation from the mirror database that a transaction has been successfully written before considering the transaction complete. This ensures that both databases are always in sync, providing high data consistency and reliability.
In a clustering environment, SQL Server database mirroring allows you to keep a copy or mirror of a SQL Server database on a standby server.
Find out how you can save hours of work when investigating the root cause of this issue.
Symptoms :
The connection between a primary SQL database and its mirror database has been lost. In some cases, Slow SQL responses come from the failure of the database mirroring.
Impact: High
When the connection between the primary and mirror databases is lost, a recovery solution will not be available in the case of a shutdown and loss of data and backups in the primary server.
Expected behavior :
The connection between the primary and mirror servers must be maintained constantly to guarantee high availability and data redundancy.
Many normal activities, such as backups and virus scannings, may cause the connection between the two databases to run slower than usual, but unexpected delays must be investigated. They may be caused by problems with a key resource, such as storage or network bandwidth, and harm the mirroring mechanism.
Possible causes:
1- High Network Latency. Priority: Medium
Higher levels of latency are a by-product of the additional network traffic involved in mirroring. After enabling mirroring, the default thresholds for latency and usage should be adjusted accordingly.
There might be cases when all network bandwidth is consumed and not available for the mirror.
Problem identification:
Check current traffic, which applications use most of it, and if it happens while the mirror is disconnected.
- Using a network monitoring tool, you should check how much traffic flows through your network and which applications or devices use the most bandwidth. Take into account that most monitoring tools help pinpoint when a problem starts, with which you can’t compare time frames.
- Review your network logs to see if there are any unusual patterns. This might take considerable time.
- Cross these events with disconnected mirror time periods in order to see if they happen in parallel to higher latency.
- Monitor and analyze the data transfer load by doing an initiated test for that issue in order to identify the maximum data transfer rate possible using the network. You probably will need the help of an expert IT professional.
Recommended action :
Check what traffic occupies the bandwidth the most. Where possible, reschedule non-essential activity (e.g., virus scans, backup) to hours of lowest SQL demand.
Ideally, you should have a dedicated network between the servers that are involved in database mirroring. If the servers share a common network with other servers, the effective bandwidth that is available for mirroring communication will impact performance. It is highly recommended that you configure database mirroring in a high-bandwidth network. The network bandwidth is dictated by the log generation rate of your application. Lower bandwidth networks can adversely impact the performance of database mirroring.
2- Long SQL tasks. Priority: Medium
When a long-running query executes on the principal server, it can consume significant server resources, such as CPU, memory, and I/O. If it’s significant, it might cause the mirror to be disconnected.
If the long-running query of the mirror specifically takes too long, longer than the configured timeout value, it can cause a mirroring disconnection.
Problem identification:
Once identifying a disconnected mirror, try to identify long-running queries consuming high OS resources related to the mirror process and running long from the primary server.
- Track the database server activity: identify which SQL statements have high execution time and consume most of the resources. For SQL Server, you can use the built-in activity monitors or SQL Profiler. This mission might be complicated and take hours or days of work, requiring a highly skilled DBA.
- Enter the SQL Server Manager Studio. Access the Database Mirroring Monitor tool, which will provide real-time information about current mirroring status.
- Investigate the query code and execution plan, trying to look for missing indexes or excess subqueries. In order to get this data, you have to run the query live again from the management studio, which might increase the overload and the investigation time of this issue.
Recommended action :
Investigate and track the exact queries causing server overload or if the long-running queries are mirror processes, then do furthermore research to provide a true solution for this issue.
In order to lower the chances of mirror disconnect, make sure to optimize queries and resources regularly. For that, you can use monitoring tools.
3- Storage space is not available. Priority: High
When SQL mirroring is utilized, storage is needed for both the primary instance database backups and the standby copy of the database.
The storage is also needed for historical changes (transaction logs) that can be used for recovery and synchronization purposes.
Without adequate storage, mirroring is not a possibility. Therefore, maintaining multiple copies of the database and ensuring data integrity cannot be done.
Problem identification:
Identify large files or large-sized folders that are saved on the disk. Make sure you are looking at the drive of the mirroring.
- Go to File Explorer and review the drives’ current free space status.
- Use a performance monitor or any other system tool in order to identify large files. Take into account that you are limited to their current status.
- Run a full scan of the disk’s content in order to locate the cause of it. For that, you need a specific desktop application, such as Treesize. On one of the scans, consider focusing on several file types such as .tmp or dump application files, .bak files, and data files.
- When finding the cause, try to figure out why this exact file has increased and how you can prevent it from happening again. Without proper events history, it might be hard to do.
Recommended action :
Plan adequate storage capacity and monitor its usage over time. Make sure to separate the mirror storage from other sources utilizing storage, whether it’s database, system, or application files.
For more information, see our article about host disk space.
4- Oversized log. Priority: Medium
SQL imposes no absolute limits on file growth. In cases when there are high levels of user activity, it might cause transaction log growth. If the transaction log becomes full on the principal server, it can lead to mirroring disruptions. A possible situation is that it started growing too rapidly, causing uncontrolled pressure on both the principal and mirror servers.
Log Send Queue, which is the log cache of the mirror, stores log records. If the transaction log of a database in the principal server is oversized, it might cause it to run out of space, resulting in a disconnected mirror.
Problem identification:
Identify oversized log files of the database of the mirroring at the same time when the mirror is disconnected.
- Identify the oversized log files. You should use a script(write T-SQL query) or search for it manually in DB properties(object explorer in SSMS).
- Check the SQL Server error logs for any messages or warnings related to log file growth.
- Set up SQL Server Agent alerts to notify you about overgrown log file size. A skilled DBA might do this task.
- Check if the events of the growing transaction logs are related to the database of the mirroring and happen at the same time when there are problems with the mirroring.
Recommended action :
Make sure that transaction log backups are done to databases with the required recovery model (for example, full recovery model).
Investigate long-running queries or open transactions that might cause log files to overgrow and how to improve its performance and reduce chances for that.
You should consider implementing an automatic job that shrinks database logs successfully and safely if an adequate solution to prevent log overgrow is not found.
