DATABASE MIRROR

CloudClusteringMS-SQLSQL

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 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.

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.

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, then you lose the option for high availability and complete data redundancy. In the case of a shutdown and loss of data and backups in the primary server, a solution of recovery won’t be available.

Expected behavior :

Disconnect situations must be minimized in order to maintain high availability.
Many normal activities may cause the connection between the two databases to run slower than usual (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.

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.

Hands-on approach
Get the answer in just seconds!
Hands-on approach
  1. 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.
  2. Review your network logs to see if there are any unusual patterns. This might take considerable time.
  3. Cross these events with disconnected mirror time periods in order to see if they happen in parallel to higher latency.
  4. 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.
Get the answer in just seconds!

AimBetter raises an immediate alert once there is a network problem. If the mirror is disconnected as well, you will see notifications at the same panel and time ranges.

Further, with AimBetter, you can easily investigate what happened next to the event.

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.

Hands-on approach
Get the answer in just seconds!
Hands-on approach
  1. 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.
  2. Enter the SQL Server Manager Studio. Access the Database Mirroring Monitor tool, which will provide real-time information about current mirroring status.
  3. 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.
Get the answer in just seconds!

With AimBetter, all data is provided in one panel, both live and history, including recommendations for indexes, notes about non-optimal query plans, or irregular OS resource status notes while the query is running.

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.

Hands-on approach
Get the answer in just seconds!
Hands-on approach
  1. Go to File Explorer and review the drives’ current free space status.
  2. 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.
  3. 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.
  4. 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.
Get the answer in just seconds!

AimBetter pinpoints the root cause as soon as a low disk space issue is identified, providing the history of system changes on all OS resources. For network paths, our logs view will provide data when storage space is not enough.

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.

Hands-on approach
Get the answer in just seconds!
Hands-on approach
  1. 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).
  2. Check the SQL Server error logs for any messages or warnings related to log file growth.
  3. Set up SQL Server Agent alerts to notify you about overgrown log file size. A skilled DBA might do this task.
  4. 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.
Get the answer in just seconds!

Once the database’s log file is oversized, while the mirror is disconnected, AimBetter will notify you about this issue.

AimBetter provides all required data in one screen, database mirroring statuses, queries, list of database properties, including log file size, log file drive, and more.

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.

    Learn more how you can solve IT systems performance issues faster.



    Share with friends:

    Testimonials:

    FEATURED POSTS

    Menu
    Skip to content